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; } } }

Easy import from Excel

medini_1024169437

Excel is one of the most used tools so its natural that scripting and Excel files would nicely go together. In this article I would like to outline a few options and provide an easy to use library for medini import scripts.

Option 1: use external libraries as Apache POI

As described in a separate article in the "Solution" titled "Using additional libraries", it is possible to load and use arbitrary Java libraries. That way it would be also possible to use the libraries of the Apache POI project which supports almost any Office file format out there since years, that includes XLS as well as XLSX. However, the POI project came a long way and the API is somewhat awkward to some extend and the developer has to get used to it. Furthermore that approach forces the script developer to mainly write Java code in Java Script which is not everyone's favorite.

Option 2: use the medini lightweight Excel API

Well, now the good news: medini has its own much simpler Excel API. That API has just a few classes as ExcelDocument, Worksheet, Row, Cell and a few more. Still the API is a Java API and just disclosing it 1:1 to Java Script would technically work but is maybe not the ideal way to do it. For that reason I would like to offer a third way.

Option 3: use a simple Java Script wrapper library

Similar to the articles about Factory, Trashbin and ASIL Comparison, we have developed a slim but powerful Excel library that (1) reduces the overhead to start a simple Excel import to just a few lines but (2) still offers a flexible API to cover most use cases, at least for Excel import and (3) sits atop the Java Excel API mentioned in option 2. The library comes as a single JS file named "excel.js" and can be used to write scripts that require user interaction or scripts that do not. In the first case a second library named "ui.js" is required which offers a few methods to interact with the user.

Lets directly dive into code and have a look at the following simple script:

// the callback object which we pass to the Excel API
var callback = {
	/*
	 * Called by the Excel for each processed row. The function may do
	 * arbitrary stuff here. This function is MANDATORY.
	 */
	handleRow : function(index, row) {
		console.log("Handle row {0}...", index);
	}
};

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

As you can see, the script defines a mandatory "callback" object. Depending on the use case, this callback object can define a set of functions to drive the excel import. The minimum is a function that is named "handleRow" which is called for each row in an Excel worksheet. Starting this script will result in

a dialog to ask the user for an input Excel
a dialog to ask the user for a sheet to pick (if there are multiple)
calling the "handleRow" function for each row found

As you see, the importer is limited to a single worksheet but for most use cases that should be sufficient.

There are now many more optional callback functions that can be provided to change the importer behavior which I do not explain in text but in two sample scripts that I will attach later. Just to mention the two groups of callbacks:

callbacks that drive the selection of file, worksheet and rows
callbacks that are called before or after certain steps in the import process, e.g. after file was opened, after worksheet was opened etc.

I have added documentation into the sample scripts attached to this article to explain everything. Hope that scripts are useful and I am happy to get your feedback.

There will be the following files attached:

excel.js - the ExcelImporter API
ui.js - required if importer is run in interactive mode (default)
Test Excel Headless - sample importer showing first group of API calls
Test Excel.js - sample importer showing second group of API calls

Happy Scripting

Jan

Find more posts tagged with

medini

medini legacy

Accepted answers

medini_1024169437

Marian,

I do not think this is possible with the Excel library we use. It is a rather lightweight lib that does not support formulas.

The code we use was later donated to POI and extended and improved there. I think you need to use the POI library to manipulate an Excel file to set formulas (e.g. Setting Formulas in Excel with Apache POI | Baeldung ).

POI should be available in the platform already though, I think even 2 versions of it as several bundles have dependencies (M2doc and BIRT).

Happy Scripting

Jan

All comments

medini_1028367136

Hi Jan,

I downloaded the "excel.js" library on August 18, and it is working as expected, allowing me to import any number of MS Excel files.

I created a function meant to "wrap" some of the functionality of the "excel.js" library.

The function "GM_JS_AMA_Import_MS_Excel_As_Array_Of_Arrays", takes in two arguments:

* "MS_Excel_Path"

* "MS_Excel_Sheet_Name"

As long as there is a valid MS Excel file in the path provided as the first argument, and as long as that MS Excel file contains a sheet matching the name provided as the second argument, then the function will return the "used" portion of the MS Excel spreadsheet as an array of string arrays.

The reason I am writing is that even though my function is working correctly, I had to make one change to the "excel.js" file to get my own function to work. I have posted a screenshot of the single change that I made to the "excel.js" file. Please advise as to whether the "excel.js" file should be changed, or what I should do to my function to get it to work with the library as provided.

Kind regards, thanks in advance!

medini_1024169437

Hello Roy,

thanks for posting your script. I think its quite valuable to other users.

The fact that you had to change the excel.js to get it working is strange. In the snippet:

 var importer = new ExcelImporter(callback);
 var importer_run = importer.run(callback);

you already pass the callback to the Constructor of "ExcelImporter" (which internally assigns it to a variable. I think the difference between my sample code (which works) and your code is that the "callback" variable is defined inside a function which reveals a problem in the excel.js file: it should access the internal callback using this.callback (not just callback).

I will fix this and post another version of the file here. With that one you should not require the additional parameter.May take a while though ...

Happy Scripting

Jan

medini_1028367136

Hi Jan, thanks for your help with this. I figured it was a scoping issue but wanted you to take a look just to make sure. No rush on a fix, it's not hurting anything. I have several functions utilizing what's implemented today and there are no issues, the "excel.js" file is good.

I would be very interested in being able to use "excel.js" to write simple excel files, not sure if that's in your roadmap or not.

Thanks again!

medini_1024169437

Hi Roy,

I fixed the scoping issue in the latest version of the Excel library. Please find it attached. You should be able to use importer.run() now without any argument. There are some new methods in there as well, maybe not of use for you but for others (mainly methods to search columns, header rows etc.

There is already support to also change Excel files. On additional callback is there, named "readOnly()". if you implement it in your callback and return false, the Excel will be opened in write mode and you may change the content.

Happy Scripting

Jan

medini_1028367136

Thanks again for this, this library is immensely useful to me.

medini_1024169437

And yet another update for this library. This version fixes an issue in "collectRows". It is (and was) possible to pass an optional function here to tell the library when to stop (collecting rows). However, empty (non existing) rows were always ignored by default and the function never had the chance to stop there. So especially use cases where there is a typical table of values, then one or more empty rows followed by a number of extra rows with calculated values were affected.

If you switch to this new version, which fixes this glitch, and you have used a custom function to stop collecting rows, make sure your function can handle "undefined" rows.

In addition to that, there is now a new function to cleanup a worksheet. That is only required in export cases though.

Happy Scripting

Jan

medini_1040753747

Jan,

Is there a helper function within excel.js that finds the max number of columns in the excel worksheet? Perhaps something like:

return ws.getDimension().getBottomRight().column

I couldn't seem to find anything... thx for your support with regards to this!

Johan

medini_1024169437

Hi Johan,

there is no such helper yet but the code you proposed is exactly right, so either:

var maxRow = ws.getDimension().getBottomRight().getRow();
var maxColumn = ws.getDimension().getBottomRight().getColumn();

or simply:

var maxRow = ws.getDimension().getBottomRight().row;
var maxColumn = ws.getDimension().getBottomRight().column;

The scripting engine is able to determine the attribute "row" from the object's getter.

Have you tried that?

Jan

medini_1040753747

Jan,

I am building off of Roy's import wrapper and trying to add an export function as well. I have no issues running the import/export examples you have provided... but when I try to replicate the functionality with an array of arrays in a wrapper, I am running into errors.

1. I cannot seem to get the latest version of excel.js to work correctly; I am using the modified first version of the excel.js by Roy to get the import to run. Otherwise, I get the attached this.callback["handleRow"].jpg error if I use the latest excel.js.

2. Still using Roy's version of excel.js, I import the data from Book2.xlsx into array of arrays fine, but then get the cell.setSharedStringValue(value).jpg when I try to write my imported array of arrays to excel.

Are you able to help me determine what the issue is? I would like to use the latest version of excel.js and ui.js for this wrapper. But maybe the first step is getting the set() function to write to excel with the wrapper.

Thx for your support with regards to this!

Johan

medini_1024169437

Hi Johan,

I am a little confused :-) Maybe there are multiple problems?

So first I may need "Roy's version of the excl.js" to understand what he has changed.

Is that possible to post it here?

Regards Jan

medini_1040753747

1000 apologies, I thought it was attached in Roy's initial response. I believe it is similar to your first version of excel.js with a couple minor changes. Let me know if you need further information!

medini_1024169437

Hi Johan,

I compared the two versions with the following result:

Roy's version is just an older version of the file, its not changed I think
The major difference is the handling of hidden and empty rows and there are a number of bugs in the old version which were fixed in the latest
The first error you get I do not fully understand and its difficult to see which excel file was used, the screenshot indicates 20180818 but that#s the old one, right?
The second error indicates a problem that was fixed a while back (actually v2018-09-12) so you are not using the latest version

I suggest to first try the latest version then come back here and discuss the remaining issues. Sounds like a plan?

Jan

medini_1040753747

Jan,

I agree that I should try to use the latest excel.js - I updated the wrapper to use the old excel.js for import and new excel.js export and that was successful! So the second error is resolved :-)

For the first error, I keep getting the this.callback["handleRow"].jpg error if I use the latest excel.js for import... which references this code from excel.js:

if (this.callback["handleRow"]) {

this.callback["handleRow"].call(this.callback, row.index, row.handle);

}

I can run your "Test Excel.js" just fine with the latest excel.js, but even when I put that exact same code in my import wrapper I get the same error as before. I hope that is clear... Let me know if you need any further explanation of my scenario. Thx!

Johan

medini_1024169437

Johan, can you paste the error again, so I can see the line number? Any maybe just the code snippet that

runs the import passing the callback object?

That would help I guess

Jan

medini_1040753747

It is line number 498: https://us.v-cdn.net/6037473/uploads/medini/forum_1000229356/article_1000092094_rendering-external-image-links-in-reports/images/attachment>

This is the snippet from the import wrapper:

var importer = new ExcelImporter(callback);

if (!importer.run()) {

Hope that helps!

medini_1024169437

Johan, the image is not correctly attached... something went wrong maybe.

And more important is the line where the callback is defined.

Jan

medini_1040753747

I have attached the error again... it is line 498 in excel.js

callback is defined similar to your example:

import_ms_xlsx_ws_as_aoa: function(arg_0, arg_1) {

var array_of_string_array = [];

var max_col = 0;

var rows = 0;

// the callback object which we pass to the Excel API

var callback = {

start : 1,

ignoreEmptySheets : function() {

return true;

...

medini_1024169437

Johan! Thanks to that I could reproduce and fix this nasty little issue. Reason was that you have defined the import (and the callback object) inside of a method, so scoped. That revealed another scoping issue in the code which otherwise never came up.

I fixed it and will upload the latest version to the solution article as usual.

Thanks for reporting.

Happy Scripting

Jan

medini_1040753747

Respect it and protect it. Thx Jan!

medini_1064887566

Hello Jan,

I found the returns of getTextInCell are objects rather than strings. It is weird. Could you please explain why "getTextInCell" returns a object rather than a string? I checked excel.js and it shows that a text should be returned.

it bothered me because I couldn't do something like .split(".").length since it is not a native array of Javascript.

And I also found a workaround like below, so my problem has been solved but it is still awkward.

console.log(typeof getTextInCell(row, columnInt)); //object
console.log(typeof (getTextInCell(row, columnInt)+"")); //string

medini_1024169437

The reason is that the returned object might be a Java string and not a JS string. So this will most likely be true:

var a = getTextInCell(row, columnInt);
if (a instanceof java.lang.String) {
 alert("Yes, a Java string");
} else{
 alert("Hm, no, something else");
}

So you trick is exactly what I propose in this kind of situations, just concat it with a JS string and you are done, resp. in the JS world.

Jan

medini_1063548292

Hi ,

anybody here tried to handle (read/write cell) excel formulas with the ExcelDocument class?

I checked and it only "sees " the value of the formula:

If there is no trick or workaround, what one should use in order to handle the excel formulas?

 rows.slice(0,2).map( a=>a.cells[0]);

js: [#VALUE!,30]

I tried to toggle "show formulas in exel file and load it again but still no formulas visible in any of the cell attributes.

Marian

medini_1024169437

Marian,

I do not think this is possible with the Excel library we use. It is a rather lightweight lib that does not support formulas.

POI should be available in the platform already though, I think even 2 versions of it as several bundles have dependencies (M2doc and BIRT).

Happy Scripting

Jan

medini_1063548292

Many thanks Jan!

The fact that poi is part of platform plugins is fantastic.

One use simple bind to get the class XSSFWorkbook, instead of ExcelDocument.

var POIExcelDocument = bind("org.apache.poi","org.apache.poi.xssf.usermodel.XSSFWorkbook",false);


var excel = POIExcelDocument(File);
...

Question: would it be possible to use toe XSSFWorkbook object for the scripted model importer with excel files?

Happy Excelling,

Marian

medini_1024169437

Good idea. At some point in the future we anyway have to switch to the POI library completely I guess.

A switch would be an initial step. You think a global switch is sufficient (kind of system proeprty) or a switch "per import" or per "script"?

Jan

medini_1063548292

Thanks Jan,

I would go or a global switch on a Scripted importer preference page...

Use POI enhanced MS Excel Workbook for Excel documents

medini_1024169437

Just an update: we have added this switch now to the scripted importer, though not as a user preference but as a system property. Nice effect: the script itself may set that property when being loaded, so at the beginning, os it is evaluated at runtime when loading the file. That way the developer of the script is able to configure the importer, and doe snot have to rely on user configuration.

Happy Scripting

Jan