How to use the insert argument in template markers using XlsIO?
This article explains how to use the insert argument in template markers using XlsIO.
What is the insert argument?
The argument insert specified in the template marker inserts a new row or column, depending on the argument direction for each new cell. By default, rows and columns will not be inserted.
Syntax
%<MarkerVariable>.<Property>;insert |
The following arguments can be used in addition to the insert argument to perform specific operations.
- copystyles – to copy the current row/column style into the newly inserted row/column
- copymerges – to copy merged cells into the newly inserted row/column
- copystyles and copymerges – to copy both styles and merged cells from the current row/column into the newly inserted row/column
Steps to use the insert argument
- Create a workbook template with markers in it. Here, Employee is the marker variable referred to as a class object, followed by its properties separated by a dot (.).
The below code snippet below is used for inserting rows while copying the cell styles and merged areas to the next row.
//Adding markers dynamically with the arguments, 'insert','copystyles' and 'copymerges' worksheet["A5"].Text = "%Employee.Name;insert:copystyles,copymerges"; worksheet["C5"].Text = "%Employee.Id"; worksheet["D5"].Text = "%Employee.Age";
The code snippet below is used for inserting columns while copying the cell styles to the next column in the horizontal direction.
//Adding markers dynamically with the arguments, 'insert' and 'copystyles' and 'horizontal' worksheet["B10"].Text = "%Employee.Name;insert:copystyles;horizontal"; worksheet["B11"].Text = "%Employee.Id;horizontal"; worksheet["B12"].Text = "%Employee.Age;horizontal";
The screenshot below shows how the markers are applied for row/column insertion in the workbook template.
- Create template marker processor.
//Create template marker processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
- Add a variable name that matches 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 code snippet shows how to use a template marker with the insert argument in XlsIO.
using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.IO;
using System.Drawing;
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 = "\"Insert\" Argument";
worksheet["A3"].CellStyle.Font.RGBColor = Color.FromArgb(255, 0, 0);
worksheet["A3"].Text = "\"Row\" Insertion with copy styles and copy merges";
worksheet["A4:B4"].Merge();
worksheet["A5:B5"].Merge();
worksheet["A4"].Text = "Name";
worksheet["C4"].Text = "Id";
worksheet["D4"].Text = "Age";
worksheet["A4:F4"].CellStyle.Font.Bold = true;
worksheet["A5"].CellStyle.Font.Italic = true;
//Adding markers dynamically with the arguments, 'insert','copystyles' and 'copymerges'
worksheet["A5"].Text = "%Employee.Name;insert:copystyles,copymerges";
worksheet["C5"].Text = "%Employee.Id";
worksheet["D5"].Text = "%Employee.Age";
// This data will be moved to a new row
worksheet["A7"].Text = "Text in new row";
worksheet["A9"].CellStyle.Font.RGBColor = Color.FromArgb(255,0,0);
worksheet["A9"].Text = "\"Column\" Insertion with copy styles";
worksheet["A10"].Text = "Name";
worksheet["A11"].Text = "Id";
worksheet["A12"].Text = "Age";
worksheet["A10:A12"].CellStyle.Font.Bold = true;
worksheet["B10"].CellStyle.Color = Color.FromArgb(189, 215, 238);
//Adding markers dynamically with the arguments, 'insert' and 'copystyles' and 'horizontal'
worksheet["B10"].Text = "%Employee.Name;insert:copystyles;horizontal";
worksheet["B11"].Text = "%Employee.Id;horizontal";
worksheet["B12"].Text = "%Employee.Age;horizontal";
// This data will be moved to a new column
worksheet["C10"].Text = "Text in new column";
//Create template marker processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
//Add marker variable
marker.AddVariable("Employee", GetEmployeeDetails());
//Apply markers
marker.ApplyMarkers();
worksheet["A4"].CellStyle.Color = Color.FromArgb(77, 176, 215);
worksheet["E4"].CellStyle.Color = Color.FromArgb(77, 176, 215);
worksheet["F4"].CellStyle.Color = Color.FromArgb(77, 176, 215);
worksheet["A12:A14"].CellStyle.Color = Color.FromArgb(77, 176, 215);
//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
Imports System.Drawing
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)
'Add header text
worksheet("A1").Text = "\"Insert\" Argument"
worksheet("A3").CellStyle.Font.RGBColor = Color.FromArgb(255, 0, 0)
worksheet("A3").Text = "\"Row\" Insertion with copy styles and copy merges"
worksheet("A4:B4").Merge()
worksheet("A5:B5").Merge()
worksheet("A4").Text = "Name"
worksheet("C4").Text = "Id"
worksheet("D4").Text = "Age"
worksheet("A4:F4").CellStyle.Font.Bold = True
worksheet("A5").CellStyle.Font.Italic = True
'Adding markers dynamically with the arguments, 'insert','copystyles' and 'copymerges
worksheet("A5").Text = "%Employee.Name;insert:copystyles,copymerges"
worksheet("C5").Text = "%Employee.Id"
worksheet("D5").Text = "%Employee.Age"
'This data will be moved to a new row
worksheet("A7").Text = "Text in new row"
worksheet("A9").CellStyle.Font.RGBColor = Color.FromArgb(255, 0, 0)
worksheet("A9").Text = "\"Column\" Insertion with copy styles"
worksheet("A10").Text = "Name"
worksheet("A11").Text = "Id"
worksheet("A12").Text = "Age"
worksheet("A10:A12").CellStyle.Font.Bold = True
worksheet("B10").CellStyle.Color = Color.FromArgb(189, 215, 238)
'Adding markers dynamically with the arguments, 'insert','copystyles' and 'horizontal'
worksheet("B10").Text = "%Employee.Name;insert:copystyles;horizontal"
worksheet("B11").Text = "%Employee.Id;horizontal"
worksheet("B12").Text = "%Employee.Age;horizontal"
'This data will be moved to a new column
worksheet("C10").Text = "Text in new column"
'Create template marker processor
Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
'Add marker variable
marker.AddVariable("Employee", GetEmployeeDetails())
'Apply markers
marker.ApplyMarkers()
worksheet("A4").CellStyle.Color = Color.FromArgb(77, 176, 215)
worksheet("C4").CellStyle.Color = Color.FromArgb(77, 176, 215)
worksheet("D4").CellStyle.Color = Color.FromArgb(77, 176, 215)
worksheet("A10:A12").CellStyle.Color = Color.FromArgb(77, 176, 215)
'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 with the insert argument.
In the above output,
- Cells A5 and B5 are merged, and the font style for that cell is italic. These merged regions and styles were copied to the newly inserted row.
- Cell B12 is filled with a background color. This is copied to the next column inserted.
- The data from "C4:D7" is moved to "E4:F7" because two more columns are inserted between column B and column E while importing the data for column insertion.
Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through formulas, adding charts in worksheets or workbooks, organizing and analyzing data through tables and pivot tables, appending multiple records to a worksheet using template markers, and most importantly, PDF and image conversions, etc., with code examples.
Refer here to explore the rich set of Syncfusion Essential XlsIO features.
Note:
Starting with v16.2.0.x, if you reference Syncfusion assemblies from the trial setup or from the NuGet feed, include a license key in your projects. Refer to this link to learn about generating and registering a Syncfusion license key in your application to use the components without a trial message.
Conclusion
I hope you enjoyed learning about how to use the insert argument in template markers using XlsIO.
You can refer to our XlsIO’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!
