Friday 13 March 2009

## How the OpenXML/ODF Translator Deals with Formulas

By Annerose Hümbert, Friday 13 March 2009 at 14:56 :: posted to General

The ODF standard does not yet specify formulas. So what shall we do with these poor and neglected formulas in our OpenXML/ODF translator?

In its first versions, the OpenXML/ODF translator simply did not translate formulas at all. Only the static values were preserved. Unfortunately, users are not really interested in what is part of a standard and what not. They actually want to get a good translation which means the preservation of all visual content and even of the functional content such as formulas. Consequently, we (the OpenXML/ODF Translator team of Sonata, DIaLOGIKa and Microsoft) decided to implement a decent formula translation approach in version 2.5 after having received a number of complains.

The solution we selected should provide best user experience for MS Office and OpenOffice users. Thus, we implemented a basic support for OpenOffice formulas which use the namespace xmns:oooc=http://openoffice.org/2004/calc. If possible the value and the formula are translated. Otherwise only the value is retained. In detail the OpenXML/ODF Translator does the following:

- Formula values are always translated from OpenXML to ODF and vice versa.
- Formulas only containing functions which are supported by both OpenXML and OpenOffice, are retained. Syntax differences such as separator characters or cell references are handled (Note: Most of the OpenXML functions are also supported by OpenOffice).
- Formulas containing functions, which are not supported by one of the formats, are omitted, but the formula’s value is kept. (Note: There are a number of OpenOffice functions which do not exist in OpenXML).

It should be born in mind that the translator simply checks for the formula prefix “oooc:”. It does not check the URI linked to the namespace prefix.

After supporting OpenOffice formulas in version 2.5 of the OpenXML/ODF Translator, version 3.0 will also implement formula translation of KSpread, Symphony and MS Office 2007 SP2 (which newly comes with ODF support).

KSpread and Symphony do not specify a namespace for formulas. They seem to use a similar formula language as OpenOffice; however, we encountered already a number of functions only used by KSpread.

MS Office 2007 SP2 uses the namespace, xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula".

Version 3.0 of the OpenXML/ODF translator will give the following formula conversion results:

ODS created byODS code sampleTranslator v3.0Translator v3.0retains compatible replaces incompatibleformulas formulas by theirvaluesOpenXML/ODF oooc:=SUM([.A1];[.A2]) Yes Yes Translator v2.5 or v3.0 OpenOffice oooc:=SUM([.A1];[.A2]) Yes Yes KSpread =SUM([.A1];[.A5]) Yes* Yes* Symphony =SUM([.A1];[.A5]) Yes* Yes* Office 2007SP2 msoxl:=SUM(A1,A2) Yes Yes Any other xxxx:=SUM([.A1];[.A2]) Yes Yes translator using OpenOffice formula syntax and any namespace

\*Some “fine tuning” has been postponed to the next version.