Excel cell formulas

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 are supported.
  • In XLS files, reading and writing of all common and most advanced formulas are supported.
  • In ODS files, reading and writing of all formulas are supported, however, OpenOffice formulas don't have the same syntax as Excel formulas.
  • In CSV files, reading of all formulas is supported.

The formula values are automatically calculated when a file is opened in an Excel application. The formula can be accessed with ExcelCell.Formula and its last calculated value with ExcelCell.Value.

You can also calculate formulas using GemBox.Spreadsheet. For more information, see the Formula Calculation example.

The following example shows how you can read and write Excel formulas.

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

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.