Excel Formula Calculation in C# and VB.NET

GemBox.Spreadsheet supports a large set of Excel formula functions, including Mathematical, Statistical, Logical, Lookup, Financial and more. For a complete list visit the Supported formulas section. About ~200 of the most used Excel formulas are currently supported but note that we are continuously adding more based on user feedback.

The calculation engine also supports iterative calculations, named range references, external references, array formulas, and more. You can recalculate the formulas that are read from the Excel file and the ones you add at runtime using the ExcelCell.Formula property.

To calculate the results of the formulas, you need to call one of the following Calculate methods:

After calculating the formulas, you can access the evaluated value using the ExcelCell.Value property.

The following example shows how you can use the GemBox.Spreadsheet calculation engine to calculate Excel cell formula values in C# and VB.NET.

Excel cells with resulting values from recalculated formulas
Screenshot of Excel formulas calculated values
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Formula Calculation");

        // Some formatting.
        var row = worksheet.Rows[0];
        row.Style.Font.Weight = ExcelFont.BoldWeight;

        var column = worksheet.Columns[0];
        column.SetWidth(250, LengthUnit.Pixel);
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left;
        column = worksheet.Columns[1];
        column.SetWidth(250, LengthUnit.Pixel);
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right;

        // Use first row for column headers.
        worksheet.Cells["A1"].Value = "Formula";
        worksheet.Cells["B1"].Value = "Calculated value";

        // Enter some Excel formulas as text in first column.
        worksheet.Cells["A2"].Value = "=1 + 1";
        worksheet.Cells["A3"].Value = "=3 * (2 - 8)";
        worksheet.Cells["A4"].Value = "=3 + ABS(B3)";
        worksheet.Cells["A5"].Value = "=B4 > 15";
        worksheet.Cells["A6"].Value = "=IF(B5, \"Hello world\", \"World hello\")";
        worksheet.Cells["A7"].Value = "=B6 & \" example\"";
        worksheet.Cells["A8"].Value = "=CODE(RIGHT(B7))";
        worksheet.Cells["A9"].Value = "=POWER(B8, 3) * 0.45%";
        worksheet.Cells["A10"].Value = "=SIGN(B9)";
        worksheet.Cells["A11"].Value = "=SUM(B2:B10)";

        // Set text from first column as second row cell's formula.
        int rowIndex = 1;
        while (worksheet.Cells[rowIndex, 0].ValueType != CellValueType.Null)
            worksheet.Cells[rowIndex, 1].Formula = worksheet.Cells[rowIndex++, 0].StringValue;

        // GemBox.Spreadsheet supports single Excel cell calculation, ...
        worksheet.Cells["B2"].Calculate();

        // ... Excel worksheet calculation,
        worksheet.Calculate();

        // ... and whole Excel file calculation.
        worksheet.Parent.Calculate();

        workbook.Save("Formula Calculation.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Formula Calculation")

        ' Some formatting.
        Dim row = worksheet.Rows(0)
        row.Style.Font.Weight = ExcelFont.BoldWeight

        Dim column = worksheet.Columns(0)
        column.SetWidth(250, LengthUnit.Pixel)
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Left
        column = worksheet.Columns(1)
        column.SetWidth(250, LengthUnit.Pixel)
        column.Style.HorizontalAlignment = HorizontalAlignmentStyle.Right

        ' Use first row for column headers.
        worksheet.Cells("A1").Value = "Formula"
        worksheet.Cells("B1").Value = "Calculated value"

        ' Enter some Excel formulas as text in first column.
        worksheet.Cells("A2").Value = "=1 + 1"
        worksheet.Cells("A3").Value = "=3 * (2 - 8)"
        worksheet.Cells("A4").Value = "=3 + ABS(B3)"
        worksheet.Cells("A5").Value = "=B4 > 15"
        worksheet.Cells("A6").Value = "=IF(B5, ""Hello world"", ""World hello"")"
        worksheet.Cells("A7").Value = "=B6 & "" example"""
        worksheet.Cells("A8").Value = "=CODE(RIGHT(B7))"
        worksheet.Cells("A9").Value = "=POWER(B8, 3) * 0.45%"
        worksheet.Cells("A10").Value = "=SIGN(B9)"
        worksheet.Cells("A11").Value = "=SUM(B2:B10)"

        ' Set text from first column as second row cell's formula.
        Dim rowIndex As Integer = 0
        While worksheet.Cells(rowIndex, 0).ValueType <> CellValueType.Null
            worksheet.Cells(rowIndex, 1).Formula = worksheet.Cells(rowIndex, 0).StringValue
            rowIndex += 1
        End While

        ' GemBox.Spreadsheet supports single Excel cell calculation, ...
        worksheet.Cells("B1").Calculate()

        ' ... Excel worksheet calculation,
        worksheet.Calculate()

        ' ... and whole Excel file calculation.
        worksheet.Parent.Calculate()

        workbook.Save("Formula Calculation.%OutputFileType%")

    End Sub
End Module

GemBox.Spreadsheet also provides ExcelFile.CalculationOptions with which you can specify various calculation engine options.

For instance, by setting the CalculationOptions.EnableCaching property to true you can improve the performance of formula calculations in some cases (only for Excel files that have repeated formula expressions that are always evaluated to the same result).

Supported formulas

Here is a list of functions currently supported in GemBox.Spreadsheet:
ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CEILING.PRECISE, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COMBIN, CONCAT, CONCATENATE, COS, COSH, COT, COTH, CONFIDENCE, CONFIDENCE.NORM, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DEGREES, DOLLAR, EDATE, EOMONTH, EVEN, EXACT, EXP, FACT, FALSE, FILTER, FIND, FIXED, FLOOR, FREQUENCY, FV, GETPIVOTDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MAXIFS, MEDIAN, MID, MIN, MINA, MINIFS, MINUTE, MOD, MODE, MONTH, N, NA, NETWORKDAYS, NORMDIST, NORMSDIST, NOT, NOW, NPV, ODD, OFFSET, OR, PI, PMT, POWER, PPMT, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDARRAY, RANDBETWEEN, RANK, RANK.AVG, RANK.EQ, RATE, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SORT, SORTBY, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, T, T.DIST, TAN, TANH, TEXT, TEXTJOIN, TIME, TIMEVALUE, TODAY, TRIM, TRUE, TRUNC, TYPE, UNIQUE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC.

See also


Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy