Excel Formula Calculation in C# and VB.NET
With GemBox.Spreadsheet, you can calculate a large range of different formulas - from a simplest formula that calculates the sum of two fields, up to a complex formula that includes brackets, a range of cells, cells that contain other formulas, and even an iterative calculation. GemBox.Spreadsheet currently supports 150+ most used formulas.
The following example shows how you can use the GemBox.Spreadsheet calculation engine to calculate Excel cell formula values in C# and VB.NET.
It shows how to set cell formulas and calculate their values by calling one of the 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.
The calculation result is stored in ExcelCell.Value.
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 = 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 ModuleSupported formulas
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, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS, DATE, DATEDIF, DATEVALUE, DAY, DAYS, DAYS360, DEGREES, DOLLAR, EDATE, EOMONTH, EVEN, EXACT, EXP, FACT, FALSE, FIND, FIXED, FLOOR, FV, 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, N, NA, NETWORKDAYS, NORMSDIST, NOT, NOW, NPV, ODD, OFFSET, OR, PI, PMT, POWER, PPMT, PRODUCT, PROPER, QUOTIENT, RADIANS, RAND, RANDBETWEEN, RANK, RANK.AVG, RANK.EQ, RATE, REPLACE, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, ROW, SEARCH, SECOND, SIGN, SIN, SINH, SLOPE, SMALL, SQRT, STDEV, STDEVA, STDEVP, STDEVPA, SUBSTITUTE, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, T, T.DIST, TAN, TANH, TEXT, TEXTJOIN, TIME, TIMEVALUE, TODAY, TRIM, TRUE, TRUNC, TYPE, UPPER, VALUE, VAR, VARA, VARP, VARPA, VLOOKUP, WEEKDAY, WEEKNUM, XIRR, XLOOKUP, XMATCH, YEAR, YEARFRAC.