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 ModuleGemBox.Spreadsheet also supports advanced calculation features such as iterative calculation and dynamic array formulas. Please refer to our Calculation article for more information.
Here is a list of functions currently supported in GemBox.Spreadsheet:
ABS, ACOS, ACOSH, ACOT, ACOTH, ADDRESS, AND, ASIN, ASINH, ATAN, ATAN2, ATANH, AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS, CEILING, CEILING.PRECISE, CHAR, CHOOSE, CLEAN, CODE, COLUMN, COMBIN, CONCAT, CONCATENATE, COS, COSH, COT, COTH, CONFIDENCE, CONFIDENCE.NORM, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DEGREES, DOLLAR, EDATE, EOMONTH, EVEN, EXACT, EXP, FACT, FALSE, FILTER, FIND, FIXED, FLOOR, FORECAST, FORECAST.LINEAR, FREQUENCY, FV, GETPIVOTDATA, HLOOKUP, HOUR, HYPERLINK, IF, IFERROR, IFNA, IFS, INDEX, INDIRECT, INT, INTERCEPT, IPMT, IRR, ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISREF, ISTEXT, LARGE, LEFT, LEN, LINEST, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX, MAXA, MAXIFS, MEDIAN, MID, MIN, MINA, MINIFS, MINUTE, MOD, MODE, MONTH, MROUND, N, NA, NETWORKDAYS, NORMDIST, NORMSDIST, NOT, NOW, NPV, NUMBERVALUE, ODD, OFFSET, OR, PEARSON, PI, PMT, POWER, PPMT, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDARRAY, RANDBETWEEN, RANK, RANK.AVG, RANK.EQ, RATE, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, RSQ, SEARCH, SECOND, SIGN, SIN, SINGLE, SINH, SLOPE, SMALL, SORT, SORTBY, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SWITCH, T, T.DIST, TAN, TANH, TEXT, TEXTJOIN, TEXTSPLIT, TIME, TIMEVALUE, TODAY, TRANSPOSE, TRIM, TRUE, TRUNC, TYPE, UNICODE, UNIQUE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC.
Note that we are continuously adding more formulas based on user feedback. You can send your request for a feature or formula here.
