Author Topic: Importing Excel XLSX files in script  (Read 101 times)

Manj75

  • Senior Member
  • ****
  • Posts: 176
Importing Excel XLSX files in script
« on: October 11, 2021, 13:44:14 PM »
I was wondering if anyone has had any success in using SheetJS (https://sheetjs.com) library to create an Excel .xlsx import script?

I have previously created an Excel export script using the myExcel library package, but this has no ability to import in.  Searching this forum I found ony one topic where JB suggested to use SheetJS (Topic 1086: https://forum.archimatetool.com/index.php?topic=1086.msg5801#msg5801).

I have tried to use SheetJS but it's not a simple API for loading in the xlsx file from localhost.  All examples used are web based and use HTTP methods to fetch the file, which seems complex for my entry level ability ;-)

What I have tried is to use the java.io.FileReader to read the file byte at a time and then convert to a typed buffer (Uint8Buffer), but when parsing it into
Code: [Select]
XLSX.read(data, {type:'array'})
it just reads it as a new workbook and does not parse it.  A work book is returned but the first sheet name is 'Sheet 1' when it should be 'Test Sheet 1', and there is no data read in when there is in the xlsx file.

I would welcome any help from anyone who has successfully used SheetJS in a jArchi script - let me know if you want me to post the code snippet.

Phil Beauvoir

  • Administrator
  • Hero Member
  • *****
  • Posts: 1639
  • Code Punk
    • Archi
Re: Importing Excel XLSX files in script
« Reply #1 on: October 11, 2021, 22:10:59 PM »
If you can post the code that you tried so far, I can take a look at it.
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

Jean-Baptiste Sarrodie

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 729
  • Archi Evangelist and Contributor. Visionary.
    • About me...
Re: Importing Excel XLSX files in script
« Reply #2 on: October 15, 2021, 08:10:27 AM »
Hi,

I was wondering if anyone has had any success in using SheetJS (https://sheetjs.com) library to create an Excel .xlsx import script?

I've tried several time (and tried again since you published your message) and using different libraries, but I've never been able to make it work. As suggested by Phil, please share your current attempts and maybe together we'll make it work.

On my side, I've tested the following libs:

For most if not all of them, I had to use GraalVM script engine. And for some of them which can only be loaded through 'require()', I had to use jvm-npm.

In addition, some rely on readfully for which you can use this polyfill (which works most of the time):
Code: [Select]
function readFully(url) {
    var result = "";
    var imports = new JavaImporter(java.net, java.lang, java.io, java.nio.charset);

    with (imports) {

        var urlObj = null;

        try {
            urlObj = new URL(url);
        } catch (e) {
            // If the URL cannot be built, assume it is a file path.
            urlObj = new URL(new File(url).toURI().toURL());
        }

return new String(urlObj.openStream().readAllBytes(), StandardCharsets.UTF_8);
    }

    return result;
}

But at the end, I was not able to read the content of an XLSX file.

Regards,

JB

Edit: just in case, I've found another simpler, maybe useful, polyfill for "require". It is not meant to be used outside a brower (relies on WMLHttpRequest) but could easily be adapted for Nashorn and GraalVM. You can find it here.
« Last Edit: October 15, 2021, 08:29:17 AM by Jean-Baptiste Sarrodie »
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate

Jean-Baptiste Sarrodie

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 729
  • Archi Evangelist and Contributor. Visionary.
    • About me...
Re: Importing Excel XLSX files in script
« Reply #3 on: October 15, 2021, 13:52:58 PM »
Hi,

FWIW, I've discovered that GraalVM includes a native module loader (ie. an implementation of require()). We might enable it by default in future version of jArchi, but I've found a workaround to enable it in current version.

For it to work:

1. Create a "node_modules" folder in your "scripts" folder. You can even copy one from your Node.js if you have one.
2. Add this to the top of your script, it will bootstrap the setup:
Code: [Select]
if($.process.engine != "com.oracle.truffle.js.scriptengine.GraalJSScriptEngine") {
  console.log("This script only works with GraalVM script engine.")
  exit();
}

var System = Java.type("java.lang.System");
if(System.getProperties().get("polyglot.js.commonjs-require") != "true") {
  System.getProperties().put("polyglot.js.commonjs-require", "true");
  System.getProperties().put("polyglot.js.commonjs-require-cwd", __SCRIPTS_DIR__);
  console.log("GraalJS Module loading was previously disabled. Restart your script");
  exit();
}

To test this setup, simply create a dummy module under "node_modules" folder, for example this one named "add.js":
Code: [Select]
// add.js
function add (a, b) {
  return a + b
}

module.exports = add

And then, you can use it in your own script:
Code: [Select]
console.clear();

if($.process.engine != "com.oracle.truffle.js.scriptengine.GraalJSScriptEngine") {
  console.log("This script only works with GraalVM script engine.")
  exit();
}

var System = Java.type("java.lang.System");
if(System.getProperties().get("polyglot.js.commonjs-require") != "true") {
  System.getProperties().put("polyglot.js.commonjs-require", "true");
  System.getProperties().put("polyglot.js.commonjs-require-cwd", __SCRIPTS_DIR__);
  console.log("GraalJS Module loading was previously disabled. Restart your script");
  exit();
}

const add = require('add');

console.log(add(4, 5))

With this, it should become possible to use a local install of NPM to dowload required modules and then load them from within jArchi. This should help, but will it be enough, I don't know.

Regards,

JB
If you value and use Archi please consider making a donation! https://www.archimatetool.com/donate