How do you format currency in exceljs?
All I've found is this from their docs...which I have no clue how to type so I pasted it, but it doesn't seem to work
// Set Column 3 to Currency Format
ws.getColumn(3).numFmt = '�#,##0;[Red]-�#,##0';
Asked
Active
Viewed 1.9k times
3 Answers
19
Just took a little bit of tinkering with.
ws.getColumn(3).numFmt = '$#,##0.00;[Red]-$#,##0.00';
The #'s are optional digits. If you don't care about negative numbers being red you can leave it as $#,##0.00
tumelo
- 544
- 1
- 4
- 17
-
What about accounting format with parenthesis for negatives? Is there a preset format or do you have to mask it like this? – Mike S. Dec 27 '17 at 23:12
-
For parentheses: `'$#,##0.00;[Red]($#,##0.00)'` – Waggles Jun 05 '18 at 21:42
4
For the full Accounting format, eg left-justified '$', $- for $0.00, etc, you can use:
const numFmtStr = '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)';
cell.numFmt = numFmtStr;
343_Guilty_Spark
- 146
- 1
- 5
0
An alternate to 343_Guilty_Spark's answer which is shorter and will be red for negative values:
const numFmtStr = '$#,##0.00_);[Red]($#,##0.00)'
cell.numFmt = numFmtStr
This will keep all the qualities of it, but be less complicated for Excel.
Yacc
- 1
- 2