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
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
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.
Created attachment 101031 [details] Resulting XLSX file, as described in description of bug 79998
I can open fine your last file, but I have not excel to verify. Have you tried resetting the user profile?
(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?
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?
> 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?
- 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?
Created attachment 101728 [details] reduced test case
Created attachment 101729 [details] resulting XLSX file when reduced test case is exported
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.
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]
I can reproduce this issue on Version: 4.5.0.0.alpha0+ Build ID: 9561c2f6793bede6e5092c36a4f1c8dbb782c4f4
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
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
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.
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
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.
*** Bug 119283 has been marked as a duplicate of this bug. ***
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".
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.
is *not* a prioritised...
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?
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.
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.
*** Bug 117926 has been marked as a duplicate of this bug. ***