Format whole column with numeric values #1339

New Issue

2018-11-05T08:41:32Z

sonjadeissenboeck commented

2018-11-05 08:41:32 +00:00

(Migrated from github.com)

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!

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 commented

2018-11-06 02:46:13 +00:00

(Migrated from github.com)

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.

Are you sure the cells are numeric? As a quick test, add the line ```diff 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 commented

2018-11-06 10:11:12 +00:00

(Migrated from github.com)

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

If I add the console.log part, it simply says "cannot read property "t" of undefined". And yes, it's a numeric cell! <img width="573" alt="image" src="https://user-images.githubusercontent.com/30501609/48057573-a695d400-e1b4-11e8-8ced-7324a91c1f90.png">

SheetJSDev commented

2018-11-06 10:26:42 +00:00

(Migrated from github.com)

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;

If there is formatted text, utilities will use the cached values. Delete the formatted text as follows: ```diff if(worksheet[ref].t != 'n') continue; + delete worksheet[ref].w; worksheet[ref].z = fmt; ```

sonjadeissenboeck commented

2018-11-06 11:01:47 +00:00

(Migrated from github.com)

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 commented

2018-11-12 11:59:56 +00:00

(Migrated from github.com)

@SheetJSDev Still looking for a solution!

sonjadeissenboeck commented

2018-12-10 13:22:29 +00:00

(Migrated from github.com)

Please, still looking for help @SheetJSDev

JeremyLandi commented

2019-04-25 18:37:19 +00:00

(Migrated from github.com)

@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);

@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 commented

2022-03-09 09:36:30 +00:00

(Migrated from github.com)

To format the entire column, there are three parts:

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

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";

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'] = [];

To format the entire column, there are three parts: 1) Formatting each extant cell in the column. This loop is fairly straightforward: ```js 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 ```js if(!ws["!cols"]) ws["!cols"] = []; if(!ws["!cols"][C]) ws["!cols"][C] = { wch: 8 }; ws["!cols"][C].z = "0"; ``` 3) write with `cellStyles: true`: ```js 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 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'] = []; ```

Sign in to join this conversation.