Export xlsx file with a "number" cell format #966

New Issue

2018-01-23T16:47:59Z

HachimDev commented

2018-01-23 16:47:59 +00:00

(Migrated from github.com)

I really enjoy working with this lib. really great work !
As mentioned in the title im exporting an excel file from an array of arrays and would like to change the cell format from "standard" to "number"

any idea how ca i achieve that please. thanks a lot!

I really enjoy working with this lib. really great work ! As mentioned in the title im exporting an excel file from an array of arrays and would like to change the cell format from "standard" to "number" ![cellformat](https://user-images.githubusercontent.com/20968869/35288607-129fb8d0-005d-11e8-95b7-127a75270f34.jpg) any idea how ca i achieve that please. thanks a lot!

reviewher commented

2018-01-23 17:41:14 +00:00

(Migrated from github.com)

Unfortunately the utility doesn't have an option to set the format for a column, but you can manually walk the cells:

Number formats are stored in the .z property of cell objects. The standard number format is "0.00" (2 decimal places, no thousands separator) in US English and is probably the same in French. You can verify by saving a file with the number format, reading the file in http://oss.sheetjs.com/js-xlsx/ and inspecting the global_wb object in your console.

To manually change cells, use a walk similar to the README example:

/* new format */
var fmt = "0.00";
/* change cell format of range B2:D4 */
var range = { s: {r:1, c:1}, e: {r:2, c:3} };
for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    var cell = ws[XLSX.utils.encode_cell({r:R,c:C})];
    if(!cell || cell.t != 'n') continue; // only format numeric cells
    cell.z = fmt;
  }
}

Here's a live example: https://jsfiddle.net/1ny97xrb/1/

@sheetjsdev: eventually aoa_to_sheet should take a header type specification

Unfortunately the utility doesn't have an option to set the format for a column, but you can manually walk the cells: Number formats are stored in the `.z` property of cell objects. The standard number format is `"0.00"` (2 decimal places, no thousands separator) in US English and is probably the same in French. You can verify by saving a file with the number format, reading the file in http://oss.sheetjs.com/js-xlsx/ and inspecting the `global_wb` object in your console. To manually change cells, use a walk similar to [the README example](https://github.com/SheetJS/js-xlsx#general-structures): ```js /* new format */ var fmt = "0.00"; /* change cell format of range B2:D4 */ var range = { s: {r:1, c:1}, e: {r:2, c:3} }; for(var R = range.s.r; R <= range.e.r; ++R) { for(var C = range.s.c; C <= range.e.c; ++C) { var cell = ws[XLSX.utils.encode_cell({r:R,c:C})]; if(!cell || cell.t != 'n') continue; // only format numeric cells cell.z = fmt; } } ``` Here's a live example: https://jsfiddle.net/1ny97xrb/1/ @sheetjsdev: eventually `aoa_to_sheet` should take a header type specification

👍 7

HachimDev commented

2018-01-24 09:20:50 +00:00

(Migrated from github.com)

thanks for the answer ! I'll try it ASAP

nemanja-stojanovic-mqsoftrs commented

2018-11-28 11:08:17 +00:00

(Migrated from github.com)

Really useful answer from @reviewher. The only thing I could not figure out was how to format the entire column instead of the individual cells? If anyone has a suggestion, I would be very grateful...

👍 7

yudhiyou commented

2022-01-10 02:59:23 +00:00

(Migrated from github.com)

Unfortunately the utility doesn't have an option to set the format for a column, but you can manually walk the cells:

Number formats are stored in the .z property of cell objects. The standard number format is "0.00" (2 decimal places, no thousands separator) in US English and is probably the same in French. You can verify by saving a file with the number format, reading the file in http://oss.sheetjs.com/js-xlsx/ and inspecting the global_wb object in your console.

To manually change cells, use a walk similar to the README example:
/* new format */
var fmt = "0.00";
/* change cell format of range B2:D4 */
var range = { s: {r:1, c:1}, e: {r:2, c:3} };
for(var R = range.s.r; R <= range.e.r; ++R) {
  for(var C = range.s.c; C <= range.e.c; ++C) {
    var cell = ws[XLSX.utils.encode_cell({r:R,c:C})];
    if(!cell || cell.t != 'n') continue; // only format numeric cells
    cell.z = fmt;
  }
}
Here's a live example: https://jsfiddle.net/1ny97xrb/1/

@SheetJSDev: eventually aoa_to_sheet should take a header type specification

Thank you very much...
You have saved my life with your solution. :)

> Unfortunately the utility doesn't have an option to set the format for a column, but you can manually walk the cells: > > Number formats are stored in the `.z` property of cell objects. The standard number format is `"0.00"` (2 decimal places, no thousands separator) in US English and is probably the same in French. You can verify by saving a file with the number format, reading the file in http://oss.sheetjs.com/js-xlsx/ and inspecting the `global_wb` object in your console. > > To manually change cells, use a walk similar to [the README example](https://github.com/SheetJS/js-xlsx#general-structures): > > ```js > /* new format */ > var fmt = "0.00"; > /* change cell format of range B2:D4 */ > var range = { s: {r:1, c:1}, e: {r:2, c:3} }; > for(var R = range.s.r; R <= range.e.r; ++R) { > for(var C = range.s.c; C <= range.e.c; ++C) { > var cell = ws[XLSX.utils.encode_cell({r:R,c:C})]; > if(!cell || cell.t != 'n') continue; // only format numeric cells > cell.z = fmt; > } > } > ``` > > Here's a live example: https://jsfiddle.net/1ny97xrb/1/ > > @SheetJSDev: eventually `aoa_to_sheet` should take a header type specification Thank you very much... You have saved my life with your solution. :)

Sign in to join this conversation.