Bug 101363 - FILESAVE: Precision of column width is not limited during .xlsx export
Summary: FILESAVE: Precision of column width is not limited during .xlsx export
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Bartosz
URL:
Whiteboard: target:5.3.0 target:5.2.5 target:5.4....
Keywords:
Depends on:
Blocks:
 
Reported: 2016-08-07 08:41 UTC by Bartosz
Modified: 2019-04-24 13:29 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Bartosz 2016-08-07 08:41:15 UTC
In MS specification the output value is set with double precision after delimiter, according to formula:
 =Truncate(({width in pixels} - 5)/{Maximum Digit Width} * 100 + 0.5)/100

Explanation of magic numbers:
            // 5 number - are 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines. It is still unknown if it should be applied during LibreOffice export
            // 100 number - used to limit precision to 0.01 with formula =Truncate( {value} * 100 ) / 100
            // 0.5 number (0.005 to output value) - used to increase value before truncating, to avoid situation when 2.997 will be truncated to 2.99 and not to 3


We should also apply such precision for LibreOffice.
So instead of values like:
   <col collapsed="false" customWidth="true" hidden="false" outlineLevel="0" max="2" min="1" style="0" width="8.50510204081633"/>

It will be rounded to:
        <col collapsed="false" customWidth="true" hidden="false" outlineLevel="0" max="1025" min="1" style="0" width="8.50"/>
Comment 1 Bartosz 2016-08-07 18:15:17 UTC
Benefits of limited precision of column width:
- smaller .xlsx file size
- slightly better performance during .xlsx import
- easier to track column width changes, especially in unit tests
- according to MS Excel specification
Comment 2 Commit Notification 2016-08-07 21:50:16 UTC
Bartosz Kosiorek committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=12408dad1b2af4055b91439e3cfbe46e0df52b41

tdf#101363 Fix precision of column width according to MS specification

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 3 Commit Notification 2016-12-05 17:47:18 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=8ec7c5e332c30d807a0ed0b765e44559181c91a2&h=libreoffice-5-2

tdf#101363 Fix precision of column width according to MS specification

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 4 Commit Notification 2016-12-05 21:26:04 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=39aa5c2c653f36393416a6aadd6e599051bbfe7b

attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build for 5-2, tdf#101363

It will be available in 5.4.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 5 Commit Notification 2016-12-05 22:32:21 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

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

attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build for 5-2, tdf#101363

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 6 Commit Notification 2016-12-05 22:35:05 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build for 5-2, tdf#101363

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 7 Commit Notification 2016-12-06 20:51:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=709053fc72db3eacfec0a7d8e47557b34bc6193e

Revert "attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build for 5-2, tdf#101363"

It will be available in 5.4.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 8 Commit Notification 2016-12-06 22:24:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

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

Revert "attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build, tdf#101363"

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 9 Commit Notification 2016-12-07 08:54:59 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

Revert "attempt to fix Linux-rpm_deb-x86_71-TDF tinderbox build, tdf#101363"

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 10 Commit Notification 2016-12-07 14:24:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=20b54a228b2844ad66d80d930d4a9eb39ce8c336

stab at the sick "units in character width of the standard font", tdf#101363

It will be available in 5.4.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 Commit Notification 2016-12-07 16:00:13 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

stab at the sick "units in character width of the standard font", tdf#101363

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 12 Commit Notification 2016-12-07 21:06:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-2":

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

stab at the sick "units in character width of the standard font", tdf#101363

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 13 Mike Kaganski 2019-04-24 05:36:51 UTC
Could you please also provide a reference (a link or a document id) of the specification? In fact, MS Excel 2016 happily outputs something like this:

>    <sheetFormatPr defaultColWidth="20.7109375" defaultRowHeight="15" x14ac:dyDescent="0.25"/>
>    <cols>
>        <col min="2" max="2" width="10.7109375" customWidth="1"/>
>    </cols>

... so is this really needed?
Comment 14 Bartosz 2019-04-24 13:29:45 UTC
@mike
According to the formula used for translation, precision should be limited to 2 digits:
https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.aspx

Limitation is clearly visible with following strings:
 "* 100 + 0.5)/100"