This is the first of a planned series of posts examining the JS-API from the point of view of an Excel Office developer.
The main advantage of the JS API is that it allows you to write code that works for Windows Excel, Mac Excel, Excel Online and Excel on IOS. The JS API is still immature but is undergoing rapid development by the Microsoft Office Extensibility team, with new releases of the API arriving roughly quarterly.
- To work across such a wide variety of end-points (mobile, tablet, cloud, desktop).
- To target today’s developers who work mainly or exclusively using web technologies .
The API executes asynchronously by queuing up the API interactions with Excel until you request them to be executed in a batch (Context.Sync()).
- Arrays are not really arrays and only have one dimension, so you quickly learn about arrays of arrays. They don’t have fixed upper bounds and can be sparse. They have powerful methods like MAP, REDUCE, SLICE, and SORT. (But its really hard to make JS array sort work the same way as an Excel sort!)
- Functions are first class citizens – you can pass them around like variables – Anonymous/Lambda functions are fully supported – you really need to get your head around this!
- Asynchronous processing is handled by things called Promises which can be chained together with some difficulty (this situation is known as the Pyramid of Doom)
Two major advantages of TypeScript are:
- You can assign Types to variables and they have sensible scope!
- You can use Async Await instead of Promises which makes your code much more readable and avoids the Pyramid of Doom.
Excel API Technologies Compared
There are 4 main families of Excel APIs:
Excel ‘C’ XLL API
This API is the most tightly integrated and lowest publicly available interface to Excel:
- Best performing API
- Low-level interface requires C programming and memory management skills.
- Visual Studio IDE
- Runs in the Excel Process.
- Most capability for UDFs (multi-threaded, asynchronous, RTD, Cluster etc)
- Only covers a subset of the Excel Object Model
- Limited native UI support
- Used as a foundation layer by many other products/platforms/languages (XLL Plus C++, Excel DNA .NET, Addin Express .NET, PyXLL Python, FCell F# …)
- Windows Desktop only
- Application addin level only
- Can use all the C and C++ frameworks (Standard Template Library, BOOST …)
Excel COM and Automation API
This API is the richest and most widely used API
- Primary languages VBA and VB6
- Good performance
- Widest object model support
- Supports single-threaded synchronous UDFs
- Runs in the Excel Process
- Windows and Mac Desktops (VBA only)
- Macro recorder provides low entry point
- Built-in but ancient IDE for VBA
- Can be embedded in a document or used as an addin at application level.
- VBA largely unchanged for many years and has only a limited and dying framework
This API adds an additional .NET interop layer on top of the COM/Automation interface. Ideally this would have been a useful stopgap until a proper .NET Excel interface was developed, but sadly that never happened.
- Poor performance
- Runs in separate process
- Supports .NET framework and languages
- Windows desktop
- Visual Studio IDE
- Primarily application level but can be bound to a document using VSTO
- Not suitable for UDFs
- Microsoft’s offering is VSTO
Products such as Excel DNA and Addin Express support both the COM-Interop .NET world and the ‘C’ API to enable proper UDF support, improved performance whilst keeping the richness of the .NET framework.
Excel JS-API Release 1.4
The new kid on the block! This is the API that is currently being actively developed by Microsoft, so it is very much a moving target. Comments below reflect the 1.4 Version of the API as at February 2017.
- Your code runs unchanged across multiple endpoints.
- But this can result in lowest common denominator support
- Good Web Support
- Office Store support
- Runs in a separate browser process
- No UDF support currently
- Very poor performance
- Targeted at professional web developers
- Object model support
- Limited but rapidly expanding
- Single workbook only: cannot open, close or save workbooks
- Limited control of Calculation and Screen Updating
- No Copy Paste Special
- Hybrid Cloud/Application Level/Workbook Level
- Asynchronous processing only
My next blog posts on JS will focus on the performance of the JS-API.
If you want to learn more about the JS-API I recommend Building Office Addins by Michael Zlatkovsky, who is a developer on the Microsoft Office Extensibility team.