Bug Hunting Session
Bug 91475 - FILESAVE: Column width is not preserved in XLSX / after round trip.
Summary: FILESAVE: Column width is not preserved in XLSX / after round trip.
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.0.beta1
Hardware: Other All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: target:5.3.0.1 target:5.2.5
Keywords: bibisected, bisected, filter:xlsx, regression
: 94255 94257 94266 94923 97216 100982 101046 101053 101234 101444 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2015-05-22 12:29 UTC by darshan.gandhi
Modified: 2018-06-28 10:22 UTC (History)
21 users (show)

See Also:
Crash report or crash signature:


Attachments
Columnwidth is not being preseved after RT screenshot (205.84 KB, image/png)
2015-05-22 12:29 UTC, darshan.gandhi
Details
Original File (Before RT) (9.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-05-22 12:29 UTC, darshan.gandhi
Details
Roundtrip file (7.52 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-05-22 12:30 UTC, darshan.gandhi
Details
Test-Col-Width.xlsx bug example (5.15 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-03-11 14:54 UTC, brucehohl
Details
xlsx file created with MS Excel 2010 (10.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-08-02 21:23 UTC, Bartosz
Details
.xlsx column comparison: 1st line - LibreOffice Old algorithm, 2nd line - LibreOffice New algorithm, 3rd line - Excel for Mac 15.26 (1.76 MB, image/jpeg)
2016-10-22 13:42 UTC, Bartosz
Details
File used for comparison different algorithms and native MS Excel column widths (9.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-10-22 13:44 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description darshan.gandhi 2015-05-22 12:29:09 UTC
Created attachment 115820 [details]
Columnwidth is not being preseved after RT screenshot

Steps:

1. Create one .xlsx file in Excel, with conditional formatting init like Data Bars in Cells and mark show bars only to true.
2. Round trip same file from LO.
3. Verify that column width is being preserved after round trip and there is no visual impact on data bars.

Expected
Column width should be preserved as it.
Actual
Column width is not being preserved after round-trip. 

Note: In attached Column_width.xlsx file there is visual impact on cell "c7" because of column width is not being preserved after RT.
Before Round trip Column width: 8.43
After Round trip Column width: 7.46
Comment 1 darshan.gandhi 2015-05-22 12:29:54 UTC
Created attachment 115821 [details]
Original File (Before RT)
Comment 2 darshan.gandhi 2015-05-22 12:30:21 UTC
Created attachment 115822 [details]
Roundtrip file
Comment 3 raal 2015-05-24 11:07:09 UTC
I can confirm with Version: 5.1.0.0.alpha1+
Build ID: e929194317a7debb1c5467282230cbbabe61a710
TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-05-22_06:33:18

before> 5
after> 4,86
Comment 4 Ravindra Vidhate 2015-05-27 08:15:33 UTC
This has been started from https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=16726a1b37df8bdcae02b3c7699df814977222bd

author	Markus Mohrhard <markus.mohrhard@googlemail.com>	
	Wed, 20 May 2015 07:29:49 +0530 (03:59 +0200)
committer	Markus Mohrhard <markus.mohrhard@googlemail.com>	
	Wed, 20 May 2015 07:34:13 +0530 (04:04 +0200)
commit	16726a1b37df8bdcae02b3c7699df814977222bd.


Adding Markus for more investigation.

I think while exporting the column width, we also need to modify the calculations as well.
Comment 5 m.a.riosv 2015-10-09 22:02:38 UTC
*** Bug 94923 has been marked as a duplicate of this bug. ***
Comment 6 m.a.riosv 2015-10-09 22:04:28 UTC
*** Bug 94255 has been marked as a duplicate of this bug. ***
Comment 7 m.a.riosv 2015-10-09 22:06:13 UTC
*** Bug 94257 has been marked as a duplicate of this bug. ***
Comment 8 Cor Nouws 2015-10-10 07:14:32 UTC
regression?
Comment 9 Yousuf Philips (jay) (retired) 2015-10-10 08:56:37 UTC
I have addressed two different bugs in my two bug reports which have been placed as duplicates to this bug.

Bug 94255 is about the regression in 5.0 where the column width changed from 8 to 7.86, which had the same bibisect result as found in comment 4.

Bug 94257 is inherited from OOo where the column width was at 8 rather than being 8.43.
Comment 10 raal 2015-11-30 14:47:17 UTC
*** Bug 94266 has been marked as a duplicate of this bug. ***
Comment 11 Robinson Tryon (qubit) 2015-12-13 11:13:26 UTC Comment hidden (obsolete)
Comment 12 m.a.riosv 2016-01-18 03:03:12 UTC
*** Bug 97216 has been marked as a duplicate of this bug. ***
Comment 13 brucehohl 2016-03-11 14:54:28 UTC Comment hidden (obsolete)
Comment 14 brucehohl 2016-03-11 14:56:16 UTC
Comment on attachment 123503 [details]
Test-Col-Width.xlsx bug example

Another write-up / example of this bug.

1 Create new file

2 Add following content:
  A1 "aaa", B1 "bbbb", C1 "ccc55"
  E1 "aaa", F1 "bbbb", G1 "ccc55"

3 Auto size columns A-C and set column width on columns E-G to 0.5 IN.

4 Note size of each column in the worksheet.

5 Save worksheet in XLSX format then close.

6 Reopen and note that column width have increased.

Each time steps 4-6 are repeated the column widths grow.
An exception was column D which grew, shrunk, grew.
See attached worksheet.
Comment 15 m.a.riosv 2016-07-18 15:43:33 UTC
*** Bug 100982 has been marked as a duplicate of this bug. ***
Comment 16 Winfried Donkers (retired) 2016-07-20 11:34:19 UTC
(In reply to brucehohl from comment #14)
> Comment on attachment 123503 [details]
> Test-Col-Width.xlsx bug example
> 
> Another write-up / example of this bug.
> 
> 1 Create new file
> 
> 2 Add following content:
>   A1 "aaa", B1 "bbbb", C1 "ccc55"
>   E1 "aaa", F1 "bbbb", G1 "ccc55"
> 
> 3 Auto size columns A-C and set column width on columns E-G to 0.5 IN.
> 
> 4 Note size of each column in the worksheet.
> 
> 5 Save worksheet in XLSX format then close.
> 
> 6 Reopen and note that column width have increased.
> 
> Each time steps 4-6 are repeated the column widths grow.
> An exception was column D which grew, shrunk, grew.

When a new document is created with column A width of 2.00cm and Column B width of 5.08cm (2 in.), and then repeatedly saved as xlsx, closed and reopened, the column widths decrease every time:
Column A      B
    2.00   5.08
    1.96   5.00
    1.93   4.95
    1.91   4.89
    1.88   4.84
Comment 17 brucehohl 2016-07-21 01:30:31 UTC
Yet another round trip test using:
Help > About LibreOffice
Version: 5.1.4.2
Build ID: 1:5.1.4-0ubuntu1~trusty1
CPU Threads: 2; OS Version: Linux 3.13; UI Render: default; 
Locale: en-US (en_US.UTF-8)

Columns widths after each save as xlsx then reopen file:
A      B      C      D      E
1.01   2.01   3.01   4.01   5.01  (starting column widths in inches)
1.04   2.07   3.1    4.14   5.17
1.08   2.13   3.21   4.29   5.34
1.11   2.2    3.31   4.42   5.52
1.14   2.27   3.43   4.56   5.7
1.17   2.34   3.53   4.71   5.89
				
Change in size:				
1.0297029703   1.0298507463   1.0299003322   1.0324189526   1.0319361277
1.0384615385   1.0289855072   1.035483871    1.0362318841   1.0328820116
1.0277777778   1.0328638498   1.031152648    1.0303030303   1.0337078652
1.027027027    1.0318181818   1.0362537764   1.0316742081   1.0326086957
1.0263157895   1.0308370044   1.029154519    1.0328947368   1.0333333333

For Winfried the columns decreased slightly in size.  In my case, using the LibreOffice version noted, the columns increased by about 3% for each save then reopen.  Regardless, column width is not preserved.
Comment 18 Buovjaga 2016-07-23 18:56:23 UTC
*** Bug 101046 has been marked as a duplicate of this bug. ***
Comment 19 Buovjaga 2016-07-25 19:20:48 UTC
*** Bug 101053 has been marked as a duplicate of this bug. ***
Comment 20 Bartosz 2016-08-02 21:23:50 UTC
Created attachment 126528 [details]
xlsx file created with MS Excel 2010
Comment 21 Bartosz 2016-08-03 23:58:01 UTC
*** Bug 101234 has been marked as a duplicate of this bug. ***
Comment 22 DocJohnson 2016-08-05 09:19:47 UTC
The automatic shrinking of column width happens with any xlsx-document, no matter if it's created by Calc, or if it has any data in it at all. The width of every column keeps changing after the save and reload process in Calc. Is anyone able to confirm this 'in general' (e.g. with a simple list or just an empty spreadsheet) and not just with specific/special data-formatting or calculations?
Comment 23 brucehohl 2016-08-05 11:17:12 UTC
Regarding comment #22, my comment #17 was with empty sheet.  With 20+ comments on this bug and about 10 duplicate bugs these seems to be sufficient confirmation of this bug.

I don't how the criteria for the "Importance" setting but as an LO user who frequently works with XLSX files this bug is quite significant as it mangles the formatting of EVERY (even very simple) XLSX files opened. This impacts not only screen layout but also printing as growth in column width pushes the last column out of the print range. Here's hoping for a kind developer to help with a fix :)
Comment 24 Bartosz 2016-08-05 13:45:19 UTC
Regression which was made cannot be reverted, because it could potentially cause another regression.

Here is more details in review:
https://gerrit.libreoffice.org/#/c/27738/

Currently I'm working on fix that issue according to MS specification.
It's not trivial as it needs test of all corner cases.
Comment 25 DocJohnson 2016-08-07 13:00:37 UTC
Wondering what features or improvements this new algorithm introduced at last, which cause and 'compensate' this troublesome impact on general/basic calculation and listing tasks, considering this comprehensive issue wasn't noticeable in the lasted build of LibreOffice v4? Or is this necessary for better compatibility with the current MS Office/Excel versions?
Comment 26 Bartosz 2016-08-12 22:07:04 UTC
Second try of reverting back to old algorithm.
https://gerrit.libreoffice.org/#/c/27943/

I have added many test cases, which proove that old algorithm is working correctly in case of:
 1. After resave .xlsx file, the column width values did not change
 2. After export .ods to .xlsx displayed column width did not change
 3. After save .xlsx to .ods displayed column width did not change
Comment 27 Bartosz 2016-08-12 22:09:28 UTC
Unfortunately the same unit tests are not pass with New Algorithm.

I spend many long hours with trying to fix new algorithm, but unfortunately it is really complicated as the column width should math in wide spectrum of values.
Comment 28 Bartosz 2016-08-12 22:15:06 UTC
*** Bug 101444 has been marked as a duplicate of this bug. ***
Comment 29 Bartosz 2016-10-22 13:42:53 UTC
Created attachment 128156 [details]
.xlsx column comparison: 1st line - LibreOffice Old algorithm, 2nd line - LibreOffice New algorithm, 3rd line - Excel for Mac 15.26

Finally I have manage to build LibreOffice on OSX (I tried already with Windows, without success) and run Excel for Mac 15.26, 
print test pages and compare old algorithm and new algorithm.
I would like to find system where Excel and LibreOffice is working native. l found only OSX and Windows.
I managed to compile LibreOffice on OSX and also I installed MS Excel for Mac 15.26 on it.
(BTW To my surprise .xlsx files are generated slightly differently than on Windows)

I was downloaded LibreOffice from master and compiled it with old and new (current one) algorithm.
I'm attaching the photo from comparison of import different-column-width-excel2010.xlsx test. I have used the same printer settings.

Unfortunately I cannot say which algorithm is better.
My suggestion is to go back to the old algorithm, as it is working correctly with LibreOffice (the column width are preserved) and with .xlsx round trip. I proofed that with units tests, which will prevent from introduction such regression in future.
Comment 30 Bartosz 2016-10-22 13:44:37 UTC
Created attachment 128157 [details]
File used for comparison different algorithms and native MS Excel column widths
Comment 31 Commit Notification 2016-12-05 11:47:33 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8a92f539bae8e2253f1e926a38bf0d669384c13d&h=libreoffice-5-3

tdf#91475 Fix column width during .xlsx export

It will be available in 5.3.0.1.

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 32 Commit Notification 2016-12-05 17:51:32 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=962b53a3a36c388d7fa9706541e3d630292861c4&h=libreoffice-5-2

tdf#91475 Fix column width during .xlsx export

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.
Comment 33 Bruce 2018-06-28 02:01:16 UTC
This  says it was fixed in version 5!  I'm getting it in version 6.0.2 for an .xls file on Win10 64!  This file was created with LO.  I have also noted it in 2 other .xls files created in Excel
Comment 34 Timur 2018-06-28 10:22:35 UTC
Bruce, I can confirm this was fixed for attachment 115821 [details] and attachment 123503 [details].
In case it wasn't then we would Reopen this bug. 

If you have other example files, please open a new bug, attach those files and put this bug to Seel Also. 

You can help us by testing with diff. LO versions: 3.3, 5.1 (i.e. before this fix), 5.3.7 (with this fix), 6.0 (current), 6.2+ (master.) 
You may use Separate Install GUI tool from http://tdf.io/siguiexe which downloads and extracts different LO versions, without installing, so you may test different versions. It only needs MS Visual C++ Runtimes installed.