Export xlsx file with a "number" cell format

[HachimDev]

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!

[reviewher]

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

[HachimDev]

thanks for the answer ! I'll try it ASAP

[nemanja-stojanovic-mqsoftrs]

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

[yudhiyou]

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. :)