Looping through rows and columns #270

New Issue

2015-07-06T08:55:15Z

mazing commented

2015-07-06 08:55:15 +00:00

(Migrated from github.com)

How do I loop through rows and columns without converting the worksheet to csv or json before doing so?

Is it not possible to do something like

var worksheet = workbook.Sheets['Sheet1'];

for (var row in worksheet) {
  for (var col in row) {
    console.log(worksheet[row][col]);
  }
}

How do I loop through rows and columns without converting the worksheet to csv or json before doing so? Is it not possible to do something like ``` var worksheet = workbook.Sheets['Sheet1']; for (var row in worksheet) { for (var col in row) { console.log(worksheet[row][col]); } } ```

Mithgol commented

2015-10-07 13:14:13 +00:00

(Migrated from github.com)

Convert to an array:

var sheet2arr = function(sheet){
   var result = [];
   var row;
   var rowNum;
   var colNum;
   for(rowNum = sheet['!range'].s.r; rowNum <= sheet['!range'].e.r; rowNum++){
      row = [];
       for(colNum=sheet['!range'].s.c; colNum<=sheet['!range'].e.c; colNum++){
          var nextCell = sheet[
             xlsx.utils.encode_cell({r: rowNum, c: colNum})
          ];
          if( typeof nextCell === 'undefined' ){
             row.push(void 0);
          } else row.push(nextCell.w);
       }
       result.push(row);
   }
   return result;
};

Then loop through that array.

Convert to an array: ``` js var sheet2arr = function(sheet){ var result = []; var row; var rowNum; var colNum; for(rowNum = sheet['!range'].s.r; rowNum <= sheet['!range'].e.r; rowNum++){ row = []; for(colNum=sheet['!range'].s.c; colNum<=sheet['!range'].e.c; colNum++){ var nextCell = sheet[ xlsx.utils.encode_cell({r: rowNum, c: colNum}) ]; if( typeof nextCell === 'undefined' ){ row.push(void 0); } else row.push(nextCell.w); } result.push(row); } return result; }; ``` Then loop through that array.

👍 3 ❤️ 2

gregpinero commented

2017-03-03 15:09:15 +00:00

(Migrated from github.com)

@Mithgol, I seem to not have sheet['!range'] in xlsx files. Is there a new way to get the ranges? (This function works fine for xls files)

👍 2

reviewher commented

2017-03-03 15:58:27 +00:00

(Migrated from github.com)

@gregpinero @Mithgol the !ref key is exposed by every format. !range was used in XLS but it was ultimately confusing to store the same information in 2 different ways. Decoding using decode_range gives you the range object. So @Mithgol 's code sample would look like

var sheet2arr = function(sheet){
   var result = [];
   var row;
   var rowNum;
   var colNum;
   var range = XLSX.utils.decode_range(sheet['!ref']);
   for(rowNum = range.s.r; rowNum <= range.e.r; rowNum++){
      row = [];
       for(colNum=range.s.c; colNum<=range.e.c; colNum++){
          var nextCell = sheet[
             XLSX.utils.encode_cell({r: rowNum, c: colNum})
          ];
          if( typeof nextCell === 'undefined' ){
             row.push(void 0);
          } else row.push(nextCell.w);
       }
       result.push(row);
   }
   return result;
};

Is it worth adding an "iterator" to the utils? Basically it would take a worksheet and a callback function and repeatedly call it for every cell in the workbook, with an option to include or omit empty cells

@gregpinero @Mithgol the `!ref` key is exposed by every format. `!range` was used in XLS but it was ultimately confusing to store the same information in 2 different ways. Decoding using `decode_range` gives you the range object. So @Mithgol 's code sample would look like ```js var sheet2arr = function(sheet){ var result = []; var row; var rowNum; var colNum; var range = XLSX.utils.decode_range(sheet['!ref']); for(rowNum = range.s.r; rowNum <= range.e.r; rowNum++){ row = []; for(colNum=range.s.c; colNum<=range.e.c; colNum++){ var nextCell = sheet[ XLSX.utils.encode_cell({r: rowNum, c: colNum}) ]; if( typeof nextCell === 'undefined' ){ row.push(void 0); } else row.push(nextCell.w); } result.push(row); } return result; }; ``` Is it worth adding an "iterator" to the utils? Basically it would take a worksheet and a callback function and repeatedly call it for every cell in the workbook, with an option to include or omit empty cells

👍 29 ❤️ 3

gregpinero commented

2017-03-03 16:19:54 +00:00

(Migrated from github.com)

Thanks. That does work.

I would like something just like sheet2arr above in the utils. That what I thought XLSX.utils.sheet_to_row_object_array would do but it seemed to give me strange results missing most of the data.

Thanks. That does work. I would like something just like sheet2arr above in the utils. That what I thought XLSX.utils.sheet_to_row_object_array would do but it seemed to give me strange results missing most of the data.

👍 14 👎 3 😕 3 ❤️ 6 🎉 4 😆 4

reviewher commented

2017-03-03 16:41:15 +00:00

(Migrated from github.com)

@gregpinero the JSON conversion has a little bit of "intelligence" and is poorly documented :/

For example consider this worksheet:

|foo|bar|baz|
| 1 | 2 | 3 |
| 4 | 5 | 6 |

Let's say we are in node (the relevant parts work the same way in browser). Read the workbook:

var XLSX = require('xlsx');
var wb = XLSX.readFile('Workbook4.xlsx');
var ws = wb.Sheets.Sheet1;

So by default, the conversion will read the first row and use them as keys in the row object:

> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1)
[ { foo: '1', bar: '2', baz: '3' },
  { foo: '4', bar: '5', baz: '6' } ]

If a field is missing, the data won't show up. If there's data in a cell with no header (say, data in cell D2 but not D1), data won't show up.

passing an options argument with header controls the output:

Setting header to 1 will generate arrays:

> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:1})
[ [ 'foo', 'bar', 'baz' ], [ '1', '2', '3' ], [ '4', '5', '6' ] ]

Setting header to A will use the column names:

> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:'A'})
[ { A: 'foo', B: 'bar', C: 'baz' },
  { A: '1', B: '2', C: '3' },
  { A: '4', B: '5', C: '6' } ]

You can even give custom labels by passing an array:

> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:["what", "the", "flip"]})
[ { what: 'foo', the: 'bar', flip: 'baz' },
  { what: '1', the: '2', flip: '3' },
  { what: '4', the: '5', flip: '6' } ]

The easiest way to control the range is to change the !ref value on the sheet. It's expected to be an A1-style range like "A1:C4". Changing the value will change the range that the utils look at:

> wb.Sheets.Sheet1['!ref']
'A1:C3'
> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1)
[ { foo: '1', bar: '2', baz: '3' },
  { foo: '4', bar: '5', baz: '6' } ]

// now change to A1:A3
> wb.Sheets.Sheet1['!ref'] = 'A1:A3';
'A1:A3'
// the output will change
> XLSX.utils.sheet_to_json(wb.Sheets.Sheet1)
[ { foo: '1' }, { foo: '4' } ]

The same parameter also affects writing workbooks -- cells outside of that range are not included:

> wb.Sheets.Sheet1['!ref'] = 'A1:C3';
'A1:C3'
> XLSX.read(XLSX.write(wb, {type:'binary'}), {type:'binary'}).Sheets.Sheet1
{ '!ref': 'A1:C3',
  A1: { t: 's', v: 'foo', h: 'foo', w: 'foo' },
  B1: { t: 's', v: 'bar', h: 'bar', w: 'bar' },
  C1: { t: 's', v: 'baz', h: 'baz', w: 'baz' },
  A2: { t: 'n', v: 1, w: '1' },
  B2: { t: 'n', v: 2, w: '2' },
  C2: { t: 'n', v: 3, w: '3' },
  A3: { t: 'n', v: 4, w: '4' },
  B3: { t: 'n', v: 5, w: '5' },
  C3: { t: 'n', v: 6, w: '6' } }
> wb.Sheets.Sheet1['!ref'] = 'A1:A3';
'A1:A3'
> XLSX.read(XLSX.write(wb, {type:'binary'}), {type:'binary'}).Sheets.Sheet1
{ '!ref': 'A1:A3',
  A1: { t: 's', v: 'foo', h: 'foo', w: 'foo' },
  A2: { t: 'n', v: 1, w: '1' },
  A3: { t: 'n', v: 4, w: '4' } }

@gregpinero the JSON conversion has a little bit of "intelligence" and is poorly documented :/ For example consider this worksheet: ``` |foo|bar|baz| | 1 | 2 | 3 | | 4 | 5 | 6 | ``` Let's say we are in node (the relevant parts work the same way in browser). Read the workbook: ```js var XLSX = require('xlsx'); var wb = XLSX.readFile('Workbook4.xlsx'); var ws = wb.Sheets.Sheet1; ``` So by default, the conversion will read the first row and use them as keys in the row object: ``` > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1) [ { foo: '1', bar: '2', baz: '3' }, { foo: '4', bar: '5', baz: '6' } ] ``` If a field is missing, the data won't show up. If there's data in a cell with no header (say, data in cell D2 but not D1), data won't show up. passing an options argument with `header` controls the output: Setting header to `1` will generate arrays: ``` > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:1}) [ [ 'foo', 'bar', 'baz' ], [ '1', '2', '3' ], [ '4', '5', '6' ] ] ``` Setting header to `A` will use the column names: ``` > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:'A'}) [ { A: 'foo', B: 'bar', C: 'baz' }, { A: '1', B: '2', C: '3' }, { A: '4', B: '5', C: '6' } ] ``` You can even give custom labels by passing an array: ``` > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1, {header:["what", "the", "flip"]}) [ { what: 'foo', the: 'bar', flip: 'baz' }, { what: '1', the: '2', flip: '3' }, { what: '4', the: '5', flip: '6' } ] ``` The easiest way to control the range is to change the `!ref` value on the sheet. It's expected to be an A1-style range like "A1:C4". Changing the value will change the range that the utils look at: ``` > wb.Sheets.Sheet1['!ref'] 'A1:C3' > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1) [ { foo: '1', bar: '2', baz: '3' }, { foo: '4', bar: '5', baz: '6' } ] // now change to A1:A3 > wb.Sheets.Sheet1['!ref'] = 'A1:A3'; 'A1:A3' // the output will change > XLSX.utils.sheet_to_json(wb.Sheets.Sheet1) [ { foo: '1' }, { foo: '4' } ] ``` The same parameter also affects writing workbooks -- cells outside of that range are not included: ``` > wb.Sheets.Sheet1['!ref'] = 'A1:C3'; 'A1:C3' > XLSX.read(XLSX.write(wb, {type:'binary'}), {type:'binary'}).Sheets.Sheet1 { '!ref': 'A1:C3', A1: { t: 's', v: 'foo', h: 'foo', w: 'foo' }, B1: { t: 's', v: 'bar', h: 'bar', w: 'bar' }, C1: { t: 's', v: 'baz', h: 'baz', w: 'baz' }, A2: { t: 'n', v: 1, w: '1' }, B2: { t: 'n', v: 2, w: '2' }, C2: { t: 'n', v: 3, w: '3' }, A3: { t: 'n', v: 4, w: '4' }, B3: { t: 'n', v: 5, w: '5' }, C3: { t: 'n', v: 6, w: '6' } } > wb.Sheets.Sheet1['!ref'] = 'A1:A3'; 'A1:A3' > XLSX.read(XLSX.write(wb, {type:'binary'}), {type:'binary'}).Sheets.Sheet1 { '!ref': 'A1:A3', A1: { t: 's', v: 'foo', h: 'foo', w: 'foo' }, A2: { t: 'n', v: 1, w: '1' }, A3: { t: 'n', v: 4, w: '4' } } ```

👍 44 ❤️ 16 🎉 8

gregpinero commented

2017-03-03 17:52:54 +00:00

(Migrated from github.com)

Thanks, that makes a lot more sense now. My case was weird because I have the data I want starting at row 5. But I guess I could have manually given it a header and ignore data before row 4. I'll give that a try new time.

reviewher commented

2017-03-25 16:26:56 +00:00

(Migrated from github.com)

@gregpinero @mazing @Mithgol we improved the README a bit: https://github.com/SheetJS/js-xlsx#json -- can you take a look when you get a chance?

reviewher commented

2017-04-01 05:19:48 +00:00

(Migrated from github.com)

Closing for now, please follow up with some feedback on the relevant documentation if you get a chance :D

Anujmoglix commented

2018-04-12 07:44:01 +00:00

(Migrated from github.com)

HI My requirement is like this ,how can i achieve this?

HI My requirement is like this ,how can i achieve this? ![selection_106](https://user-images.githubusercontent.com/26056096/38663051-5b042d02-3e53-11e8-9a45-3c0a8dd5abda.png)

👍 2 👎 1

sunilbtech9 commented

2018-05-11 06:07:57 +00:00

(Migrated from github.com)

I dnt know

🎉 8 😆 23

rohitmore1995 commented

2018-12-20 06:47:48 +00:00

(Migrated from github.com)

Hello...I have one query ..actually I want to display excel file in select box in html and I'm done with this but after that,when we select option from select box that time i wan to display other information from that excel file..how can I do this...

gitBrij commented

2019-02-19 10:41:10 +00:00

(Migrated from github.com)

HI My requirement is like this ,how can i achieve this?

I know I'm late but it might help other having same requirement.
You can do that by specifying range attribute like this

var sheetName = workbook.SheetNames[0]; var d = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], { range: 5 });

And it will return data for the table in d after row 5.
Thanks

> HI My requirement is like this ,how can i achieve this? > ![selection_106](https://user-images.githubusercontent.com/26056096/38663051-5b042d02-3e53-11e8-9a45-3c0a8dd5abda.png) I know I'm late but it might help other having same requirement. You can do that by specifying range attribute like this `var sheetName = workbook.SheetNames[0]; var d = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], { range: 5 });` And it will return data for the table in d after row 5. Thanks

👍 1

DivyaArun1985 commented

2019-04-09 10:43:03 +00:00

(Migrated from github.com)

Hi,

I have exported json to excel using XLSX.utils.sheet_to_json and the output looks like below. I would like to display 'Total' and its contents as the last column. How can I achieve that? Please note that I cannot order all the columns as some columns are dynamic and varies from one situation to another. For the common ones, I have added header order. My requirement is to display the below headers first, then the dynamic ones followed by total. Total should always be in the end.

const headerInfo = ['Event Type', 'Start Date', 'End Date', 'Duration Name'];
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json, { header: headerInfo });

Hi, I have exported json to excel using XLSX.utils.sheet_to_json and the output looks like below. I would like to display 'Total' and its contents as the last column. How can I achieve that? Please note that I cannot order all the columns as some columns are dynamic and varies from one situation to another. For the common ones, I have added header order. My requirement is to display the below headers first, then the dynamic ones followed by total. Total should always be in the end. const headerInfo = ['Event Type', 'Start Date', 'End Date', 'Duration Name']; const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json, { header: headerInfo }); ![image](https://user-images.githubusercontent.com/49437516/55794293-80b58800-5ae2-11e9-9771-1a44eb004acf.png)

gitBrij commented

2019-04-09 13:06:33 +00:00

(Migrated from github.com)

Try this code, read the properties of first object in json array and create add it into headerInfo array and lastly add Total.

var headerInfo = ['Event Type', 'Start Date', 'End Date', 'Duration Name']; for (var key in json[0]) { if (headerInfo.indexOf(key) == -1 && key !='Total') headerInfo.push(key); } headerInfo.push('Total'); const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json, { header: headerInfo });

Try this code, read the properties of first object in json array and create add it into headerInfo array and lastly add Total. `var headerInfo = ['Event Type', 'Start Date', 'End Date', 'Duration Name']; for (var key in json[0]) { if (headerInfo.indexOf(key) == -1 && key !='Total') headerInfo.push(key); } headerInfo.push('Total'); const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json, { header: headerInfo });`

Sign in to join this conversation.