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
Created attachment 115821 [details] Original File (Before RT)
Created attachment 115822 [details] Roundtrip file
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
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.
*** Bug 94923 has been marked as a duplicate of this bug. ***
*** Bug 94255 has been marked as a duplicate of this bug. ***
*** Bug 94257 has been marked as a duplicate of this bug. ***
regression?
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.
*** Bug 94266 has been marked as a duplicate of this bug. ***
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]
*** Bug 97216 has been marked as a duplicate of this bug. ***
Created attachment 123503 [details] Test-Col-Width.xlsx bug example
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.
*** Bug 100982 has been marked as a duplicate of this bug. ***
(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
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.
*** Bug 101046 has been marked as a duplicate of this bug. ***
*** Bug 101053 has been marked as a duplicate of this bug. ***
Created attachment 126528 [details] xlsx file created with MS Excel 2010
*** Bug 101234 has been marked as a duplicate of this bug. ***
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?
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 :)
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.
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?
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
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.
*** Bug 101444 has been marked as a duplicate of this bug. ***
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.
Created attachment 128157 [details] File used for comparison different algorithms and native MS Excel column widths
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.
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.
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
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.