Using EXCEL API as to iterate over the lines of an EXCEL File

Question

Using the provided EXCEL API is very convenient as it avoids handcoding of file opening etc.

However, what I find inconvenient (perhaps some other people, too) is the provided mechanism with callbacks whenever a new line is consumed. For me a program is much easier to write and understand if the control flow is concentrated in one place (usually the script main function) so that I can see what the script does without jumping in the listing.

So I would really appreciate an alternative solution which provides a list or array over which I can iterate, in the style of:

// assumming that the file open dialog has already happened...

lines = myExcelFileHandle.readFile() // return value is an array of lines. This function could perhaps come with additional attributes, e.g. to read or skip the first line...

for(var i=0;i < lines.length;i++) { // also other style of iteration could be possible...

id=lines[i][ID_COLUMN];

asil=lines[i][ASIL_COLUMN]; // etc. (of course, assuming that the column names have been defined before)

}

Would something like this be possible?

medini_1024169437 · Answer

For convenience the essential part as inline code:

...

// 1. define the file - we have to use java.io.File here
var file = new java.io.File("C:\Temp\x.xlsx");

// 2. open the document - second argument means "read only"
var document = ExcelDocument.open(file, true);

// 3. get the workbook (an object)
var wb = document.workbook;

// 4. get all worksheets (an array)
var sheets = wb.worksheets;

// 5. grab the first (0 base of course)
var ws = sheets[0];
console.log("Sheet {0} with dim {1} and {2} rows", ws.name, ws.dimension, ws.rows.length);

// 6. iterate over all rows
var rows = ws.rows;
rows.forEach(function (row) {
	console.log("Handle row {0}...", row.rowIndex);

// 7. get a few cells (might be null!)
	for (var i = 0; i < 3; i++) {
		var cell = row.getCell(i);
		if (!cell) {
			console.log("No cell at index row {0}", i);
		} else {
			console.log("Cell {0}: type={1}, interpreted value={2}", cell.address, cell.type, cell.interpretedValue);
		}
	}
});

// 7. Close it, otherwise the excel is blocked
document.close();

medini_1024169437 · Answer

Hello Bernhard,

importing he "excel.js" library gives you both options, you can use the simplified callback like interface, that basically requires an object as input with various hooks to configure the import.

But you can also use the internally used Excel Java API directly. I attached a simple example to show how it works. Please use/extend on your own. Important to mention is that Excel sheets may have "holes", meaning cells may not exist in certain locations, even if there are other cells below or on their right side. So you need to take care of that yourself.

Happy Scripting

Jan