sheetjs
sheetjs copied to clipboard
Format whole column with numeric values
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!
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.
If I add the console.log part, it simply says "cannot read property "t" of undefined".
And yes, it's a numeric cell!
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;
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?
@SheetJSDev Still looking for a solution!
Please, still looking for help @SheetJSDev
@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);
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'] = [];
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)
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';
}
}
}