We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Syncfusion Feedback


Trusted by the world’s leading companies

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#

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

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#

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

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#

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

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#

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

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;
  }
}

There are 3 import data options, called default, merge, and repeat, and 2 group options, called expand group and collapse group.


Use cases

Besides this simple data binding using the Syncfusion .NET Excel Library, developers can also:




Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion is proud to hold the following industry awards.

Scroll up icon