Formulas in the Excel Export Library
- 2 minutes to read
To create a formula in a cell, use the IXlCell.SetFormula method. This method enables you to specify a formula in different ways:
Use textual representation.
The most common and straightforward way. However, it is not the fastest method to set a formula, because it requires a formula parser (a DevExpress.Export.Xl.IXlFormulaParser descendant, which should be supplied in the XlExport.CreateExporter method). The XL Export library writes data directly to the output stream in consecutive order, and you cannot evaluate formulas containing named ranges or custom functions. That is the reason why named range and custom functions cannot be written in .XLS (Excel 97-2003) format and in the .XLSX (OpenXml) format when the formula parser is specified.
Tip
You can use any string (even composed incorrectly or containing undefined names) as a formula when exporting to the .XLSX (OpenXml) format without a parser because formulas are not parsed and validated.
Use IXlFormulaParameter object.
This is the recommended way to create common formulas containing built-in functions.
Compose a formula from expression tokens.
Tokens are the descendants of the DevExpress.Export.Xl.XlPtgBase class (“parsed things”), arranged in reverse Polish notation. The method originates from .XLS file structure and is too complex for most cases.
This section contains the following examples which illustrate the use of the methods described above.