We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to use insert argument in template markers using XlsIO?

Platform: WinForms |
Control: XlsIO |
Published Date: February 28, 2018 |
Last Revised Date: June 20, 2021

This article explains how to use insert argument in template markers using XlsIO.

 

What is insert argument?

 

The argument insert specified in template marker inserts new row or column, depending on the argument direction for each new cell. By default, the rows and columns will not be inserted.

 

Syntax

 

%<MarkerVariable>.<Property>;insert

 

The following arguments can be used in addition with insert argument to do specific operations.

 

  • copystyles – to copy 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 style and merged cells from current row/column into the newly inserted row/column

 

Steps to use insert argument

 

  1. 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 below code snippet is used for inserting rows while copying the cell styles and merged area 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 below code snippet 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 below screenshot shows how the markers are applied for row/column insert in workbook template.

 

Applying marker for row/column

 

 

  1. Create template marker processor.

 

//Create template marker processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();

 

  1. Add a variable name that is equal to the class object specified in step 1.

 

//Add marker variable
marker.AddVariable("Employee", GetEmployeeDetails());

 

  1. Apply markers.

 

//Apply markers
marker.ApplyMarkers();

 

Download Complete Sample

 

The following C#/VB.NET complete code snippet shows how to use template marker with 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 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 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 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 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 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 insert argument.

 

Document generated using template markers with insert arugment

 

Note:

In the above output,

 

  • Cells A5 and B5 are merged and the font style for that cell is italic. These merged region and style were copied to the newly inserted row.

 

  • Cell B12 is filled with 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.

 

 

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile