Download it now!
Bug 84874 - FILESAVE to .xlsx. MS Excel doesn't load spreadsheets with promptTitle longer than 256 characters.
Summary: FILESAVE to .xlsx. MS Excel doesn't load spreadsheets with promptTitle longer...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.6.3 release
Hardware: All All
: high normal
Assignee: Tibor Nagy (NISZ)
URL:
Whiteboard: target:7.1.0
Keywords:
Depends on:
Blocks: Cell-Validity
  Show dependency treegraph
 
Reported: 2014-10-10 13:38 UTC by Paavo Ingalsuo
Modified: 2020-11-17 10:52 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
House heater dimensioning. (684.40 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-10 13:38 UTC, Paavo Ingalsuo
Details
This the same file without psw. (682.80 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-11 06:34 UTC, Paavo Ingalsuo
Details
Here same file in .xlsx format without psw (1.44 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-11 06:36 UTC, Paavo Ingalsuo
Details
Now unlocked.. (682.57 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-10-11 06:55 UTC, Paavo Ingalsuo
Details
printscreen of workbook.xml (205.97 KB, image/png)
2014-10-11 12:11 UTC, raal
Details
Example file from Calc master (10.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-02 12:19 UTC, NISZ LibreOffice Team
Details
Example file as xlsx with 257 character long validation (8.74 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-02 12:20 UTC, NISZ LibreOffice Team
Details
The xlsx with 257 characters long validation messages opened in Excel 13 (49.02 KB, image/png)
2020-10-02 12:21 UTC, NISZ LibreOffice Team
Details
Example file with 256 character long validation (10.82 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-02 12:22 UTC, NISZ LibreOffice Team
Details
Example file with 256 character long validation saved as xlsx (8.74 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-02 12:23 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Paavo Ingalsuo 2014-10-10 13:38:28 UTC
Created attachment 107662 [details]
House heater dimensioning.

Excel 2013 / Office 365 can´t open a file saved as .xlsx

As attachment here follows a quite large spreadsheet Bergheat46.ods
Comment 1 raal 2014-10-10 15:06:59 UTC
I can confirm with Version: 4.3.3.0.0+
Build ID: 14907346d792f2f93a00083bbab5086cf56ddb24
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:libreoffice-4-3, Time: 2014-09-26_03:56:08
Excel viewer doesn't open file. I can open file in LO.

@Paavo:could you try to find problematic part of file?
Comment 2 Paavo Ingalsuo 2014-10-10 16:45:38 UTC
@Paavo:could you try to find problematic part of file?

No.
We can try to find something, but it is not easy to me to find the reason of problem. I hope you can find the reason.

There is also one other minor bug.
In graphics on the first sheet there is text "sub-title".
I can delete this text and save worksheet and all is ok.
A little later, when i am opening it again, this "sub-title" is there again back! Why!
May be this is easier to solve.!
Comment 3 raal 2014-10-11 06:11:14 UTC
Hello Paavo,
the sheets are protected with password, please upload file without password.
Comment 4 Paavo Ingalsuo 2014-10-11 06:34:13 UTC Comment hidden (obsolete)
Comment 5 Paavo Ingalsuo 2014-10-11 06:36:22 UTC Comment hidden (obsolete)
Comment 6 raal 2014-10-11 06:51:29 UTC
@ Paavo: Tools -> protect document -> document
Pass is still here. I want to try delete parts of document to find where is the problem and I'm unable to delete sheet when document is locked.
Comment 7 Paavo Ingalsuo 2014-10-11 06:55:27 UTC
Created attachment 107698 [details]
Now unlocked..
Comment 8 raal 2014-10-11 12:11:33 UTC
Created attachment 107717 [details]
printscreen of workbook.xml

Hello Paavo,
I tried to delete all three sheets and save as xlsx. This file I can not open in excel viewer. So it looks like this file is somewhat broken. New file saved as xlsx I can open in excel viewer without problem.
Please could you verify this? Delete all sheets, save, open in Excel 2013 / Office 365.

I looked into xlsx file, in subfile workbook.xml are tags <definedNames>. After deleting them, I can resave file as .xlsx and open in the excel viewer. 

This file was originally excel file ("Excel_BuiltIn_Print_Area").
Comment 9 Markus Mohrhard 2015-04-22 04:01:13 UTC
Some initial notes. Our exported files are correct for that document.

The problem is a MSO limitation when it comes to implementing the standard. They have an artificial limit of 32 characters for the promptTitle attribute.

I'm not sure if there is a good fix yet or if we should just consider this a Microsoft Excel bug.
Comment 10 Bartosz 2016-07-24 18:16:46 UTC
Official documentation about promptTitle limitation is available at:
https://msdn.microsoft.com/en-us/library/dd948160(v=office.12).aspx
Comment 11 QA Administrators 2017-11-30 06:25:55 UTC Comment hidden (obsolete)
Comment 12 NISZ LibreOffice Team 2020-10-02 12:19:18 UTC
Created attachment 166034 [details]
Example file from Calc master

So this has changed a bit in recent Excel.

Attached example file has 4 sheets on each of them a cell validation on A2 with input title, input help, error title, error message each filled with 257 characters of loremipsum.

This exported to XLSX fails to open in my 2013, indicating invalid content on each of the 4 sheets.

Reducing the length of the texts to 256 characters makes the xlsx export open in Excel.
Comment 13 NISZ LibreOffice Team 2020-10-02 12:20:12 UTC
Created attachment 166035 [details]
Example file as xlsx with 257 character long validation
Comment 14 NISZ LibreOffice Team 2020-10-02 12:21:14 UTC
Created attachment 166036 [details]
The xlsx with 257 characters long validation messages opened in Excel 13
Comment 15 NISZ LibreOffice Team 2020-10-02 12:22:06 UTC
Created attachment 166037 [details]
Example file with 256 character long validation
Comment 16 NISZ LibreOffice Team 2020-10-02 12:23:53 UTC
Created attachment 166038 [details]
Example file with 256 character long validation saved as xlsx

This opens just fine.
Comment 17 Timur 2020-10-03 10:32:55 UTC
Hi Nisz, is this WFM now or what's left?
Comment 18 NISZ LibreOffice Team 2020-10-09 07:04:31 UTC
(In reply to Timur from comment #17)
> Hi Nisz, is this WFM now or what's left?

No, not WFM.
It is still possible to export more text for validation texts than Excel can handle original document is still bad in this regard.

Just the limits are slightly larger in 2013+ than in the documentation in comment #10 says (office.12 is 2007).

Calc / ODF can go above them so on export it is needed to cap message length to 256 characters otherwise Excel throws away the whole message (sorry for the Hungarian Excel screens).

Adjusting bug title now that we no longer aim for 2007 compatibility.
Comment 19 Commit Notification 2020-10-30 08:05:16 UTC
Tibor Nagy committed a patch related to this issue.
It has been pushed to "master":

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

tdf#84874 XLSX export: truncate validation text

It will be available in 7.1.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 20 NISZ LibreOffice Team 2020-11-17 10:10:43 UTC
Verified in:

Version: 7.1.0.0.alpha1+ (x64)
Build ID: 72056f39e01e36afac36c62988331b6813c8214f
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded

Thanks for fixing!