Excel Reports with Apache Cocoon and POI
by Steve Punte
|
Pages: 1, 2
Formatting Information
The result of the query will be a table of column named "date" and "sum". The third main section of the report directs the generator to insert the queried data into the Excel report. There are several commands to do this; <rpt:column> is the most commonly used. In this particular example, it is interpreted as insert all "date" values beginning at Excel Cell Address B6 going downward. And insert this data as a "calendar date" type as opposed to a string (default), which makes more interesting Excel processing possible.
<column> <qry-name>date</qry-name> <adrs-column>B</adrs-column> <adrs-row>6</adrs-row> <type>java.util.Date</type> </column>
The Final Result
The last stage of the Cocoon pipeline inserts the data into the supplied report template and then renders the final report in the users browser. In this case, the final report appears as
 |
| Figure 5: Simple Example Final Report |
Solution Implementation Details
Cocoon facilitates XML-directed software. The entire solution is achieved by directing generic components with the contents of XML files. Figures 6 and 7 below demonstrate how the main pipelines are composed of reusable, generic XML processing components, which are in turn directed by XML documents.
Apache Cocoon NotationThis article uses the following Apache Cocoon Graphical Notation: 
|
The Parameter Pipeline
The report generator parameter pipeline is responsible for rendering the report input parameter page. Such parameters are typically date, staff member name, company name, and so on -- whatever makes sense as a generic feature of the report technology. The parameter definitions are obtained from the report scheme document.
 |
| Figure 6: Input Parameter Cocoon Pipeline |
The Rendering Pipeline
The report generator rendering pipeline (shown in Figure 7) is the realheart of the solution.
 |
| Figure 7: Report Rendering Cocoon Pipeline |
The pipeline processing steps are as follows:
- Incoming HTTP request parameters are converted into an XML document by the request generator so as to be compatible with later components.
- An XSL transformer, directed by render.xsl, generates XInclude statements so that key elements of the report scheme document can be incorporated.
- The XInclude statements are executed retrieving the appropriate report scheme query and formatting constructs.
- The original HTTP request parameters are properly merged into the database query statements using a second XSL transformer.
- The database query statements are executed using the standard Cocoon SQL transformer, retrieving the desired report data.
- The retrieved data is arranged according to the report scheme data-block formatting statements for appropriate insertion into the Excel spreadsheet. This is accomplished by the use of a third XSL transformer.
- The retrieved data is merged with the Excel template and the final binary Excel report is generated and sent back to the user using the report serializer.
This is a clear example of how significant and productive solutions can be achieved using XML-directed architectures. The exception is the last component in the pipeline, the report serializer. It's a component I've written to support the examples in this article, is free to download and use by all, and will likely be integrated or assimilated somehow into the POI-related components now being incorporated into Cocoon.
A More Complex Example
The first example was rather trivial. Suppose your customer wanted a two-dimensional report depicting the sales amount all staff members against calendar dates as show in figure 8 below:
 |
| Figure 8: A Two Dimensional Report |
Due to lack of space I will not describe the solution in detail, rather a working Apache Cocoon solution can be download here. However, some interesting characteristics of this solution are worth noting:
- The data for this report is obtained in two separate queries. The first retrieves staff member names and their associated dollar transactions. This query is used to populate the two horizontal bluish rows. The second query retrieves calendar dates, the total transaction on each date, and the main table of data. This data populates the columns and central table area. A report can contain any number of data queries and column/row/matrix insertions.
- The Excel Report Template is created with all columns, row, and matrix areas of one element size with appropriate color and cell format. Additional report serializer tag commands copy and shift this formatting downward or to the right to achieve the appropriate size.
XML Direct SoftwareApache Cocoon and the examples presented here clearly exhibit characteristic of XML-directed software. Cocoon is an XML-based framework for aggregating and assembling SAX-based components into a web application. The components are generic in nature without any hardcoded domain-specific content. Domain-specific content -- for example, business and presentation logic -- resides in "directing" XML files and sources. |
How to Incorporate this Solution
If you are starting from scratch, Apache Cocoon can be integrated and configured to take responsibility for a URI subtree or the entire URI space by way of various deployment and configuration files available with any standard application server. See the Cocoon home page for integration instructions with all major application servers.
The best place to start is by downloading the report solution and examples; then setup a test instance based on the example from this article.
The intent is to supply the necessary collection of critical components to achieve professional web-based Excel report solutions. Variations could include querying an XML database instead of a relational database, obtaining the report data over the network by way of SOAP messaging, querying a database for input parameter selections, etc. Such variations require modification to the various Cocoon pipelines, but this is the strength and flexibility of the platform. Do the provided and standard Cocoon components have sufficient functionality to handle your situation? Probably. If in doubt, write me, and I'll respond.
While the solution described herein is built for and around Excel, this approach can be applied to any spreadsheet application whose file format can be manipulated. Indeed, the next release of Excel allows for a more readable XML document format as opposed to the current binary standard.
Summary
In this article we have described a simple, low-cost, modular, XML component oriented solution for generating and rendering real-time Excel reports. Future enhancements will probably include support for formulas, multiple sheets, and so on. Portions of this solution were developed for Amansi.com, which has donated this solution to open-source.
Resources
|
- xml to excel not in web
2006-02-26 04:41:14 amgad2002 - Problem with Excel Sheets
2003-12-15 02:08:24 Amine ELOUERGHEMMI - Problem with Excel Sheets
2005-06-14 09:16:19 kpsvas - Grouping in Cocoon
2003-11-10 21:09:53 Fahmi Fahlevi - multiple excel sheets forming a workbook
2003-11-04 01:09:59 Manish Jain - Why do it hard when you can do it easy?
2003-04-19 13:47:47 Ann Nonymous - Why do it hard when you can do it easy?
2003-04-29 22:47:43 Steven Punte - JXReports Announcement
2003-04-11 16:31:55 Steven Punte - Displaying Excel Charts
2003-04-11 08:53:31 Kamlesh Barvalia - Displaying Excel Charts
2003-04-11 16:20:15 Steven Punte - New: Tomcat Installation Direction from War file
2003-02-19 18:47:11 Steven Punte - New: Tomcat Installation Direction from War file
2004-02-24 10:18:38 Sri Karumuri - Demo Software Note
2003-02-14 10:10:29 Steven Punte - XML & Excel reporting
2003-02-12 08:18:40 Leo Cohn - XML & Excel reporting
2003-02-14 10:01:21 Steven Punte - XML & Excel reporting
2003-08-12 11:10:20 Dhriti Dave
