Category / Section
How to implement nested template marker in XlsIO?
1 min read
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.
