docs/features/functions.rst
Functions
ClosedXML can evaluate formula functions.
.. note::
Excel has a a list of functions that are defined in ISO-29500 and newer
ones that were added in some subsequent version (future functions).
The future functions generally have a prefix _xlfn. The prefix is hidden
in the GUI, but is present in the file (e.g. _xlfn.CONCAT(A1:A2) is
displayed as a =CONCAT(A1:B1) in the Excel).
The cell formula that uses a future functions that were added in later version of Excel must use a correct name of a function, including the prefix.
Version 0.105 and newer automatically translates future function names in formulas:
.. code-block:: csharp
// Formula setters automatically translate future functions
// to correct prefixed names from version 0.105 onward.
ws.Cell(1,1).FormulaA1 = "CONCAT(A1:A2)";
Assert.AreEqual("_xlfn.CONCAT(A1:A2)", ws.Cell(1,1).FormulaA1);
For pre-0.105 versions, user must use correct name in formulas:
.. code-block:: csharp
ws.Cell(1,1).FormulaA1 = "_xlfn.CONCAT(A1:A2)";
Excel won't recognize future functions without a prefix! It will try
to match the function, but won't find anything and it will display
a #NAME? error.
See the list of future functions <https://learn.microsoft.com/en-us/openspecs/office_standards/ms-xlsx/5d1b6d44-6fc1-4ecd-8fef-0b27406cc2bf>_
.. note:: ClosedXML doesn't calculate and save values of a formula cells by default. The saved cell contains only formula and when the file is opened in the Excel, it recalculates values of formulas.
You can save values by setting SaveOptions.EvaluateFormulasBeforeSaving
to true and passing the options to the XLWorkbook.SaveAs or
XLWorkbook.Save method.
Workbook without formula values can exhibit slighly odd behavior in some cases:
IXLCell.Style.Alignment.WrapText doesn't correctly auto-size cell height
when opened in Excel (#1833).Standard functions ##################
.. flat-table:: Standard function implementation status :header-rows: 1
6 Cube11 Database22 Date and Time38 Engineering52 Financial16 Information6 Logical17 Lookup and Reference61 Math and Trig82 Statistical33 Text and DataFuture functions ################
.. flat-table:: Future functions :header-rows: 1
13 Math and Trig1 Statistical6 Text and Data