Converting Datatable to HTML Table

RoboDan · September 10, 2019, 1:47pm

Hi Everyone!

I would like to email out a Datatable variable via the SMTP Activity. I need to make it pretty with HTML. I have done this before with static Datatables where I know how many rows they will have and can just all each cell needed. However, this Datatable’s number of rows changes frequently, so I need to be able to send it as a whole.

Let me know what you guys think!
Thanks!

AshwinS2 · September 10, 2019, 2:16pm

Hi @RoboDan

Use Subject property
“

”+DtTable.Rows(“ColumnName”).ToString+“

”
“

”+DtTable.Rows(“Column Name”).ToString+"

Thanks
Ashwin S

ClaytonM · September 10, 2019, 2:23pm

Search through the Community Activity feed. Pretty sure someone has an activity package that can do this to some extent. However, from my experience their activity doesn’t have very many features.

If you want to create something yourself, you can simply take the DataTable and convert it to a comma-delimited string, and replace the commas with html tags with the beginning of each new row having all the formatting values. Although, I make that sound easier than it is, cause it does take a little thought. I have built someone like this which you can create an entire email with images, text, or tables, and you can pretty much color and set any border style or font style you wish. - I can’t upload that workflow at this time unfortunately

Regards.

ClaytonM · September 10, 2019, 2:40pm

@RoboDan, I’ll go ahead and post a snippet, but it’s a giant block of code, since there are so many features you can have for a table. It will be difficult to understand maybe but might strike some ideas, that is if you want to create something yourself.

If(out_BodyInHTML.Trim<>"",out_BodyInHTML.Trim+System.Environment.NewLine,"") _
+String.Format(htmlTable, String.Format(htmlTableCell, item("Hdr Border Horizontal Width").ToString.Trim, item("Hdr Border Horizontal Style").ToString.Trim, item("Hdr Border Horizontal Color").ToString.Trim, item("Hdr Border Vertical Width").ToString.Trim, item("Hdr Border Vertical Style").ToString.Trim, item("Hdr Border Vertical Color").ToString.Trim) + _
String.Join(System.Environment.NewLine(0),item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Take(1).Select(Function(x) x.Remove(x.LastIndexOf("|"),1).Insert(x.LastIndexOf("|"), _
    "</td>"+String.Format(htmlTableCellLastCol, item("Hdr Border Horizontal Width").ToString.Trim, item("Hdr Border Horizontal Style").ToString.Trim, item("Hdr Border Horizontal Color").ToString.Trim) ) )).Trim.Replace("|", _
	"</td>"+String.Format(htmlTableCell, item("Hdr Border Horizontal Width").ToString.Trim, item("Hdr Border Horizontal Style").ToString.Trim, item("Hdr Border Horizontal Color").ToString.Trim, item("Hdr Border Vertical Width").ToString.Trim, item("Hdr Border Vertical Style").ToString.Trim, item("Hdr Border Vertical Color").ToString.Trim) ).Replace(System.Environment.NewLine, _
	"</td></tr>") + "</td></tr>", If( item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Count>2, String.Format(htmlTableRow, item("BG Color").ToString.Trim) + String.Format(htmlTableCell, item("Border Horizontal Width").ToString.Trim, item("Border Horizontal Style").ToString.Trim, item("Border Horizontal Color").ToString.Trim, item("Border Vertical Width").ToString.Trim, item("Border Vertical Style").ToString.Trim, item("Border Vertical Color").ToString.Trim) + _
String.Join(System.Environment.NewLine(0), item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Skip(1).Take(item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Count-2).Select(Function(x) x.Remove(x.LastIndexOf("|"),1).Insert(x.LastIndexOf("|"), _
    "</td>"+String.Format(htmlTableCellLastCol, item("Border Horizontal Width").ToString.Trim, item("Border Horizontal Style").ToString.Trim, item("Border Horizontal Color").ToString.Trim) ) )).Trim.Replace("|", _
	"</td>"+String.Format(htmlTableCell, item("Border Horizontal Width").ToString.Trim, item("Border Horizontal Style").ToString.Trim, item("Border Horizontal Color").ToString.Trim, item("Border Vertical Width").ToString.Trim, item("Border Vertical Style").ToString.Trim, item("Border Vertical Color").ToString.Trim) ).Replace(System.Environment.NewLine, _
	"</td></tr>"+String.Format(htmlTableRow, item("BG Color").ToString.Trim) + String.Format(htmlTableCell, item("Border Horizontal Width").ToString.Trim, item("Border Horizontal Style").ToString.Trim, item("Border Horizontal Color").ToString.Trim, item("Border Vertical Width").ToString.Trim, item("Border Vertical Style").ToString.Trim, item("Border Vertical Color").ToString.Trim) ), ""), _
    "</td></tr><tr style=""background-color:#FFFFFF;text-align:; "">"+String.Format(htmlTableCellLastRow, item("Border Vertical Width").ToString.Trim, item("Border Vertical Style").ToString.Trim, item("Border Vertical Color").ToString.Trim)+item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Last.Remove(item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Last.LastIndexOf("|"),1).Insert(item("Value").ToString.Trim.Split(System.Environment.NewLine(0)).Last.LastIndexOf("|"),"</td><td style="""">").Trim.Replace("|", _
        "</td>"+String.Format(htmlTableCellLastRow, item("Border Vertical Width").ToString.Trim, item("Border Vertical Style").ToString.Trim, item("Border Vertical Color").ToString.Trim) )+"</td></tr>", _
item("Table Width").ToString.Trim, item("Font Color").ToString.Trim, item("BG Color").ToString.Trim, item("Text Align").ToString.Trim, item("Font Size").ToString.Trim, item("Font Name").ToString.Trim, _
If(item("Bold").ToString.Trim.ToUpper="TRUE", "bold","normal"), If(item("Italic").ToString.Trim.ToUpper="TRUE", "italic","normal"), If(item("Underline").ToString.Trim.ToUpper="TRUE", "underline","normal"), _
item("Top Border Width").ToString.Trim, item("Top Border Style").ToString.Trim, item("Top Border Color").ToString.Trim, item("Bottom Border Width").ToString.Trim, item("Bottom Border Style").ToString.Trim, item("Bottom Border Color").ToString.Trim, _
item("Left Border Width").ToString.Trim, item("Left Border Style").ToString.Trim, item("Left Border Color").ToString.Trim, item("Right Border Width").ToString.Trim, item("Right Border Style").ToString.Trim, item("Right Border Color").ToString.Trim, _
item("Hdr Font Color").ToString.Trim, item("Hdr BG Color").ToString.Trim, item("Hdr Text Align").ToString.Trim, item("Hdr Font Size").ToString.Trim, item("Hdr Font Name").ToString.Trim, _
If(item("Hdr Bold").ToString.Trim.ToUpper="TRUE", "bold","normal"), If(item("Hdr Italic").ToString.Trim.ToUpper="TRUE", "italic","normal"), If(item("Hdr Underline").ToString.Trim.ToUpper="TRUE", "underline","normal") )

All the features are stored in a datatable at the beginning so I can loop through them. The formatting settings come in as Arrays in Arguments, that way I can set multiple tables so each one has its own features.

(those are not all the arguments, by the way)

Here is part of the beginning that adds all the settings to the datatable before it generates the html:

Regards.

RoboDan · September 10, 2019, 2:47pm

Wow thank you so much for the insight! I started building my own process but I got inspiration from your post to make it more dynamic and applicable for other developers. I’ll definitely be creating this and adding it to my company’s library.

Thanks for the reply

Topic		Replies	Views
DataTable to HTML using Assign Activity Tutorials datatable , excel , faq	9	3627	October 6, 2023
How to send my table in mail Studio studio , question , activities_panel	5	899	September 3, 2023
Converting Data table to HTML table (Table Format) Help mail , activities	3	4647	October 27, 2019
Data Table to HTML Activity Studio studio , question , properties_panel	9	1548	April 5, 2023
Sending an Excel Table with SMTP Mail Message Activity Help	4	1739	September 22, 2019

Converting Datatable to HTML Table

”+DtTable.Rows(“ColumnName”).ToString+“

Related topics