xlsgen > overview > Formulas

Formulas and conditional formattings bring dynamic capabilities to Excel documents. There are a number of aspects in Excel formulas :

• formulas can be written in English/French/German/Spanish/Italian/Portuguese (please contact support if you are interested in another language). This can be set with the workbook.FormulaLanguage property. Default is : English.
• the result of the formula brings a new value in the current cell or merged cells each time it gets calculated.
• the operands used in the formula might be dynamic as well, for instance if cells or range of cells are involved.
• formulas have an arbitrary level of complexity. Formulas can be imbricated one into another, or use complex conditions with operators like the IF operator.
• formulas can be either read or written in cells. If you are willing to know what is the formula in a cell, the Formula method from the worksheet object tells you what it is, using the current formula language (English by default).
• formulas can be so-called array formulas (as in {=A1:A10*B1:B10}, notice the curly braces around the formula), and it is possible to add array formulas to a whole range of cells by using the Formula exposed in the IXlsRange object.
• formulas can use array constants, as in {1;2;3}. The semi-colon is the value separator and the comma is the 2D separator in case of a 2D array constant, as in {1,1;2,2;3,3}.
• Introspecting cells for formulas is also possible when you want to know if the cell has a formula but you are not interested in the formula itself. See metadata types.
• formulas are calculated either in real-time or on-demand thanks to the xlsgen calculation engine.
• formulas can be hidden/unhidden using a property available from the Style object interface. Hiding formulas only takes effect if the worksheet is protected.
• User-defined functions (otherwise known as add-ins) can be called. You can call user defined functions from the current workbook, or from an external workbook.
• you can remove formulas from any cell, simply by passing an empty string. Removing the formula preserves the value of the cell. You can alternatively remove all formulas of the worksheet by making a simple call to DeleteFormulas(), or for a selection of cells thanks to what is exposed at the IXlsRange level. Note that removing the formulas preserves the values in cells. This technique of deleting formulas is handy if you want to hide the business logic of your spreadsheet, for instance if the spreadsheet is meant to be distributed to a broad audience.

A formula is attached to a cell at the worksheet level (see IXlsWorksheet for more information), or in the case of merged cells, at the merged cells object level (see IXlsMergedCells for more information).

Formulas are passed just like in Excel. Examples of formulas are as follows :

=SUM(C3:C5) // calculates the sum of the specified cell range
=IF(R1C4 > 5; "yes"; "no") // calculates a content depending on a given condition

Supported functions are listed in the following table.

In addition to supporting functions, xlsgen supports the following operators : +, -, *, /, =, <, <=, >, >=, <>, ^ (power), % (percent), & (concatenation).

If the wrong number of parameters is being passed, then an error is raised. A subset of functions support an arbitrary number of parameters, for instance the SUM function supports any number of parameters. Use the semi-colon character to separate parameters.

Formulas and parenthesis can be combined at an arbitrary level. Sometimes, you may have to explicitely add parenthesis for the parser to resolve ambiguities. For instance, A3-A4=0 can be better understood if you pass (A3-A4)=0 instead.

Operands supported include :

• numbers, floats
• labels (must be double-quoted as in "content")
• boolean values
• cell references, R1C1 notation, or Ax notation
• cell range references, R1C1:R1C1 notation, or Ax:Ax notation
• relative (A1) or absolute references ($A$1 or $A1, or A$1)
• defined names
• curly braces for array constants (example : {1;2;3}). Array constants are a sequence of floating-point numbers, or strings, or booleans, formula errors.
• curly braces for array formulas (example : {=A1:A10*B1:B10})

The R1C1 notation is one of the two notations supported by Excel, where in RxxxCyyy xxx is a row and yyy is a column, both starting at 1. The Ax notation is the natural Excel notation where A denotes a column (there is more than one letter after the 26th column), and x denotes a row number starting at 1.

Functions are parsed and read using the current formula language. The default formula language is English, but xlsgen supports 5 other languages. See the code below for an example of how to use localized function names.

In order to produce the example in the screen above, the following code is required :

wksht.Number(3, 3) = 5
wksht.Number(4, 3) = 10
wksht.Number(5, 3) = 15

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Font.Bold = True
style.Apply

wksht.Formula(6, 3) = "=SUM(C3:C5)"

wksht.set_Number(3,3, 5);
wksht.set_Number(4,3, 10);
wksht.set_Number(5,3, 15);

IXlsStyle style = wksht.NewStyle();
style.Font.Bold = 1;
style.Apply();

wksht.set_Formula(6,3, "=SUM(C3:C5)");

wksht->Number[3][3] = 5;
wksht->Number[4][3] = 10;
wksht->Number[5][3] = 15;

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Font->Bold = TRUE;
style->Apply();

wksht->Formula[6][3] = "=SUM(C3:C5)";

If you'd like to write this function using French function names, you can do the following :

' Tell xlsgen to recognize French function names
wbk.FormulaLanguage = xlsgen.formulalanguage_fr

wksht.Number(3, 3) = 5
wksht.Number(4, 3) = 10
wksht.Number(5, 3) = 15

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Font.Bold = True
style.Apply

' French function name, without uppercase
wksht.Formula(6, 3) = "=somme(C3:C5)"

// Tell xlsgen to recognize French function names
wbk.FormulaLanguage = (int) xlsgen.enumFormulaLanguage.formulalanguage_fr;

wksht.set_Number(3,3, 5);
wksht.set_Number(4,3, 10);
wksht.set_Number(5,3, 15);

IXlsStyle style = wksht.NewStyle();
style.Font.Bold = 1;
style.Apply();

// French function name, without uppercase
wksht.set_Formula(6,3, "=somme(C3:C5)");

// Tell xlsgen to recognize French function names
wbk->FormulaLanguage = xlsgen::formulalanguage_fr;

wksht->Number[3][3] = 5;
wksht->Number[4][3] = 10;
wksht->Number[5][3] = 15;

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Font->Bold = TRUE;
style->Apply();

// French function name, without uppercase
wksht->Formula[6][3] = "=somme(C3:C5)";

If you call user-defined functions (otherwise known as add-ins), you must declare them first using the DeclareUserDefinedFunction method. An example is :

 ' declare an external user defined function
 '  (Book1.xls exposes a VBA procedure called Discount1)
 wksht.DeclareUserDefinedFunction("Book1.xls!Discount1")

 ' create a formula with it
 wksht.Formula(15,3) = "=Book1.xls!Discount1(970;3)"

All of functions above are supported by xlsgen for reading and writing.

Functions supported by the calculation engine (see above) : a subset of the over 500 built-in Excel functions are currently supported, and support for other functions is being added on a case by case basis. In addition, xlsgen does not currently provide calculation support for the following :

• user-defined formulas such as VBA macros or external XLL addins.

If, upon closing a workbook in Excel that was generated by xlsgen, Excel brings a prompt saying "Microsoft Excel recalculates formulas when opening files last saved by an earlier version of Excel (than the one currently running)", then you may avoid this behavior by setting the appropriate Excel target version. See here.

Calculating external workbook references

Detailed here.