Using Scriptom to Automate Microsoft Excel In Groovy

http://docs.codehaus.org/display/GROOVY/Using+Scriptom+to+Automate+Microsoft+Excel

 

Why Scriptom? 

There must be a dozen different libraries out there that support reading and writing Excel documents.  Some, like POI or OpenXLS , let you manipulate Excel files directly from Java.  These libraries don't use Microsoft Excel , so they work well in mixed OS environments.  However, they don't support some key features of Excel (there are significant limitations), and their APIs don't correspond to Excel's COM API , so it can be difficult at times to figure out how use them to do even simple things. 

Other libraries, like COM4J , JCOM , and JACOB let you use Excel automation to read and create Excel files, either directly or through wrapper classes generated from COM type libraries.  This gives you full access to the complete featureset of Excel .  It also limits you to Windows.   It can be sometimes be difficult to translate from Microsoft's Visual Basic-oriented documentation to the corresponding library/wrapper in Java.  This can be particularly challenging if you aren't already up to speed on COM and the Excel API - which is probably true for most Java programmers.  Excel automation is also a lot slower than direct file manipulation (process startup overhead, process-to-process communication). 

Scriptom is a COM automation library for Groovy , so it falls squarely into the second group.  In fact, Scriptom is an extension of JACOB . JACOB does the heavy lifting, and Scriptom makes it groovy.

So why would you use Scriptom over one of the alternatives?  

Scriptom is intended to replicate the feel of COM scripting - as much as possible - but with Groovy instead of VBScript.  It looks like COM scripting, and it feels like COM scripting.  So when you are, for instance, translating a VBA macro to its equivalent Groovy, there is very little translation involved.  You can't quite do a copy-paste, but it's close.  And when you are programming against the Microsoft documentation for the Excel API, you don't have to ask a lot of questions.  The code simply works the way it's documented. 

Additionally, Scriptom provides special support for scripting Excel.  The Scriptom library contains definitions for all the constants in the Excel type library. Scriptom also includes ExcelHelper (org.codehaus.groovy.scriptom.util.excel.ExcelHelper - whew!), which gives you an easy way to work with Excel documents in a multiprocessing (server) environment.

Are you feeling groovy yet?

Scriptom is suitable for use in low-to-medium volume tasks where Excel is available.  For example, it is useful in batch jobs, where you aren't too concerned about execution time.  And it can be used to produce reports in Excel format for a web site (throw in a pie chart in a few lines of code), as long as you don't try to deliver more than one report every couple of seconds. 

If you feel the need for speed and you can live with its limitations, consider POI.  In fact, many Java-based frameworks and libraries that provide Excel support out of the box (Cocoon, ColdFusion, etc.) use POI under the covers.  However, if your goals are easy-to-write , easy-to-maintain , and full-featured , and the Windows/Excel thing isn't a showstopper, your best bet may be Scriptom . 

Let's Get Started!

LINK TO DOCUMENT WITH INSTALLATION INSTRUCTIONS 

Enough with the introductions.  Let's get started with an example that shows just how easy it is to work with Excel using Scriptom.

 

http://poi.apache.org/hssf/index.html

http://www.rgagnon.com/javadetails/java-0516.html - various ways to read and write Excel documents

minimalistic framework, and syntax that corresponds directly to the Microsoft documentation