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

New Issue

Closed

opened 2017-03-08 21:29:18 +00:00 by reidblomquist · 29 comments

reidblomquist commented 2017-03-08 21:29:18 +00:00 (Migrated from github.com)

Copy Link

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)

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)

Copy Link

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

Copy Link

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

Copy Link

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

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

Copy Link

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

Copy Link

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)

Copy Link

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

Copy Link

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)

Copy Link

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)

Copy Link

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)

Copy Link

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

Copy Link

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)

Copy Link

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

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)

Copy Link

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

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

Copy Link

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)

Copy Link

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

Copy Link

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

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

Copy Link

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

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)

Copy Link

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

Copy Link

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

Copy Link

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

Copy Link

@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.  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)

Copy Link

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)

Copy Link

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)

Copy Link

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)

Copy Link

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)

Copy Link

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

Copy Link

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

Copy Link

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)

Copy Link

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.

No Branch/Tag Specified

Branches Tags

master

sankhavaramsaitulasiram/feat-fix-2752

maybeswapnil/issue2737

gh-pages

scottysseus/2560_2

ivan-trusov/fix-basedate

nandanv2702/issue_1300

protobi/master

ThomasChan/master

grantfayvor/master

tom-groves/bug-1105/rounding-error

mgreter/master

v0.20.3

v0.20.2

v0.20.1

v0.20.0

v0.19.3

v0.19.2

v0.19.1

v0.19.0

v0.18.12

v0.18.11

v0.18.10

v0.18.9

v0.18.8

v0.18.7

v0.18.5

v0.18.4

v0.18.3

v0.18.2

v0.18.1

v0.18.0

v0.17.5

v0.17.4

v0.17.3

v0.17.2

v0.17.1

v0.17.0

v0.16.8

v0.16.7

v0.16.6

v0.16.5

v0.16.3

v0.16.2

v0.16.1

v0.16.0

v0.15.6

v0.15.5

v0.15.2

v0.14.0

v0.13.5

v0.13.4

v0.13.3

v0.13.1

v0.13.0

v0.12.13

v0.12.12

v0.12.11

v0.12.10

v0.12.9

v0.12.8

v0.12.7

v0.12.6

v0.12.5

v0.12.4

v0.12.3

v0.12.2

v0.12.1

v0.12.0

v0.11.19

v0.11.18

v0.11.17

v0.11.16

v0.11.15

v0.11.14

v0.11.13

v0.11.12

v0.11.11

v0.11.10

v0.11.9

v0.11.8

v0.11.7

v0.11.6

v0.11.5

v0.11.4

v0.11.3

v0.11.2

v0.11.1

v0.11.0

v0.10.9

v0.10.8

v0.10.7

v0.10.6

v0.10.5

v0.10.4

v0.10.3

v0.10.2

v0.10.1

v0.10.0

v0.9.13

v0.9.12

v0.9.11

v0.9.10

v0.9.9

v0.9.8

v0.9.7

v0.9.6

v0.9.5

v0.9.4

v0.9.3

v0.9.2

v0.9.1

v0.9.0

v0.8.8

v0.8.7

v0.8.6

v0.8.5

v0.8.4

v0.8.3

v0.8.2

v0.8.1

v0.8.0

v0.7.11

v0.7.10

v0.7.9

v0.7.7

v0.7.6-i

v0.7.6-h

v0.7.6-a

v0.7.6

v0.7.5

v0.7.4

v0.7.3

v0.7.2

v0.7.1

v0.5.10

v0.5.9

v0.5.8

v0.5.7

v0.5.0

v0.4.3

Labels

Clear labels   

DBF

  

Dates

  

Defined Names

  

Features

  

Formula

  

HTML

  

Images

  

Infrastructure

  

Integration

  

International

  

ODS

  

Operations

  

Performance

  

PivotTables

  

Pro

  

Protection

  

Read Bug

  

SSF

  

SYLK

  

Style

  

Write Bug

  

good first issue

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

Milestone

Clear milestone

No items

No Milestone

Assignees

Clear assignees

No Assignees

1 Participants

Notifications

Subscribe

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#586

No description provided.