Bug 124741 - Cell width/height changes when exported to XLSX
Summary: Cell width/height changes when exported to XLSX
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.0.3 release
Hardware: All All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: target:6.3.0
Keywords: bibisected, bisected, regression
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2019-04-15 05:47 UTC by Aron Budea
Modified: 2019-08-06 14:29 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample ODS (12.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-04-15 05:47 UTC, Aron Budea
Details
XLSX exported from ODS (5.79 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-04-15 05:48 UTC, Aron Budea
Details
Comparison screenshot (40.89 KB, image/png)
2019-04-15 05:50 UTC, Aron Budea
Details
Sample ODS exported to XLSX with Excel 2016 (9.23 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-04-15 09:43 UTC, Bartosz
Details
Sample ODS exported to XLSX with LO 5.2.0.4 (5.73 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-04-15 15:59 UTC, Aron Budea
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2019-04-15 05:47:51 UTC
Created attachment 150753 [details]
Sample ODS

The attached spreadsheet table was copied from bug 123421's sample, just without pivot table content. The sample is using Calibri font, which is installed on the system.

Save it as XLSX and reopen.

=> The width of the most columns and height of empty rows change.

Original 1st and 2nd column widths: 2.26 cm & 2.76 cm.
XLSX 1st and 2nd column widths: 1.7 cm & 2.76 cm.

Height of 2nd and other empty rows also changes from 0.56 cm to 0.45 cm.

Observed using LO 6.3.0.0.alpha0+ (2e3b0c5d42d60d46cd9f8b8eda9424b095c63418) & 5.3.0.3 / Windows 7.
Looks better in 5.2.0.4.
=> somewhat of a regression

In 5.2.0.4 the column widths in the exported XLSX are 2.22 and 2.73 cm respectively. The row height is still 0.45 cm.

Bibisected the 5.2 -> 5.3 difference to the following commit using repo bibisect-linux-64-5.3. Adding Cc: to Bartosz Kosiorek.

https://cgit.freedesktop.org/libreoffice/core/commit/?id=40d892a2db4d750aaf0562c63004e693c028273c
author		Bartosz Kosiorek <gang65@poczta.onet.pl>	2016-07-19 00:26:54 +0200
committer	Markus Mohrhard <markus.mohrhard@googlemail.com>	2016-07-28 23:23:49 +0200

tdf#100946 Fix width calculation and add customWidth support (.xlsx)
Comment 1 Aron Budea 2019-04-15 05:48:23 UTC
Created attachment 150754 [details]
XLSX exported from ODS
Comment 2 Aron Budea 2019-04-15 05:50:09 UTC
Created attachment 150755 [details]
Comparison screenshot
Comment 3 Aron Budea 2019-04-15 05:57:45 UTC
In Excel the exported sample has:
- 15 row height (uniform),
- 8.43 and 13.43 column widths.

Change the column width and row height
https://support.office.com/en-gb/article/change-the-column-width-and-row-height-72f5e3cc-994d-43e8-ae58-9774a0905f46
Comment 4 Bartosz 2019-04-15 09:27:01 UTC
Please attach .xlsx file exported with Calc 5.2.0.4.

The Default column width for MS Office is taken from Default Font size:
https://www.ablebits.com/office-addins-blog/2017/02/28/change-autofit-column-width-excel/

In LibreOffice it is explicetely set:
https://ask.libreoffice.org/en/question/4619/how-does-one-change-default-value-of-column-width/

Of course rows height could be easily fixed.
Comment 5 Bartosz 2019-04-15 09:43:14 UTC
Created attachment 150761 [details]
Sample ODS exported to XLSX with Excel 2016
Comment 6 Aron Budea 2019-04-15 15:59:22 UTC
Created attachment 150774 [details]
Sample ODS exported to XLSX with LO 5.2.0.4

(In reply to Bartosz from comment #4)
> Please attach .xlsx file exported with Calc 5.2.0.4.
Done, thanks!
Comment 7 Bartosz 2019-04-17 05:31:22 UTC
@Aron 
Unfortunately I was not able to create such document.
If I create new document with custom cell width and height, it is properly exported by LO and looks ok under Excel and LibreOffice.

Could you please provide instruction how to create such document?
Comment 8 Aron Budea 2019-04-18 15:32:26 UTC
(In reply to Bartosz from comment #7)
> Could you please provide instruction how to create such document?
I used attachment 149245 [details] from bug 123421, which is a pivot table, saving it as XLSX and reloading shows the narrow columns (perhaps that sample is more authentic, I just wanted to get rid of the unnecessary pivot parts).

For the sample attached here I just copied the table, deleted the pivot table, pasted the table in its place, deleted the other pivot-related data and saved it.
Comment 9 Aron Budea 2019-04-18 15:34:31 UTC
(In reply to Aron Budea from comment #8)
> saving it as XLSX and reloading shows the narrow columns
A single narrow column, to be precise.
Comment 10 Mike Kaganski 2019-04-23 13:15:23 UTC
https://gerrit.libreoffice.org/71132

I'm sorry to jump in, not seeing it's assigned. The patch above would hopefully fix this; still, there's something unusual with the default width handling that I didn't try to closely debug:

Saving it to an Excel format goes through the procedure of defining default width in XclExpColinfoBuffer::Finalize: the most-used width defined as the default. Then (previously to my patch) all the columns with such widths were removed. That is what (properly!) happened with the bug doc; but with hand-made samples, this funnily does not work:

For a simple newly created spreadsheet with two cells with arbitrary content (A1 and B1), and column B having non-default width (all other columns not resized!), the default size is defined from width of column A (equal to width of columns from C and to the end). But immediately then, checking if column A has default width (when removing defaulted columns), returns false! Thus all columns (improperly!) seem "non-default", and all are written to the file, thus not exhibiting the bug.
Comment 11 Commit Notification 2019-04-24 07:23:47 UTC
Mike Kaganski committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/071d72cc3662168c58358ce53a77ceacbf80f545%5E%21

tdf#124741: export default column width to XLSX

It will be available in 6.3.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 12 Bartosz 2019-04-24 14:45:19 UTC
I will check if this patch has resolved column width issue issue.
Still the height of rows needs to be fixed.
Comment 13 Xisco Faulí 2019-08-06 14:29:37 UTC
(In reply to Bartosz from comment #12)
> I will check if this patch has resolved column width issue issue.
> Still the height of rows needs to be fixed.

Any update ?