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
Syncfusion Feedback


Trusted by the world’s leading companies

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#

Here is an example of how to add a formula in Excel using 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# .

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




Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion is proud to hold the following industry awards.

Scroll up icon