Excel Formulas in C#

VBC#

using IronXL;
using System.Linq;

WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Set Formulas
workSheet["A1"].Formula = "Sum(B8:C12)";
workSheet["B8"].Formula = "=C9/C11";
workSheet["G30"].Formula = "Max(C3:C7)";

// Force recalculate all formula values in all sheets.
workBook.EvaluateAll();

// Get the formula's calculated value.  e.g. "52"
var formulaValue = workSheet["G30"].First().FormattedCellValue;

// Get the formula as a string. e.g. "Max(C3:C7)"
string formulaString = workSheet["G30"].Formula;

// Save changes with updated formulas and calculated values.
workBook.Save();

Imports IronXL
Imports System.Linq

Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")
Dim workSheet As WorkSheet = workBook.DefaultWorkSheet

' Set Formulas
workSheet("A1").Formula = "Sum(B8:C12)"
workSheet("B8").Formula = "=C9/C11"
workSheet("G30").Formula = "Max(C3:C7)"

' Force recalculate all formula values in all sheets.
workBook.EvaluateAll()

' Get the formula's calculated value.  e.g. "52"
Dim formulaValue = workSheet("G30").First().FormattedCellValue

' Get the formula as a string. e.g. "Max(C3:C7)"
Dim formulaString As String = workSheet("G30").Formula

' Save changes with updated formulas and calculated values.
workBook.Save()

PM > Install-Package IronXL.Excel

Install-Package IronXL.Excel

Excel Formulas in C#

Using IronXL to Work with Excel Formulas

IronXL allows setting and evaluating Excel formulas without the need for Office Interop. It supports over 150 formulas, with more being added regularly. Formulas can be assigned using the Range.Formula property. Here's an example:

A formula is an expression used to calculate the value of a cell. In Excel, functions are predefined formulas readily available.

IronXL distinguishes itself among .NET Excel libraries by supporting a wide range of Excel formulas and enabling their real-time calculation.

How to Use Excel Formulas in C#

Here are the steps to utilize Excel formulas using IronXL in a C# project:

Install an Excel Library: Download and install IronXL from NuGet to use Excel functionalities.
Load the Excel Document: Open your Excel file and select the default Worksheet where you want to apply formulas.
Set Formulas and Values: Assign formulas and any necessary values to the spreadsheet cells you wish to manipulate.
Evaluate Formulas: Use the EvaluateAll method to compute the results of all the applied formulas. Workbook
Save the Workbook: Save your Workbook object back to an Excel file, reflecting all changes and calculations.

IronXL provides a straightforward and efficient method to work with Excel formulas programmatically within C# applications without requiring Excel to be installed on the server or client.

Learn to Edit Excel Formulas in C# with IronXL

View on GitHub