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

Closed
opened 2023-02-16 17:50:42 +00:00 by Goliath · 1 comment

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.
Owner

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.
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2885
No description provided.