I am referring this example to export a worksheet https://github.com/SheetJS/js-xlsx/issues/817. How to do cell styling like background coloring,font size and increasing the width of the cells to make the data fit exactly.I have gone through the documentation but couldn't find any proper examples to use fill etc.Is there a way to do the formatting?
Below is the code snippet:
/* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);
/* add to workbook */
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "People");
/* write workbook (use type 'binary') */
var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});
/* generate a download */
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-
stream"}),"sheetjs.xlsx");
There are a bunch of community forks that allow styling. My personal favorite is xlsx-js-style. It is up to date and works well compared to other libraries.
sheetjs-style is also up to date, but i had some problems with it. See: Styles not working
xlsx-style is not up to date. Currently 397 commits behind SheetJS:master. I would not use it if possible.
All of these libraries share the same styling options. Here is a bunch of examples:
for (i in ws) {
if (typeof(ws[i]) != "object") continue;
let cell = XLSX.utils.decode_cell(i);
ws[i].s = { // styling for all cells
font: {
name: "arial"
},
alignment: {
vertical: "center",
horizontal: "center",
wrapText: '1', // any truthy value here
},
border: {
right: {
style: "thin",
color: "000000"
},
left: {
style: "thin",
color: "000000"
},
}
};
if (cell.c == 0) { // first column
ws[i].s.numFmt = "DD/MM/YYYY HH:MM"; // for dates
ws[i].z = "DD/MM/YYYY HH:MM";
} else {
ws[i].s.numFmt = "00.00"; // other numbers
}
if (cell.r == 0 ) { // first row
ws[i].s.border.bottom = { // bottom border
style: "thin",
color: "000000"
};
}
if (cell.r % 2) { // every other row
ws[i].s.fill = { // background color
patternType: "solid",
fgColor: { rgb: "b2b2b2" },
bgColor: { rgb: "b2b2b2" }
};
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With