Created attachment 145352 [details]
The original XLSX file saved with the default font style settings.
Custom row heights in Microsoft Excel are affected by the default text style set in LibreOffice Calc. LibreOffice Calc uses 10 pt Liberation Sans as its default font. When a spreadsheet created in Calc is saved as XLSX and opened in Microsoft Excel, the row heights appear a bit smaller. The difference isn't obvious at first sight, but it can cause 1-2 cm difference in how the same spreadsheet is rendered by the end of the first page.
The strange thing is, that when the height of the row is checked manually in Excel, it shows (almost) the correct value (with some rounding differences), for example 0,99 cm instead of 1 cm. Printing the document and measuring the rows with a ruler makes it obvious, that in reality the real height of a row is close to 0,93 cm, so the displayed value in Microsoft Excel is incorrect.
If we change the default font style in LibreOffice Calc to Calibri 11pt, save the same spreadsheet again as XLSX, and open it in Excel, the rows are rendered (and printed) exactly as in Calc. It doesn't matter what characters and font sizes are actually used in the spreadsheet, changing the default is enough, even when the default style is not actively used in any cells with content.
Steps to reproduce:
1. Create a new spreadsheet in LibreOffice Calc. Write something in the first ten rows (like numbers from 1 to 10).
2. Change the row height for the first then rows to 1 cm.
3. Save the file as XLSX.
4. Print the file from LibreOffice Calc and Microsoft Excel and compare the results.
5. Open the XLSX again in Calc, change the default font style in the Styles and formatting sidebar to Calibri 11.
6. Save the document again as XLSX, preferably with a new filename to keep the original file for comparison.
7. Print the spreadsheet from LibreOffice Calc and Microsoft Excel and compare the results again.
The default font style set in LibreOffice Calc affects the height of the rows in Microsoft Excel.
The default font style set in LibreOffice Calc shouldn't affect the height of the rows in Microsoft Excel.
Build ID: 52cb73911e661fb636085337374e09845f07783b
CPU threads: 1; OS: Windows 6.1; UI render: default;
TinderBox: Win-x86@42, Branch:master, Time: 2018-10-02_01:05:50
Locale: hu-HU (hu_HU); Calc: threaded
Created attachment 145353 [details]
The modified XLSX file saved with Calibri 11pt as default font style.
Created attachment 145354 [details]
The original spreadsheet saved with the default font style settings exported from Microsoft Excel as PDF.
Created attachment 145355 [details]
The modified spreadsheet saved with Calibri 11pt as default font style exported from Microsoft Excel as PDF.
Created attachment 145356 [details]
A screenshot comparing the two PDF files. The same difference can be observed when the spreadsheets are printed from Excel.
Created attachment 148553 [details]
ODS to start testing from
LibO Version: 22.214.171.124.alpha0+
Build ID: 301ff4dfb82dfd961b993aec151784bd478b4f97
CPU threads: 4; OS: Windows 6.3; UI render: default; VCL: win;
TinderBox: Win-x86@42, Branch:master, Time: 2019-01-22_22:44:18
Locale: fi-FI (fi_FI); UI-Language: en-US
Gábor: would be cool, if you could regression test.
Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Dear Gabor Kelemen,
To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.
There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.
If you have time, please do the following:
Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/
If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.
Please DO NOT
Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)
If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/
2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword
Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa
Thank you for helping us make LibreOffice even better for everyone!
I experienced some strange behavior with excel row height: different views write different row heights for the same row, (I think they rounded differently) that make it hard to analyze.
To make it more confuse, the heights what excel export to xlsx (or what displayed/printed) are rounded differently.
It seems Excel is unable to display/print an exacty 10mm height row.. it can display a 10.05mm, or a 9.79mm height row... (even if it state that the row height is 28.25 pt at some views, and exported to xlsx as 28.35pt .. the row displayed/printed as 27.75 pt high)
it sems excel round down the 10mm to 9.79mm here.
that is why 26 piece of 10mm height row fit in 257mm. :)
Calc seems to be very precise .. its rounding error is way less then 0.01mm.. so, only 25 piece of 10mm height row fit in 257mm.
So i think this bug is the excel fault.
But we could simulate the excel roundings at import time, if we can recognize that the xlsx was exported by excel.
"When you change the width of a column to a fractional number, the column width may be set to a different number depending on the font used in the Normal style. For example, with a Normal style font of Arial, if you attempt to change the width of a column to 8.5, the column is set to 8.57 or 65 pixels. This behavior occurs because of the translation of font characters to pixel units. Fractional pixel units cannot be displayed; therefore, the column width rounds to the nearest number that results in a whole pixel unit." Source: https://docs.microsoft.com/en-us/office/troubleshoot/excel/determine-column-widths
Attila has found a 0.75 pt rounding, which seems to be the factor between 96 ppi of Windows resolution and (originally) 72 ppi of monitor resolutions:
Dear Attila Szűcs,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assign it back to yourself if you're still working on this.