Menu
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.
841
An Excel add-in interacts with objects in Excel by using the Office JavaScript API, which includes two JavaScript object models: Excel JavaScript API: Introduced with Office 2016, the Excel JavaScript API provides strongly-typed objects that you can use to access worksheets, ranges, tables, charts, and more.
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. 
125
There are only a couple of examples for using xlsx-style which I didn't really find clear or all that helpful to get what I needed fast.
Here is my solution using xlsx-style with the barebones needed to create a workbook, set a cell value, and color that cell.
I struggled a little bit with getting the right xlsx.core.min.js file, for some reason not all versions have this included. I ended up copying directly from Nithin Baby (the anwsers demo)
Heres the simple version of the code
/* Object for the excel workbook data */ class Workbook { constructor() { this.SheetNames = []; this.Sheets = {}; } } /* function for downloading the excel file */ 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; } // create the worksheet data var ws_data = {} var range = { s: { c: 0, r: 0 }, e: { c: 10, r: 10 } }; // worksheet cell range ws_data['!ref'] = XLSX.utils.encode_range(range); // set cell the range var cell = { // create cell v: 'test', // value s: { // style fill: { fgColor: { rgb: "FF6666" } // red } } } ws_data[XLSX.utils.encode_cell({ c: 1, r: 1 })] = cell; // add the cell to the sheet data // create workbook and download var wb = new Workbook(); wb.SheetNames.push('test'); // create new worksheet wb.Sheets['test'] = ws_data; // set workseet data to the cell data var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' }); //workbook output saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), "Test Color.xlsx") // save workbook Couple things to note.
XLSX.utils.encode_cell({ c: 1, r: 1 }) is their way of assigning excel cords to numbers. for example: { c: 1, r: 1 } == 'B2'
If you can get the excel file to download but the cell data doesn't show up it most likely has to do with the sheets range value. Make sure it matches or is bigger than the amount of data. range = { s: { c: 0, r: 0 }, e: { c: 10, r: 10 } }; where 's' = current and 'e' = total from what i've gathered.
xlsx-style has more attributes that can be set when creating the cell its worth a quick skim to know what's there. Now its up to you to figure out how you want to create/style the cells you need for your output and set to the range value appropriately.
22
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
