Excel <> ExtendScript Bridge
To get started quickly, skip to the ‘Download’, ‘Installation’ and ‘Usage’ sections below
The Problem
A common application of After Effects and Photoshop Scripting is in the generation of data-driven graphics. A commonly encountered data-source is Microsoft Excel. Current versions of Adobe After Effects’ Scripting Engine run in a proprietary JavaScript environment which lacks any built-in functions to read Excel Files, access ODBC datasources, ActiveX or other practical means of retrieving data from Excel. Many Scripters have been working around this issue by exporting data from Excel as plain text files with comma separated values (CSV file). However, this approach lacks flexibility and, where data is being updated on a live project, re-exporting the CSV file each time becomes cumbersome.
A Solution
The solution implemented here takes the form of two components: a JavaScript library file which resides alongside the users own script, providing several new ExtendScript functions – this acts as the Client side. Second; a Server Application is run simultaneously which talks directly to Excel to fulfil the client’s requests. This communication takes place via Windows’ TCP layer.
Download
Excel to ExtendScript Bridge.zip (79k)
Installation
To access Excel spreadsheets from within ExtendScript you need to use the 3 files provided like so:
ExcelServer.jar This is the Server Application which must always be running during the execution of your scripts. It does not matter which folder you keep this in.
It is an executable JAR file and you will need Java 6 runtime installed on your machine to run it (available free from http://java.com).
JCom.dll This is a native code library that allows the ExcelServer Java application to see Windows COM objects, in this case the Excel application.
This file must be copied to the bin folder in your Java home directory (the same folder as java.exe).
An example of a common location for this folder is: c:\Program Files\Java\jre1.6.0_03\bin
Excel.jsx This is the ‘client’ function library which should be copied to the same folder as your user script.
Copy and paste this line of code as the first line of your user script:
eval((f=new File(”Excel.jsx”)).open(’r')?f.read()+(f.close()?”":”"):alert(”Import failed”));
This loads and evaluates the Excel function library; having the same effect as the ‘import’ or ‘include’ of other languages.
You also need to make sure the JAVA_HOME environment variable has been set (in My Computer > Properties > Advanced > System variables). This needs to be set to the base folder of your Java installation e.g. c:\Program Files\Java\jre1.6.0_03\
Usage
Once you have completed the steps of the installation, and included the ‘import’ line in your script, you may make use of the Excel Object in your scripts, as follows:
Excel() Creates a new instance of the Excel Client object; you need one of these first, to make use of the following functions:
Excel.connect() Establishes a connection to the Excel Server application.
Excel.disconnect() Disconnects from the Excel Server. The server returns to its original state, listening for new connections.
Excel.newSheet()
Excel.open( fileName ) Opens the excel file specified by the given filename.
Excel.saveAs( fileName ) Saves the active sheet as the specified filename.
Excel.getCell( cellRef ) Returns the value held in the specified cell. The cell reference can be made in one of two ways:
Either as a string containing the standard Excel reference e.g. A4, G5 etc.
Or, as a two-element Array containing the integer coordinates for row & column e.g.var value = getCell(new Array(x,y));
Excel.setCell( cellRef , value ) Sets contents of the specified cell of the active spreadsheet to the given value.
Excel.quit() Causes the Excel Server application to disconnect, close the associated session of Excel and quit itself.
Excel.info() Returns a string containing information about the associated session of Excel.
Excel.testConnection() Simple ‘ping’ function that returns boolean true from the server (or false if an unexpected response was encountered).
Troubleshooting
So far, I have only verified the basic functionality of this software, when used in the exact prescribed circumstances and manner.
While it has been tested with a few scripts, it is probably not very robust at this stage and any badly formed commands may leave the server in an unknown state. Should the server stop responding,you should end the windows process for the java runtime, javaw.exe, to release the system resources, then check that your script is not trying to use the functions in an inappropriate way e.g. trying to use getCell() when the ExcelServer is not connected.
I have no plans to continue work on this software at the moment but if anyone needs support and is prepared to make a small donation I will provide bugfixes as necessary.
If you think you have identified a bug I would be happy to hear from you so I can improve this software for future versions. Please report to: christopherhattonuk@gmail.com
Credits
This software makes use of the JCom (Java-Com Bridge) which is hosted at http://sourceforge.net/projects/jcom/
Many thanks to the developers of JCom for making this project possible.