HTML Table in the mail body with partial merge Dynamically

Help Studio
, ,
1

Hi Team,

I have a requirement to dynamically merge data if 2 or more employee report to same manager and if they are enrolled in same set of course, I have attached screenshot of sample table, please check and suggest HTML some code snippet which can be used for this type of requests, Please note this will be sent in body of mail along with other text content

The image is a table showing a list of employees along with their respective managers, HR contacts, and the courses they are enrolled in. (Captioned by AI)
The image is a table showing a list of employees along with their respective managers, HR contacts, and the courses they are enrolled in. (Captioned by AI)415×258 3.71 KB

2

Hi @Deeipauk

How will your input be like. Please share the screenshot.

Regards

3

Hi, input is in form of excel file

4

Hi @Deeipauk

Can you share the screenshot of input excel file.

Regards

5

Here is the C# code i got from gtp, this code converts datatable into HTML table which you can add to your mail body.

You can use invoke code activity with C# language and pass your datatable as input.

Use code from Step 2 in Invoke code

using System;
using System.Data;
using System.Linq;
using System.Text;

class Program
{
    static void Main()
    {
        // Step 1: Create and populate the DataTable
        DataTable dt = new DataTable();
        dt.Columns.Add("Employee");
        dt.Columns.Add("Manager");
        dt.Columns.Add("HR");
        dt.Columns.Add("Course");

        dt.Rows.Add("Ram", "Swami", "Lata", "C1");
        dt.Rows.Add("Ram", "Swami", "Lata", "C2");
        dt.Rows.Add("Ram", "Swami", "Lata", "C3");
        dt.Rows.Add("Sam", "Swami", "John", "C1");
        dt.Rows.Add("Sam", "Swami", "John", "C2");
        dt.Rows.Add("Sam", "Swami", "John", "C3");

        // Step 2: Sort for proper grouping
        DataView view = dt.DefaultView;
        view.Sort = "Manager, Employee, HR";
        dt = view.ToTable();

        // Step 3: Generate HTML with rowspan
        StringBuilder sb = new StringBuilder();
        sb.AppendLine("<table border='1'>");
        sb.AppendLine("<tr><th>Employee</th><th>Manager</th><th>HR</th><th>Course</th></tr>");

        int rowCount = dt.Rows.Count;
        string lastEmployee = "", lastHR = "";
        bool managerWritten = false;

        for (int i = 0; i < rowCount; i++)
        {
            string employee = dt.Rows[i]["Employee"].ToString();
            string manager = dt.Rows[i]["Manager"].ToString();
            string hr = dt.Rows[i]["HR"].ToString();
            string course = dt.Rows[i]["Course"].ToString();

            sb.Append("<tr>");

            // Write Employee only on first occurrence
            if (i == 0 || employee != lastEmployee)
            {
                int employeeRowspan = dt.Select($"Employee = '{employee}'").Length;
                sb.Append($"<td rowspan='{employeeRowspan}'>{employee}</td>");
                lastEmployee = employee;
            }

            // Write Manager only once across entire table
            if (!managerWritten)
            {
                int managerRowspan = dt.Select($"Manager = '{manager}'").Length;
                sb.Append($"<td rowspan='{managerRowspan}'>{manager}</td>");
                managerWritten = true;
            }

            // Write HR only on first occurrence within same employee and HR group
            if (i == 0 || hr != lastHR || employee != lastEmployee)
            {
                int hrRowspan = dt.Select($"Employee = '{employee}' AND HR = '{hr}'").Length;
                sb.Append($"<td rowspan='{hrRowspan}'>{hr}</td>");
                lastHR = hr;
            }

            sb.Append($"<td>{course}</td>");
            sb.AppendLine("</tr>");
        }

        sb.AppendLine("</table>");

        // Output the HTML
        Console.WriteLine(sb.ToString());
    }
}
6

Hi @Deeipauk

Hoping this is the input excel

The image is a table listing employees alongside their respective managers, HR representatives, and courses they are enrolled in. (Captioned by AI)
The image is a table listing employees alongside their respective managers, HR representatives, and courses they are enrolled in. (Captioned by AI)766×239 4.3 KB

Use this invoke code

html  = "<table border=2><tr><td>" + 
String.Join("</td><td>", YourDT.Columns.Cast(Of System.Data.DataColumn).Select(Function(x) x.ColumnName).ToArray()) + 
"</td></tr>"

Dim grouped = From row In YourDT.AsEnumerable()
              Group row By 
              Emp = row("Employee").ToString().Trim(), 
              Mgr = row("Manager").ToString().Trim(), 
              HR = row("HR").ToString().Trim() 
              Into GroupRows = Group
              Select Emp, Mgr, HR, Courses = GroupRows.Select(Function(r) r("Course").ToString().Trim()).ToList()

For Each g In grouped
    For i As Integer = 0 To g.Courses.Count - 1
        html &= "<tr>"
        If i = 0 Then
            html &= "<td rowspan='" & g.Courses.Count & "'>" & g.Emp & "</td>" &
                    "<td rowspan='" & g.Courses.Count & "'>" & g.Mgr & "</td>" &
                    "<td rowspan='" & g.Courses.Count & "'>" & g.HR & "</td>"
        End If
        html &= "<td>" & g.Courses(i) & "</td></tr>"
    Next
Next

html &= "</table>"

The image shows a table with columns labeled "Name," "Direction," "Type," and "Value," containing two rows of data with inputs "YourDT" as a DataTable and "html" as a String. (Captioned by AI)
The image shows a table with columns labeled "Name," "Direction," "Type," and "Value," containing two rows of data with inputs "YourDT" as a DataTable and "html" as a String. (Captioned by AI)872×121 6.06 KB

Output:

The image shows an HTML table with several rows and columns, detailing employee names, their managers, HR representatives, and courses, with some cells spanning multiple rows. (Captioned by AI)
The image shows an HTML table with several rows and columns, detailing employee names, their managers, HR representatives, and courses, with some cells spanning multiple rows. (Captioned by AI)670×183 9.99 KB

Do mark it as a solution if it helps!
Happy automation :innocent:

7

i added screenshot in my post, these fields from input file

8

Thanks sanjay, I created the test data as per columns I have, pls check below

The image shows a transformation of a table into a more concise format by merging repeated values in initial columns, resulting in a visually consolidated output. (Captioned by AI)
The image shows a transformation of a table into a more concise format by merging repeated values in initial columns, resulting in a visually consolidated output. (Captioned by AI)257×515 42.8 KB

9

@Deeipauk
Did you directly try the input excel directly with the code i suggested it will directly create the html String, which you can add the html string in body of mail activity with bodyAsHtml Property Ticked

10

@Deeipauk

Change the column names accordingly in the code for the above one the change in code is

html = "<table border=2><tr><td>" + 
    String.Join("</td><td>", YourDT.Columns.Cast(Of System.Data.DataColumn).Select(Function(x) x.ColumnName).ToArray()) + 
    "</td></tr>"

Dim grouped = From row In YourDT.AsEnumerable()
              Group row By 
              Col1 = row("Col1").ToString().Trim(), 
              Col2 = row("Col2").ToString().Trim(), 
              Col3 = row("Col3").ToString().Trim() 
              Into GroupRows = Group
              Select Col1, Col2, Col3, Items = GroupRows.Select(Function(r) New With {
                  Key .Col4 = r("Col4").ToString().Trim(), 
                  Key .Col5 = r("Col5").ToString().Trim()
              }).ToList()

For Each g In grouped
    ' Group Col4 items by Col5 (date), so we can rowspan by date too
    Dim byDateGroup = g.Items.GroupBy(Function(x) x.Col5).ToList()

    For i As Integer = 0 To byDateGroup.Count - 1
        Dim dateGroup = byDateGroup(i)
        For j As Integer = 0 To dateGroup.Count() - 1
            html &= "<tr>"
            If i = 0 AndAlso j = 0 Then
                html &= "<td rowspan='" & g.Items.Count & "'>" & g.Col1 & "</td>" &
                        "<td rowspan='" & g.Items.Count & "'>" & g.Col2 & "</td>" &
                        "<td rowspan='" & g.Items.Count & "'>" & g.Col3 & "</td>"
            End If
            html &= "<td>" & dateGroup(j).Col4 & "</td>"
            If j = 0 Then
                html &= "<td rowspan='" & dateGroup.Count() & "'>" & dateGroup.Key & "</td>"
            End If
            html &= "</tr>"
        Next
    Next
Next

html &= "</table>"

Output Stringvar :

The image shows a snippet of HTML code for a table with rows and columns containing data like "Prem," "Director," and various dates. (Captioned by AI)
The image shows a snippet of HTML code for a table with rows and columns containing data like "Prem," "Director," and various dates. (Captioned by AI)640×256 13.5 KB

Use this variable in the body of mail activity

Do mark it as a solution if it helps you :innocent:

11

Hi Sanjay,
Im writing this behalf of Deepauk,
input type is data table as mentioned in screenshots and expecting output also in data table.
could you please provide us any LINQ query to achieve this ?

12

Hi @ramgct25

Do you want to merge the cells or which is the input datatable?
Can you create a different thread with proper requirement

13

Thanks Sanjay…This solution is working when courses column at end, but we need to have courses column in middle of datatable (Example-Emp,Mgr,HR,Courses,Duedate,MD).
Please rewrite your logic?

14

@ramgct25
Check on this

Dim html As String = "<table border=2><tr><td>" + 
    String.Join("</td><td>", YourDT.Columns.Cast(Of System.Data.DataColumn).Select(Function(x) x.ColumnName).ToArray()) + 
    "</td></tr>"

' Grouping based on fixed columns
Dim grouped = From row In YourDT.AsEnumerable()
              Group row By 
              Emp = row("Emp").ToString().Trim(), 
              Mgr = row("Mgr").ToString().Trim(), 
              HR = row("HR").ToString().Trim() 
              Into GroupRows = Group
              Select Emp, Mgr, HR, CourseDetails = GroupRows.Select(Function(r) New With {
                  Key .Course = r("Course").ToString().Trim(),
                  Key .DueDate = r("DueDate").ToString().Trim(),
                  Key .MD = r("MD").ToString().Trim()
              }).ToList()

' Loop through each group
For Each g In grouped
    For i As Integer = 0 To g.CourseDetails.Count - 1
        html &= "<tr>"

        ' Add merged cells only for the first row in the group
        If i = 0 Then
            html &= "<td rowspan='" & g.CourseDetails.Count & "'>" & g.Emp & "</td>" &
                    "<td rowspan='" & g.CourseDetails.Count & "'>" & g.Mgr & "</td>" &
                    "<td rowspan='" & g.CourseDetails.Count & "'>" & g.HR & "</td>"
        End If

        ' Add course and related columns
        html &= "<td>" & g.CourseDetails(i).Course & "</td>" &
                "<td>" & g.CourseDetails(i).DueDate & "</td>" &
                "<td>" & g.CourseDetails(i).MD & "</td></tr>"
    Next
Next

html &= "</table>"
15

Hi Sanjay,
im using Invoke code activity, In_arugment as datatable and out_Arugument as String.
Getting this error …No Complied Code to run, error BC30734 ‘html’ is already declared as a parameter of this method. at line 0

16

@ramgct25

html = "<table border=2><tr><td>" + 
    String.Join("</td><td>", YourDT.Columns.Cast(Of System.Data.DataColumn).Select(Function(x) x.ColumnName).ToArray()) + 
    "</td></tr>"

' Grouping based on fixed columns
Dim grouped = From row In YourDT.AsEnumerable()
              Group row By 
              Emp = row("Emp").ToString().Trim(), 
              Mgr = row("Mgr").ToString().Trim(), 
              HR = row("HR").ToString().Trim() 
              Into GroupRows = Group
              Select Emp, Mgr, HR, CourseDetails = GroupRows.Select(Function(r) New With {
                  Key .Course = r("Course").ToString().Trim(),
                  Key .DueDate = r("DueDate").ToString().Trim(),
                  Key .MD = r("MD").ToString().Trim()
              }).ToList()

' Loop through each group
For Each g In grouped
    For i As Integer = 0 To g.CourseDetails.Count - 1
        html &= "<tr>"

        ' Add merged cells only for the first row in the group
        If i = 0 Then
            html &= "<td rowspan='" & g.CourseDetails.Count & "'>" & g.Emp & "</td>" &
                    "<td rowspan='" & g.CourseDetails.Count & "'>" & g.Mgr & "</td>" &
                    "<td rowspan='" & g.CourseDetails.Count & "'>" & g.HR & "</td>"
        End If

        ' Add course and related columns
        html &= "<td>" & g.CourseDetails(i).Course & "</td>" &
                "<td>" & g.CourseDetails(i).DueDate & "</td>" &
                "<td>" & g.CourseDetails(i).MD & "</td></tr>"
    Next
Next

html &= "</table>"
17

Sanjay,
Geeting output as shown in picture,

OP_AT
OP_AT485×335 5.42 KB

1.Due date and MD column not grouping.
please check your logic.

Expecting to get below like this

OP_AT2
OP_AT2362×384 10.2 KB