Sheetjs: Number Format for currency?

[lonnieCross]

Could someone please point me in the right direction? I'm trying to format a number column to a currency format such as $5.00 instead of just the number 5.

Any help would be greatly appreciated!

Thanks!

[SheetJSDev]

@lonnieCross If you just want the formatting, use the excel format code $0.00:

> XLSX.SSF.format('$0.00', 5)
'$5.00'

If you want the thousands separator, the excel format code for that is $#,##0.00:

> XLSX.SSF.format('$#,##0.00', 12345.6789)
'$12,345.68'
> XLSX.SSF.format('$0.00', 12345.6789)
'$12345.68'

If you don't care about the intermediate output and just want to set a cell format to a currency, wipe the cell's formatted text (w property) and override the number format (z property). Here is a sample nodejs session:

# load XLSX (not required in browser)
> var XLSX = require('xlsx')

# get the workbook (using browser APIs or other commands)
> var wb = XLSX.readFile('n.xlsx')

# show the original sheet
> console.log(XLSX.utils.sheet_to_csv(wb.Sheets.Sheet1))
1,2

# now we want to change the A1 to use a currency format

# first remove the existing formatted text
> var ws = wb.Sheets.Sheet1
> delete ws.A1.w

# now override the cell formatting
> ws.A1.z = '$0.00'

# (optional) if you want to format the cell before proceeding
> XLSX.utils.format_cell(ws.A1) // '$1.00'

# emit CSV again with the currency format (this will automatically reformat cells with no formatted text)
> console.log(XLSX.utils.sheet_to_csv(wb.Sheets.Sheet1))
$1.00,2

[tborg]

I need to export to .xlsx, not a delimited format or JSON, so unfortunately the advice above didn't quite fix my problem.

I developed a workaround for this by manually registering the formats I need on the XLXS.SSF._table:

  _registerNumberFormat: (fmt) ->
      table = XLSX.SSF._table
      empty = null
      i = 0; while not empty and i < 393 # 392 is the last index js-xlsx checks
        preset = table[i]
        if not preset
          empty or= i
        else if preset is fmt
          return
        i++
      if not empty  # This would be strange indeed
        throw new Error 'No more room in the number format table!'
      table[empty] = fmt

@SheetJSDev why not automatically register missing number formats instead of ignoring them?

[SheetJSDev]

@tborg FYI you should only write to the table at indices above 164. From MS-XLS 2.6.165 IFmt:

Custom number format identifiers MUST be greater than or equal to 0x00A4 and less than or equal to 0x0188, and SHOULD be less than or equal to 0x017E.

There are some third-party writers which actually go beyond that point, which is why 0x188 is the end bound and not 0x17E.

In some east asian versions of excel, other indices have implicit meaning. The full tables are in ECMA-376 section 18.8.30. For example, code 52 is not included in files, but its implicit meaning depends on locale:

| Locale | Format |
| --- | --- |
| zh-CN | yyyy"年"m"月" |
| zh-TW | 上午/下午 hh"時"mm"分" |
| ko-KR | yyyy-mm-dd |

Different versions of excel do different things if you try to override the format.

why not automatically register missing number formats instead of ignoring them?

It's coming, I have some code already but it's not ready for release yet

[tborg]

Great. Thanks for the info.

—
Sent from Mailbox for iPhone

On Fri, Feb 13, 2015 at 6:18 PM, SheetJSDev [email protected]
wrote:

@tborg FYI you should only write to the table at indices above 164. From MS-XLS 2.6.165 IFmt:

Custom number format identifiers MUST be greater than or equal to 0x00A4 and less than or equal to 0x0188, and SHOULD be less than or equal to 0x017E.
There are some third-party writers which actually go beyond that point, which is why 0x188 is the end bound and not 0x17E.
In some east asian versions of excel, other indices have implicit meaning. The full tables are in ECMA-376 section 18.8.30. For example, code 52 is not included in files, but its implicit meaning depends on locale:
|Locale|Format|
|------|------|
|zh-CN|yyyy"年"m"月"|
|zh-TW|上午/下午 hh"時"mm"分"|
|ja-JP|yyyy"年"m"月"|
Different versions of excel do different things if you try to override the format.
why not automatically register missing number formats instead of ignoring them?

It's coming, I have some code already but it's not ready for release yet

Reply to this email directly or view it on GitHub:
https://github.com/SheetJS/js-xlsx/issues/158#issuecomment-74357253

[miha-plesko]

To register a custom format before writing to file you should use SSF feature:

XLSX.SSF.load(<your-format>, <index>);

I've used index 164 and it worked.

[ngehlert]

@SheetJSDev i'm stuck with formatting currencies client side and not in node. can you give me an example?
i create my values the following way:
data[XLSX.utils.encode_cell({c: 0, r: 0})] = {v: 2000, t: 'n'};
i then tried experimenting with the z-property, with no success.
as the original author mentioned z: '0.00%' works

edit: ok i figure it out thanks to @miha-plesko
you need to create a own format as he described, in my case:

XLSX.SSF.load('#,##0.00 €;[Red](#,##0.00) €, 164);

then you are able to use it with:
data[XLSX.utils.encode_cell({c: 0, r: 0})] = {v: 2000, t: 'n', z: XLSX.SSF.get_table()[164]}

[SheetJSDev]

This is now automatically done by the XLSX/XLSB writers. No need to pre-load, the write process discovers missing formats and populates the table.

[dandv]

@SheetJSDev: is format discovery done by the ODS writer too? I've been adding .z = '#.##' and other variations to number cells but no formatting whatsoever was written to the file. All the numbers were output in the "General" format. I've done binary/XML comparisons of the output ODS before and after adding .z to cells - no difference.

The XLSX.SSF.load() and .get_table() worakrounds didn't do anything.

Is currency formatting a Pro feature, or what am I missing?

UPDATE: filed #1569.

[chanmithra]

let columnStartIndex = 10;
let columnEndIndex = 12;
console.log('filteredSearchExport : '+filteredSearchExportFlag);
if ( filteredSearchExportFlag != null
&& filteredSearchExportFlag !== undefined
&& filteredSearchExportFlag === 'filteredSearchExport') {
columnStartIndex = 11;
columnEndIndex = 13;
}
/* new format /
const fmt = "##,##,##0.00";
/ change cell format of range L2:L30 & M2:M30 & N2:N30 */
const range = { s: { r: 1, c: columnStartIndex }, e: { r: numberOfRows, c: columnEndIndex } };
for (let R = range.s.r; R <= range.e.r; ++R) {
for (let C = range.s.c; C <= range.e.c; ++C) {
const cell = worksheet[XLSX.utils.encode_cell({ r: R, c: C })];
cell.t = 'n';
cell.z = fmt;
}
}

The above code changes the column data types to number so while summing the columns i see the total below in the status bar. but this code only works in my machine. the other environment has wps office and ms-office both works very weird.
WPS office : format ##,##,##0.00 is not working, data type changed to number.
Excel 2016 : data type not changed, sum not working
note : i used XLSX.utils.sheet_to_csv to create a csv file using the same code and it works same in different environments - data type changed to numbers and while selection column values can see the sum below in status bar. my machine has excel 2016 - works well.