XLSX compression

[gtskaushik]

Hi All, The final file size is enormous. But on opening and saving the file again without any changes reduces it to normal size.

I'm already using this library with compression option. But still the size is 30 MB. But when I open the file in Microsoft Excel and save it again , it becomes 6 MB

Please direct me to solve this issue. I am using this library in angular 1.x. It creates the file on the browser client and downloads it as a BLOB.

Regards, Kaushik

[gtskaushik]

This looks like an old issue from the SheetJs/js-xlsx project. Please find the following link. The link mentions that the issue is fixed already. But still I am facing the same problem.

https://github.com/SheetJS/js-xlsx/issues/220

[gtskaushik]

Please find my code below:- var wb = new Workbook() var ws = {}; var range = {s: {c:0, r:0}, 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: {} }; if(cell.v === null) continue; var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

                //This is for the Header and timestamps cells
                if(R==0 || C==0 || C==1){
                    cell.s = {
                        "fill": {
                            "patternType": "solid",
                            "bgColor": {rgb:'003d79'},
                            "fgColor": {rgb:'003d79'}
                        },
                        "font": {
                            "color": {rgb:'ffffff'},
                            "bold": true
                        }
                    }
                }

                if(typeof cell.v === 'number') {
                    cell.t = 'n';
                    cell['s']['numFmt'] = "0.00"//Included Number Format
                }

                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;
            }
        }
        ws['!ref'] = XLSX.utils.encode_range(range)//Setting the reference range of the sheet

        ws['!cols'] = []
        data[0].forEach(function(header){ ws['!cols'].push({"wch": header.length}) })//Setting Dynamic column width

        ws['!freeze'] = { xSplit: "1", ySplit: "1", topLeftCell: "B2", activePane: "bottomRight", state: "frozen" }//Freezing the columns

       wb.SheetNames.push(ws_name);
        wb.Sheets[ws_name] = ws;

        var wopts = { bookType:'xlsx', bookSST:true, type: "binary",compression:true};

        return XLSX.write(wb,wopts);

[Plotisateur]

Ey mate, had the same problem

Seems like the compression have been erased from the xlsx.js file of this project You can add it manually in xlsx-style/xlsx.js, function write_zip_type Just rewrite it conditionnaly to add compression: 'DEFLATE' to the object passed to z.generate

[gtskaushik]

Thank You @Plotisateur . This really helped.