JavaScript as an Excel scripting language via HAMMER

I’ve demonstrated in the past how to embed JavaScript in  Excel using the C based JSDB toolset and having already provided Python as a HAMMER scripting language, I felt the time had come to do the same for JavaScript. With this, the latest release of HAMMER, JavaScript’s back!

There’s three main reason’s why I’ve added JavaScript as a partner scripting language to HAMMER’s existing Python (and VBA) integration.

  • JavaScript is everywhere, a lot more people know it and use it than have ever used, or likely to use Python, (or VBA). JavaScript is now the undisputed “glue language”.
  • I needed a scripting language that would work in all versions of HAMMER (the current IronPython implementation requires NET 4.0). For that reason I decided to use the NET2.0 based JINT rather than IronJS as my choice of JavaScript engine. JINT, is simpler, and probably slower that IronJS, but being C# based (IronJS is being developed in F#) I can more easily dig as deep as I need into JINT’s codebase without exhausting my technical skills.
  • Increasingly I’m coming across JSON as data transfer protocol and although I have inbuilt JSON processing commands in HAMMER, JavaScript and JSON are natural partners.

The main use-case I see for JavaScript scripts will be the manipulation of data, which is obviously equally possible with Python, but I see IronPython’s main use as a scripting language for .NET facilities (IronPython’s integration with .NET is complete, the JINT engine’s integration is much less advanced). And as I pointed out above, removing the NET4.0 requirement (for both Excel and the HAMMER.exe command line version) is very useful when building HAMMER Inside bespoke applications.

So my scripting language choices are:

  • VBA, when manipulating the Excel Object Model
  • IronPython when accessing .NET internals, or for complex coding requirements when not  using C# or VB.NET.
  • SQL , for tabular set manipulations, and for the fact “that SQL  does exactly what it says on the tin“.
  • JavaScript for everyday data-focused IF-THEN-ELSE’ing or JSON’ing.

Calling JavaScript from HAMMER is similar to Python. So …

=HAMMER(“myVal=’cat’; return myVal;”,”JAVASCRIPT”) will return “cat”.

Like PYTHON, a object “db” is passed into the script, this allows access to the SQLite instance via several functions:

  • db.SQL(arg) will take a string arg holding a SQL command and execute it.
  • db.SQL(arg,callback(arg)) as above but expects a SELECT statement and will return a JSON encoded object string via the callback’s arg for each row returned.
  • myJSONTable  = db.jsSQLQuery(arg) expects a SQL SELECT statement and will return the table as a single JSON encoded string as a List of Objects.
  • myJSONReturn = db.JSONhammer(arg) will make a call as an internal HAMMER command where arg is a JSON encoded string of a List of commands. Will return a JSON encoded table or a string value.
  • argValue = db.getArgNoArg(argNo) will get the value of a HAMMER argument, where argNo=1 is the last argument before the JavaScript script argument, argNo=2 is the argument prior to that, and so on.
  • tableName  =  db.getArgNoTable(argNo) like above but returns a table name if the argument position holds a table rather than a string value.

A predefined JSON object is also passed in, offering the usual JSON.parse(arg)  and JSON.stringify(obj) methods.

There’s also an alert(arg) command which will display a Window’s message box and a println(arg) command which will append to a _JSSTDOUT specified log file if one has been specified.

Use the “return” command to return either a table (a JavaScript object containing either a List of Lists or a List of Objects) or a string value.

I’ve not fully tested this, or settled completely on the interface between HAMMER and its new JAVASCRIPT engine, but I’ll do so over the next few weeks and will then include a proper examples of its use.  So use with caution.

Here’s a list of the HAMMER commands implemented so far …

Download  the latest version of HAMMER from here …


2 responses to “JavaScript as an Excel scripting language via HAMMER

  1. Pingback: Excel as a book of record. | Gobán Saor

  2. Pingback: VBA Multithreading, .NET integration via HAMMER | Gobán Saor