JavaScript as an Excel scripting language via JSDB

A few years back I posted about JavaScript as an Excel scripting language via ExcelDNA. That involved using JavaScript (in the guise of JScript.NET) as an ExcelDNA scripting language. It was purely an academic exercise to prove it could be done, I continued to use C# (or increasingly VB.NET) to build .NET user defined functions. This time however, I’ve managed to embed JavaScript (in the guise of Mozilla Foundation’s SpiderMonkey) directly via a native C interface not to prove I could do it (even though there’s a definite satisfaction in simply doing it) but to use it.

Why add another scripting language to xLite, hasn’t it already got Python?

True, Python is and remains a very powerful add-on to xLite. It’s a mature and long-established language, popular amongst IT professionals and “citizen programmers” alike. But, it’s a bit of a monster and can be awkward to package, particularly on Windows. By using Py2Exe and after a lot of digging on the issue of manifest files I have managed to package and isolate xLite’s Pythonic bits so that it can be used on a PC without first installing the required Python version (I’ve only tested against V2.6, Python’s lack of a side-by-side Windows installation capability is a major pain-in-the-butt bad news: tested against Python 2.5 & it doesn’t work; good news: side-by-side is possible; simply change the system path to reflect which-ever version you wish to run at the command line; xLite will (must) continue to use V2.6). This “version-hell” mitigates against using Python as a core-element of xLite, fine for those of us who are comfortable with and require the full power of Python, but not as the tool’s primary scripting environment.

No, what I need is:

  • a light (single EXE or DLL preferably) and an approachable popular language,
  • with native SQLite support,
  • runable as standalone executable (on both Windows & Linux),
  • embeddable (is that a word?) in Excel via VBA-friendly DLL.

Add to that essential list some nice-to-haves such as:

  • native COM-interface support (for the likes of ADO etc.),
  • native networking support, for HTTP, raw TCP sockets etc.,
  • native (and easy to use) XML and JSON parsers and emitters,
  • ability to spawn detached/attached command line processes and the ability to stream data to and from such processes; allowing me to easily orchestrate & provide a “grid” of processes (scripted, command line executables, Excel instances) both local and remote (with remote being either traditional servers, http servers or Hadoop Streaming grids).

While looking at the various implementations of Javascipt as a server/shell language, I came across JSDB – JavaScript for Databases. It’s a C++ wrapper around Mozilla’s SpiderMonkey, with lots of useful data related utility classes added. To make JSDB a perfect fit it simply required:

  • a few minor changes to the SQlite class (allow the loading of Virtual Table extensions, add the ability to pass in the address of already open SQLite memory structures);
  • a linker change to use the DLL version of SQLite;
  • plus a VBA-friendly DLL wrapper ( & VBA declares, to call the DLL) to replace the JSDB shell when embedding in Excel.

From my SQLite as the MP3 of Data post:  “Just as “fractional horsepower” electrical motors revolutionised manufacturing and eventually all our lives (car starter-motors, fridge motors, washing machines etc.), “fractional horsepower” databases can do the same for data. Distributing data to where it is needed.” I can now add a distributed “fractional horsepower” processing engine for that distributed data. This transforms xLite from a micro-ETL platform into one capable of handling (or at least orchestrating) practically any ETL (Extract, Transform & Load),DI (data integration) or “Time Asset” (see this post) process.

UPDATE:

Here’s a VBA source with examples of using the embedded JSDB engine:

http://www2.gobansaor.com/share/example_jsdb_vb_embed.bas

… and the source code for the DLL wrapper:

http://www2.gobansaor.com/share/wrap_sqlite.cpp

… plus the VBA Declares and helper Windows APIs:

http://www2.gobansaor.com/share/jsdb.bas

UPDATE:

See JSDB being used to front-end Excel as a web service http://blog.gobansaor.com/2011/02/06/excel-as-a-diy-web-service/

Advertisements

8 responses to “JavaScript as an Excel scripting language via JSDB

  1. Pingback: JavaScript as an Excel scripting language via ExcelDNA « Gobán Saor

  2. Tom,

    While not the sophisticated xLite implementation, I often use JavaScript as a integral part of my Excel applications.

    I just invisibly load a userform with an embedded webcontrol. Granted it’s really JScript, but it’s pretty close. I gain the advantages of JavaScript from my Excel project, but at the same time I can use VBA for whatever I choose.

    A very simple demonstration is my celtic muse, which uses JavaScript to control the media timing:

    http://www.excelhero.com/blog/2010/05/excel-animated-chart-2.html

    Regards,

    Daniel Ferry
    excelhero.com

    • Daniel,

      I like it! Javascript aside, the timer could be very useful in many situations (as an alternative to an RTD server for example).

      I wouldn’t say that xLite is sophisticated and this is not; just different types of sophistication 🙂

      Your blog is always worth a read, I particularly liked the “swarm” http://www.excelhero.com/blog/2010/05/multi-threaded-vba-update.html
      posts. In fact the existence of an excellent swarm (or grid) example coded using JSDB (see “grid programming toolkit” link on http://jsdb.org/ ) was what finally prompted me to embed JSDB in Excel (up until that I’d simply used it from the command line).

      By the way, I’ve never been able to run the Celtic Mist example, I get “object doesn’t support this action” at this line …

      If CLng(m_objMediaPosition.CurrentPosition) = CLng(m_objMediaPosition.Duration) Then

      … on the .Duration property (reference to the type library is set, under Excel 2010 & XP SP3).

      Tom

  3. Tom,

    Yes, I use the JStimer is many situations. It’s robust and indispensable.

    I’m not sure celtic muse runs at all in Excel 2010. I don’t have it yet, and I’ve read some feedback that suggests there’s a problem on 2010.

    If you have 2007 on a machine, I think it would be worth your time to try it, from one Celt to another 🙂

    Regards,

    Daniel Ferry
    excelhero.com

  4. Pingback: VBA & JavaScript – glue languages | Gobán Saor

  5. Pingback: The 64-bit question, and the birth of MicroETL | Gobán Saor

  6. Pingback: microETL – the ultimate datasmithing tool? | Gobán Saor

  7. Pingback: JavaScript as an Excel scripting language via HAMMER | Gobán Saor