nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Better Excel Scripts

medini_1024169437

Importing from or exporting to Excel are important topics in this board and there are a number of scripts out there how to accomplish that. This article here is more about the question "how to make a script better", how to add the remaining 10% of convenience such that the end user accepts it more.

A repeated question especially when importing huge Excel sheets is progress reporting, some imports, especially those who create model elements may take a few minutes so we should think about the user who is waiting in front of the tool now knowing when this import will end, in a second? A few seonds? Minutes? So here are some basic recipes how to improve a script. Note: There are other articles about progress reporting in general and they also mention a few limitations that crep in recently. Also remember that the script is executed in a transaction, so at the end, all changes are comotted to the project which may take a few seconds as well and at that time the script has finished already!

First script: basic Excel importer which does everything (iterating over workbook, sheet and rows:

/** $EXPERIMENTAL$
 *
 * This script is meant to illustrate how to write better Excel scripts. 
 */

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var document = ExcelDocument.open(file, true);
var wb = document.workbook;
var sheets = wb.worksheets;

// (1) Show nice progress when importing huge excel sheets
var ws = sheets[1];
console.log("Sheet {0} with dim {1} and {2} rows", ws.name, ws.dimension, ws.rows.length);

var rows = ws.rows;
progressMonitor.beginTask("Loading Excel rows...", rows.length);
for (var i=0; i<rows.length; i++) {
	var row = rows[i];
	console.log("Handle row {0}...", row.rowIndex);
	java.lang.Thread.sleep(10); // wait 100ms to simulate work
	progressMonitor.subTask("Loaded row " + i + " of " + rows.length);
	// this should normally move the progress bar but since 2025 R2 there is an issue, see https://medini.freshdesk.com/support/discussions/topics/1000121455
	progressMonitor.worked(1);
}

progressMonitor.done();
document.close();

progressMonitor.subTask("Committing changes (this may take a while)..."); 
java.lang.Thread.sleep(2000); // wait 2s to simulate transaction commit

Second scrip: does the same but this time for "callback" based workflow (import):

/** $EXPERIMENTAL$
 *
 * This script is meant to illustrate how to write better Excel scripts. 
 */

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var callback = {

	numrows : 0,

	getFile() {
		return file;
	},

	getSheet : function(wb) {
		return wb.worksheets[1];
	},

	beforeHandle : function(sheet) {
		this.numrows = sheet.rows.length;
		progressMonitor.beginTask("Loading Excel rows...", this.numrows);
	},

	afterHandle : function(sheet) {
		progressMonitor.done();
	},

	handleRow : function(index, row) {
		console.log("Handle row {0}...", index);
		java.lang.Thread.sleep(10); // wait 100ms to simulate work
		progressMonitor.subTask("Loaded row " + index + " of " + this.numrows);
		// this should normally move the progress bar but since 2025 R2 there is an issue, see https://medini.freshdesk.com/support/discussions/topics/1000121455
		progressMonitor.worked(1);
	}
};

var importer = new ExcelImporter(callback);
if (!importer.run()) {
	console.log("Import was canceled");
	undefined;
} else {
	"Import completed";
}

Hope this helps to write better scripts!

Jan

Find more posts tagged with

medini

medini legacy

Accepted answers

All comments

medini_1038955747

Hello Jan,

Thank you for the nice article.

You also provided a script to detect errors directly in Excel. If we want to implement this script, it means we will need to run the test for each FMEA column (we have about 25 columns in the DFMEA).

Philippe requires that, when a script error occurs, the message must include three stored pieces of information: the line, the column, and the content.

Do we have a function similar to the one used for monitoring the progress of imports?

progressMonitor.subTask("line " + row + "
data " + containt +"Column " + column );

medini_1024169437

Hello Abdoul,

I am not sure I understood 100% but tried to combine "data or cell/content based" progress monitoring with some exception handling.

I personally think that such fine grained progress monitoring might be a bit too much as it too fast showing small details.

Anyway, here some snippet:

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var callback = {

 numrows : 0,

 numcolumns : 0,

 // callback
 getFile() {
 return file;
 },

 // callback
 getSheet : function(wb) {
 return wb.worksheets[1];
 },

 // callback
 beforeHandle : function(sheet) {
 this.numrows = sheet.rows.length;
 // this is not 100% accurate as we just take the last column but ignore the first column
 this.numcolumns = sheet.dimension.bottomRight.column;
 console.log("Handle sheet {0} {1} {2}", sheet.dimension, this.numrows, this.numcolumns);
 progressMonitor.beginTask("Loading Excel rows...", this.numrows);
 },

 // callback
 afterHandle : function(sheet) {
 progressMonitor.done();
 },

 // callback
 handleRow : function(rowIndex, row) {
 console.log("Handle row {0}...", rowIndex);
 for (var colIndex=1; colIndex < this.numcolumns; colIndex++) {
 try {
 var cell = row.getCell(colIndex); // this may return null!
 console.log(cell);
 this.handleCell(cell, row, rowIndex, colIndex);
 // this should normally move the progress bar but since 2025 R2 there is an issue, see https://medini.freshdesk.com/support/discussions/topics/1000121455
 progressMonitor.worked(1);
 } catch (e) {
 // TODO Show some nice error using row and column index as well as content
 throw e;
 }
 }
 },

 // implementation
 handleCell : function(cell, row, rowIndex, colIndex) {
 progressMonitor.subTask(this.niceProgressLabel(cell, row, rowIndex, colIndex));
 java.lang.Thread.sleep(10); // wait 100ms to simulate work
 },

 niceProgressLabel : function(cell, row, rowIndex, colIndex) {
 return "Loading data from row " + rowIndex + ", column " + colIndex + ", data=" + (cell ? cell.getInterpretedValue() : "<empty>");
 }
};

var importer = new ExcelImporter(callback);
if (!importer.run()) {
 console.log("Import was canceled");
 undefined;
} else {
 "Import completed";
}

Happy Scripting

Jan

medini_1057179964

Hello Jan

The need is not to have in a progressmonitor but more when we have an error message to complete this one with line, column and data that generate the error

to have the equivalent control of progressmonitor to complete the error message

thanks for your help

Philippe

medini_1024169437

Understood now. I updated the script a bit and improved cod ein line 45 which is catching any error and re-throwing it. I also added some error simulation to test it. Note that I already identified a wekaness with this approach: the line number that typically tells you in which line the script failed now shows the line of teh error handling code and not anymore the original problematic line, anyway, maybe its still what you are looking for:

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var callback = {

	numrows : 0,

	numcolumns : 0,

	// callback
	getFile() {
		return file;
	},

	// callback
	getSheet : function(wb) {
		return wb.worksheets[1];
	},

	// callback
	beforeHandle : function(sheet) {
		this.numrows = sheet.rows.length;
		// this is not 100% accurate as we just take the last column but ignore the first column
		this.numcolumns = sheet.dimension.bottomRight.column;
		console.log("Handle sheet {0} {1} {2}", sheet.dimension, this.numrows, this.numcolumns);
		progressMonitor.beginTask("Loading Excel rows...", this.numrows);
	},

	// callback
	afterHandle : function(sheet) {
		progressMonitor.done();
	},

	// callback
	handleRow : function(rowIndex, row) {
		console.log("Handle row {0}...", rowIndex);
		for (var colIndex=1; colIndex < this.numcolumns; colIndex++) {
			try {
				var cell = row.getCell(colIndex); // this may return null!
				console.log(cell);
				this.handleCell(cell, row, rowIndex, colIndex);
				// this should normally move the progress bar but since 2025 R2 there is an issue, see https://medini.freshdesk.com/support/discussions/topics/1000121455
				progressMonitor.worked(1);
			} catch (e) {
				// TODO Show some nice error using row and column index as well as content
				throw e + "\n\nError happened while checking row " + rowIndex + ", column " + colIndex + ", data=" + (cell ? cell.getInterpretedValue() : "<empty>");
			}
		}
	},

	// implementation
	handleCell : function(cell, row, rowIndex, colIndex) {
		progressMonitor.subTask(this.niceProgressLabel(cell, row, rowIndex, colIndex));
		java.lang.Thread.sleep(10); // wait 100ms to simulate work

		if (rowIndex == 5 && colIndex == 5) {
			var value = cell.getStringValue(); // <== simulate an error
		} 
	},

	niceProgressLabel : function(cell, row, rowIndex, colIndex) {
		return "Loading data from row " + rowIndex + ", column " + colIndex + ", data=" + (cell ? cell.getInterpretedValue() : "<empty>");
	}
};

var importer = new ExcelImporter(callback);
if (!importer.run()) {
	console.log("Import was canceled");
	undefined;
} else {
	"Import completed";
}

Thats gives me:

Happy Scripting

Jan

medini_1057179964

Hello Jan

thanks it works

I have put this lines at the end of my scripts, and the try in my normal loop.

Periodically i memorize rowIndex, colIndex and the contain of the cell

Best regards

Philippe

medini_1024169437

Glad it works Philippe!

Jan

medini_1024169437

One more update on this subject: in my previous "better scripts", I baile dout as soon as an error was found, means the script stopped working and any change done so far is rolled back. While this is true for critical errors, it might not be for less critical or just warnings. So the idea would be to inform th user about the problem but continue working. Here is an example which basically just replaces the "throw" statement by an alert:

/** $EXPERIMENTAL$
 *
 * This script is meant to illustrate how to write better Excel scripts. 
 */

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var document = ExcelDocument.open(file, true);
var wb = document.workbook;
var sheets = wb.worksheets;

var ws = sheets[0];
console.log("Sheet {0} with dim {1} and {2} rows", ws.name, ws.dimension, ws.rows.length);

var rows = ws.rows;
for (var i=0; i<rows.length; i++) {
	var row = rows[i];
	console.log("Handle row {0}...", row.rowIndex);
	var cellA = row.getCell(1);

	// now get a string value using a helper function that checks the type
	var value = getStringWithCheck(cellA, row, i);
}

document.close();

function getStringWithCheck(cell, row, index) {
	// check the cell type first
	if (!cell) {
		// _A is defined in excel.js!
		throw "The cell at " + _A(row, index) + " is undefined but shall not";
	}

	// check the cell type first
	if (cell.type == Cell.Type.STRING) {
		return cell.getStringValue();
	}
	if (cell.type == Cell.Type.SHARED_STRING) {
		return cell.getSharedStringValue();
	}

	// Show nice error when stumbling over bad content
	// throw "The cell at " + _A(row, index) + " is not a string but shall be, please correct";
	alert("The cell at " + _A(row, index) + " is not a string but shall be, please correct. The import will continue though.");
}

So while this was an easy fix, it still has some flaws. In case there are several issues, the user would click them away and in the end forgotten what and where to fix. An optino might be to colelct all issues over time and show them together in the end instead.

/** $EXPERIMENTAL$
 *
 * This script is meant to illustrate how to write better Excel scripts. 
 */

load(".lib/excel.js");
var file = new java.io.File(__filedir + "/.data/sample.xlsx");
var document = ExcelDocument.open(file, true);
var wb = document.workbook;
var sheets = wb.worksheets;
var problems = [];

var ws = sheets[0];
console.log("Sheet {0} with dim {1} and {2} rows", ws.name, ws.dimension, ws.rows.length);

var rows = ws.rows;
for (var i=0; i<rows.length; i++) {
	var row = rows[i];
	console.log("Handle row {0}...", row.rowIndex);
	var cellA = row.getCell(1);

	// now get a string value using a helper function that checks the type
	var value = getStringWithCheck(cellA, row, i);
}

document.close();

function getStringWithCheck(cell, row, index) {
	// check the cell type first
	if (!cell) {
		// _A is defined in excel.js!
		throw "The cell at " + _A(row, index) + " is undefined but shall not";
	}

	// check the cell type first
	if (cell.type == Cell.Type.STRING) {
		return cell.getStringValue();
	}
	if (cell.type == Cell.Type.SHARED_STRING) {
		return cell.getSharedStringValue();
	}

	// Show nice error when stumbling over bad content
	// throw "The cell at " + _A(row, index) + " is not a string but shall be, please correct";
	var problem = "The cell at " + _A(row, index) + " is not a string but shall be, please correct."; 
	problems.push(problem);
	// for sake of exercise we add the same problem multiple times
	problems.push(problem);
	problems.push(problem);
}

if (problems.length) {
	alert("There have been " + problems.length + " problems during import which you may fix and try again:\n\n"
		+ problems.map(m => "- " + m).join("\n")); 
}

Happy Scripting

Jan