Excel Charting in Java

Ahh, Excel...  I hate it.  I find it rather unintuitive, to be honest.  No, no no...that is not quite saying enough.  I find it nearly impossible to use.  Business folk however, love it.  In every company that I have worked for, every user group I have served has, eventually, asked for something to be output to Excel.  Of course, for us Java folk, there is good old Apache POI.  Now, POI is pretty good.  I've used it in a number of projects and have not had any stability issues.  Performance is generally acceptable when dealing with reasonable amounts of data.  It does however, have a good number of limitations.  A large subset of the Excel functions are still unimplemented and there is almost no support for charts (To be specific: charts will be preserved but cannot be altered).  This is often a big stumbling block.  Furthermore, building spreadsheets with method calls is not a very efficient process for the developer.  Creating just a single spreadsheet can require a lot of code.

This is the problem I found myself up against recently.  In searching for a possible solution, I came up with a couple of really useful finds.  The first of which was jXLS.  jXLS is an excel templating framework of sorts.  It allows a designer to insert tags into a template spreadsheet which will be interpreted by jXLS and populated appropriately.  The tags utilize an expression language very similar to the EL used in JSPs or XSLT.  Developers who are comfortable with either of these technologies should feel right at home with jXLS.

Basically, you can create an excel sheet and throw in some expressions like so:



Then you run in through jXLS with something similar to:

Map beans = new HashMap();
beans.put("item", items);
XLSTransformer transformer = new XLSTransformer();
HSSFWorkbook results  = transformer.transformXLS(Thread.currentThread().getContextClassLoader()
    .getResourceAsStream("daysToProvideInstructions.xls"), beans);

As you can see by the fact that we get a HSSFWorkbook back from this method call, jXLS is based around POI, but it does the having lifting for the data insertion.  If you want to shove that file back out via a HttpServletResponse, you don't need much:

response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename=warrantyAgingReport.xls");
results.write(response.getOutputStream());

Put all this in your web app and you'll get:



So, jXLS gives me a nice and easy solution for populating a spreadsheet with data...but, we still cannot edit the ranges of any tables that we create.  Furthermore, if we want functions to be evaluated when the spreadsheet is opened, we have to evaluate them in POI, right?  Named Ranges to the rescue!  It just so happens that we can define a dynamic range in Excel and give it a name.  We can then use those named ranges in charts, functions, etc.  This results in a those items being dynamically evaluated and gives us the results we want.  So following Micro$oft's own instructions on the matter, we can create a couple of ranges in our template spreadsheet like so:



Using those ranges in a chart gives us the following:



Now we have the dynamic Excel charts we need, generated via our Java web application with very little excess effort.  So, while I still hate Excel...today I hate it from Java just a little less. :)

Austrailian Echoes

Would you like a fork, sir?  It looks like the Echo2 community has finally made that option a reality with the Cooee project.  Cooee (an Austrailian "Over here!",  of sorts) is being put forth by Karora, an open source group with routes in Melbourne.  The "openness" of the Echo2 project under the guidance of NextApp has been questioned by the user community for some time now.

I am, personally, torn on this issue.  I empathize with the issues raised by Karora.  I think that the leadership of the Echo2 project have given the community the impression of being forsaken or, worse yet, irrelevant.  However, I am still a little unsure of the timing.  Echo seems to be on the verge of a fairly major release and I can't help but wonder if waiting for that code to surface might have been worth it.  What is done is done, however and Karora can always spend some time merging code. :)

I want to wish the best of luck to the core Karora team in gaining acceptance as well as momentum.  A little competition never hurts...

Blogged with Flock

Work, school and other such obstacles...

So, school, work, and life have gotten in the way of posting recently.  Summer classes are always time consuming.  I am working on a couple of different tutorial ideas right now.  I've got SCRUM, JavaFX and the Swing Application Framework in my sights at the moment.  SCRUM is almost ready to go, I can decide on the App Framework or JavaFX to follow it up though.  I've begun work on both, but hit the breaks when everything else picked up.

To those of you reading, my apologize for the delay.