Category / Section
Use copyrange argument in template markers using XlsIO.
1 min read
This article explains how to use copyrange argument in template markers using XlsIO.
What is copyrange argument?
The argument copyrange specified in template marker copies the specified cells after each cell import. Cell reference addresses can be relative or absolute.
For example, when a known data is needed to be copied in every row or column for each data imported, the copy range argument will be helpful to do that operation.
Syntax
%<MarkerVariable>.<Property>;copyrange:R[Index]C[Index] |
NOTE: The copy range will begin from next row of the R[index].
Steps to use copyrange argument
- Create a workbook template with markers in it. Here, Employee is the marker variable referred to a class object, followed by its properties separated by dot (.). The cells “D4” and “E4” contains the known data which needs to be copied for every Employee object.
//Adding markers dynamically with the argument, 'copyrange' worksheet["A4"].Text = "%Employee.Name;copyrange:R[0]C4:R[0]C5"; worksheet["B4"].Text = "%Employee.Id"; worksheet["C4"].Text = "%Employee.Age"; worksheet["D4"].Text = "Software Developer"; worksheet["E4"].Text = "Canada";
The below screenshot shows how the markers are applied in 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("Employee", GetEmployeeDetails());
- Apply markers.
//Apply markers marker.ApplyMarkers();
The following C#/VB.NET complete code snippet shows how to use template marker with copyrange argument in XlsIO.
using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;
namespace TemplateMarker
{
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 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 = "Jim Halpert";
emp.Id = 1012;
emp.Age = 26;
employeeList.Add(emp);
emp = new Employee();
emp.Name = "Karen Fillippelli";
emp.Id = 1013;
emp.Age = 28;
employeeList.Add(emp);
return employeeList;
}
public 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];
//Adding header text
worksheet["A1"].Text = "\"Copyrange\" Argument";
worksheet["A3"].Text = "Name";
worksheet["B3"].Text = "Id";
worksheet["C3"].Text = "Age";
worksheet["D3"].Text = "Designation";
worksheet["E3"].Text = "Country";
worksheet["A3:E3"].CellStyle.Font.Bold = true;
//Adding markers dynamically with the argument, 'copyrange'
worksheet["A4"].Text = "%Employee.Name;copyrange:R[0]C4:R[0]C5";
worksheet["B4"].Text = "%Employee.Id";
worksheet["C4"].Text = "%Employee.Age";
worksheet["D4"].Text = "Software Developer";
worksheet["E4"].Text = "Canada";
//Create template marker processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
//Add marker variable
marker.AddVariable("Employee", GetEmployeeDetails());
//Apply markers
marker.ApplyMarkers();
//Save and close 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 TemplateMarker
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 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.Name = "Andy Bernard"
emp.Id = 1011
emp.Age = 35
employeeList.Add(emp)
emp = New Employee()
emp.Name = "Jim Halpert"
emp.Id = 1012
emp.Age = 26
employeeList.Add(emp)
emp = New Employee()
emp.Name = "Karen Fillippelli"
emp.Id = 1013
emp.Age = 28
employeeList.Add(emp)
Return employeeList
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)
'Adding header text
worksheet("A1").Text = """copyrange"" Argument"
worksheet("A3").Text = "Name"
worksheet("B3").Text = "Id"
worksheet("C3").Text = "Age"
worksheet("D3").Text = "Designation"
worksheet("E3").Text = "Country"
worksheet("A3:E3").CellStyle.Font.Bold = True
'Adding markers dynamically with the argument, 'copyrange'
worksheet("A4").Text = "%Employee.Name;copyrange:R[0]C4:R[0]C5"
worksheet("B4").Text = "%Employee.Id"
worksheet("C4").Text = "%Employee.Age"
worksheet("D4").Text = "Software Developer"
worksheet("E4").Text = "Canada"
'Create template marker processor
Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
'Add marker variable
marker.AddVariable("Employee", GetEmployeeDetails())
'Apply markers
marker.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 with copyrange argument.
