Number value cells aligned to the left when exporting to an Excel file. #2885

New Issue

Goliath · 2023-02-16T17:50:42Z

Goliath commented

2023-02-16 17:50:42 +00:00

When we use the XLSX.utils.table_to_sheet() function, the data for number cells are showing as aligned to the left, meaning Exel does not see them as numbers. That is causing issues when we try to create formulas in Excel.

Any number values like 123, 123.99, $123.99 or 12.3% should be exported to Excel and aligned to the right to show they are indeed numerical/currency values and we can create any formulas against them. Thank you.

When we use the XLSX.utils.table_to_sheet() function, the data for number cells are showing as aligned to the left, meaning Exel does not see them as numbers. That is causing issues when we try to create formulas in Excel. Any number values like 123, 123.99, $123.99 or 12.3% should be exported to Excel and aligned to the right to show they are indeed numerical/currency values and we can create any formulas against them. Thank you.

sheetjs commented

2023-02-16 19:41:45 +00:00

You can override the default behavior using data attributes, as described in https://docs.sheetjs.com/docs/api/utilities#value-override in the docs.

Someone asked the same question last night in the SheetJS discord.

In the example, the person was generating a cell like

<td><p>100,00€</p></td>

The desired format is discovered by parsing the file with the option cellNF: true. In NodeJS the code looks like

var XLSX = require("xlsx");
var wb = XLSX.readFile("yourfile.xlsx", {cellNF: true})
var ws = wb.Sheets["name of sheet"];
console.log(ws["address"].z)

The format was #,##0.00\ "€";[Red]\-#,##0.00\ "€" (currency with 2 decimal places, negative in red)

The overrides were therefore:

data-t="n" (numeric cell type)
data-v="100" (cell value -- this is a simple number, no thousands symbols or currency sigil)
data-z='#,##0.00\ "€";[Red]\-#,##0.00\ "€"' (cell number format)

Live example: https://jsfiddle.net/e79401pz/

The discussion is interesting and we will try to add some of those details to the docs.

You can override the default behavior using data attributes, as described in https://docs.sheetjs.com/docs/api/utilities#value-override in the docs. Someone asked the same question last night in the [SheetJS discord](https://discord.com/channels/1039771292859109436/1039788802220232744/1075676644263600160). In the example, the person was generating a cell like ```html <td><p>100,00€</p></td> ``` The desired format is discovered by parsing the file with the option `cellNF: true`. In NodeJS the code looks like ```js var XLSX = require("xlsx"); var wb = XLSX.readFile("yourfile.xlsx", {cellNF: true}) var ws = wb.Sheets["name of sheet"]; console.log(ws["address"].z) ``` The format was `#,##0.00\ "€";[Red]\-#,##0.00\ "€"` (currency with 2 decimal places, negative in red) The overrides were therefore: - `data-t="n"` (numeric cell type) - `data-v="100"` (cell value -- this is a simple number, no thousands symbols or currency sigil) - `data-z='#,##0.00\ "€";[Red]\-#,##0.00\ "€"'` (cell number format) Live example: https://jsfiddle.net/e79401pz/ The discussion is interesting and we will try to add some of those details to the docs.

sheetjs closed this issue

2023-02-16 19:41:45 +00:00

Sign in to join this conversation.