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: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.4.2 release
Hardware: All Windows (All)
: medium enhancement
Assignee: Serge Krot (CIB)
URL:
Whiteboard: BSA target:7.0.0 target:6.4.5
Keywords: filter:xlsx
: 117926 119283 (view as bug list)
Depends on:
Blocks: XLSX-Corrupted Sheet
  Show dependency treegraph
 
Reported: 2014-06-13 19:09 UTC by Victor V. Terber
Modified: 2020-09-07 06:17 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


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/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
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

Note You need to log in before you can comment on or make changes to this bug.
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]
Comment 13 Priyanka Gaikwad 2015-04-27 09:12:58 UTC
I can reproduce this issue on
Version: 4.5.0.0.alpha0+
Build ID: 9561c2f6793bede6e5092c36a4f1c8dbb782c4f4
Comment 14 flominator 2016-05-27 10:16:55 UTC
Behaviour can still be reproduced in LO 5.0.5.2:

1. Export an xlsx file with LO that has at least one sheet with longer name than 31 characters

2. Try to read the document via Infragistics4.Documents.Excel.v11.2.dll: it refuses to open the file until you cut the sheet names to 31 characters
Comment 15 Xisco Faulí 2016-09-14 23:08:37 UTC
Hi Priyanka,
I'm setting this ticket back to NEW as it has been inactive for more than 3
months.
Feel free to assign it back to you if you're still working on this.
Regards
Comment 16 Victor V. Terber 2016-11-26 10:28:25 UTC
The problem is still reproducible in LO 5.2.3.3.

I am willing to support a fix by running any tests.

I am also offering 50€ for a fix in LO (by warning LO users at save time, and shorten the table names appropriately if confirmed by user) within the next six months.

I already donated a bit of money to the German "Document Foundation".

Is there anything else I can do to encourage the actual fix of this bug?

This bug (and probably more problems hidden by this bug) impede my business in switching to LibreOffice. I am using LO (respectively OO) for many years for my private issues, but the inability to interchange reliably with my (MS-using) professional contacts is frustrating. Even more so when the problem is known for years, well-described, tested and an obvious fix is within the realm of LibreOffice: when exporting to XLSX let the user confirm a shortening of the table names.
Comment 17 QA Administrators 2018-07-03 02:38:57 UTC Comment hidden (obsolete)
Comment 18 Markus Mohrhard 2018-07-15 19:34:06 UTC
There is no easy fix for this as it is acutally a MS Excel bug. The exported XLSX file by LibreOffice is correct according to the spec. Excel has an internal limit of 31 characters for sheet names (that is an actual limit for XLS).

As it is not a real bug there is no correct way to handle this and anything that could be done should be classified as an enhancement.
Comment 19 Xisco Faulí 2018-08-16 08:19:05 UTC
*** Bug 119283 has been marked as a duplicate of this bug. ***
Comment 20 Victor V. Terber 2018-08-16 09:03:21 UTC
Comment 18 claimed: "There is no easy fix for this as it is acutally a MS Excel bug. The exported XLSX file by LibreOffice is correct according to the spec". Comment 12 referred to the ECMA-376 standard.

While I understand the (commendable) intention this is from user perspective completely misleading: The LO "Save as" dialog does not tell the user that it saves the document in ECMA-376 format but instead it claims "Excel 2007-2019".

Therefore I dispute the comment 18 stating "There is no easy fix for this as it is acutally a MS Excel bug". The bug is on LO side: Either fix the export to match the alleged Excel compatibility (for instance by shortening the worksheet names) or alternatively change the user-visible format description in the save dialog. The decision is obviously to the LO devs but please do not take the easy way out to simply allege (four years after this bug was validly reported!) "this is an Excel bug".
Comment 21 sverre48 2018-08-16 10:16:59 UTC
I fully agree. This is the kind of feature which heavily affects users view of product quality. If this flaw has been known since 2014, it's amazing that such an easy solution is a prioritised issue.
Comment 22 sverre48 2018-08-16 10:18:00 UTC
is *not* a prioritised...
Comment 23 James B 2018-11-19 17:56:10 UTC
I just came across the same error exporting a file to a client. Could there be some sort of separate warning for issues like this which are outside the spec but break compatibility?
Comment 24 Commit Notification 2020-05-06 21:48:52 UTC
Serge Krot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/6b75874386b7b1ec44f7acc49cd3556a56108ed8

tdf#79998 FILESAVE: XLSX export with long sheet names (length > 31 characters)

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 25 Commit Notification 2020-05-08 22:20:19 UTC
Serge Krot committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/e9124ef7cadd36329d8a5bc1cc8c3a4706e26582

tdf#79998 FILESAVE: XLSX export with long sheet names (length > 31 characters)

It will be available in 6.4.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 NISZ LibreOffice Team 2020-06-29 13:38:33 UTC
*** Bug 117926 has been marked as a duplicate of this bug. ***