Bug 83511 - FILEOPEN FILESAVE rounding XLSX numbers
Summary: FILEOPEN FILESAVE rounding XLSX numbers
Status: CLOSED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-04 21:46 UTC by SheetJS
Modified: 2018-10-29 00:25 UTC (History)
0 users

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description SheetJS 2014-09-04 21:46:23 UTC
Context: https://github.com/SheetJS/js-xls/issues/49#issuecomment-54542526

Excel stores raw IEEE754 numbers when saving.  For example, a cell with formula =0.1+0.2 will have a stored value of 0.30000000000000004.  However, LibreOffice appears to store the rounded value instead.

Based on the aforementioned context, I have collected some sample files at http://sheetjs.com/xls_issue_49/

xls_issue_49.xls is the original XLS file.  The other files are produced by opening the XLS file and saving as XLSX with the specified tool:

- xls_issue_49_2011.xlsx -- excel 2011 
- xls_issue_49_2013.xlsx -- excel 2013
- xls_issue_49_2013_strict.xlsx	-- excel 2013 Strict OpenXML
- xls_issue_49_libreoffice.xlsx	-- LibreOffice

Look at cell C172 in the sheet1.xml subfile of each.  The files based from Excel show:

      <c r="C172" s="1">
        <v>3.2400000000000005E-2</v>
      </c>

The LO file shows:

      <c r="C172" s="4" t="n">
        <v>0.0324</v>
      </c>

On a side note, the `t="n"` is strictly not needed according to ECMA-376: the default cell type is "n" as seen in the schema definition

    <xsd:attribute name="t" type="ST_CellType" use="optional" default="n"/>
Comment 1 Markus Mohrhard 2014-09-04 23:08:47 UTC
That is intentional. We ignore the last two bits for many stuff to improve the user experience.
Comment 2 SheetJS 2014-09-04 23:28:30 UTC
Related to the rounding bug is a fraction bug: set A1 to the formula =0.1+0.2 and set the format to Fraction with one digit.  LO gives 2/7 (which is what you would get if you round), but excel gives 1/3 (which is what the Aberth algorithm gives when you do include the error bits).

IMHO breaking consistency with excel's math doesn't "improve the user experience",
Comment 3 Dan Dascalescu 2018-10-29 00:25:33 UTC
Fudging numbers is not really acceptable for financial applications.