Bug 79998 - FILESAVE: XLSX export breaks or some sheet names (sheet name length >31 characters)
Summary: FILESAVE: XLSX export breaks or some sheet names (sheet name length >31 chara...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Spreadsheet (show other bugs)
Version: 4.2.4.2 release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2014-06-13 19:09 UTC by Victor V. Terber
Modified: 2014-10-18 06:42 UTC (History)
3 users (show)

See Also:
i915 platform:
i915 features:


Attachments
Example for bad exprot to Excel 2007/2010/2013. For a variation rename the name of the third sheet, and re-export again. Now it succeeds. (41.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-13 19:09 UTC, Victor V. Terber
Details
Sample file saved as xlsx (7.66 KB, application/octet-stream)
2014-06-13 21:06 UTC, m.a.riosv
Details
Resulting XLSX file, as described in description of bug 79998 (7.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-06-14 07:35 UTC, Victor V. Terber
Details
reduced test case (40.97 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-06-25 08:19 UTC, Victor V. Terber
Details
resulting XLSX file when reduced test case is exported (6.14 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-06-25 08:20 UTC, Victor V. Terber
Details
Resulting XLSX file when reduced test case is exported, but manually modified by removing the apostrophs around the sheet name in file sheet1.xml. Result: Excel now shows value of cell A1 correctly. (6.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-06-25 08:22 UTC, Victor V. Terber
Details

Description Victor V. Terber 2014-06-13 19:09:32 UTC
Created attachment 101001 [details]
Example for bad exprot to Excel 2007/2010/2013. For a variation rename the name of the third sheet, and re-export again. Now it succeeds.

Problem description: 

Export in Excel 2007/2010/2013 format fails. Failure is dependent on the name of a sheet.


Steps to reproduce:
1. Open document in native ODS format with LibreOffice Calc
2. Save the document in format 'Microsoft Excel 2007/2010/2013 XML (.xlsx)'
3. Open the document with Excel 2010.
4. Excel indicates for cell A1 in the first sheet that a reference is missing.
5. Re-open the original ODS file with LibreOffice
6. Rename the third sheet to a different name (e.g. 'C')
7. Repeat steps 2. and 3.
8. Excel now shows the correct value in cell A1 of the first sheet.


Current behavior:
LO exports a file in format "Excel 2007/2010/2013" which is not possible to open in Excel 2010.


Expected behavior:
Files exported by LO in format "Excel 2007/2010/2013" should be possible to be opened by MS Excel.
              
Operating System: Windows 7
Version: 4.2.4.2 release
Comment 1 m.a.riosv 2014-06-13 21:06:17 UTC
Created attachment 101013 [details]
Sample file saved as xlsx

Hi Victor, thanks for reporting.

Works for me with:
Win7x64
Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8

Please try resetting the user profile.
https://wiki.documentfoundation.org/UserProfile
Comment 2 Victor V. Terber 2014-06-14 07:34:51 UTC
Indeed, I can confirm that I am also able to open the file provided by "m.a.riosv" (Miguel) in comment 1 correctly with Excel 2010

I am also using the exact same LO version:

Version: 4.2.4.2
Build-ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8

But my result exporting the originally provided file to format 'Microsoft Excel 2007/2010/2013 XML (.xlsx)' is slightly different, and I am indeed unable to open it successfully with Excel 2010 (as described in the original report).

Adding my resulting XLSX file as attachment for further testing.

Only obvious differences I noted to Miguel's file:

- Miguel uses a Spanish locale, I use a German locale
- Miguel and I have different active and select cells

Both differences seem harmless, but as of now I fail to spot the crucial diff which breaks the export in my case.
Comment 3 Victor V. Terber 2014-06-14 07:35:57 UTC
Created attachment 101031 [details]
Resulting XLSX file, as described in description of bug 79998
Comment 4 m.a.riosv 2014-06-14 07:59:50 UTC
I can open fine your last file, but I have not excel to verify.
Have you tried resetting the user profile?
Comment 5 Victor V. Terber 2014-06-14 08:12:41 UTC
(In reply to comment #4)
> I can open fine your last file, but I have not excel to verify.
I can open the file with LO too, taht is not the problem. But it seems incompatible to Excel.

> Have you tried resetting the user profile?
Just reset my user profile, but problem remains reproducible. Same for another machine (also LO 4.2.4.2 German, Win7 64 Ultimate, German locales),

Any other supporting tests I can contribute?
Comment 6 Tim Lloyd 2014-06-24 05:29:05 UTC
Hi Victor,

I am confirming this with a few comments:

I opened your xlsx document under excel 2007 (Windows XP) and got an error "Excel found unreadable content..."
I asked to recover the contents of the doc and cell A1 was labelled as #REF

For reference I saved your xlsx document as xls in Calc
The xls document opened fine in excel

I guess the devs would be interested to know more information about the contents of A1=BV_CH_Prognose. As m.a.riosv can not reproduce this, maybe if you could advise any peculiarities when creating the document?
Comment 7 Victor V. Terber 2014-06-25 07:22:06 UTC
> I guess the devs would be interested to know 
> more information about the contents of A1=BV_CH_Prognose

"BV_CH_Prognose" is simply a named cell, i.e. cell A1 of sheet "B".
Cell A1 of tab "B" then points towards cell A1 of sheet "Utilities (FX Kurse, Kreditkarten etc)".

The content of all these cells is 1.

> maybe if you could advise any 
> peculiarities when creating the document?

I created the document by reducing a complex LO file which showed numerous errors when exporting towards Excel, turning LO usage in a real-world situation into a completely unfeasible alternative for me and my business. So I removed more and more cells, references and formats until it resulted in the tiny file I uploaded here for further analysis by the pros.

I still can reproduce the problem perfectly with now up-to-date LO 4.2.5.2, Build-ID: 61cb170a04bb1f12e77c884eab9192be736ec5f5.

Again, I am willing to support the analysis further. What can I do to get this problem fixed?
Comment 8 Victor V. Terber 2014-06-25 08:18:54 UTC
- I created a smaller test case. Please see the new attachments with name "Bug79998ReducedTestCase.ods" and "Bug79998ReducedTestCase.xlsx"

- I only now noted that Excel 2010 (when trying to open the LO-exported document) reports that it had to repair parts of "/xl/workbook.xml"

- I therefore unzipped the Excel format file and had a look into the content. As the problem is (as originally described) related to the name of a sheet, I looked for the affected sheet name.

In "workbook.xml" I found this:

<sheet r:id="rId3" state="visible" sheetId="2" name="Utilities (FX Kurse, Kreditkarten etc)"/>

In "sheet1.xml" I found the following:

<f aca="false">'Utilities (FX Kurse, Kreditkarten etc)'!A1</f>

Notice the apostrophes at the start and the end of the name in the second instance, which are missing in the first instance. Experimentally I removed these apostrophes from file "sheet1.xml". Result: Excel opened the file, and now showed the value in cell A1 correctly! The modified (working) Excel file is attached with name "Bug79998ReducedTestCase_ManuallyRemovedApostrophesFromSheet1XML_SoThatItWorksNow.xlsx"

Could please a LO developer have a look into this?
Comment 9 Victor V. Terber 2014-06-25 08:19:48 UTC
Created attachment 101728 [details]
reduced test case
Comment 10 Victor V. Terber 2014-06-25 08:20:23 UTC
Created attachment 101729 [details]
resulting XLSX file when reduced test case is exported
Comment 11 Victor V. Terber 2014-06-25 08:22:35 UTC
Created attachment 101730 [details]
Resulting XLSX file when reduced test case is exported, but manually modified by removing the apostrophs around the sheet name in file sheet1.xml. Result: Excel now shows value of cell A1 correctly.
Comment 12 raal 2014-10-18 06:42:48 UTC
Version: 4.4.0.0.alpha0+
Build ID: 9aa36a1ad39e37c372cc833a44fba450b8cc30cd
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2014-10-09_04:46:44

Created file from scratch with following sheet names (without ""):
"1234567890 1234567890 1234567890" - excel viewer doesn't open file (32 letters)
"1234567890 1234567890 123456789" - excel viewer open this file. Formula is correct. (31 letters)

The problem is in sheet name length. When sheet name length is >31 characters than excel viewer fails to open this file. I have not Excel 2010, but according to google discussions this should be limit in excel (also in version 2013).
Setting as NEW, LibreOffice should warn user that .xlsx file is not readable in ms office.

In ECMA specification I didn't found allowed length of sheet name:
name (Sheet Name)
Specifies the name of the sheet. This name shall be unique
This attribute is required.
The possible values for this attribute are defined by the ST_Xstring simple type
(§22.9.2.19).

22.9.2.19
ST_Xstring (Escaped String)
String of characters with support for escaped invalid-XML characters.
For all characters which cannot be represented in XML as defined by the XML 1.0 specification, the characters
are escaped using the Unicode numerical character representation escape character format _xHHHH_, where H
represents a hexadecimal character in the character's value. [Example: The Unicode character 8 is not permitted
in an XML 1.0 document, so it must be escaped as _x0008_. end example]
This simple type's contents are a restriction of the W3C XML Schema string datatype.
[Note: The W3C XML Schema definition of this simple type’s content model (ST_Xstring) is located in §A.6.9. end
note]


Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.