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.