Apache OpenOffice (AOO) Bugzilla – Issue 96018
Excel 2003 XML export XSLT uses 'of' ODF 1.2 namespace in ss:Formula
Last modified: 2013-08-07 15:13:10 UTC
Sorry if this is not a bug, I didn't know how else to ask this. A spreadsheet has cell A2 containing the formula =A1*2, and is then Saved As in Office Excel 2003 XML (.xml) file format. In OpenOffice v2.xx, the XML cell referencing is: <Cell ss:StyleID="Default" ss:Formula="=R[-1]C*2"> but in OpenOffice v3.xx it is: <Cell ss:Formula="of:=[.A1]*2"> I'm trying to help some developers who are trying to import XML spreadsheets into their app (the v2 ones work, the v3 ones don't), and I'm wondering if you can tell me what the old and new standards are so I can point them to documentation on them, please? Then they can add support for OOo v3 XML files to their parser. I failed to find any information about this change on the OOo sites, or I wouldn't have resorted to posting this here, my apologies.
After some more digging: the issue seems to be that somewhere along the line between v2.xx and v3.xx, OpenOffice has moved to exporting Office Excel 2003 XML formula tags in the ODFF [.a1] bracketed schema, instead of the R1C1 that was exported before. This breaks compatibility between OpenOffice XML and MS Excel itself, as an XML file created by OpenOffice v3 fails to import correctly into Ms Excel 2008 (mac), throwing up an error message and losing all its formulae. I am therefore flagging this a a DEFECT now.
In OOo3.0RC4 On WinXP，the issue also exists.
Is this the same as issue 95039?
Issue 95039 is a subset of this issue - the point the other poster is making is just that OOo v3.xx fails to correctly import Excel 2003 XML docs, inserting a rogue ']' into formulae with cell references. The point I'm making is a broader one, of which that is just a symptom: * OOo v2.xx correctly both imported AND exported Excel 2003 XML files * OOo v3.xx fails to do either correctly The reason why both these issues are happening is the same: * OOo v2.xx exported correctly to the Excel 2003 XML standard because it exported using R1C1 cell referencing, which is what Excel 2003 XML uses (XML files saved in Excel and OOo v2.xx have essentially exactly the same contents). * OOo v3.xx for some reason is suddenly exporting (and expecting for import) XML files using the ODF [.a1] bracket standard instead of R1C1 - this is not supported by Excel, and therefore breaks the compatibility with Excel that was working fine in v2.xx. XML files saved in Excel will not open properly in OOo v3.xx - XML files saved in OOo v3.xx will not open properly in Excel. The working export code exists from v2.xx - so it shouldn't be hard to fix this, it was clearly some kind of oversight to change the export format, since the Save As dropdown explicitly says 'MS Excel 2003 XML'. Perhaps there's a plan to add ODF XML export capability to the Save As list, and it ended up in the Excel XML code by mistake, I can only guess how this might have come about. Anyway, I hope that I've explained this more clearly than I did before.
ODF formula namespace. *** This issue has been marked as a duplicate of 94261 ***
Closing dup.
Hmpf.. not exactly a dup of issue 94261, that's about import and someone interspersed it with comments about export as well.. so, this one here is for export, adapting summary to something more useful.
Confirming.
I'm not sure when this happened, but somewhere between me posting this issue and the release of v3.0.1 my issue has been fixed, mostly. MS Excel 03 XML files import correctly into OpenOffice 3.0.1. MS Excel 03 XML files exported from OpenOffice v3.0.1 import correctly into MS Excel 04/08 - though if ODF 1.2 is used (rather than ODF 1.0/1.1), a Table error is thrown up during import into MS Excel, the XML doc still seems to import correctly.