TL;DR: Let’s see how to use the Syncfusion Excel Library in C# to manage formulas in Excel. This guide covers creating, reading, and calculating formulas, leveraging advanced features like named ranges and cross-sheet references.
The Syncfusion Excel Library (XlsIO) is a powerful tool that enables developers to programmatically create, manipulate, and manage Excel files. It offers comprehensive functionality for handling formulas, allowing for advanced operations such as reading, writing, and calculating formulas in Excel. It supports 400+ Excel built-in functions, enabling users to create formulas without restrictions.
In this blog, we’ll explore how to read, write, and calculate formulas in Excel using the Syncfusion Excel Library in C#.
Formulas in Excel are created by inserting expressions into cells that perform calculations based on the cell’s contents, other cell references, or predefined Excel functions.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Set values to the cells.
sheet.Range["A1"].Number = 10;
sheet.Range["B1"].Number = 20;
//Set formula in the cell.
sheet.Range["C1"].Formula = "=SUM(A1,B1)";
//Saving the workbook as stream.
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
} After executing the provided code example, the output document will resemble the following image. Reading formulas in Excel involves retrieving the formula expression from a cell, which is stored as a string. This can be done using the Formula property of the IRange interface.
Refer to the following code example.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream fileStream = new FileStream("Sample.xlsx",FileMode.Open,FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet sheet = workbook.Worksheets[0];
//Returns the formula in C1 style notation.
string formula = sheet["C1"].Formula;
//Dispose stream.
fileStream.Dispose();
} To perform calculations in an Excel workbook, it is necessary to invoke the EnableSheetCalculations method of the IWorksheet. This method initializes the CalcEngine objects and ensures that the formulas in the worksheet are calculated properly.
Accessing a calculated value involves evaluating a formula within an Excel worksheet after enabling sheet calculations. This process returns the formula’s calculated value as a string, providing users with the computation’s result.
Refer to the following code example to access a calculated value from a cell.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();
//Returns the calculated value of a formula using the most current inputs.
string calculatedValue = sheet["C1"].CalculatedValue;
sheet.DisableSheetCalculations();
//Saving the workbook as stream.
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
} The named range or a defined name in Excel allows you to assign a name to a cell or cell range in the worksheet. They can be used in formulas to reference the cell or cell range. The named ranges can be accessed across worksheets and help us create formulas without explicitly providing the cell ranges.
Refer to the following code example to add a named range to the Excel worksheet.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Defining a name in the workbook level for cell A1.
IName name1 = workbook.Names.Add("One");
name1.RefersToRange = sheet.Range["A1"];
//Defining a name in the workbook level for cell B1.
IName name2 = workbook.Names.Add("Two");
name2.RefersToRange = sheet.Range["B1"];
//Formula using defined names.
sheet.Range["C1"].Formula = "=SUM(One,Two)";
//Saving the workbook as a stream.
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
} After running the above code example, the output document will look like the following image.
Cross-sheet references allow formulas to use data from multiple sheets within the same workbook.
Refer to the following code example.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create();
IWorksheet sheet = workbook.Worksheets[0];
//Setting formula for the range with cross-sheet reference.
sheet.Range["C2"].Formula = "=SUM(Sheet2!B2,Sheet1!A2)";
//Saving the workbook as stream.
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
For more details, refer to the working with formulas in Excel using C# documentation and GitHub demo.
Thanks for reading! In this blog, we’ve explored how to add and read formulas in an Excel document using C# and Syncfusion Excel Library (XlsIO). The Excel Library also allows you to export Excel data to images, data tables, CSV, TSV, HTML, collections of objects, ODS, JSON, and other file formats. You can also explore our .NET Excel Library demos to understand how to create and manipulate data.
Feel free to try out this versatile .NET Excel Library and share your feedback in the comments section of this blog post!
If you’re an existing customer, you can check out these features from the License and Downloads page. If you are new to Syncfusion, try our 30-day free trial.
You can also contact us through our support forums, support portal, or feedback portal. We are always happy to assist you!