Articles in this section
Category / Section

Update formula for each row using Template Markers in C#, VB.NET

5 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. Using this library, you can update the formula for each row using Template Markers.

Steps to update formula for each row using Template Markers:

Step 1: Create a new C# console application project.

Create a new C# console application project

Create a new C# console application project

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.org.

Install NuGet package to the project

Install NuGet package to the project

Step 3: To update the formula for each row using Template Marker, first you should create an Excel workbook with markers in it.

C#

//Adding markers dynamically with the argument, 'copyrange'
worksheet.Range["A4"].Text = "%Employee.Name;copyrange:R[0]C[4]";
worksheet.Range["B4"].Text = "%Employee.Id";
worksheet.Range["C4"].Text = "%Employee.Age";
worksheet.Range["D4"].Text = "%Employee.Expenses";
worksheet.Range["E4"].Formula = "=D4*12";

 

VB.NET

'Adding markers dynamically with the argument, 'copyrange'
worksheet.Range("A4").Text = "%Employee.Name;copyrange:R[0]C[4]"
worksheet.Range("B4").Text = "%Employee.Id"
worksheet.Range("C4").Text = "%Employee.Age"
worksheet.Range("D4").Text = "%Employee.Expenses"
worksheet.Range("E4").Formula = "=D4*12"

 

Here, Employee is the marker variable referred to a class object, followed by its properties separated by a dot (.). The formula is updated when it is copied to another cell.

To copy the cells from one cell to another while importing the data using Template Markers, you can use the copyrange argument with Template Markers. This copyrange argument specified in template marker copies the specified cells after each cell import.

You will get the Excel workbook with markers applied as shown below.

Excel document with markers

Excel document with markers

Step 4: Include the following namespaces in Program.cs file.

C#

using Syncfusion.XlsIO;
using System.Collections.Generic;

 

VB.NET

Imports Syncfusion.XlsIO
Imports System.Collections.Generic

 

Step 5: Include the following code snippet in main method of Program.cs file to update formula for each row using Template Markers.

C#

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the Excel application object
    IApplication application = excelEngine.Excel;
 
    //Create a new Excel workbook
    IWorkbook workbook = application.Workbooks.Create(1);
 
    //Get the first worksheet in workbook into IWorksheet
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Adding header text
    worksheet.Range["A1"].Text = "Formula Update for each row using Template Markers";
    worksheet.Range["A1:E1"].Merge();
 
    //Assign data in the worksheet
    worksheet.Range["A3"].Text = "Name";
    worksheet.Range["B3"].Text = "Id";
    worksheet.Range["C3"].Text = "Age";
    worksheet.Range["D3"].Text = "Expenses";
    worksheet.Range["E3"].Text = "Annual Expenses";
    worksheet.Range["A3:E3"].CellStyle.Font.Bold = true;
 
    //Adding markers dynamically with the argument, 'copyrange'
    worksheet.Range["A4"].Text = "%Employee.Name;copyrange:R[0]C[4]";
    worksheet.Range["B4"].Text = "%Employee.Id";
    worksheet.Range["C4"].Text = "%Employee.Age";
    worksheet.Range["D4"].Text = "%Employee.Expenses";
    worksheet.Range["E4"].Formula = "=D4*12";
 
    //Create template marker processor
    ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
 
    //Add marker variable
    marker.AddVariable("Employee", GetEmployeeDetails());
 
    //Apply markers
    marker.ApplyMarkers();
 
    //Autofit worksheet columns
    worksheet.UsedRange.AutofitColumns();
 
    //Save the Excel workbook
    workbook.SaveAs("Output.xlsx");
}

 

VB.NET

Using excelEngine As ExcelEngine = New ExcelEngine()
    'Instantiate the Excel application object
    Dim application As IApplication = excelEngine.Excel
 
    'Create a new Excel workbook
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
 
    'Get the first worksheet in workbook into IWorksheet
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
    'Adding header text
    worksheet.Range("A1").Text = "Formula Update for each row using Template Markers"
    worksheet.Range("A1:E1").Merge()
 
    'Assign data in the worksheet
    worksheet.Range("A3").Text = "Name"
    worksheet.Range("B3").Text = "Id"
    worksheet.Range("C3").Text = "Age"
    worksheet.Range("D3").Text = "Expenses"
    worksheet.Range("E3").Text = "Annual Expenses"
    worksheet.Range("A3:E3").CellStyle.Font.Bold = True
 
    'Adding markers dynamically with the argument, 'copyrange'
    worksheet.Range("A4").Text = "%Employee.Name;copyrange:R[0]C[4]"
    worksheet.Range("B4").Text = "%Employee.Id"
    worksheet.Range("C4").Text = "%Employee.Age"
    worksheet.Range("D4").Text = "%Employee.Expenses"
    worksheet.Range("E4").Formula = "=D4*12"
 
    'Create template marker processor
    Dim marker As ITemplateMarkersProcessor = workbook.CreateTemplateMarkersProcessor()
 
    'Add marker variable
    marker.AddVariable("Employee", GetEmployeeDetails())
 
    'Apply markers
    marker.ApplyMarkers()
 
    'Autofit worksheet columns
    worksheet.UsedRange.AutofitColumns()
 
    'Save the Excel workbook
    workbook.SaveAs("Output.xlsx")
End Using

 

Step 6: Include the following code snippet in GetEmployeeDetails() static method, to add employee details into the list.

C#

//Initialize an employee list
List<Employee> employeeList = new List<Employee>();
 
//Set the employee details and add into list
Employee employee = new Employee();
employee.Name = "Andy Bernard";
employee.Id = 1011;
employee.Age = 35;
employee.Expenses = 1000;
employeeList.Add(employee);
 
employee = new Employee();
employee.Name = "Jim Halpert";
employee.Id = 1012;
employee.Age = 26;
employee.Expenses = 1230;
employeeList.Add(employee);
 
employee = new Employee();
employee.Name = "Karen Fillippelli";
employee.Id = 1013;
employee.Age = 28;
employee.Expenses = 1363;
employeeList.Add(employee);
 
employee = new Employee();
employee.Name = "Phyllis Lapin";
employee.Id = 1014;
employee.Age = 33;
employee.Expenses = 1568;
employeeList.Add(employee);
 
employee = new Employee();
employee.Name = "Stanley Hudson";
employee.Id = 1015;
employee.Age = 31;
employee.Expenses = 1427;
employeeList.Add(employee);
 
//Return the employee list
return employeeList;

 

VB.NET

'Initialize an employee list
Dim employeeList As List(Of Employee) = New List(Of Employee)
 
'Set the employee details and add into list
Dim employee As Employee = New Employee()
employee.Name = "Andy Bernard"
employee.Id = 1011
employee.Age = 35
employee.Expenses = 1000
employeeList.Add(employee)
 
employee = New Employee()
employee.Name = "Jim Halpert"
employee.Id = 1012
employee.Age = 26
employee.Expenses = 1230
employeeList.Add(employee)
 
employee = New Employee()
employee.Name = "Karen Fillippelli"
employee.Id = 1013
employee.Age = 28
employee.Expenses = 1363
employeeList.Add(employee)
 
employee = New Employee()
employee.Name = "Phyllis Lapin"
employee.Id = 1014
employee.Age = 33
employee.Expenses = 1568
employeeList.Add(employee)
 
employee = New Employee()
employee.Name = "Stanley Hudson"
employee.Id = 1015
employee.Age = 31
employee.Expenses = 1427
employeeList.Add(employee)
 
'Return the employee list
Return employeeList

 

Step 7: Create a class with name as Employee and have the required properties.

C#

private string m_name;
private int m_id;
private int m_age;
private int m_expenses;
 
//Employee Name
public string Name
{
    get
    {
        return m_name;
    }
    set
    {
        m_name = value;
    }
}
 
//Employee ID
public int Id
{
    get
    {
        return m_id;
    }
    set
    {
        m_id = value;
    }
}
 
//Employee Age
public int Age
{
    get
    {
        return m_age;
    }
    set
    {
        m_age = value;
    }
}
 
//Employee Expenses
public int Expenses
{
    get
    {
        return m_expenses;
    }
    set
    {
        m_expenses = value;
    }
}

 

VB.NET

Private m_name As String
Private m_id As Integer
Private m_age As Integer
Private m_expenses As Integer
 
'Employee Name
Public Property Name As String
    Get
        Return m_name
    End Get
    Set
        m_name = Value
    End Set
End Property
 
'Employee ID
Public Property Id As Integer
    Get
        Return m_id
    End Get
    Set
        m_id = Value
    End Set
End Property
 
'Employee Age
Public Property Age As Integer
    Get
        Return m_age
    End Get
    Set
        m_age = Value
    End Set
End Property
 
'Employee Expenses
Public Property Expenses As Integer
    Get
        Return m_expenses
    End Get
    Set
        m_expenses = Value
    End Set
End Property

 

A complete working sample to update the formula for each row using Template Markers can be downloaded from Update-Formula.zip.

By executing the program, you will get the output Excel document as follows.

Update formula for each row using Template Marker

Output Excel document

Take a moment to peruse the documentation where you will find other options like bind from Array, DataTable, Collection Objects with Images, Template Marker with Conditional Formatting and Hyperlink with code examples.

Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

 

Conclusion

I hope you enjoyed learning about update formula for each row using Template Markers in C#, VB.NET.

You can refer to our WinForms XIsIO’s feature tour page to know about its other groundbreaking feature representations. You can also explore our WinForms XIsIO documentation to understand how to present and manipulate data.

For current customers, you can check out our WinForms components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WinForms XIsIO and other WinForms components.

If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forumsDirect-Trac, or feedback portal. We are always happy to assist you!

 

 

 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied