Excel formulas in the BIFF export

Starting from FR VCL 4.11.15 the BIFF export can export formulas. For example, to export the formula SUM(A1:B2) put a TfrxMemoView on a report and write in it

Code

=SUM(A1:B2)

The first character of the text must be the equality sign "=", and the rest of it - a correct Excel formula. Exporting of formuals is controlled by the BIFF export's property ExportFormulas and to disable this option, simply write

Code

procedure DisableFormulas(Exp: TfrxBIFFExport);
begin
 Exp.ExportFormulas := False
end;

By default, the exporting of formulas is enabled.

What will happen if an incorrect formula is encountered in a report? The export will try to process it, but having found an error, will save it to an xls file as a simple text cell.

What formulas the export supports

The export supports Excel formulas. It's noteworthy that formulas syntax in Excel, OpenOffice and LibreOffice is different in details and, despite, in most cases you don't have to encounter these differences, it must be taken into account that only Excel formulas syntax is supported. One of examples of such differences can be found in the form of a reference to an external cell. Let's assume a report have pages PageA and PageB and the A1 cell on Page is needed to be equal to the sum of first ten cells in the third column on PageB. This can be written with the following formula in Excel:

Code

=SUM(PageB!C1:C10)

at the same time, this formulas is written differently in LibreOffice:

Code

=SUM($PageB.C1:C10)

It must be known that the BIFF export supports only the first formula.

Operators

Excel formulas allow to use various operators and functions. Below are listed those of them that are supported in the BIFF export:

1. Unary operators + - and binary operators + - * / ^ and, also, comparsion operators < <= = >= > <>
2. The unary operator % that's written after its operand and divides it by 100. For instance the formula =A1% equals =A1/100
3. The operator : that makes a cell range. In order to calculate the sum of first tree cells in the column G one can write =SUM(G1, G2, G3) or =SUM(G1:G3)
4. The operator & joining strings: = "abc" & "def" is equal to ="abcdef"
5. The operator ! that allows to make a reference to a cell or a cell range placed on another sheet within the same document. Such an example already occured: =SUM(PageB!C1:C10) There are other kinds of the operator ! but they are currently not supported by the export. They can be added if users of the export need them.
6. The range instersection operator denoted by the space sign. For example the expression A2:C2 B1:H8 equals B2

Strings

The export supports two kind of strings, distinguished by quotes enclosing them: 'abc' and "abc". To insert a quote into a string it can be doubled or a string with the other enclosing quotes can be used. For example the following strings are identical: "abc""def" and 'abc"def'

When referencing to an external cell with the operator ! strings can be used, if a page name contains spaces or written in a national alphabet. Following formulas are identical:

Code

=SUM(PageB!C1:C10)
=SUM("PageB"!C1:C10)
=SUM('PageB'!C1:C10)

The two last methods allow to use complex page names: =SUM("Another Page In This Document"!C1:C10)

Functions

Built-in Excel functions can be called from formulas. One of them is the widely known function SUM - it sums its arguments. Excel supports a very big number of functions. The BIFF export supports about 150 of them only. Among them there are widely used SUM, AVERAGE, INDIRECT, MIN, MAX, AND, OR and so on. To add the support of a new built-in function to the export, simple append frxBIFF.pas with one line, as this example demonstrates:

Code

class procedure TBiffFormulaFuncList.Init;
begin
 if GetCount > 0 then
 Exit;
 
 {http://sc.openoffice.org/excelfileformat.pdf
  http://msdn.microsoft.com/en-us/library/dd904817.aspx }
 
 Add(0, 'count', 1, 30, 'v', 'r');
 Add(1, 'if', 2, 3, 'r', 'vr');
 Add(2, 'isna', 1, 1, 'v', 'v');
 
 <...>
 
 Add(362, 'maxa', 1, 30, 'v', 'r');
 Add(363, 'mina', 1, 30, 'v', 'r');
end;

Descriptions of functions can be found at the above links. It's also possible to report me that a new function is needed and I will add it.

Technical details

A formula in a xls file is represented by a usual cell. It looks like a record with code 6 (http://msdn.microsoft.com/en-us/library/dd908919.aspx) that contains the row, the column, the formatting (an index to the XF record), the formula's result and the formula's code.

The formula's code is a sequence of variadic length instructions, operating within a virtual machine without registers and with the LIFO stack. Instructions can be divided into two groups: those that push new values onto the stack and those that pop a few values from the stack, perform an operation with them and push a result back onto the stack. An example is a simple formula: int(1) int(2) add The first two ones pushes two 4-byte integers 1 and 2, and the third one pops two values from the stack, sums them pushes the sum back onto the stack, leaving on the stack only one value 3.

The same formula can be represented by different sets of instructions. The BIFF export tries to choose instructions that occupy less space. For example let's consider a simple formula consisting of a single number: =-7.0 There are two ways to write this formula:

Code

1	double(-7.0)

this code occupies 9 bytes; and the second way:

1 2	int(7) neg

this code occupies 6 bytes. The same is true for more complicated cases.

=SUM(PageB!C1:C10)

at the same time, this formulas is written differently in LibreOffice:

Code

=SUM($PageB.C1:C10)

=SUM(PageB!C1:C10)
=SUM("PageB"!C1:C10)
=SUM('PageB'!C1:C10)