Created XLSX compression #220

SzaboAdamImre · 2015-05-05T08:50:57Z

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 · 2015-05-05T11:02:58Z

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 · 2015-05-05T11:09:32Z

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

SheetJSDev · 2015-05-05T15:11:35Z

@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 · 2015-05-05T18:23:02Z

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

bchr02 · 2015-08-06T00:36:47Z

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

bchr02 · 2015-08-06T04:07:47Z

@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- · 2015-11-05T21:05:56Z

@bchr02 So many thanks, this is great!

gtskaushik · 2017-10-06T18:07:08Z

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 · 2017-10-07T01:12:59Z

@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 · 2017-10-08T08:16:05Z

@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 · 2017-10-08T13:36:16Z

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

gtskaushik · 2017-10-08T17:57:28Z

                //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 · 2018-05-18T07:58:36Z

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

shdb1993 · 2020-07-10T02:22:44Z

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

@sao93859

- README and example cleanup - basic XLSB and ODS write support - flow typecheck for ODS file Note: xlsx.js flow fails: facebook/flow#380 - exposed jszip compression (fixes SheetJS#220, closes SheetJS#284) README issues: | id | author | comment | |-----:|:---------------|:---------------------------------------------| | SheetJS#202 | @sao93859 | closes SheetJS#202 | | SheetJS#211 | @alexanderchan | closes SheetJS#211 corrected examples | | SheetJS#327 | @cskaandorp | changed saveAs example to match write tests | | SheetJS#424 | @dskrvk | added note about s2roa h/t @LeonardoPatignio | | SheetJS#496 | @jimmywarting | closes SheetJS#496 adapted rABS examples with rAAS | ODS file format issues: | id | author | comment | |-----:|:---------------|:---------------------------------------------| | protobi#148 | @user4815162342| closes protobi#148 h/t @ziacik | | protobi#166 | @paulproteus | closes protobi#166 rudimentary ODS write support | | protobi#177 | @ziacik | closes protobi#177 | | protobi#179 | @ziacik | closes protobi#179 use JSON when available | | SheetJS#317 | @ziacik | closes SheetJS#317 | | SheetJS#328 | @think01 | closes SheetJS#328 | | SheetJS#383 | @mdamt | closes SheetJS#383 duplicate cells should be copied | | SheetJS#430 | @RB-Lab | closes SheetJS#430 | | SheetJS#546 | @lgodard | closes SheetJS#546 thanks to other changes |

SheetJSDev closed this as completed in 86d6a09 Feb 3, 2017

mohnish4795 mentioned this issue Aug 11, 2017

Incredible huge file size andreyan-andreev/node-excel-export#22

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Created XLSX compression #220

Created XLSX compression #220

SzaboAdamImre commented May 5, 2015

Siri0n commented May 5, 2015

SzaboAdamImre commented May 5, 2015

SheetJSDev commented May 5, 2015

SzaboAdamImre commented May 5, 2015

bchr02 commented Aug 6, 2015

bchr02 commented Aug 6, 2015

chk- commented Nov 5, 2015

gtskaushik commented Oct 6, 2017

reviewher commented Oct 7, 2017

gtskaushik commented Oct 8, 2017

reviewher commented Oct 8, 2017

gtskaushik commented Oct 8, 2017

you96 commented May 18, 2018

shdb1993 commented Jul 10, 2020 •

edited

Loading

Created XLSX compression #220

Created XLSX compression #220

Comments

SzaboAdamImre commented May 5, 2015

Siri0n commented May 5, 2015

SzaboAdamImre commented May 5, 2015

SheetJSDev commented May 5, 2015

SzaboAdamImre commented May 5, 2015

bchr02 commented Aug 6, 2015

bchr02 commented Aug 6, 2015

chk- commented Nov 5, 2015

gtskaushik commented Oct 6, 2017

reviewher commented Oct 7, 2017

gtskaushik commented Oct 8, 2017

reviewher commented Oct 8, 2017

gtskaushik commented Oct 8, 2017

you96 commented May 18, 2018

shdb1993 commented Jul 10, 2020 • edited Loading

shdb1993 commented Jul 10, 2020 •

edited

Loading