Is it possible to format a column with a currency when using json_to_sheet?

[DannyRyman]

If this is not supported, what is the best mechanism for creating a sheet that has formatted columns?

[SheetJSDev]

The json_to_sheet algorithm is pretty simple: for each key of a given row object, determine the corresponding column and write to the appropriate cell. The header row is written at the end.

If you just want to format a specific column, after generating the worksheet with json_to_sheet, find the right column for the field you want then walk the cells of that column and assign the cell's .z number format. For example, to reformat the numbers in the "B" column:

var C = XLSX.utils.decode_col("B"); // 1
var fmt = '$0.00'; // or '"$"#,##0.00_);[Red]\\("$"#,##0.00\\)' or any Excel number format

/* get worksheet range */
var range = XLSX.utils.decode_range(ws['!ref']);
for(var i = range.s.r + 1; i <= range.e.r; ++i) {
  /* find the data cell (range.s.r + 1 skips the header row of the worksheet) */
  var ref = XLSX.utils.encode_cell({r:i, c:C});
  /* if the particular row did not contain data for the column, the cell will not be generated */
  if(!ws[ref]) continue;
  /* `.t == "n"` for number cells */
  if(ws[ref].t != 'n') continue;
  /* assign the `.z` number format */
  ws[ref].z = fmt;
}

[sonjadeissenboeck]

@SheetJSDev
I tried to use your answer but my worksheet[ref] is always undefined even though worksheet as well as ref is not undefined. What could be my mistake?

[dandv]

This doesn't work for ODS files - #1569.

[bburns]

Here's a function -

function formatColumn(worksheet, col, fmt) {
  const range = XLSX.utils.decode_range(worksheet['!ref'])
  // note: range.s.r + 1 skips the header row
  for (let row = range.s.r + 1; row <= range.e.r; ++row) {
    const ref = XLSX.utils.encode_cell({ r: row, c: col })
    if (worksheet[ref] && worksheet[ref].t === 'n') {
      worksheet[ref].z = fmt
    }
  }
}

Example:

const rows = [
  ['name', 'cost', 'price'], 
  ['dino', 3.45, 7.95]
]

const workbook = XLSX.utils.book_new()
const worksheet = XLSX.utils.aoa_to_sheet(rows) // array of arrays
    
const currency = '$0.00'
for (let col of [1, 2]) {
  formatColumn(worksheet, col, currency)
}
    
XLSX.utils.book_append_sheet(workbook, worksheet, 'Details')