Sheetjs: Angular 2 Issue

[junaidinam]

Hi,
I am new to SheetJS and Angular 2 with typescript so please forgive me if i am making any obvious mistake.

First i created a an html file to test writexls. It was working fine but when i try to recreate the same thing in my Angular 2 App. It gives me an error.

:
TypeError: s.t.match is not a function at write_sst_xml (http://localhost:9091/xlsx/dist/xlsx.core.min.js:8:27644)

angular version is 2.3.0 and xlsx version is 0.8.1. My angular code is

var wb = new Workbook();
//var wb: any = {}; // doesnt work
var data = [[1, 2, 3], [true, false, null, "sheetjs"], ["foo", "bar", new Date("2014-02-19T14:30Z"), "0.3"], ["baz", null, "qux"]];
var ws_name = "SheetJS";
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = this.sheet_from_array_of_arrays(data);
// this is where error occurs
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' });
saveAs(new Blob([this.s2ab(wbout)], { type: "application/octet-stream" }), "test.xlsx");

export class Workbook {
SheetNames: any = [];
Sheets: any = {};
}
export class Cell {
t?: any;
v?: any;
}

I know people have done this before so there is definitely something wrong that i am doing here. Could it be angular issue?
I would really appreciate any help in this regard.
Thanks

[junaidinam]

I was able to figure out the issue. I was providing the wrong input.

[reviewher]

@junaidinam for posterity sake can you explain what happened with "wrong input"?

[x77a1]

I was facing the same issue. Passing bookSST as false in the write function fixed the issue for me.

The write function would look something like this:
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });

[reviewher]

@x77a1 that seems strange, can you share some code where it works with bookSST false but fails with bookSST true?

[tosehee75]

Hello junaidinam.

Can you share your angular 2 code by chance?

I am trying to do the same, and a lot of these functions that I see in the code snippet doesn't exist or I can't find them..

[junaidinam]

Sorry guys for not responding. i will definitely share my code. In fact if you could give me some time i will create a small angular 2 library which will only take a variable and generate an excel file based on that. Please give me this weekend to work on it.

[tosehee75]

I'd appreciate that, and that'll help me tremendously.

I've spent few hours on searching, so if you can share the code snippet to begin with, I could satisfy my curiosity...

Following your code above, I created Workbook and Cell.

However, the code snippet for sheet_from_array_of_arrays() and datenum is either outdated or needs refactoring to work with your code..

I get a compile error in angular 2 as is.

[junaidinam]

saveExcel(param: any, filename: string) {
    var wb = new Workbook();

    var write = new Array;
    param.forEach(function (row,index) {

        var each = new Array;
        var keys = Object.keys(row); // all the keys
        if (index == 0) {
            // column headers
            for (var i = 0; i < keys.length; i++) {
                each.push(keys[i]);
            }
            write.push(each); // write header
            each = [];
            for (var i = 0; i < keys.length; i++) {
                each.push(row[keys[i]]);
            }
        }
        else
        {
            for (var i = 0; i < keys.length; i++) {
                each.push(row[keys[i]]);
            }
        }
        write.push(each);
    }, this);

    var data = write;

    var ws_name = "Sheet 1";
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = this.sheet_from_array_of_arrays(data);

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


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

sheet_from_array_of_arrays(data: any, opts?: any): any {
    var ws: any = {};

    var wscols = [
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 },
        { wch: 20 }
    ];

    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], t: 'n' };
            //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 cell.t = 's';

            //var cell = new Cell();
            var cell: any = {};
            cell.v = data[R][C];
            //console.log(cell);
            var cell_ref = XLSX.utils.encode_cell({ c: C, r: R });
            //console.log(cell_ref);
            if (cell.v == null) continue;
            if (typeof cell.v === 'number') cell.t = 'n';
            else if (typeof cell.v === 'boolean') cell.t = 'b';
            else cell.t = 's';
            //console.log(cell);
            ws[cell_ref] = cell;
            //console.log(ws);
        }
    }
    if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    ws['!cols'] = wscols;
    return ws;
}

Please let me know if it helps.

[tosehee75]

Wow.

Thanks a bunch. I will give it a try.

Do you happen to have a api for saveAs()? And is Workbook/Cell same as above?

and what version of js-xlsx are you using?

I am getting an invalid argument error with the below.

if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);

1 more.. How did you get around this error with angular-cli?

ERROR in ./~/xlsx/dist/cpexcel.js Module not found: Error: Can't resolve './cptable' in '/Users/slee/work/deposco/node_modules/xlsx/dist' @ ./~/xlsx/dist/cpexcel.js 808:16-41 @ ./~/xlsx/xlsx.js @ ./src/app/components/export-data/export-data.component.ts @ ./src/app/app.module.ts @ ./src/main.ts @ multi webpack-dev-server/client?http://localhost:4200 ./src/main.ts

[shadowkrusha]

Thank you for the example code @junaidinam, this pointed me in the right direction.

I've created a repo with a working angular-cli example if people want to see how I solved the compile issues.

https://bitbucket.org/shadowkrusha/test-xlsx

[tosehee75]

Updated to 0.9.2, and it compiles fine now. I will give it a try..
Thanks a bunch again.

• Update

Works like a charm!

Thanks!!!!!!!!!

[bogdancar]

I added an example on exporting a xlsx file from an array of jsons in Angular2/4:

https://github.com/bogdancar/xlsx-json-to-xlsx-demo-Angular2

[niekoost]

@bogdancar When I click on your link I get a 404 error, when I copy the link text (the displayed URL) I get to the correct page. Please update the target of the link.

[bogdancar]

@niekoost Fixed the link, thank you for the feedback.

[shivabathiniggk]

@bogdancar have u tried adding styling to the cells . i tried to add styles by referencing each cellobject and adding style property to it .But it is not working .can u help me on it