Excel Formulas in C#

VB C#

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

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

' Set Formulas
Private workSheet("A1").Formula = "Sum(B8:C12)"
Private workSheet("B8").Formula = "=C9/C11"
Private 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()

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:

// Assigns a formula to cell A2 to calculate the square root of value in cell A1
workSheet["A2"].Formula = "=SQRT(A1)";

// Assigns a formula to cell B8 performing a division of C9 by C11
workSheet["B8"].Formula = "=C9/C11";

// Assigns a formula to cell G31 to compute the tangent of the value in G30
workSheet["G31"].Formula = "=TAN(G30)";

// Assigns a formula to cell A2 to calculate the square root of value in cell A1
workSheet["A2"].Formula = "=SQRT(A1)";

// Assigns a formula to cell B8 performing a division of C9 by C11
workSheet["B8"].Formula = "=C9/C11";

// Assigns a formula to cell G31 to compute the tangent of the value in G30
workSheet["G31"].Formula = "=TAN(G30)";

' Assigns a formula to cell A2 to calculate the square root of value in cell A1
workSheet("A2").Formula = "=SQRT(A1)"

' Assigns a formula to cell B8 performing a division of C9 by C11
workSheet("B8").Formula = "=C9/C11"

' Assigns a formula to cell G31 to compute the tangent of the value in G30
workSheet("G31").Formula = "=TAN(G30)"

$vbLabelText $csharpLabel

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.
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.