Created XLSX compression

[SzaboAdamImre]

Hello!

I might be missing some functionality here, but I can't enable the compression on the files... I'm using the node version of this library (https://www.npmjs.com/package/xlsx) to convert some server side data to XLSX, and serve it as downloadable file (without saving the file itself on the server side). The code I'm using: (the callback function is a HAPI reply function)

var wopts = {bookType: 'xlsx', bookSST: false};
var binary = XLSX.write(workbook, wopts);
var response = callback(binary);
response.type('application/octet-stream');
response.header('Content-Disposition', 'attachment; filename="' + fileName + '.xlsx"');

This code works fine, the xlsx is available for download, and the data is correct. The problem is that the file size is too big... F.E a downloaded file is 1,3 MB. I open that file in Excel, modify a small detail (f.e. modify a number), and the file size becomes 153 KB.

I have reproduced this with LibreOffice, and with the writeFile function as well.

What am I missing?

Thx in advance

[Siri0n]

I could guess it's concerned with Shared String Table (ISO/IEC 29500-1 specification, p.18.4). In than case devs just should implement that mechanism in js-xlsx.

[SzaboAdamImre]

Tried it with and without SST (bookSST true/false), no change (also with xlsx, xlsb and xlsm mode - neither was good)

[SheetJSDev]

@SzaboAdamImre @Siri0n the zip library is not compressing (it is storing the files as plaintext rather than using the DEFLATE algorithm). That option needs to be made available to the write functions

[SzaboAdamImre]

@SheetJSDev So it is not available in the current version?

[bchr02]

@SzaboAdamImre Did you ever figure out how to compress your file? I am running into the same exact problem. Thanks.

[bchr02]

@SzaboAdamImre @Siri0n @SheetJSDev

I created a pull request which will give us the ability to add a compression option.

Example usage: XLSX.writeFile(wb, 'out.xlsx', {compression: 'DEFLATE'});

If you don't want to wait for the next version commit then just edit your xlsx.js file and replace the write_zip_type function with the updated one:

function write_zip_type(wb, opts) {
    var o = {}, z;
    opts = opts||{};
    z = write_zip(wb, opts);

    switch(opts.type) {
        case "base64": o.type = "base64"; if (typeof opts.compression === 'string') {o.compression = opts.compression;} break;
        case "binary": o.type = "string"; if (typeof opts.compression === 'string') {o.compression = opts.compression;} break;
        case "buffer": o.type = "nodebuffer"; if (typeof opts.compression === 'string') {o.compression = opts.compression;} break;
        case "file": o.type = "nodebuffer"; if (typeof opts.compression === 'string') {o.compression = opts.compression;} break;
        default: throw new Error("Unrecognized type " + opts.type);
    }

    if(opts.type === "file") {
        return _fs.writeFileSync(opts.file, z.generate(o));
    }
    else {
        return z.generate(o);
    }
}

[chk-]

@bchr02 So many thanks, this is great!

[gtskaushik]

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

Please let me know whether this issue is fixed?

[reviewher]

@gtskaushik it's not enabled by default, you have to pass the compression:true option to the write function:

var wopts = {bookType: 'xlsx', bookSST: true, compression:true};
var binary = XLSX.write(workbook, wopts);

[gtskaushik]

@reviewher Thanks for the immediate reply. 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

[reviewher]

@gtskaushik can you share the code that generated the file?

[gtskaushik]

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);

[you96]

@gtskaushik hello, got the same problem, did you fix this issue? many thanks:)

[shdb1993]

Same Problem Here.File Size is large on download but on resaving, the size of the file reduces.Please share the solution for this if anyone has come up with one