Working with Formulas

29 Nov 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;

	#region Set Formula
	//Setting formula in the cell
	sheet.Range["C1"].Formula = "=SUM(A1,B1)";
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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(2);
	IWorksheet sheet1 = workbook.Worksheets[0];
	IWorksheet sheet2 = workbook.Worksheets[1];

	sheet1.Range["A2"].Value = "20";
	sheet2.Range["B2"].Value = "10";

	#region Cross Sheet Formula
	//Setting formula for the range with cross-sheet reference
	sheet1.Range["C2"].Formula = "=SUM(Sheet2!B2,Sheet1!A2)";
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet sheet = workbook.Worksheets[0];

	sheet.Range["A1"].Value = "10";
	sheet.Range["B1"].Value = "20";

	sheet.Range["C1"].Formula = "=A1+B1";

	#region Calculated Value
	sheet.EnableSheetCalculations();

	//Returns the calculated value of a formula using the most current inputs
	string calculatedValue = sheet["C1"].CalculatedValue;
	sheet.Range["C3"].Value = "Calculated Value of the formula in C1 calculated through XlsIO is : " + calculatedValue;
	
	sheet.DisableSheetCalculations();
	#endregion

	sheet.Range["C3"].AutofitColumns();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet sheet = workbook.Worksheets[0];

	sheet.Range["A1"].Value = "10";
	sheet.Range["B1"].Value = "20";

	sheet.Range["C1"].Formula = "=A1+B1";

	#region Calculated Value
	sheet.EnableSheetCalculations();

	//Returns the calculated value of a formula using the most current inputs
	string calculatedValue = sheet["C1"].CalculatedValue;
	sheet.Range["C3"].Value = "Calculated Value of the formula in C1 calculated through XlsIO is : " + calculatedValue;
	
	sheet.DisableSheetCalculations();
	#endregion

	sheet.Range["C3"].AutofitColumns();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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);

	#region Set Separators
	//Setting the argument separator
	workbook.SetSeparators(';', ',');
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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];

	#region Formula Array
	//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";
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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";

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/IncrementalFormula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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 = "[C:/Syncfusion/One.xlsx]Sheet1!$A$1*5";

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/ExternalFormula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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 = 300;
	worksheet[3, 3].Number = 3;

	//Set table formula
	table.Columns[3].CalculatedFormula = "SUM(20,[Rate]*[Quantity])";

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/CalculatedColumn.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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];
	sheet.Range["A1"].Value = "10";
	sheet.Range["B1"].Value = "20";

	//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)";

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Formula.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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(Path.GetFullPath(@"Data/InputTemplate.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;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/FormulaAuditing.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/CalculationMode.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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;

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Iteration.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.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.