Issue 96018 - Excel 2003 XML export XSLT uses 'of' ODF 1.2 namespace in ss:Formula
Summary: Excel 2003 XML export XSLT uses 'of' ODF 1.2 namespace in ss:Formula
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: OOo 3.0 RC4
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2008-11-10 00:06 UTC by padmavyuha
Modified: 2013-08-07 15:13 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description padmavyuha 2008-11-10 00:06:02 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.
Comment 1 padmavyuha 2008-11-10 00:36:21 UTC
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.
Comment 2 chl207 2008-11-21 05:00:52 UTC
In OOo3.0RC4 On WinXP,the issue also exists.
Comment 3 openofficeiscool 2008-12-16 22:36:56 UTC
Is this the same as issue 95039?
Comment 4 padmavyuha 2008-12-16 23:47:04 UTC
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.
Comment 5 ooo 2009-03-17 21:16:02 UTC
ODF formula namespace.

*** This issue has been marked as a duplicate of 94261 ***
Comment 6 ooo 2009-03-17 21:16:27 UTC
Closing dup.
Comment 7 ooo 2009-03-17 21:30:27 UTC
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.
Comment 8 ooo 2009-03-17 21:31:04 UTC
Comment 9 padmavyuha 2009-04-08 11:47:11 UTC
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