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 by     ODS code sample          Translator v3.0       Translator v3.0 
                                            retains compatible    replaces incompatible 
                                            formulas              formulas by their
                                                                  values 

OpenXML/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.