Template Markers | .NET Excel library

Overview

Template markers are a variant of the template-based approach to importing data. The difference is that the end user places special markers or placeholders in the template spreadsheet that get replaced along with the data during runtime. This performs similarly to the mail merge feature in Word.

The Syncfusion .NET Excel Library (XlsIO) allows the generation of various real-world reports like invoices and payroll from formatted Excel templates using template markers. This process is faster, and the generated document can be saved as an Excel document, PDF, HTML, and more.

The Excel mail merge works seamlessly on these platforms: .NET MAUI, ASP.NET, ASP.NET MVC, Blazor, Windows Forms, UWP, WinUI, WPF, and Xamarin.

.NET template markers.

Syncfusion Excel Library allows users to bind template markers from different data sources like arrays, data tables, collection objects and nested collection objects.

How to bind template markers from an array in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Load the Excel document into IWorkbook.
Initialize ITemplateMarkersProcessor by calling the CreateTemplateMarkersProcessor() method of IWorkbook.
Initialize the arrays with data.
Add the marker variables using the AddVariable() method of *ITemplatesMarkerProcessor.
Call the ApplyMarkers() method of *ITemplateMarkersProcessor to bind the data.
Save the workbook object to a MemoryStream object.

Here is an example of how to bind template markers from an array in C# using the Syncfusion .NET Excel Library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
   IApplication application = excelEngine.Excel;
   application.DefaultVersion = ExcelVersion.Xlsx;
   using(FileStream excelStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
   {
      IWorkbook workbook = application.Workbooks.Open(excelStream);

      //Create Template Marker Processor.
      ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

      //Insert Array Horizontally.
      string[] names = new string[] { "Mickey", "Donald", "Tom", "Jerry" };
      string[] descriptions = new string[] { "Mouse", "Duck", "Cat", "Mouse" };

      //Add collections to the marker variables where the name should match with input template.
      marker.AddVariable("Names", names);
      marker.AddVariable("Descriptions", descriptions);

      //Process the markers in the template.
      marker.ApplyMarkers();

      //Save the document into a stream.
      using (MemoryStream outputStream = new MemoryStream()) 
      { 
         workbook.Save(outputStream); 
      }
   }
}

How to bind template markers from a data table in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Load the Excel document into IWorkbook.
Initialize ITemplateMarkersProcessor by calling the CreateTemplateMarkersProcessor() method of IWorkbook.
Initialize the DataTable and add rows and columns with data.
Add the marker variables using the AddVariable() method of *ITemplatesMarkerProcessor.
Call the ApplyMarkers() method of *ITemplateMarkersProcessor to bind the data.
Save the workbook object to a MemoryStream object.

Here is an example of how to bind a template marker from a data table in C# using the Syncfusion .NET Excel Library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
   IApplication application = excelEngine.Excel;
   application.DefaultVersion = ExcelVersion.Xlsx;
   using(FileStream excelStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
   {
      IWorkbook workbook = application.Workbooks.Open(excelStream);

      //Create Template Marker Processor.
      ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

      //Initialize data table with data.
      DataTable reports = new DataTable();
      reports.Columns.Add("SalesPerson");
      reports.Columns.Add("FromDate", typeof(DateTime));
      reports.Columns.Add("ToDate", typeof(DateTime));
      reports.Rows.Add("Andy Bernard", new DateTime(2014, 09, 08), new DateTime(2014, 09, 11));
      reports.Rows.Add("Jim Halpert", new DateTime(2014, 09, 11), new DateTime(2014, 09, 15));
      reports.Rows.Add("Karen Fillippelli", new DateTime(2014, 09, 15), new DateTime(2014, 09, 20));
      reports.Rows.Add("Phyllis Lapin", new DateTime(2014, 09, 21), new DateTime(2014, 09, 25));
      reports.Rows.Add("Stanley Hudson", new DateTime(2014, 09, 26), new DateTime(2014, 09, 30));

      //Add collection to the marker variables where the name should match with input template.  
      //Detects number format in DateTable values
      marker.AddVariable("Reports", reports, VariableTypeAction.DetectNumberFormat);

      //Save the document into a stream.
      using (MemoryStream outputStream = new MemoryStream()) 
      { 
         workbook.Save(outputStream); 
      }
   }
}

How to bind template markers from collection objects in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Load the Excel document into IWorkbook.
Initialize ITemplateMarkersProcessor by calling the CreateTemplateMarkersProcessor() method of IWorkbook.
Get the data into the collection object.
Add the marker variables using the AddVariable() method of *ITemplatesMarkerProcessor.
Call the ApplyMarkers() method of *ITemplateMarkersProcessor to bind the data.
Save the workbook object to a MemoryStream object.

Here is an example of how to bind template markers from a collection object in C# using the Syncfusion .NET Excel Library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
   IApplication application = excelEngine.Excel;
   application.DefaultVersion = ExcelVersion.Xlsx;
   using(FileStream excelStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
   {
      IWorkbook workbook = application.Workbooks.Open(excelStream);

      //Create Template Marker Processor.
      ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

      //Get the data into collection object.
      IList<Report> reports = GetSalesReports();

      //Add collections to the marker variables where the name should match with input template.
      marker.AddVariable("Reports", reports);

      //Save the document into a stream.
      using (MemoryStream outputStream = new MemoryStream()) 
      { 
         workbook.Save(outputStream); 
      }
   }
}

//Gets a list of sales reports.
private static List<Report> GetSalesReports()
{
  List<Report> reports = new List<Report>();
  reports.Add(new Report("Andy Bernard", "45000", "58000", 29 , "Andy.jpg"));
  reports.Add(new Report("Jim Halpert", "34000", "65000", 91, "Jim.png"));
  reports.Add(new Report("Karen Fillippelli", "75000", "64000", -14, "Karen.jpg"));
  reports.Add(new Report("Phyllis Lapin", "56500", "33600", -40, "Phyllis.png"));
  reports.Add(new Report("Stanley Hudson", "46500", "52000", 12, "Stanley.jpg"));
  return reports;
}

//Sales report.
public class Report
{
  public string SalesPerson { get; set; }
  public string SalesJanJun { get; set; }
  public string SalesJulDec { get; set; }
  public int Change { get; set; }
  public byte[] Image { get; set; }

  public Report(string name, string janToJun, string julToDec, int change, string imagePath)
  {
	SalesPerson = name;
	SalesJanJun = janToJun;
	SalesJulDec = julToDec;
	Change = change;
	Image = File.ReadAllBytes(imagePath);
  }
}

How to bind template markers from nested collection objects in C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Load the Excel document into IWorkbook.
Initialize ITemplateMarkersProcessor by calling the CreateTemplateMarkersProcessor() method of IWorkbook.
Get the data from the nested collection.
Add the marker variables using the AddVariable() method of *ITemplatesMarkerProcessor.
Call the ApplyMarkers() method of *ITemplateMarkersProcessor to bind the data.
Save the workbook object to a MemoryStream object.

Here is an example of how to bind template markers from nested collection objects in C# using the Syncfusion .NET Excel Library.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
   IApplication application = excelEngine.Excel;
   application.DefaultVersion = ExcelVersion.Xlsx;
   using(FileStream excelStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
   {
      IWorkbook workbook = application.Workbooks.Open(excelStream);

      //Create Template Marker Processor.
      ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

      //Add collection to the marker variables where the name should match with input template.
      marker.AddVariable("Customer", GetSalesReports());

      //Save the document into a stream.
      using (MemoryStream outputStream = new MemoryStream()) 
      { 
         workbook.Save(outputStream); 
      }
   }
}

//Gets a list of sales reports.
public static List<Customer> GetSalesReports()
{
  List<Customer> reports = new List<Customer>();

  List<Order> orders = new List<Order>();
  orders.Add(new Order(1408, 451.75));
  orders.Add(new Order(1278, 340.00));
  orders.Add(new Order(1123, 290.50));

  Customer c1 = new Customer(002107, "Andy Bernard", 45);
  c1.Orders = orders;
  Customer c2 = new Customer(011564, "Jim Halpert", 34);
  c2.Orders = orders;
  Customer c3 = new Customer(002097, "Karen Fillippelli", 35);
  c3.Orders = orders;
  Customer c4 = new Customer(001846, "Phyllis Lapin", 37);
  c4.Orders = orders;
  Customer c5 = new Customer(012167, "Stanley Hudson", 41);
  c5.Orders = orders;

  reports.Add(c1);
  reports.Add(c2);
  reports.Add(c3);
  reports.Add(c4);
  reports.Add(c5);

  return reports;
}

//Customer details.
public partial class Customer
{
  public int Id { get; set; }
  public string Name { get; set; }
  public int Age { get; set; }
  public IList<Order> Orders { get; set; }
  public Customer(int id, string name, int age)
  {
    Id = id;
    Name = name;
    Age = age;
  }
}
//Order details.
public partial class Order
{
  public int Order_Id { get; set; }
  public double Price { get; set; }

  public Order(int id, double price)
  {
    Order_Id = id;
    Price = price;
  }
}