Excel formulas
The following example shows how to read and write Excel formulas using GemBox.Spreadsheet in 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 Module
Calculate Excel formulas
The following example shows how you can calculate Excel cell formulas using GemBox.Spreadsheet's calculation engine.
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("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 the 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 supports advanced calculation features such as iterative calculation and dynamic array formulas. For more information, see the Run Excel Calculations article.
GemBox.Spreadsheet supports a wide range of Excel formula, you can find the complete list on Calculation Engine help page. Note that we are continuously adding more formulas based on user feedback. You can send your request for a feature or formula here.
Calculate direct Excel formulas
The following example shows how you can directly calculate Excel formulas, without writing them to Excel cells, using GemBox.Spreadsheet's calculation engine.
using GemBox.Spreadsheet;
using System;
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("Formula Evaluation");
// Enter values in some cells.
worksheet.Cells["A1"].Value = 1;
worksheet.Cells["B1"].Value = 2;
worksheet.Cells["C1"].Value = -1;
Console.WriteLine($"A1: {worksheet.Cells["A1"].Value}");
Console.WriteLine($"B1: {worksheet.Cells["B1"].Value}");
Console.WriteLine($"C1: {worksheet.Cells["C1"].Value}");
Console.WriteLine();
// Evaluation of a formula that returns just one value.
var formula = "=A1 + B1 + C1";
var value = worksheet.CalculateFormula(formula);
Console.WriteLine($"Formula: {formula}");
Console.WriteLine($"Result: {value[0, 0]}");
Console.WriteLine();
// Evaluation of a formula that returns more than one value.
formula = "=ABS(A1:C1)";
value = worksheet.CalculateFormula(formula);
Console.WriteLine($"Formula: {formula}");
for (int i = 0; i < value.GetLength(0); i++)
for (int j = 0; j < value.GetLength(1); j++)
Console.WriteLine($"Result [{i}, {j}]: {value[i, j]}");
}
}
Imports GemBox.Spreadsheet
Imports System
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("Formula Evaluation")
' Enter values in some cells.
worksheet.Cells("A1").Value = 1
worksheet.Cells("B1").Value = 2
worksheet.Cells("C1").Value = -1
Console.WriteLine($"A1: {worksheet.Cells(CStr("A1")).Value}")
Console.WriteLine($"B1: {worksheet.Cells(CStr("B1")).Value}")
Console.WriteLine($"C1: {worksheet.Cells(CStr("C1")).Value}")
Console.WriteLine()
' Evaluation of a formula that returns just one value.
Dim formula = "=A1 + B1 + C1"
Dim value = worksheet.CalculateFormula(formula)
Console.WriteLine($"Formula: {formula}")
Console.WriteLine($"Result: {value(0, 0)}")
Console.WriteLine()
' Evaluation of a formula that returns more than one value.
formula = "=ABS(A1:C1)"
value = worksheet.CalculateFormula(formula)
Console.WriteLine($"Formula: {formula}")
For i As Integer = 0 To value.GetLength(0) - 1
For j As Integer = 0 To value.GetLength(1) - 1
Console.WriteLine($"Result [{i}, {j}]: {value(i, j)}")
Next
Next
End Sub
End Module
With CalculateFormula method, you can perform the calculation for a given formula and retrieve the results as a bidimensional array of objects. You can use the CalculateFormula method for normal and dynamic array formulas. For more information, see the Run Excel Calculations article.
