Contents | Parent Topic | Previous Topic | Next Topic
Home | Catalog
Spreadsheet style calculations have been introduced into SDF using the standard [[ ]] syntax with a prefix of + (or =) indicating that the expression is to be evaluated by the calculation routines.
This extension has been loosely modelled on Microsoft Excel® in terms of the initial functions supported and the syntax used.
The spreadsheet expression evaluator and the documentation below was written by Tim Hudson (tjh@cryptsoft.com).
Each cell in a table has an cellid which is made up of a single uppercase letter indicating the column index and a number indicating the row index (counting from 1 and excluding the heading rows). The upper left cell is hence A1.
An example grid indicating cellids:
Title1 | Title2 | Title3 | Title4 | Title5 |
A1 | B1 | C1 | D1 | E1 |
A2 | B2 | C2 | D2 | E2 |
A3 | B3 | C3 | D3 | E3 |
... | ... | ... | ... | ... |
A100 | B100 | C100 | D100 | E100 |
A range of cellids is specified using the syntax cellid1:cellid2. For example: A1:C1 is exactly the same as A1,B1,C1
An expression consists of a combination of standard Perl operators and spreadsheet functions and cellids or cellid ranges.
Standard Perl operators include:
Spreadsheet functions use the syntax FUNCTION(ARG1,ARG2,...ARGN).
The following functions are supported:
A simple example is shown below:
!block table; style="grid" Count Price Total 10 5 [[=A1*B1]] 15 5.23 [[=ROWPROD]] [[=COLSUM]] [[=B1+B2]] [[=COLSUM]] !endblock
This generates the result below. (Ok, summing two prices is meaningless, but it illustrates the syntax.)
Count | Price | Total |
10 | 5 | 50.00 |
15 | 5.23 | 78.45 |
25.00 | 10.23 | 128.45 |
Values are available until the next table is processed so you can refer to data inside normal paragraphs after the table like this [[=A1]] (which evaluates to 10.00).
A spreadsheet expression will recursively evaluate any expressions contained in cells that are used in an expression. In the example above, the expression in cell C3 depends on the results of the expression in cell C1 and C2.
Calculation support for a table can be disabled by adding in an attribute of nocalcs. (Without this, the pointers required to table data that are needed when doing spreadsheet calculations occur for each table cell.)
Contents | Parent Topic | Previous Topic | Next Topic
Home | Catalog