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.