Bug 100946 - Excel on OS X ignored column widths in XLSX last saved by LO
Summary: Excel on OS X ignored column widths in XLSX last saved by LO
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.6.3 release
Hardware: All Mac OS X (All)
: medium normal
Assignee: Bartosz
QA Contact:
URL:
Whiteboard: target:5.3.0 target:5.2.5
Keywords:
: 92753 (view as bug list)
Depends on:
Blocks: MacOS-UI
  Show dependency treegraph
 
Reported: 2016-07-16 09:15 UTC by Kuno Meyer
Modified: 2016-12-05 17:46 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
File created with LO causing MS Excel on Mac to behave badly. (5.90 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-16 09:15 UTC, Kuno Meyer
Details
File created with LO and manually fixed by adding customWidth="1" (5.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-16 20:57 UTC, Bartosz
Details
File created with LO and manually fixed by adding customWidth="1" and removed workbookProtection (5.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-19 23:44 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kuno Meyer 2016-07-16 09:15:44 UTC
Created attachment 126228 [details]
File created with LO causing MS Excel on Mac to behave badly.

See the attached file 'corrupt-column-widths-on-mac.xlsx'. It was last saved with LO 5.0.6.

a) When you open that file with MS Excel 2011 on Mac OS, the column widths are lost and all columns have default width. 

b) In addition, while the column width issue can easily reproduced with any document, for this specific document you neither can resize the columns nor you can move the spreadsheet window on your MAC OS desktop.

So, something is clearly incompatible between LO and MS Excel on MAC. Is it the increased precision for floating point numbers? Is it the additional "collapsed"/"hidden" attributes that are not there when the file is created by MS Excel? I don't know.

<col collapsed="false" hidden="false" max="1" min="1" style="1" width="44.4651162790698"/>
Comment 1 steve -_- 2016-07-16 15:48:09 UTC
Confirming this. tested with LO master

* save file with various column widths als xlsx file
* open with excel

Issue a) ✔
→ width settings are gone when opened with excel
→ when opened xlsx file with LO column width is correctly opened

Issue b) ✔
when opened with excel, window cannot be moved around. really odd.

Version: 5.3.0.0.alpha0+
Build ID: e6c004dd9f24c32f5e7468182a5e8d42293ec7b6
CPU Threads: 4; OS Version: Mac OS X 10.11.5; UI Render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2016-06-17_06:40:25
Locale: de-DE (de.UTF-8)

Setting to NEW
Comment 2 Bartosz 2016-07-16 20:57:46 UTC
Created attachment 126242 [details]
File created with LO and manually fixed by adding customWidth="1"

I have added customWidth="1" to example file and everything is working perfectly fine.
Unfortunately there is still missing defaultColWidth field.

I will try to resolve that issue soon.
Comment 3 Bartosz 2016-07-18 22:38:35 UTC
Review is available at:
https://gerrit.libreoffice.org/#/c/27306/
Comment 4 Kuno Meyer 2016-07-19 18:28:34 UTC
Thanks for the very quick response!

I tested attachment 126242 [details] on my machine:
- The document opens in Excel correctly with regard to the column widths (case a)
- It is still not fully possible to resize the document window: I can make it smaller (not gradually - it fully collapses), but I cannot make it larger (case b).
Comment 5 Bartosz 2016-07-19 23:15:58 UTC
Could you please attach original .ods file, which after export to .xlsx is causing such problems?
Comment 6 Bartosz 2016-07-19 23:44:41 UTC
Created attachment 126311 [details]
File created with LO and manually fixed by adding customWidth="1" and removed workbookProtection

It looks like for this .xlsx file, the workbookProtection was enabled.
I have removed workbookProtection manually from that file. Please check if it is working correctly now.

Can you disable workbookProtection from MS Excel?
I'm wondering if this is an feature or bug which we need fix.

More information about Protection:
https://support.office.com/en-us/article/Protect-a-workbook-7e365a4d-3e89-4616-84ca-1931257c1517
https://help.libreoffice.org/Calc/Protecting_Cells_from_Changes
Comment 7 Kuno Meyer 2016-07-20 07:04:03 UTC
Regarding comment 5: I'm sorry, but I do not have an original .ods version of that file. The document  might have been created by any of {Excel/Win, Excel/OSX, LO/Win} (I cannot really say), but it has only ever been saved as .xlsx.

Regarding comment 6: I will try attachment 126311 [details] this evening on my wife's iMac. I am quite sure (close to 100%) that none of us two ever activated explicitly the cell/workbook protection for that document.
Comment 8 Kuno Meyer 2016-07-20 18:58:55 UTC
Case a) has been demonstrated solved with attachment 126242 [details].

Case b) still persists with attachment 126311 [details], so it looks as if there is no connection to cell protection. However, my latest tests with LO 5.1.4 indicate that there is a connection with having "View > frozen rows and columns" (for scrolling) being active. I am going to open a new bug for that.

In conclusion, this current bug report can be reduced to case a) only.
Comment 9 Kuno Meyer 2016-07-20 19:10:15 UTC
Created bug 101042 for case b).
Comment 10 Commit Notification 2016-07-28 21:24:36 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=40d892a2db4d750aaf0562c63004e693c028273c

tdf#100946 Fix width calculation and add customWidth support (.xlsx)

It will be available in 5.3.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 11 Bartosz 2016-07-31 21:28:41 UTC
*** Bug 92753 has been marked as a duplicate of this bug. ***
Comment 12 steve -_- 2016-07-31 21:47:26 UTC
Version: 5.3.0.0.alpha0+
Build ID: 59f5436f60a0201c8aa6a2415625b71134022af9
CPU Threads: 8; OS Version: Mac OS X 10.11.6; UI Render: default; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2016-07-31_00:43:07
Locale: de-DE (de.UTF-8); Calc: group

Verified.

Bartosz, thanks a lot for this fix. Another compatibility annoyance gone.
Comment 13 Commit Notification 2016-12-05 17:46:48 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=02d93c7a53f55f0416d321b3cddfb8b6fc9e6c59&h=libreoffice-5-2

tdf#100946 Fix width calculation and add customWidth support (.xlsx)

It will be available in 5.2.5.

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

Affected users are encouraged to test the fix and report feedback.