Sheetjs: SheetJS xlsx-style need cell style in excel

[biswassuman23]

I am trying to export excel using SheetJS/xlsx and want to format cell. I am using following code and excel is generating but can't format a cell. Can any one point the issue or can share a complete sample code for this?

Loading library files

<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script type="text/javascript" src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script>
<script type="text/javascript" src="http://sheetjs.com/demos/FileSaver.js"></script>

Remaining code are

function Workbook() {
    if(!(this instanceof Workbook)) return new Workbook();
    this.SheetNames = [];
    this.Sheets = {};
}

function sheet_from_array_of_arrays(data, opts) {
    var ws = {};
    var range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
    for(var R = 0; R != data.length; ++R) {
        for(var C = 0; C != data[R].length; ++C) {
            if(range.s.r > R) range.s.r = R;
            if(range.s.c > C) range.s.c = C;
            if(range.e.r < R) range.e.r = R;
            if(range.e.c < C) range.e.c = C;
            var cell = {v: data[R][C],
                  s: { alignment: {textRotation: 90 },
                       font: {sz: 14, bold: true, color: #FF00FF }
                };
        //cell.s = {}
       /*var cell ={ v: '2.4.2014',
        t: 's',
        r: '<t>2.4.2014</t>',
        h: '2.4.2014',
        w: '2.4.2014',
        s: 
        { patternType: 'solid',
          fgColor: { theme: 8, tint: 0.3999755851924192, rgb: '9ED2E0' },
          bgColor: { indexed: 64 } } };

      */
            if(cell.v == null) continue;
            var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

            if(typeof cell.v === 'number') cell.t = 'n';
            else if(typeof cell.v === 'boolean') cell.t = 'b';
            else if(cell.v instanceof Date) {
                cell.t = 'n'; cell.z = XLSX.SSF._table[14];
                cell.v = datenum(cell.v);
            }
            else cell.t = 's';

            ws[cell_ref] = cell;
        }
    }
    if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
}
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;
}
function GenerateExcelFile(inData, colWidth){
  var wb = new Workbook();
  var ws = sheet_from_array_of_arrays(inData); 

  var ws_name = "SheetJS";  

  /* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
/* TEST: column widths */
ws['!cols'] = colWidth;

var wbout = XLSX.write(wb, {bookType:'xlsx', bookSST:true, type: 'binary'});
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), "test.xlsx")

}
````
Calling with these code

var excelData = "JSON DATA";
var wscols = [
{wch:30},
{wch:20},
{wch:20}
];

Export
```
Please help me finding out where I am wrong.

Thanks Suman

[sathish804]

I am also having the similar issue, any luck with the style information writing?

[mellis3]

I've been using a fork of XLSX which is better with styling https://github.com/protobi/js-xlsx

On caveat, there's an issue with dist/cpexcel.js, but here's the fix: https://github.com/protobi/js-xlsx/issues/78

[polash62]

I have also same problem. I don't know how to make header text bold and excel column size need to adjustable with text size.

[elhamsarikhani]

+1

[polash62]

By the way, I have solved this issues by doing {raw=true}, which keep all cell format as like html table.

var tbl = XLSX.utils.table_to_book(document.getElementById('tableforexcel'), {raw:true}); XLSX.writeFile(tbl, file_name, {cellStyles:true});

[elhamsarikhani]

That won't help me because I am generating the file from the data that is not on the screen entirely.
my table has paging and I am exporting all the data into the excel file.

[polash62]

Then I think Datatable Export will help you..
Here is a the library...
https://datatables.net/extensions/buttons/examples/initialisation/export.html

[SheetJSDev]

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist.