Adding a number-filled string to a cell adds an extra quote character at the beginning of the cell #3025

New Issue

grindpride · 2023-11-08T17:10:37Z

grindpride commented

2023-11-08 17:10:37 +00:00

You can reproduse that on your own playground

https://docs.sheetjs.com/docs/api/utilities/array#array-of-arrays-input

function SheetJSAddAOA() { return ( <button onClick={() => {
  /* Start from an empty worksheet */
  var ws = XLSX.utils.aoa_to_sheet([[]]);

  XLSX.utils.sheet_add_aoa(ws, [['33333333333333333','qweqweqweqweqweqweqwe']], {origin: "A1"});

  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
  XLSX.writeFile(wb, "SheetJSAddAOA.xlsx");
}}>Click to export Array of Arrays</button> ); }

You can reproduse that on your own playground https://docs.sheetjs.com/docs/api/utilities/array#array-of-arrays-input ```js function SheetJSAddAOA() { return ( <button onClick={() => { /* Start from an empty worksheet */ var ws = XLSX.utils.aoa_to_sheet([[]]); XLSX.utils.sheet_add_aoa(ws, [['33333333333333333','qweqweqweqweqweqweqwe']], {origin: "A1"}); var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "SheetJSAddAOA.xlsx"); }}>Click to export Array of Arrays</button> ); } ``` ![image](/attachments/45978c45-12d5-4817-be47-ea28a6013ecc)

image.png

6.8 KiB

sheetjs commented

2023-11-08 17:26:58 +00:00

In Chrome 118, the snippet generates the attached file.

The string value '33333333333333333' is serialized as a text cell. To distinguish between numbers and "strings that look like numbers", Excel displays the text cells with a leading apostrophe. It is not in the actual cell value.

If the actual text value has an apostrophe, please share the file and find your browser version number. We can try to reproduce.

In Chrome 118, the snippet generates the attached file. ![i3025.png](/attachments/34920afe-f4c7-41d7-ae80-6ffaa29447bf) The string value `'33333333333333333'` is serialized as a text cell. To distinguish between numbers and "strings that look like numbers", Excel displays the text cells with a leading apostrophe. It is not in the actual cell value. If the actual text value has an apostrophe, please share the file and find your browser version number. We can try to reproduce.

i3025.png

186 KiB

SheetJSAddAOA.xlsx

16 KiB

sheetjs closed this issue

2023-11-08 17:26:58 +00:00

grindpride commented

2023-11-08 17:31:39 +00:00

Windows 11
Version 119.0.6045.106 (Official Build) (64-bit)
Also this behavior can be reproduse on Node v20.9.0

Windows 11 Version 119.0.6045.106 (Official Build) (64-bit) Also this behavior can be reproduse on Node v20.9.0

SheetJSAddAOA (6).xlsx

16 KiB

grindpride reopened this issue

2023-11-08 17:31:39 +00:00

grindpride commented

2023-11-08 17:38:50 +00:00

How to force set cell format to 'Text' using sheet_add_aoa function?

sheetjs commented

2023-11-08 17:45:51 +00:00

You are highlighting a behavior of Excel.

Cell A1 is stored without an apostrophe:

<c r="A1" t="str"><v>33333333333333333</v></c>

That is saying cell A1 is an inline string with value "33333333333333333".

Excel displays the content with a leading apostrophe to visually distinguish strings like "333" from numbers like 333.

If you want to verify manually, enter 33333333333333333 in a cell and it will be converted to the number:

Enter '33333333333333333 in the next cell and it will be saved as text:

If you select the cell, the formula bar will show the leading apostrophe.

set cell format to 'Text'

You can set the number format of the cell to @:

ws["A1"].z = "@";

Using sheet_add_aoa, pass a cell object:

  XLSX.utils.sheet_add_aoa(ws, [[{t:"s", v:'33333333333333333', z:"@"}]], {origin: "A1"});

You are highlighting a behavior of Excel. Cell A1 is stored without an apostrophe: ```xml <c r="A1" t="str"><v>33333333333333333</v></c> ``` That is saying cell A1 is an inline string with value "33333333333333333". Excel displays the content with a leading apostrophe to visually distinguish strings like "333" from numbers like 333. If you want to verify manually, enter `33333333333333333` in a cell and it will be converted to the number: ![image](/attachments/29b5d313-c5fe-4890-9a8c-36bbc0d68e27) Enter `'33333333333333333` in the next cell and it will be saved as text: ![image](/attachments/11173a99-db74-4960-8fc6-ca72d3590433) If you select the cell, the formula bar will show the leading apostrophe. --- > set cell format to 'Text' You can set the [number format of the cell](https://docs.sheetjs.com/docs/csf/features/nf) to `@`: ```js ws["A1"].z = "@"; ``` Using `sheet_add_aoa`, pass a cell object: ```js XLSX.utils.sheet_add_aoa(ws, [[{t:"s", v:'33333333333333333', z:"@"}]], {origin: "A1"}); ```

image.png

881 B

image.png

881 B

image.png

507 B

❤️ 1

sheetjs closed this issue

2023-11-08 17:45:51 +00:00

Sign in to join this conversation.