GlideExcelParser - merge column headers + changing header row number

[TomášD]

Hello,

I have some troubles figuring out this challenge:
I am supposed to parse an excel file into the json format. Using GlideExcelParser, I encountered these issues:

a) Column headers in the excel file are not situated in row 1, but row 3. I tried to use:

parser.setHeaderRowNumber(2);
var headers = parser.getColumnHeaders();

which doesn't seem to work (not sure if the row numbering starts from 0 or 1 - tried both). No matter what, it always processes the first row as a header line.

b) In the row 2, there are some merged headers, which like group several columns to logical units. How do I retrieve names of these merged columns to be able to add them to the json structure?

c) Because of the merged columns, some names in the header row 3 are duplicated, because they are distinct by the merge column to which they belong. So, the question is, can I access a value in the row object by its position instead of its header name? This doesn't work:

var rowValues = {};
var rowNumber = 0;
while (parser.next()) {
rowNumber++;
var row = parser.getRow();
var values = [];
for (var i=0; i<headers.length; i++) {
values[i] = row[i];
}
var rowName = 'row_' + rowNumber;
rowValues[rowName] = values;
}

 Thanks for all sugestions!

[TomášD]

Update:
I did some research myself and I figured out few things:

add a) The trick is to use setHeaderRowNumber() BEFORE you parse, so:

parser.setHeaderRowNumber(2); // headers are in the third row 
parser.parse(dataStream);
var headers = parser.getColumnHeaders();

Rows are counted from 0 btw.
 
add b) This is still unsolved challenge, since the parser recognizes the header name only in the first column of the merge columns, it claims the rest to be empty.

add c) You can process row object keys using:

...
var columnCount = 0;
var values = [];
var row = parser.getRow();
for (var key in row) {
values[columnCount] = row[key];
columnCount++;
}
...

so you can easily track column numbers that way.