Currency format can't be used in formula #3038

New Issue

Martin-Hogge · 2023-11-29T07:58:01Z

Martin-Hogge commented

2023-11-29 07:58:01 +00:00

Hi,

I try to export a sheet with currency formatted cells. Based on https://github.com/SheetJS/sheetjs/issues/242#issuecomment-301882390 I set my currency format like this: ws[cellName].z = '#,##0.00\\ "€"';

But their is something weird: cells are being recognized as a currency and can be summed by using the '+' operator but they cannot be summed by using the SUM() excel function (called SOMME() in my example because it's in french).

Cells are recognized as currency:

Can someone explain me why and what should I do for being able to use formula with my currency formatted cells ?
Thank you.

Hi, I try to export a sheet with currency formatted cells. Based on https://github.com/SheetJS/sheetjs/issues/242#issuecomment-301882390 I set my currency format like this: `ws[cellName].z = '#,##0.00\\ "€"';` But their is something weird: cells are being recognized as a currency and can be summed by using the '+' operator but they cannot be summed by using the `SUM()` excel function (called `SOMME()` in my example because it's in french). ![image](/attachments/5458a112-b02c-4929-8cad-4a5f24593a29) ![image](/attachments/163d4188-b174-44b7-a2d0-2923d36d5e79) Cells are recognized as currency: ![image](/attachments/58f7a7b3-23bc-4a0e-8cdd-17b3581545d0) Can someone explain me why and what should I do for being able to use formula with my currency formatted cells ? Thank you.

image.png

26 KiB

image.png

30 KiB

image.png

41 KiB

sheetjs commented

2023-11-29 09:39:46 +00:00

You stumbled upon a quirk in Excel.

Based on the screenshots, cells A2 and A3 are text. Check your starting point and ensure you are generating numeric cells.

For example, if you are using aoa_to_sheet, the string "12" will be translated to a text cell while the number 34 will be a numeric cell:

var ws = XLSX.utils.aoa_to_sheet([
  ["12", "<-- this cell will be text"],
  [34, "<-- this cell will be numeric"]
]);

If you'd like a more detailed explanation of Excel's inconsistent behavior, feel free to ask in the chat

You stumbled upon a quirk in Excel. Based on the screenshots, cells A2 and A3 are text. Check your starting point and ensure you are generating numeric cells. For example, if you are using `aoa_to_sheet`, the string `"12"` will be translated to a text cell while the number `34` will be a numeric cell: ```js var ws = XLSX.utils.aoa_to_sheet([ ["12", "<-- this cell will be text"], [34, "<-- this cell will be numeric"] ]); ``` If you'd like a more detailed explanation of Excel's inconsistent behavior, feel free to ask [in the chat](https://sheetjs.com/chat)

👍 1

sheetjs closed this issue

2023-11-29 09:39:46 +00:00

Sign in to join this conversation.