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. :)