Update on ODF Spreadsheet Interoperability
May 05, 2009, 17:02 (0 Talkback[s])
"The new entry to the mix is Microsoft Office 2007 SP2, which
has added integrated ODF support. Unfortunately this support did
not fare well in my tests. The problem appears to be how it treats
spreadsheet formulas in ODF documents. When reading an ODF
document, Excel SP2 silently strips out formulas. What is left is
the last value that cell had, when previously saved.
"This can cause subtle and not so subtle errors and data loss.
For example, in the test document I presented above, the current
date is encoded using the TODAY() spreadsheet function. If the
formulas are stripped, then this cell no longer updates, and will
return the wrong value. Similarly, if Maya tries to continue her
ledger of expenses by copying the formula cells from column E down
a row, this will cause incorrect calculations, since there is no
longer a formula to copy, so she would just be copying the prior
balance. In general, SP2 converts an ODF spreadsheet into a mere
"table of numbers" and any calculation logic is lost.
"In the other direction, when writing out spreadsheets in ODF
format, Excel 2007 SP2 does include spreadsheet formulas but places
them into an Excel namespace. This namespace is not what OpenOffice
and other ODF applications use. It is not the ODF 1.2 namespace. It
isn't even the OOXML namespace. I have no idea what it is or what
it means. Not every ODF application checks the namespace of
formulas when loading documents, but the ones that do reject the
SP2 documents altogether. And the ones that do not check the
namespace try and fail to load a formula since it is syntactically
different than what they expected. The applications essentially
display a corrupted document that is shows neither the formula nor
the value correctly. For example, a SP2 document, loaded in MS
Office using the Sun ODF Plugin looks like this:"