Excel Formula Calculation in C# and VB.NET

With GemBox.Spreadsheet, you can calculate a large range of different formulas - from a simplest formula that calculates the sum of two fields, up to a complex formula that includes brackets, a range of cells, cells that contain other formulas, and even an iterative calculation. GemBox.Spreadsheet currently supports 140+ most used formulas. The whole list can be found here.

The following example shows how to use the GemBox.Spreadsheet calculation engine to calculate Excel cell formula values in C# and VB.NET. It demonstrates how to set cell formulas and calculate their values by calling one of the methods: ExcelCell.Calculate, ExcelWorksheet.Calculate, or ExcelFile.Calculate. The calculation result is stored in ExcelCell.Value.

Screenshot of Excel formulas calculated with GemBox.Spreadsheet

Output file type

C#
VB.NET

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

Check next example or download examples from GitHub.