Inaccurate converting big numbers. #2562

New Issue

2018-08-30T07:29:52Z

monqkim commented

2018-08-30 07:29:52 +00:00

(Migrated from github.com)

I have an xlsx file including some big numbers below.

After converting this file using XLSX.sheet_to_json(), the part of result data is

"B50": {
    "t": "n",
    "v": 23000000,
    "w": "23000000"
  },
  "C50": {
    "t": "n",
    "v": 32199999.999999996,
    "w": "32200000"
  },
  "D50": {
    "t": "n",
    "v": 48299999.99999999,
    "w": "48300000"
  },
  "E50": {
    "t": "n",
    "v": 77199999.99999999,
    "w": "77200000"
  },
  "F50": {
    "t": "n",
    "v": 153999999.99999997,
    "w": "154000000"
  },
  "G50": {
    "t": "n",
    "v": 337999999.99999994,
    "w": "338000000"
  },
  "H50": {
    "t": "n",
    "v": 810999999.9999999,
    "w": "811000000"
  },
  "I50": {
    "t": "n",
    "v": 2099999999.9999998,
    "w": "2100000000"
  },
  "J50": {
    "t": "n",
    "v": 5879999999.999999,
    "w": "5880000000"
  },
  "K50": {
    "t": "n",
    "v": 18799999999.999996,
    "w": "18799999999"
  },

Due to inaccurate number converting, I used to use 'w' property to make accurate result, but in "K50" cell case, 'w' property is not accurate also. (original value is 18800000000)

I have an xlsx file including some big numbers below. ![image](https://user-images.githubusercontent.com/18694747/44835835-4a707a00-ac70-11e8-9571-43bc2adc2d5a.png) After converting this file using XLSX.sheet_to_json(), the part of result data is ``` "B50": { "t": "n", "v": 23000000, "w": "23000000" }, "C50": { "t": "n", "v": 32199999.999999996, "w": "32200000" }, "D50": { "t": "n", "v": 48299999.99999999, "w": "48300000" }, "E50": { "t": "n", "v": 77199999.99999999, "w": "77200000" }, "F50": { "t": "n", "v": 153999999.99999997, "w": "154000000" }, "G50": { "t": "n", "v": 337999999.99999994, "w": "338000000" }, "H50": { "t": "n", "v": 810999999.9999999, "w": "811000000" }, "I50": { "t": "n", "v": 2099999999.9999998, "w": "2100000000" }, "J50": { "t": "n", "v": 5879999999.999999, "w": "5880000000" }, "K50": { "t": "n", "v": 18799999999.999996, "w": "18799999999" }, ``` Due to inaccurate number converting, I used to use 'w' property to make accurate result, but in "K50" cell case, 'w' property is not accurate also. (original value is 18800000000)

SheetJSDev commented

2018-08-30 07:31:45 +00:00

(Migrated from github.com)

@monqkim the v values are coming from the file itself directly, which means that Excel likely stored the values with the remainder. Can you share a sample file?

@monqkim the `v` values are coming from the file itself directly, which means that Excel likely stored the values with the remainder. Can you share a sample file?

monqkim commented

2018-08-30 07:43:26 +00:00

(Migrated from github.com)

sample.xlsx

and here is my options.

const XLSX_READ_FILE_OPTS = {
  bookDeps: false,
  bookFiles: false,
  bookProps: false,
  bookSheets: false,
  bookVBA: false,
  cellDates: true,
  cellFormula: false,
  cellHTML: false,
  cellNF: false,
  cellStyles: false,
  cellText: true,
  dense: false,
  raw: false,
  sheetStubs: false,
  WTF: false,
};

[sample.xlsx](https://github.com/SheetJS/js-xlsx/files/2335128/sample.xlsx) and here is my options. ``` const XLSX_READ_FILE_OPTS = { bookDeps: false, bookFiles: false, bookProps: false, bookSheets: false, bookVBA: false, cellDates: true, cellFormula: false, cellHTML: false, cellNF: false, cellStyles: false, cellText: true, dense: false, raw: false, sheetStubs: false, WTF: false, }; ```

SheetJSDev commented

2018-08-30 07:48:15 +00:00

(Migrated from github.com)

Just so we're on the same page here, the v values are exactly what is stored in the Excel file. To see, go to
https://sheetjs.com/cfb-editor/ then drag and drop the sample file, click "xl/worksheets/sheet1.xml" then click "View as Text" to see what is actually stored in the file:

It's clearly an artifact of the IEEE754 representation that Excel and JavaScript use, which is unfortunate but it is what it is.

Just so we're on the same page here, the `v` values are exactly what is stored in the Excel file. To see, go to https://sheetjs.com/cfb-editor/ then drag and drop the sample file, click "xl/worksheets/sheet1.xml" then click "View as Text" to see what is actually stored in the file: <img width="379" alt="" src="https://user-images.githubusercontent.com/6070939/44837292-38a6bc80-ac07-11e8-919d-9f6334d74004.png"> It's clearly an artifact of the IEEE754 representation that Excel and JavaScript use, which is unfortunate but it is what it is.

monqkim commented

2018-08-30 08:03:34 +00:00

(Migrated from github.com)

Thanks for the answer, but why the 'w' value is different from the others? The 'w' of other cells reflects original formatted text accurately, but only 18800000000 cell's 'w' is different from the original value. Is it related to the digits after the floating point?

Thanks for the answer, but why the 'w' value is different from the others? The 'w' of other cells reflects original formatted text accurately, but only `18800000000` cell's 'w' is different from the original value. Is it related to the digits after the floating point?

jamesdanner commented

2021-06-06 09:25:57 +00:00

(Migrated from github.com)

Has anyone solved this problem

Sign in to join this conversation.