Large Number value converting it to exponential Numbers #3053

New Issue

Nagaraj172004 · 2024-01-08T11:10:49Z

Nagaraj172004 commented

2024-01-08 11:10:49 +00:00

Large Number value converting it to exponential Numbers used
{row = true in sheet_to_json method and dateNF: 'yyyy-mm-dd' } the reason is i need date in the yyy-mm-dd and large number let say 18 digits number .

in XLSXSample.utils.sheet_to_json method :-

**if i give raw = false ,dateNF: 'yyyy-mm-dd', , large number shows exponential number for 1234567891089760 it is giving '1.23457E+15' , and yes date is giving correct format as i mentioned in the dateNF.

if i give raw = true ,dateNF: 'yyyy-mm-dd', then then Date is taking text / string format
ex: Fri Jan 05 1990 23:59:50 GMT+0530 (India Standard Time) and largenumber number showing correct ,**

how to fix this in both column Date and ID number ,

FYI: Id number holds 18 digit number and Date holds Date data.

Large Number value converting it to exponential Numbers used {row = true in sheet_to_json method and dateNF: 'yyyy-mm-dd' } the reason is i need date in the yyy-mm-dd and large number let say 18 digits number . in XLSXSample.utils.sheet_to_json method :- **if i give raw = false ,dateNF: 'yyyy-mm-dd', , large number shows exponential number for 1234567891089760 it is giving '1.23457E+15' , and yes date is giving correct format as i mentioned in the dateNF. if i give raw = true ,dateNF: 'yyyy-mm-dd', then then Date is taking text / string format ex: Fri Jan 05 1990 23:59:50 GMT+0530 (India Standard Time) and largenumber number showing correct ,** how to fix this in both column Date and ID number , FYI: Id number holds 18 digit number and Date holds Date data.

sheetjs commented

2024-01-08 18:56:28 +00:00

Please read the following pages in the documentation first:

https://docs.sheetjs.com/docs/api/parse-options (readFile)
https://docs.sheetjs.com/docs/api/utilities/array#array-output (sheet_to_json)
https://docs.sheetjs.com/docs/csf/features/nf ("Number Formats")

The following explanation should be clear if you have read the documentation. If not, please let us know how the docs can be improved.

For each case, you should set cellDates: true when reading the file

If you want numbers and dates to be stringified according to Excel rules, set the following options in sheet_to_json:

raw: false

If you want numbers to be stringified but Dates to be passed back as date objects, set the following options in sheet_to_json:

rawNumbers: false

If you want dates to be stringified but numbers to be passed back as raw values, set the following options in sheet_to_json:

raw: false, rawNumbers: true

If you want numbers and dates to be passed back as raw values, do not set either option (or set both to true)

Excel "General" format uses exponential for your case.

If the file itself is using "General" but you want to force "0" format, you can loop through the relevant cells and set the number format / delete the old formatted text.

Please read the following pages in the documentation first: - https://docs.sheetjs.com/docs/api/parse-options (`readFile`) - https://docs.sheetjs.com/docs/api/utilities/array#array-output (`sheet_to_json`) - https://docs.sheetjs.com/docs/csf/features/nf ("Number Formats") The following explanation should be clear if you have read the documentation. If not, please let us know how the docs can be improved. --- For each case, you should set `cellDates: true` when reading the file If you want numbers and dates to be stringified according to Excel rules, set the following options in `sheet_to_json`: - `raw: false` If you want numbers to be stringified but Dates to be passed back as date objects, set the following options in `sheet_to_json`: - `rawNumbers: false` If you want dates to be stringified but numbers to be passed back as raw values, set the following options in `sheet_to_json`: - `raw: false, rawNumbers: true` If you want numbers and dates to be passed back as raw values, do not set either option (or set both to `true`) --- Excel "General" format uses exponential for your case. If the file itself is using "General" but you want to force "0" format, you can loop through the relevant cells and set the number format / delete the old formatted text.

i3053.xlsx

8.7 KiB

sheetjs closed this issue

2024-01-08 18:56:28 +00:00

Nagaraj172004 commented

2024-01-09 05:37:38 +00:00

thank you

Sign in to join this conversation.