xls => json => manipulation => xlsx #586

New Issue

2017-03-08T21:29:18Z

reidblomquist commented

2017-03-08 21:29:18 +00:00

(Migrated from github.com)

So, I've been digging around this repo a bit and it seems like the go-to method for filtering etc is to convert things to json using XLSX.utils.sheet_to_json(wb.Sheets.SheetName), however I can't find any documentation on then turning that manipulated json back into a workbook. Please advise! (thanks in advance)

SheetJSDev commented

2017-03-08 22:35:24 +00:00

(Migrated from github.com)

@reidblomquist at a high level, sheet_to_json takes an options argument. The header field of the options argument controls the output format: default is an array of JSON objects whose keys are determined by the first row, "A" will use the column labels rather than the first row, and 1 will generate an array of arrays. By default it will emit the formatted strings, but also setting raw:true will give you the underlying values. Using the write.xlsx file from the test files repo :

> var wb = XLSX.readFile('test_files/write.xlsx');
> XLSX.utils.sheet_to_json(wb.Sheets.SheetJS, {header:1})
[ [ '1', '2', '3' ],
  [ 'TRUE', 'FALSE', , 'sheetjs' ],
  [ 'foo', 'bar', '2/19/14', '0.3' ],
  [ 'baz', , 'qux' ] ]
> XLSX.utils.sheet_to_json(wb.Sheets.SheetJS, {header:1, raw:true})
[ [ 1, 2, 3 ],
  [ true, false, , 'sheetjs' ],
  [ 'foo', 'bar', 41689.604166666664, '0.3' ],
  [ 'baz', , 'qux' ] ]

https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js includes an example of how to convert an array of arrays back into a worksheet. The essence of it is to walk the array and generate a cell object for each value based on its position.

This will eventually have to be merged into the codebase when we add in support from js-harb -- for formats like DIF and PRN, which effectively represent everything as text, it's structurally easier to generate an array of arrays.

PS: The date/number confusion is due to how Excel stores dates -- they are actually date codes with a special number format. This will be addressed when we settle on the correct behavior for cellDates option.

Keep this open until we add all of this to the documentation and have a proper roundtrip demo

@reidblomquist at a high level, sheet_to_json takes an options argument. The header field of the options argument controls the output format: default is an array of JSON objects whose keys are determined by the first row, `"A"` will use the column labels rather than the first row, and `1` will generate an array of arrays. By default it will emit the formatted strings, but also setting raw:true will give you the underlying values. Using the [write.xlsx file from the test files repo](https://github.com/SheetJS/test_files/blob/master/write.xlsx?raw=true) : ```js > var wb = XLSX.readFile('test_files/write.xlsx'); > XLSX.utils.sheet_to_json(wb.Sheets.SheetJS, {header:1}) [ [ '1', '2', '3' ], [ 'TRUE', 'FALSE', , 'sheetjs' ], [ 'foo', 'bar', '2/19/14', '0.3' ], [ 'baz', , 'qux' ] ] > XLSX.utils.sheet_to_json(wb.Sheets.SheetJS, {header:1, raw:true}) [ [ 1, 2, 3 ], [ true, false, , 'sheetjs' ], [ 'foo', 'bar', 41689.604166666664, '0.3' ], [ 'baz', , 'qux' ] ] ``` https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js includes an example of how to convert an array of arrays back into a worksheet. The essence of it is to walk the array and generate a cell object for each value based on its position. This will eventually have to be merged into the codebase when we add in support from [js-harb](https://github.com/SheetJS/js-harb) -- for formats like DIF and PRN, which effectively represent everything as text, it's structurally easier to generate an array of arrays. PS: The date/number confusion is due to how Excel stores dates -- they are actually date codes with a special number format. This will be addressed when [we settle on the correct behavior for cellDates option](https://github.com/SheetJS/js-xlsx/pull/581). Keep this open until we add all of this to the documentation and have a proper roundtrip demo

reidblomquist commented

2017-03-10 20:51:20 +00:00

(Migrated from github.com)

@SheetJSDev thank you very much for the prompt response! I wanted to leave my json a bit more structured for my filtering purposes (so, an array of objects instead of an array of array) - but (just in case anyone else wants the sauce there) I managed to walk the resulting data as an array of arrays like so:

  function json2ws(data, opts) {
    var ws = {};
    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 !== Object.keys(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][Object.keys(data[R])[C]] };
        if(cell.v === null) continue;
        var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

        /* TEST: proper cell types and value handling */
        if(typeof cell.v === 'number') cell.t = 'n';
        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;
      }
    }

    // TEST: proper range
    if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
    return ws;
  }

@SheetJSDev thank you very much for the prompt response! I wanted to leave my json a bit more structured for my filtering purposes (so, an array of objects instead of an array of array) - but (just in case anyone else wants the sauce there) I managed to walk the resulting data as an array of arrays like so: function json2ws(data, opts) { var ws = {}; 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 !== Object.keys(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][Object.keys(data[R])[C]] }; if(cell.v === null) continue; var cell_ref = XLSX.utils.encode_cell({c:C,r:R}); /* TEST: proper cell types and value handling */ if(typeof cell.v === 'number') cell.t = 'n'; 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; } } // TEST: proper range if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range); return ws; }

SheetJSDev commented

2017-03-30 03:39:40 +00:00

(Migrated from github.com)

@reidblomquist here's an example: https://runkit.com/sheetjs/58dc6ea1eae8b800146359eb Let us know if that makes any sense

roccomuso commented

2017-04-01 15:58:31 +00:00

(Migrated from github.com)

@SheetJSDev I'm having the same issue with the Dates.. What's the best approach to get back the "date" format on the XLS file and not a number?

SheetJSDev commented

2017-04-01 16:02:40 +00:00

(Migrated from github.com)

If you are starting from a file, pass the option cellDates:true:

> var XLSX = require('xlsx'), wb;

// default
> wb = XLSX.readFile('write.xlsx'); XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1})
[ [ 1, 2, 3 ],
  [ true, false, , 'sheetjs' ],
  [ 'foo', 'bar', 41689.604166666664, '0.3' ],
  [ 'baz', , 'qux' ] ]

// cellDates:true
> wb = XLSX.readFile('write.xlsx', {cellDates:true}); XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1})
[ [ 1, 2, 3 ],
  [ true, false, , 'sheetjs' ],
  [ 'foo', 'bar', 2014-02-19T14:30:00.000Z, '0.3' ],
  [ 'baz', , 'qux' ] ]

If you are starting from a file, pass the option `cellDates:true`: ```js > var XLSX = require('xlsx'), wb; // default > wb = XLSX.readFile('write.xlsx'); XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1}) [ [ 1, 2, 3 ], [ true, false, , 'sheetjs' ], [ 'foo', 'bar', 41689.604166666664, '0.3' ], [ 'baz', , 'qux' ] ] // cellDates:true > wb = XLSX.readFile('write.xlsx', {cellDates:true}); XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1}) [ [ 1, 2, 3 ], [ true, false, , 'sheetjs' ], [ 'foo', 'bar', 2014-02-19T14:30:00.000Z, '0.3' ], [ 'baz', , 'qux' ] ] ```

roccomuso commented

2017-04-01 16:07:14 +00:00

(Migrated from github.com)

@SheetJSDev cool. Now it reads the Date. But when I write back to file i see just the numbers instead of the Dates.

module.exports = function(aoa, fileName){
  var wb = aoa_to_workbook(aoa); // wb will be a workbook with one sheet
  XLSX.writeFile(wb, fileName); // save to file
}

function sheet_to_workbook(sheet/*:Worksheet*/, opts)/*:Workbook*/ {
	var n = opts && opts.sheet ? opts.sheet : "Sheet1";
	var sheets = {}; sheets[n] = sheet;
	return { SheetNames: [n], Sheets: sheets };
}

function aoa_to_workbook(data/*:Array<Array<any> >*/, opts)/*:Workbook*/ {
	return sheet_to_workbook(XLSX.utils.aoa_to_sheet(data, opts), opts);
}

@SheetJSDev cool. Now it reads the Date. But when I write back to file i see just the numbers instead of the Dates. ```javascript module.exports = function(aoa, fileName){ var wb = aoa_to_workbook(aoa); // wb will be a workbook with one sheet XLSX.writeFile(wb, fileName); // save to file } function sheet_to_workbook(sheet/*:Worksheet*/, opts)/*:Workbook*/ { var n = opts && opts.sheet ? opts.sheet : "Sheet1"; var sheets = {}; sheets[n] = sheet; return { SheetNames: [n], Sheets: sheets }; } function aoa_to_workbook(data/*:Array<Array<any> >*/, opts)/*:Workbook*/ { return sheet_to_workbook(XLSX.utils.aoa_to_sheet(data, opts), opts); } ```

SheetJSDev commented

2017-04-01 16:15:41 +00:00

(Migrated from github.com)

aoa_to_sheet also needs the argument:

var wb = XLSX.readFile('write.xlsx', {cellDates:true});
var json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1});
var wb2 = {SheetNames:["SheetJS"], Sheets:{SheetJS:XLSX.utils.aoa_to_sheet(json, {cellDates:1})}};
XLSX.writeFile(wb2, 'writenew.xlsx', {cellDates:true});

The relevant part of the docs is https://sheetjs.gitbooks.io/docs/#array-of-arrays-input -- how should it be changed to explain that behavior? Currently it says:

cellDates false Store dates as type d (default is n)

I guess it should just say "raw dates" rather than "type d"?

aoa_to_sheet also needs the argument: ```js var wb = XLSX.readFile('write.xlsx', {cellDates:true}); var json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1}); var wb2 = {SheetNames:["SheetJS"], Sheets:{SheetJS:XLSX.utils.aoa_to_sheet(json, {cellDates:1})}}; XLSX.writeFile(wb2, 'writenew.xlsx', {cellDates:true}); ``` The relevant part of the docs is https://sheetjs.gitbooks.io/docs/#array-of-arrays-input -- how should it be changed to explain that behavior? Currently it says: >cellDates false Store dates as type d (default is n) I guess it should just say "raw dates" rather than "type d"?

roccomuso commented

2017-04-01 16:34:19 +00:00

(Migrated from github.com)

Thank you!
Yeah "raw dates" would be better.
Btw, I changed the code this way:

module.exports = function(aoa, fileName){
  var wb = aoa_to_workbook(aoa, {cellDates: true}); // wb will be a workbook with one sheet
  XLSX.writeFile(wb, fileName, {cellDates: true}); // save to file
}

function sheet_to_workbook(sheet/*:Worksheet*/, opts)/*:Workbook*/ {
	var n = opts && opts.sheet ? opts.sheet : "Sheet1";
	var sheets = {}; sheets[n] = sheet;
	return { SheetNames: [n], Sheets: sheets };
}

function aoa_to_workbook(data/*:Array<Array<any> >*/, opts)/*:Workbook*/ {
	return sheet_to_workbook(XLSX.utils.aoa_to_sheet(data, opts), opts);
}

But now the Date columns are blank.
PS. my date format is "mm/gg/yyyy"

Thank you! Yeah "raw dates" would be better. Btw, I changed the code this way: ```javascript module.exports = function(aoa, fileName){ var wb = aoa_to_workbook(aoa, {cellDates: true}); // wb will be a workbook with one sheet XLSX.writeFile(wb, fileName, {cellDates: true}); // save to file } function sheet_to_workbook(sheet/*:Worksheet*/, opts)/*:Workbook*/ { var n = opts && opts.sheet ? opts.sheet : "Sheet1"; var sheets = {}; sheets[n] = sheet; return { SheetNames: [n], Sheets: sheets }; } function aoa_to_workbook(data/*:Array<Array<any> >*/, opts)/*:Workbook*/ { return sheet_to_workbook(XLSX.utils.aoa_to_sheet(data, opts), opts); } ``` But now the Date columns are blank. PS. my date format is "mm/gg/yyyy"

SheetJSDev commented

2017-04-01 16:37:32 +00:00

(Migrated from github.com)

Excel does some weird things with localization. Can you try with the date format mm/dd/yyyy? Also, if you can share an input file I'd love to take a look

Excel does some weird things with localization. Can you try with the date format `mm/dd/yyyy`? Also, if you can share an input file I'd love to take a look

roccomuso commented

2017-04-01 16:42:56 +00:00

(Migrated from github.com)

@SheetJSDev sorry i made a typo in my previous post, date is already mm/dd/yyyy. I'm using a standard XLS file.. Are you able to reproduce the issue?

@SheetJSDev sorry i made a typo in my previous post, date is already `mm/dd/yyyy`. I'm using a standard XLS file.. Are you able to reproduce the issue?

SheetJSDev commented

2017-04-01 16:51:02 +00:00

(Migrated from github.com)

Just tried:

var XLSX = require('xlsx');
var wb = XLSX.readFile('write.xlsx', {cellDates:true});
var json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1});

/* Set D4 to a date */
json[3][3] = new Date("2017-02-19 14:30");
var wb2 = {SheetNames:["SheetJS"], Sheets:{SheetJS:XLSX.utils.aoa_to_sheet(json, {cellDates:1})}};

/* force the cell format to `mm/dd/yyyy` */
wb2.Sheets.SheetJS.D4.z = "mm/dd/yyyy";

/* write it */
XLSX.writeFile(wb2, 'writenew.xlsx', {cellDates:true});

source file: write.xlsx

output file: writenew.xlsx

The loss of the original format is expected (since the JSON loses the format info) but setting the format to mm/dd/yyyy doesn't result in a blank cell:

Just tried: ```js var XLSX = require('xlsx'); var wb = XLSX.readFile('write.xlsx', {cellDates:true}); var json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]], {raw:true, header:1}); /* Set D4 to a date */ json[3][3] = new Date("2017-02-19 14:30"); var wb2 = {SheetNames:["SheetJS"], Sheets:{SheetJS:XLSX.utils.aoa_to_sheet(json, {cellDates:1})}}; /* force the cell format to `mm/dd/yyyy` */ wb2.Sheets.SheetJS.D4.z = "mm/dd/yyyy"; /* write it */ XLSX.writeFile(wb2, 'writenew.xlsx', {cellDates:true}); ``` source file: [write.xlsx](https://github.com/SheetJS/js-xlsx/files/887807/write.xlsx) output file: [writenew.xlsx](https://github.com/SheetJS/js-xlsx/files/887808/writenew.xlsx) The loss of the original format is expected (since the JSON loses the format info) but setting the format to `mm/dd/yyyy` doesn't result in a blank cell: <img width="284" alt="writenew" src="https://cloud.githubusercontent.com/assets/6070939/24580673/d7550004-16d9-11e7-88ea-ae5c78c6d5c8.png">

roccomuso commented

2017-04-01 19:03:03 +00:00

(Migrated from github.com)

Really weird. Still having that issue. Btw I'm using XLS files not XLSX.

SheetJSDev commented

2017-04-01 19:26:44 +00:00

(Migrated from github.com)

@roccomuso yes I can reproduce with the XLS write, there are two issues:

the blank cells are coming from a missing case in https://github.com/SheetJS/js-xlsx/blob/master/bits/78_writebiff.js#L59 . XLS has no native date cell concept, so it has to be converted back to number. This fix is easy:

        if(cell.v != null) switch(cell.t) {
-               case 'n':
-                       if((cell.v == (cell.v|0)) && (cell.v >= 0) && (cell.v < 65536))
-                               write_biff_rec(ba, 0x0002, write_BIFF2INT(R, C, cell.v));
+               case 'd': case 'n':
+                       var v = cell.t == 'd' ? datenum(cell.v) : cell.v;
+                       if((v == (v|0)) && (v >= 0) && (v < 65536))
+                               write_biff_rec(ba, 0x0002, write_BIFF2INT(R, C, v));
                        else
-                               write_biff_rec(ba, 0x0003, write_BIFF2NUMBER(R,C, cell.v));
+                               write_biff_rec(ba, 0x0003, write_BIFF2NUMBER(R,C, v));
                        return;

XLS writer is not saving the format table. That's a bit more involved. We'll look into it.

@roccomuso yes I can reproduce with the XLS write, there are two issues: 1) the blank cells are coming from a missing case in https://github.com/SheetJS/js-xlsx/blob/master/bits/78_writebiff.js#L59 . XLS has no native date cell concept, so it has to be converted back to number. This fix is easy: ```diff if(cell.v != null) switch(cell.t) { - case 'n': - if((cell.v == (cell.v|0)) && (cell.v >= 0) && (cell.v < 65536)) - write_biff_rec(ba, 0x0002, write_BIFF2INT(R, C, cell.v)); + case 'd': case 'n': + var v = cell.t == 'd' ? datenum(cell.v) : cell.v; + if((v == (v|0)) && (v >= 0) && (v < 65536)) + write_biff_rec(ba, 0x0002, write_BIFF2INT(R, C, v)); else - write_biff_rec(ba, 0x0003, write_BIFF2NUMBER(R,C, cell.v)); + write_biff_rec(ba, 0x0003, write_BIFF2NUMBER(R,C, v)); return; ``` 2) XLS writer is not saving the format table. That's a bit more involved. We'll look into it.

roccomuso commented

2017-04-04 16:32:15 +00:00

(Migrated from github.com)

I saw the 3a310bd commit. When will it be released? 👍

I saw the 3a310bd commit. When will it be released? :+1:

SheetJSDev commented

2017-04-04 16:35:08 +00:00

(Migrated from github.com)

@roccomuso it should be in 0.9.9 (the latest version) already. As mentioned, that is only the first half of the fix (to ensure that the date cells aren't blank).

roccomuso commented

2017-04-04 16:37:35 +00:00

(Migrated from github.com)

@SheetJSDev alright, what's missing exactly? will the v0.9.9 output the data cells as expected?

SheetJSDev commented

2017-04-04 16:44:05 +00:00

(Migrated from github.com)

The dates will show up as numbers. forcing dates isn't particularly hard, but we're trying to rebuild the formatting library before attacking those issues

👍 2

GreggOD commented

2017-06-22 10:54:20 +00:00

(Migrated from github.com)

@SheetJSDev thanks for being so responsive here.

Excuse me for asking another question but i've reached the point where I cant find the answer and I need to ask somebody.

In my excel sheet the date is inserted as: "03/12/2015", but excel formats it to: "03-Dec-15" .
When I turn the sheet into json, the date comes through as the formatted version "03-Dec-15".. I want the unformatted version "03/12/2015". I have added {raw:true} and {cellDates:true} but this isnt producing the result im looking for.

Any help or advice? :)

@SheetJSDev thanks for being so responsive here. Excuse me for asking another question but i've reached the point where I cant find the answer and I need to ask somebody. In my excel sheet the date is inserted as: "03/12/2015", but excel formats it to: "03-Dec-15" . When I turn the sheet into json, the date comes through as the formatted version "03-Dec-15".. I want the unformatted version "03/12/2015". I have added {raw:true} and {cellDates:true} but this isnt producing the result im looking for. Any help or advice? :)

SheetJSDev commented

2017-06-22 16:48:23 +00:00

(Migrated from github.com)

@GreggOD the specific input format is not preserved in the file. For example, if you type Jan 1 in US English Excel the input bar will show 1/1/2017 and the cell will show 1-Jan. So where's Jan 1? it was lost.

If you want to see 03/12/2015, you can force the output to render using the format code mm/dd/yyyy:

force input dates with cellDates:true option in the input function (read, readFile, aoa_to_sheet, ... however you are building the worksheet)
force output date format with dateNF:"mm/dd/yyyy" in sheet_to_json

Alternatively you can change the output date format manually by editing the z property of the relevant cells to mm/dd/yyyy.

@GreggOD the specific input format is not preserved in the file. For example, if you type `Jan 1` in US English Excel the input bar will show `1/1/2017` and the cell will show `1-Jan`. So where's `Jan 1`? it was lost. If you want to see `03/12/2015`, you can force the output to render using the format code `mm/dd/yyyy`: - force input dates with `cellDates:true` option in the input function (`read`, `readFile`, `aoa_to_sheet`, ... however you are building the worksheet) - force output date format with `dateNF:"mm/dd/yyyy"` in `sheet_to_json` Alternatively you can change the output date format manually by editing the `z` property of the relevant cells to `mm/dd/yyyy`.

SheetJSDev commented

2018-05-20 18:43:06 +00:00

(Migrated from github.com)

@reidblomquist there are new utility functions sheet_add_aoa and sheet_add_json that can directly append to the worksheet without having to convert to JSON.

@reidblomquist there are new utility functions `sheet_add_aoa` and `sheet_add_json` that can directly append to the worksheet without having to convert to JSON.

rahulpathak commented

2019-07-15 08:37:48 +00:00

(Migrated from github.com)

@SheetJSDev

How we can join multiple array in one object.
Suppose i have two xlsx file.
var url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx'];
XLSX.utils.sheet_to_json(worksheet, { raw: true });

In that i am getting two JSON data.

Now how we can merge in one object ? is there any method to merge multiple object in to one ?

@SheetJSDev How we can join multiple array in one object. Suppose i have two xlsx file. `var url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx'];` `XLSX.utils.sheet_to_json(worksheet, { raw: true });` In that i am getting two JSON data. ![image](https://user-images.githubusercontent.com/2330971/61203980-8e922f00-a709-11e9-848c-3470fa251d4d.png) Now how we can merge in one object ? is there any method to merge multiple object in to one ?

GreggOD commented

2019-07-15 11:01:01 +00:00

(Migrated from github.com)

I feel like that's a JavaScript question and not a sheetjs question @rahulpathak

Merge your two arrays using the spread operator:

let data = [...sheetArray1, ...sheetArray2]

And then if you want to sort the data use sorting methods from JavaScript.

I feel like that's a JavaScript question and not a sheetjs question @rahulpathak Merge your two arrays using the spread operator: ``` let data = [...sheetArray1, ...sheetArray2] ``` And then if you want to sort the data use sorting methods from JavaScript.

rahulpathak commented

2019-07-15 12:02:14 +00:00

(Migrated from github.com)

Hi @SheetJSDev / @GreggOD

I am using angular 7, I have to read multiple excel file. so i created one function to read data and after read data i have to merge all data in one object.
But here urldata not getting any value. In datalog getting value but not returning any value.
Please help me out why i am not getting any value he saying 'undefined'.

ngOnInit() {
        // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx'];

        const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{
            // Here i am getting datalog data but this data not retunning any value
            console.log(JSON.stringify(datalog));
            return datalog;
        }});
        
        // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{
        //     console.log(JSON.stringify(datalog1));
        //     return datalog1;
        // }});

        console.log(urldata);
    }

filereader(url, callback) {
        const req = new XMLHttpRequest();
        req.open('GET', url, true);
        req.responseType = 'arraybuffer';
        req.onload = (e) => {
            const data = new Uint8Array(req.response);
            const arr = [];
            for (let j = 0; j !== data.length; ++j) {
                arr[j] = String.fromCharCode(data[j]);
            }
            const bstr = arr.join('');
            const workbook = XLSX.read(bstr, { type: 'binary' });
            const first_sheet_name = workbook.SheetNames[0];
            const worksheet = workbook.Sheets[first_sheet_name];
            this.jsonData = XLSX.utils.sheet_to_json(worksheet, { raw: true });
            // create callback function to get data form here
            callback(this.jsonData);
        };
        req.send();
    }

Hi @SheetJSDev / @GreggOD I am using angular 7, I have to read multiple excel file. so i created one function to read data and after read data i have to merge all data in one object. But here urldata not getting any value. In datalog getting value but not returning any value. Please help me out why i am not getting any value he saying 'undefined'. ``` ngOnInit() { // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx']; const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ // Here i am getting datalog data but this data not retunning any value console.log(JSON.stringify(datalog)); return datalog; }}); // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{ // console.log(JSON.stringify(datalog1)); // return datalog1; // }}); console.log(urldata); } filereader(url, callback) { const req = new XMLHttpRequest(); req.open('GET', url, true); req.responseType = 'arraybuffer'; req.onload = (e) => { const data = new Uint8Array(req.response); const arr = []; for (let j = 0; j !== data.length; ++j) { arr[j] = String.fromCharCode(data[j]); } const bstr = arr.join(''); const workbook = XLSX.read(bstr, { type: 'binary' }); const first_sheet_name = workbook.SheetNames[0]; const worksheet = workbook.Sheets[first_sheet_name]; this.jsonData = XLSX.utils.sheet_to_json(worksheet, { raw: true }); // create callback function to get data form here callback(this.jsonData); }; req.send(); } ```

GreggOD commented

2019-07-15 15:51:05 +00:00

(Migrated from github.com)

ngOnInit() {
        // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx'];

        const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{
            // Here i am getting datalog data but this data not retunning any value
            console.log(JSON.stringify(datalog));
            return datalog;
        }});
        
        // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{
        //     console.log(JSON.stringify(datalog1));
        //     return datalog1;
        // }});

        console.log(urldata); <-- DO YOU MEAN THIS IS UNDEFINED?
    }

You need to return your callback in your filereader function:

return callback(this.jsonData);

> ``` > ngOnInit() { > // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx']; > > const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ > // Here i am getting datalog data but this data not retunning any value > console.log(JSON.stringify(datalog)); > return datalog; > }}); > > // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{ > // console.log(JSON.stringify(datalog1)); > // return datalog1; > // }}); > > console.log(urldata); <-- DO YOU MEAN THIS IS UNDEFINED? > } > ``` You need to return your callback in your `filereader` function: ``` return callback(this.jsonData); ```

rahulpathak commented

2019-07-15 16:17:37 +00:00

(Migrated from github.com)

ngOnInit() {
        // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx'];

        const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{
            // Here i am getting datalog data but this data not retunning any value
            console.log(JSON.stringify(datalog));
            return datalog;
        }});
        
        // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{
        //     console.log(JSON.stringify(datalog1));
        //     return datalog1;
        // }});

        console.log(urldata); <-- DO YOU MEAN THIS IS UNDEFINED?
    }

You need to return your callback in your filereader function:

return callback(this.jsonData);

after return callback() still getting 'undefined'

> > ``` > > ngOnInit() { > > // this.url = ['assets/sample-dataset.xlsx', 'assets/sheet2.xlsx']; > > > > const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ > > // Here i am getting datalog data but this data not retunning any value > > console.log(JSON.stringify(datalog)); > > return datalog; > > }}); > > > > // const urldata1 = this.filereader('assets/sheet2.xlsx', (datalog1) => {{ > > // console.log(JSON.stringify(datalog1)); > > // return datalog1; > > // }}); > > > > console.log(urldata); <-- DO YOU MEAN THIS IS UNDEFINED? > > } > > ``` > > You need to return your callback in your `filereader` function: > > ``` > return callback(this.jsonData); > ``` after return callback() still getting 'undefined'

GreggOD commented

2019-07-16 07:44:46 +00:00

(Migrated from github.com)

@rahulpathak
Does the datalog in here have data?

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{
            console.log(JSON.stringify(datalog)); <-- does this print out data?
            return datalog;
        }});

@rahulpathak Does the `datalog` in here have data? ``` const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ console.log(JSON.stringify(datalog)); <-- does this print out data? return datalog; }}); ```

rahulpathak commented

2019-07-16 07:56:54 +00:00

(Migrated from github.com)

@rahulpathak
Does the datalog in here have data?

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{
            console.log(JSON.stringify(datalog)); <-- yes datalog having data. 
            return datalog;
        }});
console.log(urldata) <-- 'undefined' data

But 'urldata' variable doesn't have any value. getting 'undefined' value. I guess filereader function not returning any value. I check with setTimeout() also but no still same output. Is there any logic need to change ?

> @rahulpathak > Does the `datalog` in here have data? > > ``` > const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ > console.log(JSON.stringify(datalog)); <-- yes datalog having data. > return datalog; > }}); > console.log(urldata) <-- 'undefined' data > ``` But 'urldata' variable doesn't have any value. getting 'undefined' value. I guess filereader function not returning any value. I check with setTimeout() also but no still same output. Is there any logic need to change ?

GreggOD commented

2019-07-18 14:48:27 +00:00

(Migrated from github.com)

Im a little confused why you have double brackets on your function

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ <-- remove one bracket
            console.log(JSON.stringify(datalog)); //<-- yes datalog having data. 
            return datalog;
        }} //<-- remove one bracket);

Shorthand would actually look like this

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => datalog);

Im a little confused why you have double brackets on your function ```javascript const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ <-- remove one bracket console.log(JSON.stringify(datalog)); //<-- yes datalog having data. return datalog; }} //<-- remove one bracket); ``` Shorthand would actually look like this ```javascript const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => datalog); ```

rahulpathak commented

2019-07-18 16:20:41 +00:00

(Migrated from github.com)

Im a little confused why you have double brackets on your function

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ <-- remove one bracket
            console.log(JSON.stringify(datalog)); //<-- yes datalog having data. 
            return datalog;
        }} //<-- remove one bracket);

Shorthand would actually look like this

const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => datalog);

Yes i have done changes. But still not working.
I have attached sample excel sheet and use above code.

sheet2.xlsx;

> Im a little confused why you have double brackets on your function > > ```js > const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => {{ <-- remove one bracket > console.log(JSON.stringify(datalog)); //<-- yes datalog having data. > return datalog; > }} //<-- remove one bracket); > ``` > > Shorthand would actually look like this > > ```js > const urldata = this.filereader('assets/sample-dataset.xlsx', (datalog) => datalog); > ``` Yes i have done changes. But still not working. I have attached sample excel sheet and use above code. [sheet2.xlsx](https://github.com/SheetJS/js-xlsx/files/3407439/sheet2.xlsx);

Sign in to join this conversation.