Formulas | .NET Excel Library

Overview

The Syncfusion .NET Excel Library (XlsIO) provides comprehensive support for handling and manipulating Excel formulas. Users can seamlessly create, manage, and calculate formulas within worksheets, extending the functionality and capabilities of their Excel workbooks. This library supports 400+ built-in Excel functions for creating Excel documents.

.NET formula.

How to add a formula to an Excel cell using C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Initialize an IWorkbook instance.
Create an IWorksheet instance to access the sheet in Excel.
Set the formula in the cell by using the Formula property of IRange.
Save the workbook object to a FileStream object.

Here is an example of how to add a formula in Excel using C#.

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
 
  //Setting values to the cells.
  sheet.Range["A1"].Number = 10;
  sheet.Range["B1"].Number = 10;
 
  //Setting 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();
}

Calculated value

Getting a calculated value involves evaluating a formula within an Excel worksheet after enabling sheet calculations. It returns the calculated value of the formula as a string, providing users with the result of the computation. Additionally, apart from string values, the evaluated results can also be accessed as Boolean, date-time, and double data types. Invoking EnableSheetCalculations initializes CalcEngine objects, allowing for the retrieval of the calculated values of formulas within a worksheet. On the other hand, using DisableSheetCalculations upon completion of worksheet calculation disposes of all CalcEngine objects, ensuring efficient resource management.

Here is an example of how to get the calculated value for an Excel formula using C# .

c#
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();
}