How to use template marker with collection object?
This article explains how to use template markers with collection objects using XlsIO in C#/VB.NET.
How to use template markers 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 to the names of the marker fields in the template Excel document and fills the data.
When an object contains another object collection as a member, 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 an object, you will need to follow the steps below.
Steps to use horizontal argument:
- Create a workbook template with markers in it. Here, FirmDetails is the marker variable that refers to a class object, followed by its properties separated by a 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 screenshot below shows how the markers are applied in the workbook template.
- Create template marker processor.
// Create template marker processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
- Add a variable name that is equal to the class object specified in step 1.
// Add marker variable
marker.AddVariable("FirmDetails", GetEmployeeDetails());
- Apply markers.
// Apply markers
marker.ApplyMarkers();
To learn more about template markers with various import options using XlsIO, please refer to the documentation.
The following C#/VB.NET complete code snippet shows how to use template markers with collection objects.
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 Industries";
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 Industries"
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 screenshot below shows the output document generated using template markers.
Conclusion
I hope you enjoyed learning about how to use template markers with collection objects using XlsIO in C#/VB.NET.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums or feedback portal. We are always happy to assist you!
