Open Malaysia

There has been a lot of interest in the OpenDocument Format (ODF) and the 'problem' of formulas not being included in the standardisation process. Here is what I have learnt from this issue, and hope that it clarifies the issues surrounding this topic.

The ODF does define simple formulas within its specification, e.g. the SUM() function, but it does not proceed to define ALL the other mathematical functions nor syntax on how to use them. It did not want to limit nor constrain how an application implements  specific formulas. The committee decided this implementation detail was out of the scope of the ODF standard.

"A comment was submitted concerning the (inclusion) of a grammar for spreadsheet formulas which conforming implementations should support. While we think that having interoperability on that level would be of great benefit to users, we do not believe that this is in the scope of the current specification. Especially since it is not specifically related to the actual XML format the specification describes. The TC will work on a solution concerning the documentation of interoperabilty standards that go beyond what is defined in the specification" (OASIS, 2005).

This however doesn't mean that formulas are NOT supported within the productivity suite! Formulas have been working fine on my OpenOffice.org app for my regular spreadsheets and I had not faced any problems. However if I were to send my spreadsheet to other productivity apps like KOffice, then there may be problems with the syntax of the formulas, as this excellent article in newsforge reports:

=SUM(A1;A3;CEILING(A4;3;2)) LOG(A1 10;10) - SQRT(A4)

created in OOo would have to be rewritten as:

=SUM(A1;A3;CEILING(A4)) LOG(A1 10) - SQRT(A4)

for KOffice's KCalc to execute.

It must be noted that this article was published on 20th Sept 2005, and its good to know that there has been substantial progress since then. The reason being, the proponents of OOo and KOffice together with many other implementors have been working together and this joint task in creating a universal syntax is defined by The OpenFormula Technical Committee, headed by David Wheeler.

The result of this work is better interoperability between many different productivity suites which support the ODF. The maturity of this is highlighted by this recent post in the OASIS mailing list by David Wheeler summarising the progress. What is truely notable is the effort placed by so many seperate vendors to support this one format for true interoperability:

Developing any good standard requires representatives from multiple implementors, and we are blessed to have a large list! We have reps from OpenOffice.org and Sun StarOffice (Eike Rathke), KDE KOffice (David Faure and Tomas Mecir), Gnumeric (Andreas J. Guelzow and Jody Goldberg), IBM/Lotus 1-2-3 (Rob Weir), and wikiCalc (Dan Bricklin, co-creator of the spreadsheet). We also have many experienced users (such Tom Metcalf, a scientist specializing in the astrophysics of the Sun). Several mathematicians, both users and developers, are a part. Our most recently added member is Dr. Andreas J. Guelzow, the Gnumeric developer leading the implementation of its OpenDocument import and export.

Most importantly, they have decided on the syntax of the format:

OpenDocument is able to support multiple formula formats, so after discussion we agreed on giving this formula specification a particular name. We have decided to keep the name "OpenFormula" for this specific format, with the conventional prefix "of:" and namespace "urn:oasis:names:tc:opendocument:xmlns:openformula:1.0";
table cell entries end up looking like this:

      <table:table-cell table:formula="of:=5 2*3"></table:table-cell>

This means that the decision to keep the grammar 'open' in the original docs of ODF was a good decision as now committees can further refine the structure of the formulas to ensure that user functions are well documented. It also allows versioning of the functions which means that future applications are completely backward compatible in a well documented manner.

It is also reported that KOffice v1.5 works very well with OpenOffice v2.0 as of early 2006 (a few months after the newsforge article (KOffice v1.4 and OOo v1.9 - free upgrades are available) was released)

It must be strongly noted that ODF v1.0 ( ISO 26300 ) supports spreadsheet formula functions. It is not true, that just because a committee had to be set up, it implies that ODF is crippled and does not support simple functions like '=A1 A2'. 
ODF v1.2, with the input of the OpenFormula TC will recommend a  specific formula language, which will guarantee inteoperability between the many productivity apps in the ODFAlliance. The issue OpenFormula had to solve is the interoperability between independant implementations of the standard, something which the current dominant format never had to solve.

So although it is important to standardise on the grammar of these implementation specific features, OASIS made a good decision in first standardising the broad ODF XML structure, and then proceeding to define in detail specific areas which matter to interoperability between different implementations.

yk.

[Update 1:43am, I have removed the section on MSOOXML as it is now not relevant.]