How to implement nested template marker in XlsIO?
This article explains how to implement nested template marker using C#/VB.NET in XlsIO.
What is a nested template marker?
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 a data source contains another data source as a member, it is considered as a nested data. This nested data can be imported by template marker in XlsIO by mapping the two data sources into a single data source or object.
To implement nested template marker, you need to follow the below steps.
Steps to implement nested template marker:
1. The template marker in the Excel file needs to be given according to the newly mapped object.
Syntax
%<MappedObject>.Property
The below screenshot shows the template markers in the input file.
2. Create a template marker processor for the workbook.
//Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
3. Add template marker variable using AddVariable(string strName, object variable) method. Here, “list” is the data to be imported and “Result” is the reference to the data.
//Add marker variable marker.AddVariable("Result", list);
4.Apply the markers.
//Process the markers in the template marker.ApplyMarkers();
To know more about template marker in XlsIO, please refer the documentation.
The following C#/VB.NET complete code snippet shows how to implement nested template marker.
using Syncfusion.XlsIO; using System.Collections.Generic; using System.IO; using System.Reflection; namespace NestedTemplateMarker { public class ModuleDetails { private string m_moduleName; private int m_net; private int m_netx; private int m_nety; private int m_netz; private int m_cont; private int m_contPer; private int m_gross; private int m_grossx; private int m_grossy; private int m_grossz; public string ModuleName { get { return m_moduleName; } set { m_moduleName = value; } } public int Net { get { return m_net; } set { m_net = value; } } public int NetX { get { return m_netx; } set { m_netx = value; } } public int NetY { get { return m_nety; } set { m_nety = value; } } public int NetZ { get { return m_netz; } set { m_netz = value; } } public int Cont { get { return m_cont; } set { m_cont = value; } } public int ContPer { get { return m_contPer; } set { m_contPer = value; } } public int Gross { get { return m_gross; } set { m_gross = value; } } public int GrossX { get { return m_grossx; } set { m_grossx = value; } } public int GrossY { get { return m_grossy; } set { m_grossy = value; } } public int GrossZ { get { return m_grossz; } set { m_grossz = value; } } public ModuleDetails(string module, int net, int netx, int nety, int netz, int cont, int contPer, int gross, int grossx, int grossy, int grossz) { ModuleName = module; Net = net; NetX = netx; NetY = nety; NetZ = netz; Cont = cont; ContPer = contPer; Gross = gross; GrossX = grossx; GrossY = grossy; GrossZ = grossz; } } public class Phase { private string m_phaseName; private List<ModuleDetails> m_moduleList; public string PhaseName { get { return m_phaseName; } set { m_phaseName = value; } } public List<ModuleDetails> ModuleList { get { if (m_moduleList == null) m_moduleList = new List<ModuleDetails>(); return m_moduleList; } set { m_moduleList = value; } } public Phase(string name, List<ModuleDetails> mDetails) { m_phaseName = name; m_moduleList = mDetails; } } public class MappedResult { private ModuleDetails mapModule; private string m_phaseName; public MappedResult(string phaseName, ModuleDetails m) { this.m_phaseName = phaseName; this.mapModule = m; } public string PhaseName { get { return m_phaseName; } } public string ModuleName { get { return mapModule.ModuleName; } } public int Net { get { return mapModule.Net; } } public int NetX { get { return mapModule.NetX; } } public int NetY { get { return mapModule.NetY; } } public int NetZ { get { return mapModule.NetZ; } } public int Cont { get { return mapModule.Cont; } } public int ContPer { get { return mapModule.ContPer; } } public int Gross { get { return mapModule.Gross; } } public int GrossX { get { return mapModule.GrossX; } } public int GrossY { get { return mapModule.GrossY; } } public int GrossZ { get { return mapModule.GrossZ; } } } class Program { public static List<Phase> GetPhaseDetails() { List<Phase> phaseList = new List<Phase>(); Phase phase = new Phase("Phase1", GetModule1Details()); phaseList.Add(phase); phase = new Phase("Phase2", GetModule2Details()); phaseList.Add(phase); phase = new Phase("Phase3", GetModule3Details()); phaseList.Add(phase); return phaseList; } public static List<ModuleDetails> GetModule1Details() { List<ModuleDetails> modules = new List<ModuleDetails>(); ModuleDetails module = new ModuleDetails("Module1", 1, 1, 1, 1, 100, 100, 1, 1, 1, 1); modules.Add(module); module = new ModuleDetails("Module2", 2, 2, 2, 2, 100, 100, 2, 2, 2, 2); modules.Add(module); module = new ModuleDetails("Module3", 3, 3, 3, 3, 100, 100, 3, 3, 3, 3); modules.Add(module); return modules; } public static List<ModuleDetails> GetModule2Details() { List<ModuleDetails> modules = new List<ModuleDetails>(); ModuleDetails module = new ModuleDetails("Module1", 4, 4, 4, 4, 100, 100, 4, 4, 4, 4); modules.Add(module); module = new ModuleDetails("Module2", 5, 5, 5, 5, 100, 100, 5, 5, 5, 5); modules.Add(module); module = new ModuleDetails("Module3", 6, 6, 6, 6, 100, 100, 6, 6, 6, 6); modules.Add(module); return modules; } public static List<ModuleDetails> GetModule3Details() { List<ModuleDetails> modules = new List<ModuleDetails>(); ModuleDetails module = new ModuleDetails("Module1", 7, 7, 7, 7, 100, 100, 7, 7, 7, 7); modules.Add(module); module = new ModuleDetails("Module2", 8, 8, 8, 8, 100, 100, 8, 8, 8, 8); modules.Add(module); module = new ModuleDetails("Module3", 9, 9, 9, 9, 100, 100, 9, 9, 9, 9); modules.Add(module); return modules; } static void Main(string[] args) { //Instantiate the spreadsheet creation engine using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the excel application object IApplication application = excelEngine.Excel; //The workbook is opened IWorkbook workbook; //Open existing workbook with data entered Assembly assembly = typeof(Program).GetTypeInfo().Assembly; Stream fileStream = assembly.GetManifestResourceStream("NestedTemplateMarker.PhaseTemplate.xlsx"); workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic); //The first worksheet object in the worksheets collection is accessed IWorksheet worksheet = workbook.Worksheets[0]; List<Phase> phaseList = GetPhaseDetails(); List<MappedResult> list = new List<MappedResult>(); foreach (Phase phase in phaseList) { foreach (ModuleDetails module in phase.ModuleList) { list.Add(new MappedResult(phase.PhaseName, module)); } } //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); //Add marker variable marker.AddVariable("Result", list); //Process the markers in the template marker.ApplyMarkers(); //Saving and closing the workbook Stream stream = File.Create("Output.xlsx"); workbook.SaveAs(stream); } } } }
Imports Syncfusion.XlsIO Imports System.Collections.Generic Imports System.IO Imports System.Reflection Namespace NestedTemplateMarker Public Class ModuleDetails Private m_moduleName As String Private m_net As Integer Private m_netx As Integer Private m_nety As Integer Private m_netz As Integer Private m_cont As Integer Private m_contPer As Integer Private m_gross As Integer Private m_grossx As Integer Private m_grossy As Integer Private m_grossz As Integer Public Property ModuleName As String Get Return m_moduleName End Get Set(ByVal value As String) m_moduleName = value End Set End Property Public Property Net As Integer Get Return m_net End Get Set(ByVal value As Integer) m_net = value End Set End Property Public Property NetX As Integer Get Return m_netx End Get Set(ByVal value As Integer) m_netx = value End Set End Property Public Property NetY As Integer Get Return m_nety End Get Set(ByVal value As Integer) m_nety = value End Set End Property Public Property NetZ As Integer Get Return m_netz End Get Set(ByVal value As Integer) m_netz = value End Set End Property Public Property Cont As Integer Get Return m_cont End Get Set(ByVal value As Integer) m_cont = value End Set End Property Public Property ContPer As Integer Get Return m_contPer End Get Set(ByVal value As Integer) m_contPer = value End Set End Property Public Property Gross As Integer Get Return m_gross End Get Set(ByVal value As Integer) m_gross = value End Set End Property Public Property GrossX As Integer Get Return m_grossx End Get Set(ByVal value As Integer) m_grossx = value End Set End Property Public Property GrossY As Integer Get Return m_grossy End Get Set(ByVal value As Integer) m_grossy = value End Set End Property Public Property GrossZ As Integer Get Return m_grossz End Get Set(ByVal value As Integer) m_grossz = value End Set End Property Public Sub New(ByVal moduleName As String, ByVal net As Integer, ByVal netx As Integer, ByVal nety As Integer, ByVal netz As Integer, ByVal cont As Integer, ByVal contPer As Integer, ByVal gross As Integer, ByVal grossx As Integer, ByVal grossy As Integer, ByVal grossz As Integer) Me.ModuleName = moduleName Me.Net = net Me.NetX = netx Me.NetY = nety Me.NetZ = netz Me.Cont = cont Me.ContPer = contPer Me.Gross = gross Me.GrossX = grossx Me.GrossY = grossy Me.GrossZ = grossz End Sub End Class Public Class Phase Private m_phaseName As String Private m_moduleList As List(Of ModuleDetails) Public Property PhaseName As String Get Return m_phaseName End Get Set(ByVal value As String) m_phaseName = value End Set End Property Public Property ModuleList As List(Of ModuleDetails) Get If m_moduleList Is Nothing Then m_moduleList = New List(Of ModuleDetails)() Return m_moduleList End Get Set(ByVal value As List(Of ModuleDetails)) m_moduleList = value End Set End Property Public Sub New(ByVal name As String, ByVal mDetails As List(Of ModuleDetails)) m_phaseName = name m_moduleList = mDetails End Sub End Class Public Class MappedResult Private mapModule As ModuleDetails Private m_phaseName As String Public Sub New(ByVal phaseName As String, ByVal m As ModuleDetails) Me.m_phaseName = phaseName Me.mapModule = m End Sub Public ReadOnly Property PhaseName As String Get Return m_phaseName End Get End Property Public ReadOnly Property ModuleName As String Get Return mapModule.ModuleName End Get End Property Public ReadOnly Property Net As Integer Get Return mapModule.Net End Get End Property Public ReadOnly Property NetX As Integer Get Return mapModule.NetX End Get End Property Public ReadOnly Property NetY As Integer Get Return mapModule.NetY End Get End Property Public ReadOnly Property NetZ As Integer Get Return mapModule.NetZ End Get End Property Public ReadOnly Property Cont As Integer Get Return mapModule.Cont End Get End Property Public ReadOnly Property ContPer As Integer Get Return mapModule.ContPer End Get End Property Public ReadOnly Property Gross As Integer Get Return mapModule.Gross End Get End Property Public ReadOnly Property GrossX As Integer Get Return mapModule.GrossX End Get End Property Public ReadOnly Property GrossY As Integer Get Return mapModule.GrossY End Get End Property Public ReadOnly Property GrossZ As Integer Get Return mapModule.GrossZ End Get End Property End Class Class Program Public Shared Function GetModule1Details() As List(Of ModuleDetails) Dim modules As List(Of ModuleDetails) = New List(Of ModuleDetails)() Dim [module] As ModuleDetails = New ModuleDetails("Module1", 1, 1, 1, 1, 100, 100, 1, 1, 1, 1) modules.Add([module]) [module] = New ModuleDetails("Module2", 2, 2, 2, 2, 100, 100, 2, 2, 2, 2) modules.Add([module]) [module] = New ModuleDetails("Module3", 3, 3, 3, 3, 100, 100, 3, 3, 3, 3) modules.Add([module]) Return modules End Function Public Shared Function GetModule2Details() As List(Of ModuleDetails) Dim modules As List(Of ModuleDetails) = New List(Of ModuleDetails)() Dim [module] As ModuleDetails = New ModuleDetails("Module1", 4, 4, 4, 4, 100, 100, 4, 4, 4, 4) modules.Add([module]) [module] = New ModuleDetails("Module2", 5, 5, 5, 5, 100, 100, 5, 5, 5, 5) modules.Add([module]) [module] = New ModuleDetails("Module3", 6, 6, 6, 6, 100, 100, 6, 6, 6, 6) modules.Add([module]) Return modules End Function Public Shared Function GetModule3Details() As List(Of ModuleDetails) Dim modules As List(Of ModuleDetails) = New List(Of ModuleDetails)() Dim [module] As ModuleDetails = New ModuleDetails("Module1", 7, 7, 7, 7, 100, 100, 7, 7, 7, 7) modules.Add([module]) [module] = New ModuleDetails("Module2", 8, 8, 8, 8, 100, 100, 8, 8, 8, 8) modules.Add([module]) [module] = New ModuleDetails("Module3", 9, 9, 9, 9, 100, 100, 9, 9, 9, 9) modules.Add([module]) Return modules End Function Public Shared Function GetPhaseDetails() As List(Of Phase) Dim phaseList As List(Of Phase) = New List(Of Phase)() Dim phase As Phase = New Phase("Phase1", GetModule1Details()) phaseList.Add(phase) phase = New Phase("Phase2", GetModule2Details()) phaseList.Add(phase) phase = New Phase("Phase3", GetModule3Details()) phaseList.Add(phase) Return phaseList End Function Public Shared Sub Main(ByVal args As String()) 'Instantiate the spreadsheet creation engine Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate the excel application object Dim application As IApplication = excelEngine.Excel 'Open existing workbook Dim workbook As IWorkbook Dim assembly As Assembly = GetType(Program).GetTypeInfo().Assembly Dim fileStream As Stream = assembly.GetManifestResourceStream("ChartSample.PhaseTemplate.xlsx") workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic) 'The first worksheet object in the worksheets collection is accessed Dim worksheet As IWorksheet = workbook.Worksheets(0) Dim phaseList As List(Of Phase) = GetPhaseDetails() Dim list As List(Of MappedResult) = New List(Of MappedResult)() For Each phase As Phase In phaseList For Each [module] As ModuleDetails In phase.ModuleList list.Add(New MappedResult(phase.PhaseName, [module])) Next Next 'Create Template Marker Processor Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor() 'Add marker variable marker.AddVariable("Result", list) 'Process the markers in the template marker.ApplyMarkers() 'Saving and closing 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 of the Excel file generated by the nested template marker in XlsIO.