Create an excel file with a few styles using client side JavaScript (if possible using js-xlsx library)
I want to create an excel file(in .xlsx format) and make it available for download using Client Side JavaScript. I was able to create a sample file using js-xlsx library. But I am not able to apply any styles to it. At least some basic styles including background color to header, bold font for header and text wrapping for the cells are required.
js-xlsx library documentation says that we can give styles using Cell Object.
I tried giving styles using the cell object but it is not reflecting in the downloaded .xlsx file. I even tried reading a .xlsx file and writing the same file back using XLSX.write() function. But it gives back an excel file with no styles at all. Ideally I expect the downloaded file to have the same styles of uploaded file. No font color or background colors were applied in the recreated file. I use Excel 2013 for testing the downloaded files.
Please find below the excel screenshots before and after uploading.
Original File
Downloaded File
The code is given below.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<script type="text/javascript" src="xlsx.core.min.js"></script>
<script type="text/javascript" src="Blob.js"></script>
<script type="text/javascript" src="FileSaver.js"></script>
<script>
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;
}
/* set up XMLHttpRequest */
var url = "template-sample.xlsx";
var oReq = new XMLHttpRequest();
oReq.open("GET", url, true);
oReq.responseType = "arraybuffer";
oReq.onload = function(e) {
var arraybuffer = oReq.response;
/* convert data to binary string */
var data = new Uint8Array(arraybuffer);
var arr = new Array();
for(var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
var bstr = arr.join("");
/* Call XLSX */
var workbook = XLSX.read(bstr, {type:"binary", cellStyles:true});
console.log("read workbook");
console.log(workbook);
/* DO SOMETHING WITH workbook HERE */
var wbout = XLSX.write(workbook, {bookType:'xlsx', bookSST:true, type: 'binary', cellStyles: true});
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "template-download.xlsx");
}
function read(){
oReq.send();
}
</script>
</head>
<body>
<button onclick="read()">save xlsx</button>
</body></html>
Sample code was taken from here.
What I look forward is either an option to give styles to cells using js-xlsx library or another library which provides this functionality. I found a library named exceljs , but it requires node js to support it. I am looking for a purely client side based solution. This is to be used for a Cordova based tablet and desktop application.
Answer
After some research I was able to find the solution to my own question. I found a new library named xlsx-style for giving styles. xlsx-style is build on top of js-xlsx for giving styles also to the generated excel file. The styles can be given to the cells using a new attribute inside cell object.
The explanation is available at the npm xlsx-style page.
Styling is given using a style object associated with each cell. Font, Color, alignment etc can be given using this style object.
I have added a minimalist demo in a github page. The sample code is available at this github repository.
You can find the screenshot of the generated excel page below.
