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

New Issue

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

gokinasaiajay commented

2022-03-13 17:59:35 +00:00

(Migrated from github.com)

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.

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 commented

2022-03-13 23:05:55 +00:00

(Migrated from github.com)

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.

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/ ```js 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.

Mikecarbon commented

2022-03-18 03:58:38 +00:00

(Migrated from github.com)

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.
22 is the sum so break that down to intergers 2 and 11

> 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. 22 is the sum so break that down to intergers 2 and 11

Mikecarbon commented

2022-03-18 08:05:50 +00:00

(Migrated from github.com)

Give 2 and 11 value

Sarfraz-droid commented

2022-03-23 07:57:11 +00:00

(Migrated from github.com)

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.

Hi, should it do something like this ?

txt = ('' + (o.rawNumbers && val.t == "n" ? val.v : parseInt(format_cell(val,null, o)) === 1 ? val.v : format_cell(val,null, o) ));

> 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/ > > ```js > 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. Hi, should it do something like this ? ``` txt = ('' + (o.rawNumbers && val.t == "n" ? val.v : parseInt(format_cell(val,null, o)) === 1 ? val.v : format_cell(val,null, o) )); ````

AbhiramTS commented

2023-02-03 06:45:31 +00:00

diff --git a/bits/90_utils.js b/bits/90_utils.js
index c7df543..8db4bc2 100644
--- a/bits/90_utils.js
+++ b/bits/90_utils.js
@@ -110,10 +110,13 @@ function make_csv_row(sheet/*:Worksheet*/, r/*:Range*/, R/*:number*/, cols/*:Arr
 			isempty = false;
 			txt = ''+(o.rawNumbers && val.t == "n" ? val.v : format_cell(val, null, o));
 			for(var i = 0, cc = 0; i !== txt.length; ++i) if((cc = txt.charCodeAt(i)) === fs || cc === rs || cc === 34 || o.forceQuotes) {txt = "\"" + txt.replace(qreg, '""') + "\""; break; }
+			if (parseInt(txt) == txt) txt = "\"" + txt + "\"";
 			if(txt == "ID") txt = '"ID"';
 		} else if(val.f != null && !val.F) {
 			isempty = false;
-			txt = '=' + val.f; if(txt.indexOf(",") >= 0) txt = '"' + txt.replace(qreg, '""') + '"';
+			txt = '=' + val.f;
+			if (parseInt(val.f) == val.f) txt = "\"" + val.f + "\"";
+			if(txt.indexOf(",") >= 0) txt = '"' + txt.replace(qreg, '""') + '"';
 		} else txt = "";
 		/* NOTE: Excel CSV does not support array formulae */
 		row.push(txt);

Hey, will something like this work. Could you please explain to me what "Cell type checks" are? A developer mentioned it to me, and I was wondering if it would be a solution for my issue.

Ref:here

``` diff --git a/bits/90_utils.js b/bits/90_utils.js index c7df543..8db4bc2 100644 --- a/bits/90_utils.js +++ b/bits/90_utils.js @@ -110,10 +110,13 @@ function make_csv_row(sheet/*:Worksheet*/, r/*:Range*/, R/*:number*/, cols/*:Arr isempty = false; txt = ''+(o.rawNumbers && val.t == "n" ? val.v : format_cell(val, null, o)); for(var i = 0, cc = 0; i !== txt.length; ++i) if((cc = txt.charCodeAt(i)) === fs || cc === rs || cc === 34 || o.forceQuotes) {txt = "\"" + txt.replace(qreg, '""') + "\""; break; } + if (parseInt(txt) == txt) txt = "\"" + txt + "\""; if(txt == "ID") txt = '"ID"'; } else if(val.f != null && !val.F) { isempty = false; - txt = '=' + val.f; if(txt.indexOf(",") >= 0) txt = '"' + txt.replace(qreg, '""') + '"'; + txt = '=' + val.f; + if (parseInt(val.f) == val.f) txt = "\"" + val.f + "\""; + if(txt.indexOf(",") >= 0) txt = '"' + txt.replace(qreg, '""') + '"'; } else txt = ""; /* NOTE: Excel CSV does not support array formulae */ row.push(txt); ``` Hey, will something like this work. Could you please explain to me what "Cell type checks" are? A developer mentioned it to me, and I was wondering if it would be a solution for my issue. Ref:[here](https://github.com/SheetJS/sheetjs/pull/2814)

Sign in to join this conversation.