How to import data table using template markers with grouping?

3 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using this library, you can import/export data to a predefined Excel template with grouping options.

But grouping can be applied to the nested collection objects alone. It is not possible to apply if importing from a data table.

However, this requirement can be achieved when you use IRange.Group(ExcelGroupBy groupBy) method to group the rows in the worksheet after the template marker is processed.

Steps to import data table using template markers and group data programmatically:

Step 1: Create a new C# console application project.

Create a new C# console application

Step 2: Install Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from the NuGet.org.

Install NuGet package

Step 3: Include the following namespaces in the Program.cs file.

using Syncfusion.XlsIO;
using System;
using System.Data;

VB.NET

Imports Syncfusion.XlsIO
Imports System
Imports System.Data

Step 4: Add the following code snippet to import data table using template markers and group data.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    worksheet.Range["A1"].Text = "Dosage";
    worksheet.Range["B1"].Text = "Drug";
    worksheet.Range["C1"].Text = "Patient";
    worksheet.Range["D1"].Text = "Date";
 
    worksheet.Range["A3"].Text = "%Markers.Dosage;jump:R[2]C";
    worksheet.Range["B3"].Text = "%Markers.Drug;jump:R[2]C";
    worksheet.Range["C3"].Text = "%Markers.Patient;jump:R[2]C";
    worksheet.Range["D3"].Text = "%Markers.Date;jump:R[2]C";
 
    // Initialize the DataTable
    DataTable table = GetDataTable();
 
    // Create Template Marker Processor
    ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
 
    // Bind data table to the marker variables
    marker.AddVariable("Markers", table, VariableTypeAction.None);
 
    // Process the markers
    marker.ApplyMarkers();
 
    // Group Rows
    worksheet.Range["A1:A2"].Group(ExcelGroupBy.ByRows);
    worksheet.Range["A3:A11"].Group(ExcelGroupBy.ByRows, true);
 
    worksheet.AutofitColumn(4);
 
    workbook.SaveAs("Output.xlsx");
}

VB.NET

Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2016
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    ' Insert column names
    worksheet.Range("A1").Text = "Dosage"
    worksheet.Range("B1").Text = "Drug"
    worksheet.Range("C1").Text = "Patient"
    worksheet.Range("D1").Text = "Date"
 
    ' Insert markers
    worksheet.Range("A3").Text = "%Markers.Dosage;jump:R[2]C"
    worksheet.Range("B3").Text = "%Markers.Drug;jump:R[2]C"
    worksheet.Range("C3").Text = "%Markers.Patient;jump:R[2]C"
    worksheet.Range("D3").Text = "%Markers.Date;jump:R[2]C"
 
    ' Initialize the DataTable
    Dim table As DataTable = GetDataTable()
 
    ' Create Template Marker Processor
    Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
 
    ' Bind data table to the marker variables
    marker.AddVariable("Markers", table, VariableTypeAction.None)
 
    ' Process the markers
    marker.ApplyMarkers()
 
    ' Group rows
    worksheet.Range("A1:A2").Group(ExcelGroupBy.ByRows)
    worksheet.Range("A3:A11").Group(ExcelGroupBy.ByRows, True)
 
    worksheet.AutofitColumn(4)
 
    workbook.SaveAs("Output.xlsx")
End Using

Step 5: Load the DataTable using the following simple static method.

private static DataTable GetDataTable()
{
    // Create a DataTable with four columns
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));
 
    // Add five DataRows
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
 
    return table;
}

VB.NET

Private Function GetDataTable() As DataTable
 
    ' Create a DataTable with four columns
    Dim table As DataTable = New DataTable
    table.Columns.Add("Dosage", GetType(System.Int32))
    table.Columns.Add("Drug", GetType(System.String))
    table.Columns.Add("Patient", GetType(System.String))
    table.Columns.Add("Date", GetType(DateTime))
 
    ' Add five DataRows
    table.Rows.Add(25, "Indocin", "David", DateTime.Now)
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now)
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now)
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now)
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now)
 
    Return table
End Function

A complete working example to import data table using template markers and group data can be downloaded from Import DataTable using template markers with grouping.zip.

By executing the program, you will get the output Excel file as below.

Output Excel file after importing data table using template markers and grouping the data

Output Excel file

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through formulas, adding charts in worksheets or workbooks, organizing and analyzing data through tables and pivot tables, appending multiple records to a worksheet using template markers, and most importantly, PDF and image conversions, etc., with code examples.

Refer here to explore the rich set of Syncfusion Essential XlsIO features.

An online sample link to generate Excel file.

How to import data table using template markers with grouping?

Steps to import data table using template markers and group data programmatically:

See Also:

Access denied