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
|
Share your comments or questions on this article in our forum.
(* You must be a member of XML.com to use this feature.)
Comment on this Article
| Titles Only | Titles Only | Newest First |
- xml to excel not in web
2006-02-26 04:41:14 amgad2002 [Reply]
Hi Steve,
Can i convert data from xml to excel file in java application locally without using web server using cocoon/POI hssf serializer & how if possible
- Problem with Excel Sheets
2003-12-15 02:08:24 Amine ELOUERGHEMMI [Reply]
Hi Steve,
Thank you for this very interesting article.
In my Office, I try to use Poi combined with servlets to generate Excel Sheets into my IE browser.The result is very nice. But It doesn't stop interfeering with other Excel Sheets, on my workstation: these sheets are reduced. Is There any solution to stop this interference.
Thanks
Amine
- Problem with Excel Sheets
2005-06-14 09:16:19 kpsvas [Reply]
hi i got same problem,
please send one how to install cocoon and poi with this how can generate reports from database to excel through jsp application
- Problem with Excel Sheets
- Grouping in Cocoon
2003-11-10 21:09:53 Fahmi Fahlevi [Reply]
Hi Steve,
Grouping in cocoon, could Cocoon do this?
Can you explain it to me?
thanks & regards
Levi
- multiple excel sheets forming a workbook
2003-11-04 01:09:59 Manish Jain [Reply]
Hi! steve,
i found your article very useful in understanding the concept behind generating excel sheets. It would be very nice If you can provide me some information regarding generating a workbook comprising to various worksheets using similar approach.
- Why do it hard when you can do it easy?
2003-04-19 13:47:47 Ann Nonymous [Reply]
This seems like an awful lot of trouble to go to when Cocoon already has a built-in Excel file generator.
Also JFWIW, I think the preceding post violates xml.com's Terms of Service--specifically "2. Do not use this discussion area for any commercial purposes, to distribute any advertising or solicitation of funds or goods and services."
- Why do it hard when you can do it easy?
2003-04-29 22:47:43 Steven Punte [Reply]
The technology "sweet spot" of using Cocoon +
POI + Excel that is described in this article
uses Excel as a beginning template. The
previously existing HSSF serializer wasn't
really built with this paradimg in mind: i.e.
shifting around exsiting content dynamcially
as new content is inserted. Thus, the reason
for the custom Cocoon Excel POI serializer. It
does some very specific actions on the excel
binary doc to achieve the overall paradigm.
Ooops, sorry, yes I may have violated an
xml.com regulation here.
- Why do it hard when you can do it easy?
- JXReports Announcement
2003-04-11 16:31:55 Steven Punte [Reply]
This article turned out to be quite
popular and I received a significant
amount of response.
Due to the enormous interest, an associate
and myself has launched the company JXReports
(http://www.jxreports.com) to provide
"Semi Custom Report Solutions" based on
this technology.
Our core premise is that reporting needs
are often too irregular to be service
by a monolithic solution, and many many
clients require several custom features
like special handling of passwords,
additional security, data from web
services, data from legacy systems,
special pre-processing, multi-page-
sequence reports, etc....
We at JXReports will assemble the
necessary building blocks to meet
your needs.
Visit us at:
http://www.jxreports.com
- Displaying Excel Charts
2003-04-11 08:53:31 Kamlesh Barvalia [Reply]
Hi Steve
thanks for the informative article. I have two quick questions:
1. How do I display excel charts in the report as opposed to just the text lists. (If I created the chart in the template excel file, will it be created on the client?)
2. Do I need to install POI separately on my app server (websphere 4.0) in addition to Cocoon or does Cocoon include necesasry components from POI?
Thanks again.
Kamlesh
- Displaying Excel Charts
2003-04-11 16:20:15 Steven Punte [Reply]
Kamlesh,
Excel has great facilities for charts.
One would use them in exactly the same
manner for a simple stating excel file.
However, IF you have an unknown number
of results (i.e. an unknown number of
rows of data that will be produced by
the sql query), then things are more
tricky.
I haven't attempted this, but I belive
using Excel "Named Cells" will properly
bridge between the two technologies.
Cocoon comes with the poi library.
Steve Punte
- Displaying Excel Charts
- New: Tomcat Installation Direction from War file
2003-02-19 18:47:11 Steven Punte [Reply]
INSTALLATION FROM WAR (Tested so far with Tomcat 4.1.12)
0. Download war file from http://candlelightsoftware.com/downloads/cocoon-reports.war
1. Place cocoon-report in ~jakarta-tomcat-4.1.12/webapps/cocoon-reports.war
2. Start up Tomcat
3. Place browser URL to http://localhost:8080/cocoon-reports
You should now see selection for the two demo report.
This will also un-archive the war file so the additional
configuration and setup steps below can be performed.
4. Create MySql database table: mysql < ~/cocoon-reports/demo.sql on command line.
5. Adjust Cocoon Sitemap file:
5.1 Adjust URL for database if needed: see line 230 of site map file
5.2 Adjust line 237 of sitemap file (i.e. parameter "absolute-path"
to reflect your installation.
- New: Tomcat Installation Direction from War file
2004-02-24 10:18:38 Sri Karumuri [Reply]
Steve,
I have been using Tomcat/JSPs tech. for a while but relatively new to Cocoon. Si, I decided to try out your example reports for a specific project and got the following error message after step 3(I will paste only the appropriate part of it):
escription org.apache.cocoon.ProcessingException: Language Exception: org.apache.cocoon.components.language.LanguageException: Error compiling sitemap_xmap: Line 0, column 0: error: java.io.IOException: read error 1 error
stack-trace
org.apache.cocoon.ProcessingException: Language Exception: org.apache.cocoon.components.language.LanguageException: Error compiling sitemap_xmap:
Line 0, column 0:
error: java.io.IOException: read error
1 error
at org.apache.cocoon.components.language.generator.ProgramGeneratorImpl.createResource(ProgramGeneratorImpl.java:340)
at org.apache.cocoon.components.language.generator.ProgramGeneratorImpl.load(ProgramGeneratorImpl.java:292)
at org.apache.cocoon.sitemap.Handler.run(Handler.java:265)
at java.lang.Thread.run(Thread.java:534)
Caused by: org.apache.cocoon.components.language.LanguageException: Error compiling sitemap_xmap:
Line 0, column 0:
error: java.io.IOException: read error
1 error
Sorry if I missed the obvious.
Sri
- New: Tomcat Installation Direction from War file
- Demo Software Note
2003-02-14 10:10:29 Steven Punte [Reply]
To All Readers:
I have seen an enormous amount of downloading
of the demo software, but have only heard
back from a few people.
Note that this soulution is non-trivial in
that:
a) You need to already have a J2EE app
server and the Cocoon platform up
and going.
b) The demo utilizes a relational database,
and to date I've only tested it against
MySql.
Thus, given these various needs and hooks,
it may take a little finess to get it
operational.
However, with that said, the advantage of this
solution is that it is easily mutable to
fit a wide variety of situations. As a matter
a fact, the who reason I created it was that
none of the off the shell solutions quite
fit in to what I was doing.
In summary, it seems like everyones needs of
a report system are slightly different, and
thus canned solutions often don't fit in.
This soultion can be easily molded.
Steve Punte
- XML & Excel reporting
2003-02-12 08:18:40 Leo Cohn [Reply]
I use the Office web components, consuming an XML recordset, to build pivot tables on the web page with IIS.
Can I do this with your approach?
Can I host the page with Excel in a <<browser>> and not just on the desktop?
Great project, by the way.
- XML & Excel reporting
2003-02-14 10:01:21 Steven Punte [Reply]
Leo:
The primary rending pipeline can be
rearranged to just consume XML instead
of obtaing the data from a database.
Then, you would need to perform the
equivalent of your piviot transformation
in XSL.
The download demo I've provided DOES emitt
the reults over HTTP into preferrably
IE which results in MS Excel being brough
alive IN the browser.
Steve
- XML & Excel reporting
2003-08-12 11:10:20 Dhriti Dave [Reply]
Hello Steve,
This article is really helpful.
I have one xml file and I want to generate excel from that. I did email about this before and I also got a satisfied answer from Andrew Olivier.
There is one line in this article:
"XQuery statements could be used against an XML database or SOAP web services over the net"
how can I use XQuery against XML? any example?
I am confused.
Thanks,
Dhriti Dave
Fidelity Investments
PH: 617.392.0179
- XML & Excel reporting
- XML & Excel reporting
