Populating An Existing, Formatted Excel Document Using ColdFusion And POI

If the title of this post is familiar, it's because I copied and pasted it from a post by Ben Nadel on Feb. 19. He has been building a Utility cfc that helps you use the included-with-ColdFusion java library that works with Microsoft Excel files (aka, Jakarta POI). I have recently been tasked with coming up with better excel export capabilities and read through his posts with great interest. Ben has done some great work. However, he has concentrated on straightforward query results, not much different from a basic table listing the data.

My assignment requires something more. For example, I need the ability to have header text - not just the top of the column, but a report title, date, etc. I also have to display results from multiple queries, some on one sheet, others on other sheets. So after spending some time following Ben's work, I was needing more. But the idea from his most recent POI related post - to take an existing formatted Excel file and populated it with your data is where I needed to go. And it seems this thought has been thought before.

Enter jXLS, a project by Leonid Vysochyn and others. Taken directly from the jXLS project page on Source Forge:  "jXLS is small and easy-to-use Java library for generating Excel files using XLS templates." The simple idea, as Ben had done, is to create an Excel file formated how you want it, including font settings, colors, borders, row heights, column widths, data formats, and you get the idea. In addition to the formatting, you also put in some simple tag based markup that jXLS will use to populate the Excel file. One tag example used to loop over an array of departments looks like so:
<jx:forEach items="${departments}" var="department">
 ${department.name} ${department.chief}
</jx:forEach>

Not too crazy to understand at a glance. Other capabilities include grouping and a conditional tag <jx:if condition>..output..</jx:if>. So how do you get started to make this work in CF? Well, you could do what I did and pour through the docs, sample apps and a java book (which I'm sure you could do, probably even faster than me). Or you could wait until my next post which will include a handy-dandy CFC that handles the communication with the jxls java package for you.

Bear in mind, however, I did have to install 2 java packages on my local CF server. Having never done this myself before, I did some googling and learned how simple it really is, provided you have root access to the CF install. I found my answer on a post by Christian Cantrell from 2004. The good thing is that they java packages are free and are under the GNU Lesser General Public License.

Java Utilities, Excel  |  Send
Posted 3/3/07 @ 6:42 AM by Matt Williams