Working with Formulas
26 Aug 202424 minutes to read
Formulas are entries in Excel that have equations, by which values are calculated. A typical formula might contain cell references, constants, and even functions.
Enable and Disable Calculation
To perform calculation in an Excel workbook, it is recommended to invoke EnableSheetCalculations method of IWorksheet. Enabling this method will initialize CalcEngine objects and retrieves calculated values of formulas in a worksheet.
The following code sample illustrates on how to enable worksheet formula calculations.
IWorksheet sheet = workbook.Worksheets[0];
//Formula calculation is enabled for the sheet
sheet.EnableSheetCalculations();
IWorksheet sheet = workbook.Worksheets[0];
//Formula calculation is enabled for the sheet
sheet.EnableSheetCalculations();
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Formula calculation is enabled for the sheet
sheet.EnableSheetCalculations()
On completion of worksheet calculation, it is recommended to invoke DisableSheetCalculations method of IWorksheet. This will dispose all the CalcEngine objects.
The following code sample illustrates on how to disable worksheet formula calculations.
IWorksheet sheet = workbook.Worksheets[0];
//Formula calculation is disabled for the sheet
sheet.DisableSheetCalculations();
IWorksheet sheet = workbook.Worksheets[0];
//Formula calculation is disabled for the sheet
sheet.DisableSheetCalculations();
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Formula calculation is disabled for the sheet
sheet.DisableSheetCalculations()
Writing a Formula
In a worksheet, formulas can be entered by using the Formula property of IRange instance. Following code example illustrates on how to write a formula.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting values to the cells
sheet.Range["A1"].Number = 10;
sheet.Range["B1"].Number = 10;
//Setting formula in the cell
sheet.Range["C1"].Formula = "=SUM(A1,B1)";
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting values to the cells
sheet.Range["A1"].Number = 10;
sheet.Range["B1"].Number = 10;
//Setting formula in the cell
sheet.Range["C1"].Formula = "=SUM(A1,B1)";
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Setting values to the cells
sheet.Range("A1").Number = 10
sheet.Range("B1").Number = 10
'Setting formula for the range
sheet.Range("C1").Formula = "=SUM(A1,B1)"
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to insert formula in Excel cell in C# is present on this GitHub page.
Formula with Cross-sheet References
XlsIO supports using formulas across worksheets. The following code shows how to apply formula with cross-sheet references.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create();
IWorksheet sheet = workbook.Worksheets[0];
//Setting formula for the range with cross-sheet reference
sheet.Range["C2"].Formula = "=SUM(Sheet2!B2,Sheet1!A2)";
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create();
IWorksheet sheet = workbook.Worksheets[0];
//Setting formula for the range with cross-sheet reference
sheet.Range["C2"].Formula = "=SUM(Sheet2!B2,Sheet1!A2)";
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create()
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Setting formula for the range with cross-sheet reference
sheet.Range("C2").Formula = "=SUM(Sheet2!B2,Sheet1!A2)"
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to insert formula in Excel cell with cross sheet reference in C# is present on this GitHub page.
Reading a Formula
Formulas are string values which can be accessed using Formula property of IRange. If a cell has formula, the Value property of IRange will also return the formula as string.
The following code shows how to read a formula.
//Returns the formula in C1 style notation
string formula = sheet["C1"].Formula;
//Returns the formula in C1 style notation
string formula = sheet["C1"].Formula;
'Returns the formula in C1 style notation
Dim formula as String = sheet("C1").Formula
Accessing a Calculated value
To evaluate formula, it is must to enable sheet calculation in prior. After enabling the sheet calculation, the formula can be evaluated using CalculatedValue of IRange, which returns a string value.
The following code shows how to access a calculated value.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();
//Returns the calculated value of a formula using the most current inputs
string calculatedValue = sheet["C1"].CalculatedValue;
sheet.DisableSheetCalculations();
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();
//Returns the calculated value of a formula using the most current inputs
string calculatedValue = sheet["A1"].CalculatedValue;
sheet.DisableSheetCalculations();
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
Dim sheet As IWorksheet = workbook.Worksheets(0)
sheet.EnableSheetCalculations()
'Returns the calculated value of a formula using the most current inputs
Dim calculatedValue As String = sheet("C1").CalculatedValue
sheet.DisableSheetCalculations()
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to access calculated value of a formula in C# is present on this GitHub page.
Apart from CalculatedValue property, the evaluated values can also be accessed as bool, DateTime and double data types. To obtain the updated values of these types, CalculatedValue property must be called in prior.
To know more about evaluated values, please refer IRange in API section.
The following code shows how to access calculated values in different types.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
//Previous Value '2'
sheet["E1"].Number = 3;
sheet.EnableSheetCalculations();
//It has formula 'ISEVEN(E1)'
//Returns the calculated value of a formula as Boolean
bool B1_PreviousValue = sheet["B1"].FormulaBoolValue;
string value = sheet.Range["B1"].CalculatedValue;
bool B1_LatestValue = sheet["B1"].FormulaBoolValue;
//It has formula 'TODAY()'
//Returns the calculated value of a formula as DateTime
DateTime C1_PreviousValue = sheet["C1"].FormulaDateTime;
value = sheet.Range["C1"].CalculatedValue;
DateTime C1_LatestValue = sheet["C1"].FormulaDateTime;
//It has formula '=E1'
//Returns the calculated value of a formula as double
double D1_PreviousValue = sheet["D1"].FormulaNumberValue;
value = sheet.Range["D1"].CalculatedValue;
double D1_LatestValue = sheet["D1"].FormulaNumberValue;
sheet.DisableSheetCalculations();
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
//Output
//B1_PreviousValue - true B1_LatestValue - false
//C1_PreviousValue - {3/27/2018 12:00:00 AM} C1_LatestValue - {3/28/2018 12:00:00 AM}
//D1_PreviousValue - 2.0 D1_LatestValue - 3.0
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
//Previous Value '2'
sheet["E1"].Number = 3;
sheet.EnableSheetCalculations();
//It has formula 'ISEVEN(E1)'
//Returns the calculated value of a formula as Boolean
bool B1_PreviousValue = sheet["B1"].FormulaBoolValue;
string value = sheet.Range["B1"].CalculatedValue;
bool B1_LatestValue = sheet["B1"].FormulaBoolValue;
//It has formula 'TODAY()'
//Returns the calculated value of a formula as DateTime
DateTime C1_PreviousValue = sheet["C1"].FormulaDateTime;
value = sheet.Range["C1"].CalculatedValue;
DateTime C1_LatestValue = sheet["C1"].FormulaDateTime;
//It has formula '=E1'
//Returns the calculated value of a formula as double
double D1_PreviousValue = sheet["D1"].FormulaNumberValue;
value = sheet.Range["D1"].CalculatedValue;
double D1_LatestValue = sheet["D1"].FormulaNumberValue;
sheet.DisableSheetCalculations();
workbook.SaveAs("Formula.xlsx");
}
//Output
//B1_PreviousValue - true B1_LatestValue - false
//C1_PreviousValue - {3/27/2018 12:00:00 AM} C1_LatestValue - {3/28/2018 12:00:00 AM}
//D1_PreviousValue - 2.0 D1_LatestValue - 3.0
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Previous Value '2'
sheet("E1").Number = 3
sheet.EnableSheetCalculations()
'It has formula 'ISEVEN(E1)'
'Returns the calculated value of a formula as Boolean
Dim B1_PreviousValue As Boolean = sheet("B1").FormulaBoolValue
Dim value As String = sheet.Range("B1").CalculatedValue
Dim B1_LatestValue As Boolean = sheet("B1").FormulaBoolValue
'It has formula 'TODAY()'
'Returns the calculated value of a formula as DateTime
Dim C1_PreviousValue As DateTime = sheet("C1").FormulaDateTime
value = sheet.Range("C1").CalculatedValue
Dim C1_LatestValue As DateTime = sheet("C1").FormulaDateTime
'It has formula '=E1'
'Returns the calculated value of a formula as double
Dim D1_PreviousValue As Double = sheet("D1").FormulaNumberValue
value = sheet.Range("D1").CalculatedValue
Dim D1_LatestValue As Double = sheet("D1").FormulaNumberValue
sheet.DisableSheetCalculations()
workbook.SaveAs("Formula.xlsx")
End Using
'Output
'B1_PreviousValue - true B1_LatestValue - false
'C1_PreviousValue - {3/27/2018 12:00:00 AM} C1_LatestValue - {3/28/2018 12:00:00 AM}
'D1_PreviousValue - 2.0 D1_LatestValue - 3.0
A complete working example to access calculated value of a formula in different forms in C# is present on this GitHub page.
NOTE
Calculated value for external reference formulas can also be evaluated in XlsIO.
Applying Argument Separators Based on Cultures
Formula separators vary for different cultures, and exceptions can be thrown in such cases. This can be overcome by setting the separators by using SetSeparators method of IWorkbook.
Following code illustrates on how to change the formula separators.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
//Setting the argument separator
workbook.SetSeparators(';', ',');
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Setting the argument separator
workbook.SetSeparators(';', ',');
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
'Setting the argument separator
workbook.SetSeparators(";", ",")
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to add argument separator in C# is present on this GitHub page.
Array of Formula
Array formula is a special type of formula in Excel. It works with an array or series of data values, rather than a single data value which can be done through FormulaArray property of IRange instance.
Following code shows how an array of values from Named Range is used for computation.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assign array formula
sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";
//Adding a named range for the range A1 to D1
sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]);
//Assign formula array with named range
sheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";
//Saving the workbook as stream
FileStream stream = new FileStream("FormulaArray.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Assign array formula
sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";
//Adding a named range for the range A1 to D1
sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]);
//Assign formula array with named range
sheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";
string fileName = "FormulaArray.xlsx";
workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Assign array formula
sheet.Range("A1:D1").FormulaArray = "{1,2,3,4}"
'Adding a named range for the range A1 to D1
sheet.Names.Add("ArrayRange", sheet.Range("A1:D1"))
'Assign formula array with named range
sheet.Range("A2:D2").FormulaArray = "ArrayRange+100"
workbook.SaveAs("FormulaArray.xlsx")
End Using
A complete working example to add formula array in C# is present on this GitHub page.
Incremental Formula
The relative cell references in the formulas are automatically incremented by 1, when you fill formulas down a column or across a row by enabling the EnableIncrementalFormula property of IApplication interface.
The below code snippet shows how to increment the cell references by 1 in the formulas.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Enables the incremental formula to updates the reference in cell
application.EnableIncrementalFormula = true;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Formula are automatically increments by one for the range of cells
sheet["A1:A5"].Formula = "=B1+C1";
//Saving the workbook as stream
FileStream stream = new FileStream("IncrementalFormula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Enables the incremental formula to updates the reference in cell
application.EnableIncrementalFormula = true;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Formula are automatically increments by one for the range of cells
sheet["A1:A5"].Formula = "=B1+C1";
workbook.SaveAs("IncrementalFormula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
'Enables the incremental formula to updates the reference in cell
application.EnableIncrementalFormula = True
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Formula are automatically increments by one for the range of cells
sheet("A1:A5").Formula = "=B1+C1"
workbook.SaveAs("IncrementalFormula.xlsx")
End Using
A complete working example to add incremental formula in C# is present on this GitHub page.
External Formula
XlsIO supports to write and preserve external formula.
External formula is the one which refers to a cell or a range of cells or a defined named range from outside the current worksheet/workbook. The main benefit of using an external reference is that whenever the referenced cell(s) in another worksheet changes, the value returned by the external cell reference is automatically updated.
Following code illustrates the insertion of a formula that refers to cell ‘A1’ in another workbook which is enclosed in a square bracket [One.xlsx].
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Write an external formula value
sheet.Range["C1"].Formula = "[One.xlsx]Sheet1!$A$1*5";
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Write an external formula value
sheet.Range["C1"].Formula = "[One.xlsx]Sheet1!$A$1*5";
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Write an external formula value
sheet.Range("C1").Formula = "[One.xlsx]Sheet1!$A$1*5"
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to add external formula in C# is present on this GitHub page.
NOTE
Links are updated automatically in Microsoft Excel to view the result for the preceding code.
Calculated Column
XlsIO supports to create, access and modify calculated column in a table. When a formulas is entered in a table column, Excel creates a calculated column. This column uses a single formula that’s automatically extended to additional rows in the column and adjusted for each row. Enter a formula once, and Excel immediately fills it down to create the calculated column.
Also, XlsIO supports structured reference in calculated column in table from Excel 2013.
The following code snippet illustrates how to create a calculated column.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create Table with data in the given range
IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:D3"]);
//Create data
worksheet[1, 1].Text = "Products";
worksheet[1, 2].Text = "Rate";
worksheet[1, 3].Text = "Quantity";
worksheet[1, 4].Text = "Total";
worksheet[2, 1].Text = "Item1";
worksheet[2, 2].Number = 200;
worksheet[2, 3].Number = 2;
worksheet[3, 1].Text = "Item2";
worksheet[3, 2].Number = 200;
worksheet[3, 3].Number = 2;
//Set table formula
table.Columns[3].CalculatedFormula = "SUM(20,[Rate]*[Quantity])";
//Saving the workbook as stream
FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Create Table with data in the given range
IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:D3"]);
//Create data
worksheet[1, 1].Text = "Products";
worksheet[1, 2].Text = "Rate";
worksheet[1, 3].Text = "Quantity";
worksheet[1, 4].Text = "Total";
worksheet[2, 1].Text = "Item1";
worksheet[2, 2].Number = 200;
worksheet[2, 3].Number = 2;
worksheet[3, 1].Text = "Item2";
worksheet[3, 2].Number = 200;
worksheet[3, 3].Number = 2;
//Set table formula
table.Columns[3].CalculatedFormula = "SUM(20,[Rate]*[Quantity])";
string fileName = "Output.xlsx";
workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Create Table with data in the given range
Dim table As IListObject = worksheet.ListObjects.Create("Table1", worksheet("A1:D3"))
'Create data
worksheet(1, 1).Text = "Products"
worksheet(1, 2).Text = "Rate"
worksheet(1, 3).Text = "Quantity"
worksheet(1, 4).Text = "Total"
worksheet(2, 1).Text = "Item1"
worksheet(2, 2).Number = 200
worksheet(2, 3).Number = 2
worksheet(3, 1).Text = "Item2"
worksheet(3, 2).Number = 200
worksheet(3, 3).Number = 2
'Set table formula
table.Columns(3).CalculatedFormula = "SUM(20,[Rate]*[Quantity])"
Dim fileName As String = "Output.xlsx"
workbook.SaveAs(fileName)
End Using
A complete working example for calculated column in C# is present on this GitHub page.
Supported Functions
XlsIO supports all the formulas supported by Excel. Whereas, below is the list of functions that XlsIO performs calculation and returns a calculated value.
Functions |
Description |
---|---|
ABS |
Returns the absolute value of a number |
ACOS |
Returns the arccosine of a number |
ACOSH |
Returns the inverse hyperbolic cosine of a number |
ADDRESS |
Returns a reference as text to a single cell in a worksheet |
AND |
Returns TRUE if all of its arguments are TRUE |
AREAS |
Returns the number of areas in a reference |
ARRAYTOTEXT |
Returns the text representation of an array. Calculating this formula result is not supported in XlsIO |
ASC |
Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters |
ASIN |
Returns the arcsine of a number |
ASINH |
Returns the inverse hyperbolic sine of a number |
ATAN |
Returns the arctangent of a number |
ATAN2 |
Returns the arctangent from x- and y-coordinates |
ATANH |
Returns the inverse hyperbolic tangent of a number |
AVEDEV |
Returns the average of the absolute deviations of data points from their mean |
AVERAGE |
Returns the average of its arguments |
AVERAGEA |
Returns the average of its arguments, including numbers, text, and logical values |
AVERAGEIF |
Returns the average (arithmetic mean) of all the cells in a range that meet a given criterion |
AVERAGEIFS |
Returns the average (arithmetic mean) of all cells that meet multiple criteria |
BESSELI |
Returns the modified Bessel function In(x) |
BESSELJ |
Returns the Bessel function Jn(x) |
BESSELK |
Returns the modified Bessel function Kn(x) |
BESSELY |
Returns the Bessel function Yn(x) |
BIN2DEC |
Converts a binary number to decimal |
BIN2HEX |
Converts a binary number to hexadecimal |
BIN2OCT |
Converts a binary number to octal |
BINOMDIST |
Returns the individual term binomial distribution probability |
BYCOL |
Applies a LAMBDA to each column and returns an array of the results |
BYROW |
Applies a LAMBDA to each row and returns an array of the results |
CEILING |
Rounds a number to the nearest integer or to the nearest multiple of significance |
CELL |
Returns information about the formatting, location, or contents of a cell |
CHAR |
Returns the character specified by the code number |
CHIDIST |
Returns the one-tailed probability of the chi-squared distribution |
CHIINV |
Returns the inverse of the one-tailed probability of the chi-squared distribution |
CHITEST |
Returns the test for independence |
CHOOSE |
Chooses a value from a list of values |
CHOOSECOLS |
Returns specified columns from an array |
CHOOSEROWS |
Returns specified rows from an array |
CLEAN |
Removes all non-printable characters from text |
CODE |
Returns a numeric code for the first character in a text string |
COLUMN |
Returns the column number of a reference |
COLUMNS |
Returns the number of columns in a reference |
COMBIN |
Returns the number of combinations for a given number of objects |
COMPLEX |
Converts real and imaginary coefficients into a complex number |
CONCAT |
Combines the text from multiple ranges and/or strings |
CONCATENATE |
Joins several text items into one text item |
CONFIDENCE |
Returns the confidence interval for a population mean |
CONVERT |
Converts a number from one measurement system to another |
CORREL |
Returns the correlation coefficient between two data sets |
COS |
Returns the cosine of a number |
COSH |
Returns the hyperbolic cosine of a number |
COUNT |
Counts how many numbers are in the list of arguments |
COUNTA |
Counts how many values are in the list of arguments |
COUNTBLANK |
Counts the number of blank cells within a range |
COUNTIF |
Counts the number of non-blank cells within a range that meet the given criteria |
COVAR |
Returns covariance, the average of the products of paired deviations |
CRITBINOM |
Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value |
CUMIPMT |
Returns the cumulative interest paid between two periods |
CUMPRINC |
Returns the cumulative principal paid on a loan between two periods |
DATE |
Returns the serial number of a particular date |
DATEVALUE |
Converts a date in the form of text to a serial number |
DAY |
Converts a serial number to a day of the month |
DAYS360 |
Calculates the number of days between two dates based on a 360-day year |
DB |
Returns the depreciation of an asset for a specified period by using the fixed-declining balance method |
DDB |
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify |
DEC2BIN |
Converts a decimal number to binary |
DECHEX |
Converts a decimal number to hexadecimal |
DEC2OCT |
Converts a decimal number to octal |
DEGREES |
Converts radians to degrees |
DELTA |
Tests whether two values are equal |
DEVSQ |
Returns the sum of squares of deviations |
DISC |
Returns the discount rate for a security |
DOLLAR |
Converts a number to text, using the $ (dollar) currency format |
DOLLARDE |
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number |
DOLLARFR |
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction |
DURATION |
Returns the annual duration of a security with periodic interest payments |
EDATE |
Returns the serial number of the date that is the indicated number of months before or after the start date |
EFFECT |
Returns the effective annual interest rate |
EOMONTH |
Returns the serial number of the last day of the month before or after a specified number of months |
ERF |
Returns the error function |
ERFC |
Returns the complementary error function |
ERROR.TYPE |
Returns a number corresponding to an error type |
EVEN |
Rounds a number up to the nearest even integer |
EXACT |
Checks to see if two text values are identical |
EXP |
Returns e raised to the power of a given number |
EXPONDIST |
Returns the exponential distribution |
FACT |
Returns the factorial of a number |
FACTDOUBLE |
Returns the double factorial of a number |
FDIST |
Returns the F probability distribution |
FIND, FINDB |
Finds one text value within another (case-sensitive) |
FINV |
Returns the inverse of the F probability distribution |
FISHER |
Returns the Fisher transformation |
FISHER |
Returns the inverse of the Fisher transformation |
FIXED |
Formats a number as text with a fixed number of decimals |
FLOOR |
Rounds a number down, toward zero |
FORECAST |
Returns a value along a linear trend |
FV |
Returns the future value of an investment |
FVSCHEDULE |
Returns the future value of an initial principal after applying a series of compound interest rates |
GAMMADIST |
Returns the gamma distribution |
GAMMAINV |
Returns the inverse of the gamma cumulative distribution |
GAMMALIN |
Returns the natural logarithm of the gamma function, Γ(x) |
GCD |
Returns the greatest common divisor |
GEOMEAN |
Returns the geometric mean |
GESTEP |
Tests whether a number is greater than a threshold value |
GROWTH |
Returns values along an exponential trend |
HARMEAN |
Returns the harmonic mean |
HEX2BIN |
Converts a hexadecimal number to binary |
HEX2DEC |
Converts a hexadecimal number to decimal |
HEX2OCT |
Converts a hexadecimal number to octal |
HLOOKUP |
Looks in the top row of an array and returns the value of the indicated cell |
HOUR |
Converts a serial number to an hour |
HYPERLINK |
Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet |
HYPGEOMDIST |
Returns the hypergeometric distribution |
IF |
Specifies a logical test to perform |
IFERROR |
Returns a specified value if a formula evaluates to an error. |
IFS |
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition |
IMABS |
Returns the absolute value (modulus) of a complex number |
IMAGINARY |
Returns the imaginary coefficient of a complex number |
IMARGUMENT |
Returns the argument theta, an angle expressed in radians |
IMCONJUGATE |
Returns the complex conjugate of a complex number |
IMCOS |
Returns the cosine of a complex number |
IMDIV |
Returns the quotient of two complex numbers |
IMEXP |
Returns the exponential of a complex number |
IMLN |
Returns the natural logarithm of a complex number |
IMLOG10 |
Returns the base-10 logarithm of a complex number |
IMLOG2 |
Returns the base-2 logarithm of a complex number |
IMPOWER |
Returns a complex number raised to an integer power |
IMPRODUCT |
Returns the product of from 2 to 29 complex numbers |
IMREAL |
Returns the real coefficient of a complex number |
IMSIN |
Returns the sine of a complex number |
IMSQRT |
Returns the square root of a complex number |
IMSUB |
Returns the difference between two complex numbers |
IMSUM |
Returns the sum of complex numbers |
INDEX |
Uses an index to choose a value from a reference or array |
INDIRECT |
Returns a reference indicated by a text value |
INFO |
Returns information about the current operating environment |
INT |
Rounds a number down to the nearest integer |
INTERCEPT |
Returns the intercept of the linear regression line |
INTRATE |
Returns the interest rate for a fully invested security |
IPMT |
Returns the interest payment for an investment for a given period |
IRR |
Returns the internal rate of return for a series of cash flows |
ISBLANK |
Returns TRUE if the value is blank |
ISERR |
Returns TRUE if the value is any error value except #N/A |
ISERROR |
Returns TRUE if the value is any error value |
ISEVEN |
Returns TRUE if the number is even |
ISLOGICAL |
Returns TRUE if the value is a logical value |
ISAN |
Returns TRUE if the value is the #N/A error value |
ISNONTEXT |
Returns TRUE if the value is not text |
ISNUMBER |
Returns TRUE if the value is a number |
ISODD |
Returns TRUE if the number is odd |
ISMPT |
Calculates the interest paid during a specific period of an investment |
ISREF |
Returns TRUE if the value is a reference |
ISTEXT |
Returns TRUE if the value is text |
KURT |
Returns the kurtosis of a data set |
LAMBDA |
Allows to use own formula parameters and logic |
LARGE |
Returns the k-th largest value in a data set |
LCM |
Returns the least common multiple |
LEFT, LEFTB |
Returns the leftmost characters from a text value |
LEN, LENB |
Returns the number of characters in a text string |
LET |
Returns the result of a formula that can use variables. Calculating this formula result is not supported in XlsIO |
LN |
Returns the natural logarithm of a number |
LOG |
Returns the logarithm of a number to a specified base |
LOG10 |
Returns the base-10 logarithm of a number |
LOGEST |
Returns the parameters of an exponential trend |
LOGINV |
Returns the inverse of the log-normal distribution |
LOGNORMDIST |
Returns the cumulative log-normal distribution |
LOOKUP |
Looks up values in a vector or array |
LOWER |
Converts text to lowercase |
MATCH |
Looks up values in a reference or array |
MAX |
Returns the maximum value in a list of arguments |
MAXA |
Returns the maximum value in a list of arguments, including numbers, text, and logical values |
MAXIFS |
Returns the maximum value among cells specified by a given set of conditions or criteria |
MDETERM |
Returns the matrix determinant of an array |
MEDIAN |
Returns the median of the given numbers |
MID, MIDB |
Returns a specific number of characters from a text string starting at the position you specify |
MIN |
Returns the minimum value in a list of arguments |
MINA |
Returns the smallest value in a list of arguments, including numbers, text, and logical values |
MINIFS |
Returns the minimum value among cells specified by a given set of conditions or criteria |
MINUTE |
Converts a serial number to a minute |
MINVERSE |
Returns the matrix inverse of an array |
MIRR |
Returns the internal rate of return where positive and negative cash flows are financed at different rates |
MMULT |
Returns the matrix product of two arrays |
MOD |
Returns the remainder from division |
MODE |
Returns the most common value in a data set |
MMONTH |
Converts a serial number to a month |
MROUND |
Returns a number rounded to the desired multiple |
MULTINOMINAL |
Returns the multinomial of a set of numbers |
N |
Returns a value converted to a number |
NA |
Returns the error value #N/A |
NEGBINOMDIST |
Returns the negative binomial distribution |
NETWORKDAYS |
Returns the number of whole workdays between two dates |
NORMDIST |
Returns the normal cumulative distribution |
NORMINV |
Returns the inverse of the normal cumulative distribution |
NORMSDIST |
Returns the standard normal cumulative distribution |
NORMSINV |
Returns the inverse of the standard normal cumulative distribution |
NOT |
Reverses the logic of its argument |
NOW |
Returns the serial number of the current date and time |
NPER |
Returns the number of periods for an investment |
NPV |
Returns the net present value of an investment based on a series of periodic cash flows and a discount rate |
OCT2BIN |
Converts an octal number to binary |
OCT2DEC |
Converts an octal number to decimal |
OCT2HEX |
Converts an octal number to hexadecimal |
ODD |
Rounds a number up to the nearest odd integer |
OFFSET |
Returns a reference offset from a given reference |
OR |
Returns TRUE if any argument is TRUE |
PEARSON |
Returns the Pearson product moment correlation coefficient |
PERCENTILE |
Returns the k-th percentile of values in a range |
PERCENTRANK |
Returns the percentage rank of a value in a data set |
PERMUT |
Returns the number of permutations for a given number of objects |
PI |
Returns the value of pi |
PMT |
Returns the periodic payment for an annuity |
POISSON |
Returns the Poisson distribution |
POWER |
Returns the result of a number raised to a power |
PPMT |
Returns the payment on the principal for an investment for a given period |
PROB |
Returns the probability that values in a range are between two limits |
PRODUCT |
Multiplies its arguments |
PROPER |
Capitalizes the first letter in each word of a text value |
PV |
Returns the present value of an investment |
QUARTILE |
Returns the quartile of a data set |
QUOTIENT |
Returns the integer portion of a division |
RADIANS |
Converts degrees to radians |
RAND |
Returns a random number between 0 and 1 |
RANDBETWEEN |
Returns a random number between the numbers you specify |
RANK |
Returns the rank of a number in a list of numbers |
RATE |
Returns the interest rate per period of an annuity |
RECEIVED |
Returns the amount received at maturity for a fully invested security |
REPLACE, REPLACEB |
Replaces characters within text |
REPT |
Repeats text a given number of times |
RIGHT, RIGHTB |
Returns the rightmost characters from a text value |
ROMAN |
Converts an Arabic numeral to roman, as text |
ROUND |
Rounds a number to a specified number of digits |
ROUNDDOWN |
Rounds a number down, toward zero |
ROUNDUP |
Rounds a number up, away from zero |
ROW |
Returns the row number of a reference |
ROWS |
Returns the number of rows in a reference |
RSQ |
Returns the square of the Pearson product moment correlation coefficient |
SEARCH, SEARCHB |
Finds one text value within another (not case-sensitive) |
SECOND |
Converts a serial number to a second |
SERIESSUM |
Returns the sum of a power series based on the formula |
SIGN |
Returns the sign of a number |
SIN |
Returns the sine of the given angle |
SINH |
Returns the hyperbolic sine of a number |
SKEW |
Returns the skewness of a distribution |
SLN |
Returns the straight-line depreciation of an asset for one period |
SLOPE |
Returns the slope of the linear regression line |
SORT |
Returns the cell range values in ascending or descending order |
SMALL |
Returns the k-th smallest value in a data set |
SQRT |
Returns a positive square root |
SQRTPI |
Returns the square root of (number * pi) |
STANDARDIZE |
Returns a normalized value |
STDEV |
Estimates standard deviation based on a sample |
STDEVA |
Estimates standard deviation based on a sample, including numbers, text, and logical values |
STDEVP |
Calculates standard deviation based on the entire population |
STDEVPA |
Calculates standard deviation based on the entire population, including numbers, text, and logical values |
STEYX |
Returns the standard error of the predicted y-value for each x in the regression |
SUBSTITUTE |
Substitutes new text for old text in a text string |
SUBTOTAL |
Returns a subtotal in a list or database |
SUM |
Adds its arguments |
SUMIF |
Adds the cells specified by a given criteria |
SUMPRODUCT |
Returns the sum of the products of corresponding array components |
SUMSQ |
Returns the sum of the squares of the arguments |
SUMX2MY2 |
Returns the sum of the difference of squares of corresponding values in two arrays |
SUMX2PY2 |
Returns the sum of the sum of squares of corresponding values in two arrays |
SUMXMY2 |
Returns the sum of squares of differences of corresponding values in two arrays |
SWITCH |
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. |
SYD |
Returns the sum-of-years'digits depreciation of an asset for a specified period |
T |
Converts its arguments to text |
TAN |
Returns the tangent of a number |
TANH |
Returns the hyperbolic tangent of a number |
TEXT |
Formats a number and converts it to text |
TEXTBEFORE |
Returns text that occurs before a given character |
TEXTJOIN |
Combines the text from multiple ranges and/or strings with a delimiter you specify between each text value that will be combined |
TEXTSPLIT |
Splits the cell text across rows or columns |
TIME |
Returns the serial number of a particular time |
TIMEVALUE |
Converts a time in the form of text to a serial number |
TOCOL |
Transforms an array into a single column |
TODAY |
Returns the serial number of today's date |
TOROW |
Transforms an array into a single row |
TRANSPORSE |
Returns the transpose of an array |
TRIM |
Removes spaces from text |
TRIMMEAN |
Returns the mean of the interior of a data set |
TRUNC |
Truncates a number to an integer |
TYPE |
Returns a number indicating the data type of a value |
UNIQUE |
Returns the list of unique values present inside a list or range. Calculating the formula result in XlsIO is not supported. |
UPPER |
Converts text to uppercase |
VALUE |
Converts a text argument to a number |
VALUETOTEXT |
Returns the text from any specified value. Calculating this formula result is not supported in XlsIO. |
VAR |
Estimates variance based on a sample |
VARA |
Estimates variance based on a sample, including numbers, text, and logical values |
VARP |
Calculates variance based on the entire population |
VARPA |
Calculates variance based on the entire population, including numbers, text, and logical values |
VDB |
Returns the depreciation of an asset for a specified or partial period by using a declining balance method |
VLOOKUP |
Looks in the first column of an array and moves across the row to return the value of a cell |
WEEKDAY |
Converts a serial number to a day of the week |
WEEKNUM |
Converts a serial number to a number representing where the week falls numerically with a year |
WEIBULL |
Returns the Weibull distribution |
WORKDAY |
Returns the serial number of the date before or after a specified number of workdays |
XIRR |
Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic |
XLOOKUP |
Returns the value corresponding to the first match it finds else returns the next approximate match. Calculating this formula result is not supported in XlsIO. |
XMATCH |
Returns the position of a value in a list, table or cell range. Calculating this formula result is not supported in XlsIO. |
YEAR |
Converts a serial number to a year |
YEARFRAC |
Returns the year fraction representing the number of whole days between start_date and end_date |
ZTEST |
Returns the one-tailed probability-value of a z-test |
FALSE |
Returns the logical value FALSE |
TRUE |
Returns the logical value TRUE |
Add-in Functions
Add-ins are mini-programs or custom functions that enhance the feature set of the Microsoft Excel application. These Add-ins can be accessed by registering it at first from Excel and refer it using XlsIO. For more details on adding AddIn functions, see Add or remove Add-ins
The following code illustrates on how to include and access Add-ins in XlsIO.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IAddInFunctions unknownFunctions = workbook.AddInFunctions;
//Adding the XLAM file reference to AddIn functions
//NOTE: The add-in name must be same as the function name
unknownFunctions.Add("AddInFunction");
//Use the function. The expected result is 30
sheet.Range["A3"].Formula = "AddInFunction(10,20)";
//Saving the workbook as stream
FileStream stream = new FileStream("AddIn.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
//Step1: Create AddIn (AddIn.xlam)
//AddIn.xlam file has the below custom function
//Function AddInFunction(firstValue As Integer, secondValue As Integer) As Integer
//
//Dim result As Integer
//result = firstValue + secondValue
//AddInFunction = result
//
//End Function
//Step2: Register the AddIn in Excel by adding the above file to Excel Application by locating the .xlam file through the menu (Developer -> Add-ins -> Browse)
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
IAddInFunctions unknownFunctions = workbook.AddInFunctions;
//Adding the XLAM file reference to AddIn functions
//NOTE: The add-in name must be same as the function name
unknownFunctions.Add("D:/AddIn.xlam", "AddInFunction");
//Use the function. The expected result is 30
sheet.Range["A3"].Formula = "AddInFunction(10,20)";
string fileName = "AddIn.xlsx";
workbook.Version = ExcelVersion.Excel2010;
workbook.SaveAs(fileName);
}
'Step1: Create AddIn (AddIn.xlam)
'AddIn.xlam file has the below custom function
'Function AddInFunction(firstValue As Integer, secondValue As Integer) As Integer
'
'Dim result As Integer
'result = firstValue + secondValue
'AddInFunction = result
'
'End Function
'Step2: Register the AddIn in Excel by adding the above file to Excel Application by locating the .xlam file through the menu (Developer -> Add-ins -> Browse)
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
Dim unknownFunctions As IAddInFunctions = workbook.AddInFunctions
'Adding the XLAM file reference to AddIn functions
'NOTE: The add-in name must be same as the function name
unknownFunctions.Add("D:/AddIn.xlam", "AddInFunction")
'Use the function. The expected result is 30
sheet.Range("A3").Formula = "AddInFunction(10,20)"
Dim fileName As String = "AddIn.xlsx"
workbook.Version = ExcelVersion.Excel2010
workbook.SaveAs(fileName)
End Using
NOTE
If you move the file to another computer, or distribute it, the workbook will expect to find the same Add-In, in the same place, on their computers. But, if the Add-In is moved or deleted from the computer, the workbook won’t be able to find it, and your code won’t work. Make sure that the Add-In is accessed by locating the .xlam file through the menu (Developer -> Add-ins -> Browse).
Defined Names
Cell ranges can be defined by names to perform formula calculation. This section explains about creating named ranges and accessing them from workbook or worksheet levels.
The following code shows how to define a named range from workbook level.
//Defining a name in workbook level for the cell A1
IName name = workbook.Names.Add("BookLevelName");
name.RefersToRange = worksheet.Range["A1"];
//Defining a name in workbook level for the cell A1
IName name = workbook.Names.Add("BookLevelName");
name.RefersToRange = worksheet.Range["A1"];
'Defining a name in workbook level for the cell A1
Dim name As IName = workbook.Names.Add("BookLevelName")
name.RefersToRange = worksheet.Range("A1")
The following code shows how to define a named range from worksheet level.
//Defining a name in worksheet level for the cell B1
IName name = worksheet.Names.Add("SheetLevelName");
name.RefersToRange = worksheet.Range["B1"];
//Defining a name in worksheet level for the cell B1
IName name = worksheet.Names.Add("SheetLevelName");
name.RefersToRange = worksheet.Range["B1"];
'Defining a name in worksheet level for the cell B1
Dim name As IName = worksheet.Names.Add("SheetLevelName")
name.RefersToRange = worksheet.Range("B1")
Named Ranges in Formulas
Following code example illustrates how to create workbook-level named ranges and use it in formulas.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Defining a name in workbook level for the cell A1
IName name1 = workbook.Names.Add("One");
name1.RefersToRange = sheet.Range["A1"];
//Defining a name in workbook level for the cell B1
IName name2 = workbook.Names.Add("Two");
name2.RefersToRange = sheet.Range["B1"];
//Formula using defined names
sheet.Range["C1"].Formula = "=SUM(One,Two)";
//Saving the workbook as stream
FileStream stream = new FileStream("Formula.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Defining a name in workbook level for the cell A1
IName name1 = workbook.Names.Add("One");
name1.RefersToRange = sheet.Range["A1"];
//Defining a name in workbook level for the cell B1
IName name2 = workbook.Names.Add("Two");
name2.RefersToRange = sheet.Range["B1"];
//Formula using defined names
sheet.Range["C1"].Formula = "=SUM(One,Two)";
workbook.SaveAs("Formula.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Defining a name in workbook level for the cell A1
Dim name1 As IName = workbook.Names.Add("One")
name1.RefersToRange = sheet.Range("A1")
'Defining a name in workbook level for the cell B1
Dim name2 As IName = workbook.Names.Add("Two")
name2.RefersToRange = sheet.Range("B1")
'Formula using defined names
sheet.Range("C1").Formula = "=SUM(One,Two)"
workbook.SaveAs("Formula.xlsx")
End Using
A complete working example to use named ranges in formulas in C# is present on this GitHub page.
Deleting Named Ranges
Named ranges defined in workbook and worksheet levels can be deleted in different ways. The following code shows the possibilities of deleting named ranges.
//Deleting named range object
IName name = workbook.Names[0];
name.Delete();
//Deleting named range from workbook
workbook.Names["BookLevelName"].Delete();
//Deleting named range from worksheet
sheet.Names["SheetLevelName"].Delete();
//Deleting named range object
IName name = workbook.Names[0];
name.Delete();
//Deleting named range from workbook
workbook.Names["BookLevelName"].Delete();
//Deleting named range from worksheet
sheet.Names["SheetLevelName"].Delete();
'Deleting named range object
Dim name As IName = workbook.Names(0)
name.Delete()
'Deleting named range from workbook
workbook.Names("BookLevelName").Delete()
'Deleting named range from worksheet
sheet.Names("SheetLevelName").Delete()
A complete working example to delete named ranges in C# is present on this GitHub page.
Formula Auditing
Microsoft Excel constantly checks in the background for potential errors in your worksheets, when open. If an error is located (or, at the least, what Excel thinks is an error), then the cell is “flagged” with a small green triangle in the upper-left corner of the cell. Auditing a formula helps to identify the error in it.
In certain cases, these errors can be ignored so that the error will not appear in further error checks. The IgnoreErrorOptions property of IRange manages different types of errors checks, for example numbers stored as text, formula calculation errors and validation errors.
Following code illustrates on how to ignore or set error indicators.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Sets warning if number is entered as text.
sheet.Range["A2:D2"].IgnoreErrorOptions = ExcelIgnoreError.NumberAsText;
//Ignores all the error warnings.
sheet.Range["A3"].IgnoreErrorOptions = ExcelIgnoreError.None;
//Saving the workbook as stream
FileStream file = new FileStream("FormulaAuditing.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(file);
file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet sheet = workbook.Worksheets[0];
//Sets warning if number is entered as text
sheet.Range["A2:D2"].IgnoreErrorOptions = ExcelIgnoreError.NumberAsText;
//Ignores all the error warnings
sheet.Range["A3"].IgnoreErrorOptions = ExcelIgnoreError.None;
workbook.SaveAs("FormulaAuditing.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Sets warning if number is entered as text
sheet.Range("A2:D2").IgnoreErrorOptions = ExcelIgnoreError.NumberAsText
'Ignores all the error warnings
sheet.Range("A3").IgnoreErrorOptions = ExcelIgnoreError.None
workbook.SaveAs("FormulaAuditing.xlsx")
End Using
A complete working example to ignore error in C# is present on this GitHub page.
Calculation Engine
Essential Calculate is now (from v9.1.x.x) integrated with Essential XlsIO, and thus makes it possible to calculate formulas entered at runtime without any additional references or packages.
NOTE
Do not add reference to Syncfusion.Calculate.Base. It will throw conflict errors as these are already integrated with XlsIO.
NOTE
Only the formulas that are supported by Calculate engine can be calculated at runtime using Essential XlsIO.
Calculate Options
Calculate engines provides certain options like calculation modes, Recalculate before Save and Enable iterations to perform specific calculation.
Calculation Modes
There are various calculation modes that enable users to customize formula calculations according to their needs. They are:
- Automatic
- Automatic except for Data Tables
- Manual
Following code illustrates on how to set calculation mode in XlsIO.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting calculation mode for a workbook
workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Manual;
//Saving the workbook as stream
FileStream stream = new FileStream("CalculationMode.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting calculation mode for a workbook
workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Manual;
workbook.SaveAs("CalculationMode.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Setting calculation mode for a workbook
workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Manual
workbook.SaveAs("CalculationMode.xlsx")
End Using
A complete working example of calculation modes in C# is present on this GitHub page.
Recalculate Before Save
In Manual mode, this option controls whether Microsoft Excel should recalculate the workbook as a part of Save process. This option can be set through RecalcOnSave property of ICalculationOptions interface.
ICalculationOptions calcOptions = workbook.CalculationOptions;
//Set RecalcOnSave to false to avoid re calculation of workbook while saving
calcOptions.RecalcOnSave = false;
ICalculationOptions calcOptions = workbook.CalculationOptions;
//Set RecalcOnSave to false to avoid re calculation of workbook while saving
calcOptions.RecalcOnSave = false;
Dim calcOptions As ICalculationOptions = workbook.CalculationOptions
'Set RecalcOnSave to false to avoid re calculation of workbook while saving
calcOptions.RecalcOnSave = False
Iteration
Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. If a formula refers back to one of its own cells, it is must determine how many times the formula should recalculate.
Iteration settings will control the maximum number of iteration and the amount of acceptable change. By default, IsIterationEnabled is false, so that Excel does not try to solve accidental circular references.
Following code snippet illustrates how to set the Iterations.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting iteration
workbook.CalculationOptions.IsIterationEnabled = true;
//Number of times to recalculate
workbook.CalculationOptions.MaximumIteration = 99;
//Number of acceptable changes
workbook.CalculationOptions.MaximumChange = 40;
//Saving the workbook as stream
FileStream stream = new FileStream("Iteration.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(stream);
stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Setting iteration
workbook.CalculationOptions.IsIterationEnabled = true;
//Number of times to recalculate
workbook.CalculationOptions.MaximumIteration = 99;
//Number of acceptable changes
workbook.CalculationOptions.MaximumChange = 40;
workbook.SaveAs("Iteration.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Xlsx
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorkbook = workbook.Worksheets(0)
'Setting Iteration
workbook.CalculationOptions.IsIterationEnabled = True
'Number of times to recalculate
workbook.CalculationOptions.MaximumIteration = 99
'Number of acceptable changes
workbook.CalculationOptions.MaximumChange = 40
workbook.SaveAs("Iteration.xlsx")
End Using
A complete working example of iteration in C# is present on this GitHub page.