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

Solution 1:

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.

Solution 2:

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 */classWorkbook {
    constructor() {
        this.SheetNames = [];
        this.Sheets = {};
    }
}

/* function for downloading the excel file */functions2ab(s) {
    var buf = newArrayBuffer(s.length);
    var view = newUint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}

// create the worksheet datavar 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 rangevar cell = { // create cellv: 'test', // values: { // stylefill: {
            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 downloadvar wb = newWorkbook();
wb.SheetNames.push('test'); // create new worksheet
wb.Sheets['test'] = ws_data; // set workseet data to the cell datavar wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' }); //workbook outputsaveAs(newBlob([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.

Solution 3:

As another alternative for writing simple *.xlsx files I'd propose write-excel-file package.

https://npmjs.com/package/write-excel-file

It supports styling cells with bold font, text color, background color, horizontal alignment, vertical alignment and text wrapping on overflow.

import writeXlsxFile from'write-excel-file'const data = [
  [{
    value: 'Row 1, Col 1',
    fontWeight: 'bold'
  }, {
    value: 'Row 1, Col 2',
    color: '#ffffff',
    backgroundColor: '#cc0000'
  }],
  [{
    value: 'Row 2, Col 1',
    align: 'right',
    alignVertical: 'top'
  }, {
    value: 'Row 2, Col 2. Long Text \n Multi-line',
    wrap: true
  }]
]

awaitwriteXlsxFile(data, {
  fileName: 'file.xlsx'
})

Solution 4:

Using xlsx-style, foreach yout collection 'WorkSheet' and add the style before add in 'WorkBook'. The property responsible for this process is the 's' (style).

Sample:

_.forEach(ws, (v, c) => {
    if (c !== '!ref') {
        if (header.indexOf(v.v) >= 0) {
            ws[c]['s'] = {
                fill: {
                patternType: 'solid', // none / solidfgColor: {rgb: 'FFD3D3D3'}
                }
            }
        }
    }
})