Update formula for each row using Template Markers in C#, VB.NET
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
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
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
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.
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.
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.
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 forums, Direct-Trac, or feedback portal. We are always happy to assist you!