Excel Formula Calculation in C# and VB.NET
GemBox.Spreadsheet supports a large set of Excel formula functions, including Mathematical, Statistical, Logical, Lookup, Financial and more. For a complete list visit the Supported formulas section. About ~200 of the most used Excel formulas are currently supported but note that we are continuously adding more based on user feedback.
The calculation engine also supports iterative calculations, named range references, external references, array formulas, and more. You can recalculate the formulas that are read from the Excel file and the ones you add at runtime using the ExcelCell.Formula property.
To calculate the results of the formulas, you need to call one of the following Calculate methods:
ExcelCell.Calculate- Calculates a single cell formula and its references, if any.ExcelWorksheet.Calculate- Calculates all of the cells in a single worksheet.ExcelFile.Calculate- Calculates all the cells in an Excel file.
After calculating the formulas, you can access the evaluated value using the ExcelCell.Value property.
The following example shows how you can use the GemBox.Spreadsheet calculation engine to calculate Excel cell formula values in C# and VB.NET.
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("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 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 provides ExcelFile.CalculationOptions with which you can specify various calculation engine options.
For instance, by setting the Here is a list of functions currently supported in GemBox.Spreadsheet:CalculationOptions.EnableCaching property to true you can improve the performance of formula calculations in some cases (only for Excel files that have repeated formula expressions that are always evaluated to the same result).Supported formulas
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, 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, 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, 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, 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, TIME, TIMEVALUE, TODAY, TRIM, TRUE, TRUNC, TYPE, UNIQUE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC.
