Format whole column with numeric values

[sonjadeissenboeck]

I tried to add a formatter to my table columns that applies a new format to numeric cells. I used this https://github.com/SheetJS/js-xlsx/issues/885 issue as a guide to my solution but it didn't seem to work.

Here's what I have:

`var column = oEvent.getSource().getParent().getParent().getLabel().getText();
var C = XLSX.utils.decode_col(column);
var fmt = "#,##0";
var range = XLSX.utils.decode_range(worksheet['!ref']);
for(var i = range.s.r + 1; i <= range.e.r; ++i) {
	 var ref = XLSX.utils.encode_cell({r:i, c:C});
	if(!worksheet[ref]) continue;
	if(worksheet[ref].t != 'n') continue;
	worksheet[ref].z = fmt;
}`

When I debug this code, I see that worksheet[ref] is "undefined". Even though ref itself is not undefined (e.g. it's "NIAYPRLSVBUYWS15") and my worksheet is also filled with the correct data.

Am I doing something wrong? Thanks in advacne!

Note I'd like to apply that format AFTER the table has been rendered!

[SheetJSDev]

Are you sure the cells are numeric? As a quick test, add the line

	if(!worksheet[ref]) continue;
+	console.log(worksheet[ref].t);
	if(worksheet[ref].t != 'n') continue;

... and verify you are seeing a string of n for numeric cells.

[sonjadeissenboeck]

If I add the console.log part, it simply says "cannot read property "t" of undefined". And yes, it's a numeric cell!

[SheetJSDev]

If there is formatted text, utilities will use the cached values. Delete the formatted text as follows:

	if(worksheet[ref].t != 'n') continue;
+	delete worksheet[ref].w;
	worksheet[ref].z = fmt;

[sonjadeissenboeck]

That's not working either and if you ask me, I understand why. worksheet[ref] is not defined, so you also can't delete it. The error must be somewhere earlier in the code, where worksheet[ref] is defined?

[sonjadeissenboeck]

@SheetJSDev Still looking for a solution!

[sonjadeissenboeck]

Please, still looking for help @SheetJSDev

[JeremyLandi]

@sonjadeissenboeck A little late, and I'm sure you already checked this, but I don't see where you are defining 'worksheet'. You need to add something like this

var worksheet = XLSX.utils.json_to_sheet(arrayOfObjects);

[SheetJSDev]

To format the entire column, there are three parts:

1. Formatting each extant cell in the column. This loop is fairly straightforward:

var range = XLSX.utils.decode_range(ws["!ref"]);
range.s.c = range.e.c = C; // C is the column you want to format
for(var R = range.s.r; R <= range.e.r; ++R) {
  var addr = XLSX.utils.encode_cell({r:R, c:C});
  if(!ws[addr]) continue;
  ws[addr].z = "0"; // the actual number format, "0.00" for two decimal places, "#,##0.00" for thousands, etc.
}

2. Applying a format to the column object. In theory it would be

if(!ws["!cols"]) ws["!cols"] = [];
if(!ws["!cols"][C]) ws["!cols"][C] = { wch: 8 };
ws["!cols"][C].z = "0"; 

3. write with cellStyles: true:

XLSX.writeFile(wb, "out.xlsx", {cellStyles: true});

Step 2 is not currently supported. Fortunately the patch is fairly straightforward. Modify bits/67_wsxml.js:

diff --git a/bits/67_wsxml.js b/bits/67_wsxml.js
@@ -203,11 +203,14 @@ function parse_ws_xml_cols(columns, cols) {
 		while(colm <= colM) columns[colm++] = dup(coll);
 	}
 }
-function write_ws_xml_cols(ws, cols)/*:string*/ {
+function write_ws_xml_cols(ws, cols, opts)/*:string*/ {
 	var o = ["<cols>"], col;
 	for(var i = 0; i != cols.length; ++i) {
 		if(!(col = cols[i])) continue;
-		o[o.length] = (writextag('col', null, col_obj_w(i, col)));
+		var cow = col_obj_w(i, col);
+		var os = col.z ? get_cell_style(opts.cellXfs, {z:col.z}, opts) : 0;
+		if(os !== 0) cow.style = os;
+		o[o.length] = (writextag('col', null, cow));
 	}
 	o[o.length] = "</cols>";
 	return o.join("");
@@ -570,7 +573,7 @@ function write_ws_xml(idx/*:number*/, opts, wb/*:Workbook*/, rels)/*:string*/ {
 		outlineLevelRow:opts.sheetFormat.outlineLevelRow||'7'
 	}));
 
-	if(ws['!cols'] != null && ws['!cols'].length > 0) o[o.length] = (write_ws_xml_cols(ws, ws['!cols']));
+	if(ws['!cols'] != null && ws['!cols'].length > 0) o[o.length] = (write_ws_xml_cols(ws, ws['!cols'], opts));
 
 	o[sidx = o.length] = '<sheetData/>';
 	ws['!links'] = [];

[MichalReifer]

that worked for me: (this includes also bolding the headers line and formatting alignment in all of the cells)

      Object.keys(data[0]).forEach((field, col) => {
          for (let row = 0; row < data.length + 1; row += 1) {
              const cell = XLSX.utils.encode_cell({ c: col, r: row })
              ws[cell].s = { 
                   alignment: { wrapText: true, vertical: 'center' } 
               }
              if (row === 0) {
                  ws[cell].s.font = { bold: true }
              }
              if (field === 'some field name')){
                  ws[cell].s.fill = {
                      type: 'pattern',
                      patternType: 'solid',
                      fgColor: { rgb: "ece9f8" },
                  }
              }
          }
      })

(the data is an array of objects)

[JeffCEmploynet]

Just in case anyone has a similar issue, I was getting the type change with this, but numbers were still not summing up on the exported excel. Logging the worksheet[ref] showed me that my 't' value was always 's'. At the end of my function, I set worksheet[ref].t = 'n' and it solved the problem. The following is based on a function to solve a similar issue provided by bburns here: https://github.com/SheetJS/sheetjs/issues/885#issuecomment-814794497

function FormatColumn(worksheet, col) {
    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].z = "0.00";
        worksheet[ref].t = 'n';
      }
    }
}