Friday, August 1, 2008

Groovy Excel Report Library

I just finished a series of reports for one of my customers using grails as the backend and Flex for the user interface. As with many of my reports for this customer, they requested the output be delivered as CSV files. Easy, and very boring.

So, with the help of JExcel and the jxl APIs from Andy Khan, I created an alternate output of an excel spreadsheet rather than CSV flat file. This has the additional benefit of sending multi-dimensional results, e.g., summary and details on two separate sheets, output statistics and charting on a third. This is much more useful for the end-user.

To make life easy, I created a wrapper around the jxl libraries to make it possible to use an alternate excel API such as Apache POI. At the same time, I created a set of conventions that use a template spreadsheet to easily create all the output without having to program any of the "view" components (i.e., classic MVC). The controller class is Workbook and the Model data is configured in Worksheet objects. All the formatting for dates, numbers, layout, etc. is in configured in templates (xsl files) easily configured and modified by non-programmers.

Since the target for this is inside war-deployed web applications I had to configure an external templates folder to enable report format changes without having to re-war/deploy or bounce the server. The templates folder is part of a more extensive Exchange class that coordinates remote data exchanges to a known area outside the web-space.

Conventions: Below is a list of convetions used to build reports...
  • xls template contains all formatting and sheets for the report
  • key words embedded into the template provides cell locations where data is to be written. values stored in the Worksheet object are used by the Workbook controller to update the spreadsheet output.
  • a special type of keyword called "ReportData" is used to insert multilple rows into the spreadsheet, based on query result sets (or any list of data).
The Worksheet models also have a place holder for "options" that change/enhance the controller's behavior as it builds output. Examples include "greenbar" to create green-bar reports, "showItemNumbers" to write out the actual row number to the left of the ReportData.

Working Example: To demonstrate the report library I created a "Customer Invoice" report. The template has three sheets, includes graphic images, and shows two of the three "ReportData" sets in green bar (and blue bar) with automatic item numbering.

The Code: The full report can be built and run with just a few lines of code. Here is a typical example that prints the invoice, details, and expenses for a single period.

The three sheets are defined as details, expenses and invoice. Invoice details and expense rows are fetched for a specific period with GORM in the typical way
. Report fields are extracted to match the template report data layout. The main invoice sheet has a few extra named values (line 28-31) and constructs two lines of data (lines 33 & 34).

Report Output
: Here are the three sheets created by the code above.


Christopher said...

We're doing Groovy/Excel reporting too, with POI. One frustration I have with the open source Excel libraries is that you can do very little with charts. You mentioned doing charts: how are you making out with JExcel's lack of chart support?

darryl.west said...

Well, when it comes to charts, I'm cheating. I let the designers create charts in the template, then I poplate the data on the backend. In the past, I have used a graphics library (java 2D) to paint the chart then include it in the sheet as a graphic. A bit hard-core, but in my mind easier than relying on the spreadsheet.

Tale Harate said...

Is it possible to update open worksheets with row(s), instead of having to create a new worksheet?
We use POI currently and go OutofMemory when faced with anything above 200,000+ rows

darryl.west said...

@Tale; My guess would be that you would suffer the same out-of-memory crash with JExcel. My suggestion would be to use -Xmx command line switch to increase the JVM's available memory.

Mr. X Mormon said...

I'm newbie to grails, my client wants to upload a certain excel file in oracle database, do you think POI is the best API for this task?

darryl.west said...

If Excel is all you need to read, then JExcel is probably easier, but PIO will certainly do the job. Be aware that the latest readers only read upto 2003 versions of Excel.

Mr. X Mormon said...

Thanks for the info. My boss really hates me, he wanted to convert this macro thing into web (groovy on grails). I have done the following in my controller

import jxl.biff.formula.*;

def process = {

Double d = 1.50

Float x = Normsinv(d)

println x


but, still cannot have output. do you have any sample using normsinv?