Issue 94261

Summary: Export to Excel 2003 writes ODF 1.2+ formulas for XSLT
Product: Calc Reporter: sethnakht <contact>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P4 CC: daniel.rentz, issues, kamataki, kyoshida, ooo, rb.henschel, svante.schubert, tdgoodman, yuko.ohsumi
Version: OOO310m4Keywords: oooqa, regression
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
simple example of sum excel xml 2003
new example created with OOO 3 RC4
new example with other formulas created with OOO 3.0
screenshot for SUM in Excel and Calc
Excel SpreadSheet 2003 xml file with Japanese none

Description sethnakht 2008-09-24 08:48:12 UTC
I saved a little file with microsoft excel 2003 in xml format 

there is only some numbers and one simple sum

when i try to load this file in OOO3 ( in every milestones )

the sum appears now like this =SUM(A1:A3])) and not like this =SUM(A1:A3)

Where does the "])" comes from ?
Comment 1 sethnakht 2008-10-06 08:40:16 UTC
Created attachment 57014 [details]
simple example of sum excel xml 2003
Comment 2 Regina Henschel 2008-10-06 09:35:03 UTC
I see the error in German OOo3.0RC3. But is was already there in OOoDEV300m29. I
see it also in DEV300m20, but that is my own build, so I'm not sure. It is OK in
Comment 3 frank 2008-10-06 10:49:30 UTC
Hi Daniel,

I know 2k3 xml filters are developed by sus but afaik he didn't changed anything
for for OOo3. So please have a look at this one.

Comment 4 sethnakht 2008-10-11 10:25:26 UTC
Created attachment 57118 [details]
new example created with OOO 3 RC4
Comment 5 sethnakht 2008-10-11 10:30:40 UTC
the new example created with the last release candidate OOO 3 RC4 shows that the
code for the sum formula written in the file in excel 2003 xml format  is the
same that is written in the ods format ->  of:]=SUM([.A1:.A3])
Comment 6 Regina Henschel 2008-10-11 15:01:13 UTC
What you see are problems with the new namespace, which is needed for ODFv1.2.

To solve the problem for _export_, go to Tools > Options > Load/Save > General
and set the ODF format version to 1.0/1.1. If you now export the spreadsheet to
the Excel 2003 XML format, you will get correct formulas.

But this setting does not solve the import error.
Comment 7 jmichae3 2008-10-22 23:48:56 UTC
excel xml 2003 file with AVERAGE() function will do the same thing. adds an
extra right peren.  I am pretty sure you will find that all functions will do this.
result in OOo3.0.0 is a formula =AVERAGE(A1:A2])) and resulting #NAME?
Comment 8 sethnakht 2008-10-23 09:11:45 UTC
Created attachment 57398 [details]
new example with other formulas created with OOO 3.0
Comment 9 yuko 2008-10-27 08:52:08 UTC
This issue occurs in ja locale in OOo 3.0.
Please see the attached screenshot.
Comment 10 yuko 2008-10-27 08:53:09 UTC
Created attachment 57490 [details]
screenshot for SUM in Excel and Calc
Comment 11 yuko 2008-10-27 08:54:15 UTC
Created attachment 57492 [details]
Excel SpreadSheet 2003 xml file with Japanese
Comment 12 frank 2008-11-01 14:23:49 UTC
*** Issue 95733 has been marked as a duplicate of this issue. ***
Comment 13 daniel.rentz 2008-12-15 15:06:54 UTC
retargeted due to 3.1 deadline
Comment 14 ooo 2009-03-17 21:16:00 UTC
*** Issue 96018 has been marked as a duplicate of this issue. ***
Comment 15 daniel.rentz 2009-09-22 13:54:25 UTC
new target, this file format is not very important
Comment 16 bz922x 2009-09-22 20:49:29 UTC
The problem that I had with this issue seems to be fixed in OOo3.1.1 for
windows.  Is anyone still seeing this issue?
Comment 17 bz922x 2009-10-03 02:44:45 UTC
After discussion with other OOo users, let me clarify what works and what does
not work with Excel 2003 xml files and OOo calc.

READING an Excel 2003 xml file seems to work in OOo 3.1.1 (310m19 Build:9420).

WRITING an Excel 2003 xml file does NOT work and results in a corrupt file.
Regina's work around (set the ODF format version to 1.0/1.1) does write good
files, but this work around is annoying and confusing for casual users.

dr: while this format may not be "very important" in terms of total usage, for
those of us who have applications that read and write this format, it is
critical to have this working before we can recommend OOo to our users.
Comment 18 kalata 2010-03-19 10:46:35 UTC
I see no error in OOo3.2.0 (Build 9483). Thanks.
Comment 19 Regina Henschel 2010-11-28 19:48:08 UTC
*** Issue 115812 has been marked as a duplicate of this issue. ***
Comment 20 Regina Henschel 2010-11-28 19:50:27 UTC
The error is still there in DEV300m93 on WinXP.
Comment 21 ooo 2010-12-03 12:37:16 UTC
The Excel2003 filter uses XSLT and gets ODF 1.2+ including OpenFormula as
source (in fact the configured version), which it can't cope with. So either
a) use ODF 1.0/1.1 as source for the current XSLT, which somewhere needs
   overriding the configured ODF version for specific exports => @mib
b) use current ODF as source and adapt XSLT => @sus

I propose a)

See also issue 95312 for .sxc export.
Comment 22 ooo 2010-12-03 12:37:35 UTC
*** Issue 115877 has been marked as a duplicate of this issue. ***
Comment 23 michael.brauer 2010-12-03 13:31:08 UTC
mib->er: I'm not responsible for the XSLTFilterAdapter. That's sus.

But actually, I could imagine that it is easier to correct the Excel export XSLT
than to add an option to the XSLTFilterAdapter that allows to select the ODF
version to use for all such filters individually.
Comment 24 niklas.nebel 2011-03-01 10:58:28 UTC
This was changed to P4 on 2011-01-13, apparently the change was lost when moving to the new infrastructure.