Using jXLS and jXLSUtiltiy.cfc to export Excel files - multiple sheets

Just an update on using jXLS. In my last post about it I said I haven't yet attempted an Excel file with multiple sheets. Well I just did and it works with no further code changes on my end. You simply put additional tag markup in a second sheet and boom, it's there.

I definitely recommend this to anyone who needs to export complicated excel spreadsheets (i.e., anything more than just a simple list/query dump).

Using jXLS and jXLSUtiltiy.cfc to export Excel files

In my last post I described the what and why of jXLS. So now let's see what you have to do to use it. I mentioned you will need 2 java packages. The first is the jXLS package itself. The second is one that is required by jXLS, Commons JEXL. See the jXLS installation page for this. There are other required packages, but this is the only one I found I needed (by way of an exception before it was installed). You must also have POI 2.5.1 or higher. On my dev workstation I have CF 7 installed, and that is the version of POI that came with CF. To find out what version you have go to your CF Adminstrator, go to System Information and do a find on "POI". That should show you the .jar file that is installed, including the version number.

Once you've got those installed, grab my jXLS demo files and (see below) unzip them into a directory you can browse to. From there you can open the Exel template file. A partial screen shot can be seen below.

If everything is installed correctly, you should be able to run http://localhost/jXLSDemo/jXLS_ContactsUseCFC.cfm and create the output excel file. Look at the code in that file and the jXLSUtility.cfc to see what is going on. It's pretty straightforward. I also have working, but not included in this zip, a port of the Tags Sample that is included in the jXLS download from Source Forge. It uses Lightweight Transfer Objects instead of a query to generate the data and hashMap.

Next I hope to figure out how to do multiple sheets. I saw some method calls that should handle this, just have not yet attempted it. I also hope to further explore the API of jXLS, though this contacts example and the Tags Sample will get me pretty far in what I need.

Leave a comment if you have any questions. I hope someone else finds this useful. Based on the comments on Ben Nadel's Excel Export posts, there seems to be quite an interest in this type of functionality.

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.