Inaccurate converting big numbers.

[monqkim]

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)

[SheetJSDev]

@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]

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]

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.

[monqkim]

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]

Has anyone solved this problem

[enessusan00]

I solved this problem for my case

in excel:

in DOM

the code i use is

 const wb = XLSX.read(fileContent, { type: 'binary' });
 const wsname = wb.SheetNames[0];
 const ws = wb.Sheets[wsname];
 const jsonData: any[][] = XLSX.utils.sheet_to_json(ws, { header: 1, raw: false});

raw:true is also fixed the problem in different project 😂 . It is not about the .w format.