Created XLSX compression #220

New Issue

2015-05-05T08:50:57Z

SzaboAdamImre commented

2015-05-05 08:50:57 +00:00

(Migrated from github.com)

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

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) ``` javascript 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

👍 1

Siri0n commented

2015-05-05 11:02:58 +00:00

(Migrated from github.com)

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 commented

2015-05-05 11:09:32 +00:00

(Migrated from github.com)

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

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

SheetJSDev commented

2015-05-05 15:11:35 +00:00

(Migrated from github.com)

@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 commented

2015-05-05 18:23:02 +00:00

(Migrated from github.com)

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

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

bchr02 commented

2015-08-06 00:36:47 +00:00

(Migrated from github.com)

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

bchr02 commented

2015-08-06 04:07:47 +00:00

(Migrated from github.com)

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

@SzaboAdamImre @Siri0n @SheetJSDev I created a [pull request](https://github.com/SheetJS/js-xlsx/pull/284) 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- commented

2015-11-05 21:05:56 +00:00

(Migrated from github.com)

@bchr02 So many thanks, this is great!

gtskaushik commented

2017-10-06 18:07:08 +00:00

(Migrated from github.com)

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?

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 commented

2017-10-07 01:12:59 +00:00

(Migrated from github.com)

@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 it's not enabled by default, you have to pass the [`compression:true` option](https://docs.sheetjs.com/#writing-options) to the write function: ```js var wopts = {bookType: 'xlsx', bookSST: true, compression:true}; var binary = XLSX.write(workbook, wopts); ```

gtskaushik commented

2017-10-08 08:16:05 +00:00

(Migrated from github.com)

@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 commented

2017-10-08 13:36:16 +00:00

(Migrated from github.com)

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

gtskaushik commented

2017-10-08 17:57:28 +00:00

(Migrated from github.com)

                //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 commented

2018-05-18 07:58:36 +00:00

(Migrated from github.com)

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

shdb1993 commented

2020-07-10 02:22:44 +00:00

(Migrated from github.com)

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

Sign in to join this conversation.