Friday, April 25, 2008

Dealing with Excel in the Java (with JExcel, in particular)

Just because you're an open-source snob and programming in Java doesn't mean you're free of Microsoft Orifice, particularly Excel. Because I do a lot of database related coding and stuff, I deal with Excel a lot. JExcel is a major lifesaver.

Just in case you don't know, JExcel provides an api to Excel spreadsheets. You can read and create xls files, deal with various structures you'd expect such as cells, columns, and rows, as well as Worksheets and formatting.

I know this isn't the only Java API for dealing with Excel files, and I'm sure others are equally wonderful. Apache POI is venerable and also sounds good, especially since it deals more generally with the OLE 2 Compound Document Format, which gives you more general involvement with those other file formats, such as Word and Access and other horror shows. Also the name is awesome.

JExcel's really clean object model also appears to be a lot easier to comprehend than the OLE specific POI api. For quick and dirty, there's little better.

Groovy also has Scriptom for dealing directly with COM objects, but you have to be running your code on Winblows (not that there's anything wrong with that).

The power of dealing with an xls file directly, instead of having to open and save to csv files endlessly is huge. On a recent project, I was able to construct some groovy code which downloaded via http the most recent version of a spec spreadsheet, and do a lot of complicated parsing of field names to generate sql. 

In a sense, this is all pretty trivial, but in the bread-and-butter existence of the Commodity Developer's life, the quality of life improvement is huge.

I'll try to provide some examples later.

No comments: