Sheetjs: How do I access a row?

[gabzim]

I have:

var xls = require('xlsjs');
var workbook = xls.readFile('lp.xls');
var sheet_name_list = workbook.SheetNames;
var sheet = workbook.Sheets[sheet_name_list[0]];

I need a function that lets me iterate over each row, as in:

sheet.rows.forEach(function(row) {
   //do something with row.
})

Is this possible?

By the way, my file doesn't have a proper column with header format so I can't use the function in utils.

[notatestuser]

oh. I see. would indexed columns work? i.e.

[{
    0: 'r1c1',
    1: 'r1c2'
    2: 'r1c3'
}, ...]

[gabzim]

Well as long as I can do a per row iteration (instead of per column), I could manage the rest, I'm just not sure how to do it.

[notatestuser]

Try these

[SheetJSDev]

@notatestuser very nice :) would it be better to add an option to the existing function (opts.header = true reads the headers, opts.header = false uses columns)?

Also @arg20 I see the merit of an "array of arrays" approach, which is coming soon.

[SheetJSDev]

@arg20 how are you doing?

[notatestuser]

@SheetJSDev yes, absolutely. I'm sure you're aware there's not really any new logic here... mostly just edits to the loop's conditions. so it'd be nice to have the existing methods support this mode.

[SheetJSDev]

@notatestuser my initial thought is to add an option header to the options object, whose values would be:

• "A" would generate using the standard column headers
• 1 would generate an array of arrays
• passing an array would generate using those labels as column headers

it would also probably make sense to accept a range or rows parameter to specify the region (it's possible to do that now by manipulating the sheet !ref but that's not the best solution)

Any thoughts?

[notatestuser]

I think for my use case it would be ideal to be able to specify a row # to capture the headings from. So that would be like "A" but with an optional row index parameter.
3 would also be useful for doing this though - I'd just have to capture the headings before the rows are transformed to json objects.

[SheetJSDev]

@arg20 @notatestuser I played with it a bit and settled on the following options:

• opts.header = 1 generates an array of arrays
• opts.header = 'A' generates a structure using the column headers as keys
• opts.header = ['header1', 'header2', ...] will use the specified labels

(default behavior remains: use the first row)

As for controlling the row @notatestuser there is a range option:

• opts.range = n starts from row n (0-indexed), so in your case it would be range=2
• opts.range = range_as_string will use specified range (standard A1:B2 format)
• opts.range = range_object will use the specified range (standard format {s:{r:0,c:0},e:{r:1,c:1}})

(default is to use the entire sheet)

I also updated the XLS tests: https://github.com/SheetJS/js-xls/blob/2bd2d1eaf3cbcfe317dd7c9c03ca355fb2c22afd/test.js#L487-L586

[SheetJSDev]

@arg20 to answer your question:

var xls = require('xlsjs');
var workbook = xls.readFile('lp.xls');
var sheet_name_list = workbook.SheetNames;
var sheet = workbook.Sheets[sheet_name_list[0]];
var data = xls.utils.sheet_to_json(sheet, {header:1}); // <-- this is an array of arrays that you can use

Also, feel free to delete your older comment

[gabzim]

Awesome feature. I also love that you added the option to pass an array specifying the headers. Thanks a lot for this. I will try to make some progress in my script and hopefully I won't have problems anymore.

[shaibv]

@SheetJSDev Thank you, this is verry helpfull.