Excel cell formulas in C# and VB.NET

With GemBox.Spreadsheet you can read and write Excel formulas, but the level of support will depend on the file format:

  • In XLSX files, reading and writing of all formulas is supported.
  • In XLS files, GemBox.Spreadsheet supports reading and writing of all standard and most advanced formulas.
  • In XLSB files, GemBox.Spreadsheet supports reading and writing of all standard and most advanced formulas.
  • In ODS files, reading and writing of all formulas is supported. However, OpenOffice formulas don't have the same syntax as Excel formulas.
  • In CSV files, reading of all formulas is supported.

To set, update, or remove a cell formula, use the ExcelCell.Formula property. You can get the last calculated value with the ExcelCell.Valueproperty.

By default, the formulas are automatically calculated when you open a file in Microsoft Excel. To calculate a formula in C# or VB.NET with GemBox.Spreadsheet, refer to our Formula Calculation example.

The following example shows how to read and write Excel formulas using C# and VB.NET.

Reading and writing Excel cell formulas
Screenshot of reading and writing Excel cell formulas
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("Formulas");

        worksheet.Rows[0].Style = workbook.Styles[BuiltInCellStyleName.Heading1];
        worksheet.Columns[0].Width = 9 * 256;
        worksheet.Columns[1].Width = 36 * 256;
        worksheet.Columns[2].Width = 18 * 256;

        worksheet.Cells[0, 0].Value = "Data";
        worksheet.Cells[0, 1].Value = "Formula";
        worksheet.Cells[0, 2].Value = "Result";

        // Add sample data values.
        worksheet.Cells["A2"].Value = 3;
        worksheet.Cells["A3"].Value = 4.1;
        worksheet.Cells["A4"].Value = 5.2;
        worksheet.Cells["A5"].Value = 6;
        worksheet.Cells["A6"].Value = 7;

        // Add named range.
        worksheet.NamedRanges.Add("MyRange1", worksheet.Cells.GetSubrange("A2:A6"));

        // Sample formulas.
        string[] formulas =
        {
            "=NOW()+123",
            "=MINUTE(0.5)-1343/35",
            "=HOUR(56)-23/35",
            "=YEAR(DATE(2020,1,1)) + 12",
            "=MONTH(3)-2342/235345",
            "=RAND()",
            "=TEXT(\"text\", \"$d\")",
            "=VAR(1,2)",
            "=MOD(1,2)",
            "=NOT(FALSE)",
            "=AND(TRUE)",
            "=TRUE()",
            "=VALUE(3)",
            "=LEN(\"hello\")",
            "=MID(\"hello\",1,1)",
            "=ROUND(1,2)",
            "=SIGN(-2)",
            "=INT(3)",
            "=ABS(-3)",
            "=LN(2)",
            "=EXP(4)",
            "=SQRT(2)",
            "=PI()",
            "=COS(4)",
            "=MAX(1,2)",
            "=MIN(1,2)",
            "=AVERAGE(1,2)",
            "=SUM(1,3)",
            "=IF(1,2,3)",
            "=COUNT(1,2,3)",
            "=SUBTOTAL(1,A2:A4)",                           // Function with cells range.
            "=SUM(MyRange1)",                               // Function with named range.
            "=COUNT(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)", // Function with miss argument.
            "=cOs( 1 )",                                    // Functions with different letters case.
            "=+++5",                                        // Unary operators.
            "=(1)-(2)+(3/2+34)/2+12232-32-4",               // Binary operators.
            "=TRUE",                                        // Operand tokens, bool.
            "=20",                                          // Operand tokens, int.
            "=2235.5132",                                   // Operand tokens, num.
            "=\"hello world!\"",                            // Operand tokens, str.
            "=#NULL!"                                       // Operand tokens, error.
        };

        // Write formulas to Excel cells.
        for (int i = 0; i < formulas.Length; i++)
        {
            string formula = formulas[i];
            worksheet.Cells[i + 1, 1].Value = formula;
            worksheet.Cells[i + 1, 2].Formula = formula;
        }

        workbook.Save("Formulas.%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("Formulas")

        worksheet.Rows(0).Style = workbook.Styles(BuiltInCellStyleName.Heading1)
        worksheet.Columns(0).Width = 9 * 256
        worksheet.Columns(1).Width = 36 * 256
        worksheet.Columns(2).Width = 18 * 256

        worksheet.Cells(0, 0).Value = "Data"
        worksheet.Cells(0, 1).Value = "Formula"
        worksheet.Cells(0, 2).Value = "Result"

        ' Add sample data values.
        worksheet.Cells("A2").Value = 3
        worksheet.Cells("A3").Value = 4.1
        worksheet.Cells("A4").Value = 5.2
        worksheet.Cells("A5").Value = 6
        worksheet.Cells("A6").Value = 7

        ' Add named range.
        worksheet.NamedRanges.Add("MyRange1", worksheet.Cells.GetSubrange("A2:A6"))

        ' Sample formulas.
        Dim formulas As String() =
        {
            "=NOW()+123",
            "=MINUTE(0.5)-1343/35",
            "=HOUR(56)-23/35",
            "=YEAR(DATE(2020,1,1)) + 12",
            "=MONTH(3)-2342/235345",
            "=RAND()",
            "=TEXT(""text"", ""$d"")",
            "=VAR(1,2)",
            "=MOD(1,2)",
            "=NOT(FALSE)",
            "=AND(TRUE)",
            "=TRUE()",
            "=VALUE(3)",
            "=LEN(""hello"")",
            "=MID(""hello"",1,1)",
            "=ROUND(1,2)",
            "=SIGN(-2)",
            "=INT(3)",
            "=ABS(-3)",
            "=LN(2)",
            "=EXP(4)",
            "=SQRT(2)",
            "=PI()",
            "=COS(4)",
            "=MAX(1,2)",
            "=MIN(1,2)",
            "=AVERAGE(1,2)",
            "=SUM(1,3)",
            "=IF(1,2,3)",
            "=COUNT(1,2,3)",
            "=SUBTOTAL(1,A2:A4)",                           ' Function with cells range.
            "=SUM(MyRange1)",                               ' Function with named range.
            "=COUNT(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)", ' Function with miss argument.
            "=cOs( 1 )",                                    ' Functions with different letters case.
            "=+++5",                                        ' Unary operators.
            "=(1)-(2)+(3/2+34)/2+12232-32-4",               ' Binary operators.
            "=TRUE",                                        ' Operand tokens, bool.
            "=20",                                          ' Operand tokens, int.
            "=2235.5132",                                   ' Operand tokens, num.
            "=""hello world!""",                            ' Operand tokens, str.
            "=#NULL!"                                       ' Operand tokens, error.
        }

        ' Write formulas to Excel cells.
        For i = 0 To formulas.Length - 1
            Dim formula As String = formulas(i)
            worksheet.Cells(i + 1, 1).Value = formula
            worksheet.Cells(i + 1, 2).Formula = formula
        Next

        workbook.Save("Formulas.%OutputFileType%")

    End Sub
End Module

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