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 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.Value property.
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.
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the 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 the 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 ModuleDynamic array formulas and legacy array formulas
When adding formulas in new versions of Excel, it often automatically interprets them as dynamic array formulas.
Dynamic array formulas can return an array of values that are spilled into neighboring cells. An example of this formula is =SQRT(A1:A5), which calculates the square root for every value in the A1:A5 range and outputs the resulting values into the cell that holds the formula and cells in the following four rows.
The array of values returned by the dynamic array formula can be of variable size. It can even return just a single value. For example, formula =SUM(SQRT(A1:A5)) returns a single value, the sum of square roots in cells A1:A5. However, to achieve this behavior, the formula needs to be created as a dynamic array formula because SQRT function works by default with a single value. It needs to be inside the dynamic array formula (or legacy array formula) to be able to accept an array of values (in this case, range A1:A5) and return an array of values. Otherwise, the function would be evaluated using the intersection operator, which has different logic and, in most cases, is not the intended behavior.
When you are working with formulas with Excel, it creates this formula as a dynamic array formula behind the scenes. With Gembox.Spreadsheet, you need to use the ExcelCell.SetDynamicArrayFormula method to set the dynamic array formula.
Dynamic array formulas are fully supported only in Excel 365, Excel 2021, and newer versions. Dynamic array formulas opened in Excel 2019 or older will be shown as legacy array formulas (also known as CSE formulas).The main difference between the legacy array formula and the dynamic array formula is that the range of cells to which the result of the calculation will be written is not determined dynamically, but it needs to be known when setting the formula. Microsoft recommends favoring dynamic array formulas for newer versions of Excel.
GemBox.Spreadsheet provides the API and calculation support for both dynamic and legacy array formulas.
The following example shows how you can create dynamic and legacy array formulas. It also shows the calculation difference between a dynamic array formula and a regular formula.
