[back-blogging, the act of blogging about things that happened in the past]

Most of my time last year and this year went into the OASIS OpenDocument Format Formula specification (aka ODFF aka OpenFormula) and its implementation for the OOoCalc spreadsheet application.

For OOo3.0 the highlights are:

Differentiation between the table:formula attribute's oooc: namespace prefix and the new of: namespace prefix of the OpenDocument >= v1.2 file format.

Function names stored in an OpenDocument file are defined by the OASIS OpenDocument formula subcommittee, and especially names of functions that are implemented as Add-Ins and part of the basic function set differ from those that are stored and expected by earlier releases of OOo that wrote the programmatic name, such as com.sun.star.sheet.addin.Analysis.getDuration that now is simply DURATION.

As that increased the set of formula languages to be supported to 3 (native UI visible, old file format and new ODFF names) plus different cell reference conventions (A1 vs. R1C1 vs. ODFF's bracketed [.A1] schema), parts of the formula compiler's tokenizer and stringizer were rewritten to use different grammar sets.

It now is even possible to use English formulas in a non-English UI, but since that is an undocumented, experimental and unsupported feature, the advanced curious adventurous user could go to Environment Variables (wiki) and look for OOO_CALC_USE_ENGLISH_FORMULAS ;-)

The : colon range operator is fully implemented.

You're already used to the range operator in cell range expressions such as A1:B2, specifying a fixed cell range reference. New is that now the colon is a binary operator taking one reference argument on each side, forming a range encompassing the area of its arguments. Sounds complicated? The area of A1:B2 is of course top left cell A1 to bottom right cell B2. Here are some examples:

If a defined name NAMEDCELL is defined to cell reference C3, A1:NAMEDCELL returns the range reference A1:C3, similar if OTHERCELL is defined to cell reference D4, OTHERCELL:NAMEDCELL returns the range reference C3:D4. Note that the order of arguments does not matter, the range is always justified from top left to bottom right.

This works also for ranges as arguments, e.g. A1:B2:C3 returns a range reference of A1:C3, respectively NAMEDCELL:NAMEDRANGE returns A1:C3 if NAMEDCELL is defined to C3 and NAMEDRANGE is defined to A1:B2.

Functions that return a cell reference can be used as an argument to the range operator, for example if A1 contains the string "C3", B2:INDIRECT(A1) returns the range B2:C3.

See also the feature announcement mail.

The ~ tilde reference concatenation operator.

The reference concatenation operator or range list operator concatenates cell references. This is also called a union operator, but it is not a union set in the mathematical sense, duplicates are not eliminated. ODFF defines that to be the tilde character, but also the somewhat awkward syntax of another spreadsheet application is supported, where a range list is identified by an extra pair of surrounding parentheses and ranges are separated by the parameter separator. Hence the expressions A1:B2~C3:D4 and (A1:B2;C3:D4) are identical, though the latter is transformed to read (A1:B2~C3:D4) after input.

The AREAS() and INDEX() functions evaluate the number of ranges if a range list was passed.

Functions that expect a range reference parameter and where the order of elements does not matter in evaluation now accept a range list as argument as well. For details see the feature announcement mail.

Arbitrary characters in sheet names.

Sheet names now can consist of almost all characters, except those that one other spreadsheet application does not allow, for interoperability reasons. However, reading a sheet name containing those characters from an OpenDocument file is possible, just using them when giving a sheet a new name is not. For details and when you have to enclose the name in single quotes for cell reference expressions see the feature announcement mail.

Kudos to Caolan and Kohei for providing the patches.

ADDRESS() and INDIRECT() support the additional A1/R1C1 parameter.

As specified in ODFF for interoperability, the ADDRESS() and INDIRECT() functions now support the parameter that switches between A1 and R1C1 address style. For details see the feature announcement mail.

ISERROR(undefinedname) results in TRUE.

The ISERROR() function now can be used to check for undefined names, missing Add-In functions and the like. See feature announcement mail.

Enhancements to various spreadsheet functions.

Many implementation details changed for compliance with ODFF and interoperability. Places to check are the schedule of finished implementations in the wiki and a list of related issues and the list of feature announcement mails.

Kudos to Kohei and Lvyue for patch contribution.

Of course there is more to come for OOo3.1 and OOo3.2, stay tuned.

tags: calc oasis odf odff opendocument openformula openoffice.org spreadsheet