Auto converting string data with all integer to exponential. #2609

gokinasaiajay · 2022-03-13T17:59:35Z

While writing string data (example = "121212121212121" -> string of all integer characters) into excel.
while user download and see, the downloaded xl/CSV, the data is shown in exponential format. Is there a way to show the same data. without converting into exponential in this case.

SheetJSDev · 2022-03-13T23:05:55Z

To force the string 121212121212121, Excel needs to see ="121212121212121" (a formula whose result is the desired string). =121212121212121 is interpreted as numeric, as is "121212121212121"

Currently, this can be generated by setting the formula of a cell. A sample: https://jsfiddle.net/sheetjs/no2gx45e/

var num = 121212121212121, str = "121212121212121";
var ws = XLSX.utils.aoa_to_sheet([
  ["num",     num               ], // { t: "n", n: num }.    -> 1.21212E+14        -> number
  ["str",     str               ], // { t: "s", v: str }     -> 121212121212121    -> number
  ["numfmla", { f: str }        ], // { f: str }             -> =121212121212121   -> number
  ["strfmla", { f: `"${str}"` } ]  // { f: '"' + str + '"' } -> ="121212121212121" -> string
]);

We'd accept a PR that looks for strings that may be interpreted as numbers. https://github.com/SheetJS/sheetjs/blob/master/bits/90_utils.js#L111-L112 is where the actual string values are generated, and a simple check using parseInt would address the problem.

SheetJSDev added the good first issue label Mar 13, 2022

SheetJS / sheetjs Public

Auto converting string data with all integer to exponential. #2609

Auto converting string data with all integer to exponential. #2609

gokinasaiajay commented Mar 13, 2022

SheetJSDev commented Mar 13, 2022

SheetJS / sheetjs Public

Auto converting string data with all integer to exponential. #2609

Auto converting string data with all integer to exponential. #2609

Comments

gokinasaiajay commented Mar 13, 2022

SheetJSDev commented Mar 13, 2022