Articles in this section
Category / Section

How to use template marker with collection object?

4 mins read

This article explains how to use template marker with collection object using XlsIO in C#/VB.NET.

How to use template marker with collections?

A template marker is a special marker symbol created in an Excel template that appends multiple records from a data source into a worksheet. This marker automatically maps the column name in the data source and names of the marker fields in the template Excel document and fills the data.

When an object contains another object collection as a member, then it can be imported by the template marker by detecting the enumerable content in every phase of the collection.

 

To import data from a collection in a object, you will need to follow the below steps.

Steps to use horizontal argument

  1. Create a workbook template with markers in it. Here, FirmDetails is the marker variable referred to a class object, followed by its properties separated by dot (.).

 

//Add markers 
worksheet["A2"].Text = "%FirmDetails.Name";
worksheet["B2"].Text = "%FirmDetails.Location";
worksheet["C3"].Text = "%FirmDetails.EmployeesList.Id";
worksheet["D3"].Text = "%FirmDetails.EmployeesList.Name";
worksheet["E3"].Text = "%FirmDetails.EmployeesList.Age";

 

The below screenshot shows how the markers are applied in workbook template.

Markers applied in workbook template
                       

 

  1. Create template marker processor.

 

//Create template marker processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

 

  1. Add a variable name that is equal to the class object specified in step 1.

 

//Add marker variable
marker.AddVariable("FirmDetails", GetEmployeeDetails());

 

  1. Apply markers.

 

//Apply markers
marker.ApplyMarkers();

 

To know more about template marker with various import options using XlsIO, please refer the documentation.

 

Download Complete Sample

 

The following C#/VB.NET complete code snippet shows how to use template marker with collection object.

using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;
 
namespace CollectionTemplateMarker
{
    class Employee
    {
        private string m_name;
        private int m_id;
        private int m_age;
 
        public string Name
        {
            get
            {
                return m_name;
            }
 
            set
            {
                m_name = value;
            }
        }
        public int Id
        {
            get
            {
                return m_id;
            }
 
            set
            {
                m_id = value;
            }
        }
        public int Age
        {
            get
            {
                return m_age;
            }
 
            set
            {
                m_age = value;
            }
        }
    }
    class FirmDetails
    {
        private List<Employee> m_empList;
        private string m_name;
        private string m_location;
 
        public string Name
        {
            get
            {
                return m_name;
            }
 
            set
            {
                m_name = value;
            }
        }
 
        public string Location
        {
            get
            {
                return m_location;
            }
            set
            {
                m_location = value;
            }
        }
 
        public List<Employee> EmployeesList
        {
            get
            {
                if (m_empList == null)
                    EmployeesList = new List<Employee>();
                return m_empList;
            }
            set
            {
                m_empList = value;
            }
        }
    }
    class Program
    {
        public static List<Employee> GetEmployeeDetails()
        {
            List<Employee> employeeList = new List<Employee>();
 
            Employee emp = new Employee();
            emp.Name = "Andy Bernard";
            emp.Id = 1011;
            emp.Age = 35;
            employeeList.Add(emp);
 
            emp = new Employee();
            emp.Name = "Karen Fillippelli";
            emp.Id = 1012;
            emp.Age = 26;
            employeeList.Add(emp);
 
            emp = new Employee();
            emp.Name = "Patricia Mckenna";
            emp.Id = 1013;
            emp.Age = 28;
            employeeList.Add(emp);
 
            return employeeList;
        }        
        public static FirmDetails GetFirmDetails()
        {
            FirmDetails firm = new FirmDetails();
            firm.Name = "Hi-Tech Industires";
            firm.Location = "Canada";
            firm.EmployeesList = GetEmployeeDetails();
           
            return firm;
        }
        static void Main(string[] args)
        {
            //Instantiate the spreadsheet creation engine
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];
 
                worksheet["A1:C1"].CellStyle.Font.Bold = true;
                worksheet["C2:E2"].CellStyle.Font.Bold = true;
 
                //Data
                worksheet["A1"].Text = "FirmName";
                worksheet["B1"].Text = "Location";
                worksheet["C1"].Text = "Employee List";
                worksheet["C1:E1"].Merge();
                worksheet["C1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
 
                worksheet["C2"].Text = "Id";
                worksheet["D2"].Text = "Name";
                worksheet["E2"].Text = "Age";
 
                //Add markers 
                worksheet["A2"].Text = "%FirmDetails.Name";
                worksheet["B2"].Text = "%FirmDetails.Location";
                worksheet["C3"].Text = "%FirmDetails.EmployeesList.Id";
                worksheet["D3"].Text = "%FirmDetails.EmployeesList.Name";
                worksheet["E3"].Text = "%FirmDetails.EmployeesList.Age";
 
 
                //Create Template Marker Processor
                ITemplateMarkersProcessor markers = workbook.CreateTemplateMarkersProcessor();
 
                //Add marker variable
                markers.AddVariable("FirmDetails", GetFirmDetails());
 
                //Process the markers in the template
                markers.ApplyMarkers();
 
                //Saving the workbook
                Stream stream = File.Create("Output.xlsx");
                worksheet.UsedRange.AutofitColumns();
                workbook.SaveAs(stream);
 
            }
        }
    }
}
 

 

Imports Syncfusion.XlsIO
Imports System.Collections.Generic
Imports System.IO
 
Namespace CollectionTemplateMarker
 
    Class Employee
 
        Private m_name As String
        Private m_id As Integer
        Private m_age As Integer
 
        Public Property Name As String
            Get
                Return m_name
            End Get
 
            Set(ByVal value As String)
                m_name = value
            End Set
        End Property
 
        Public Property Id As Integer
            Get
                Return m_id
            End Get
 
            Set(ByVal value As Integer)
                m_id = value
            End Set
        End Property
 
        Public Property Age As Integer
            Get
                Return m_age
            End Get
 
            Set(ByVal value As Integer)
                m_age = value
            End Set
        End Property
    End Class
 
    Class FirmDetails
 
        Private m_empList As List(Of Employee)
 
        Private m_name As String
 
        Private m_location As String
 
        Public Property Name As String
            Get
                Return m_name
            End Get
 
            Set(ByVal value As String)
                m_name = value
            End Set
        End Property
 
        Public Property Location As String
            Get
                Return m_location
            End Get
 
            Set(ByVal value As String)
                m_location = value
            End Set
        End Property
 
        Public Property EmployeesList As List(Of Employee)
            Get
                If m_empList Is Nothing Then EmployeesList = New List(Of Employee)()
                Return m_empList
            End Get
 
            Set(ByVal value As List(Of Employee))
                m_empList = value
            End Set
        End Property
    End Class
 
    Class Program
 
        Public Shared Function GetEmployeeDetails() As List(Of Employee)
 
            Dim employeeList As List(Of Employee) = New List(Of Employee)()
            Dim emp As Employee = New Employee()
            emp.Id = 1011
            emp.Name = "Andy Bernard"
            emp.Age = 35
            employeeList.Add(emp)
 
            emp = New Employee()
            emp.Id = 1024
            emp.Name = "Karen Fillippelli"
            emp.Age = 26
            employeeList.Add(emp)
 
            emp = New Employee()
            emp.Id = 1024
            emp.Name = "Patricia Mckenna"
            emp.Age = 26
            employeeList.Add(emp)
 
            Return employeeList
        End Function
 
        Public Shared Function GetFirmDetails() As FirmDetails
            Dim firm As FirmDetails = New FirmDetails()
            firm.Name = "Hi-Tech Industires"
            firm.Location = "Canada"
            firm.EmployeesList = GetEmployeeDetails()
            Return firm
        End Function
 
        Public Shared Sub Main(ByVal args As String())
 
            'Instantiate the spreadsheet creation engine
            Using excelEngine As ExcelEngine = New ExcelEngine()
                Dim application As IApplication = excelEngine.Excel
                Dim workbook As IWorkbook = application.Workbooks.Create(1)
                Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
                'Add header text
                worksheet("A1:C1").CellStyle.Font.Bold = True
                worksheet("C2:E2").CellStyle.Font.Bold = True
                worksheet("A1").Text = "FirmName"
                worksheet("B1").Text = "Location"
                worksheet("C1").Text = "Employee List"
                worksheet("C1:E1").Merge()
                worksheet("C1").HorizontalAlignment = ExcelHAlign.HAlignCenter
                worksheet("C2").Text = "Id"
                worksheet("D2").Text = "Name"
                worksheet("E2").Text = "Age"
 
                'Add markers
                worksheet("A2").Text = "%FirmDetails.Name"
                worksheet("B2").Text = "%FirmDetails.Location"
                worksheet("C3").Text = "%FirmDetails.EmployeesList.Id"
                worksheet("D3").Text = "%FirmDetails.EmployeesList.Name"
                worksheet("E3").Text = "%FirmDetails.EmployeesList.Age"
 
                'Create Template marker processor
                Dim template As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
                'Add marker variable
                template.AddVariable("FirmDetails", GetFirmDetails())
                'Apply Markers
                template.ApplyMarkers()
 
                'Save and close the workbook
                Dim stream As Stream = File.Create("Output.xlsx")
                worksheet.UsedRange.AutofitColumns()
                workbook.SaveAs(stream)
            End Using
        End Sub
    End Class
End Namespace

 

The below screenshot shows the output document generated using template markers.

Template Markers

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied