Creating Excel reports from java is easy


Yeah, things sometime become really easy if you choose right tools and approach.
I’ve to create relatively complex Excel report which is not “die-hard” task itself (you can use Apache-POI or JExcel API)
But creating the formatting with Apache-POI API is really boring and it is too time consuming task. Since project was a JBoss-Seam based I also have an option to create it with Seam-Excel-support (btw, Seam use JExcel inside).  Much better then just a POI-API – you can define your Excel template in JSF template. Good enough, but still too much dancing around formatting is needed.

So, I decided to try to use JXLS which I use before for relatively simple reports.
JXLS allow you to define your dynamic Excel template right in Excel file, so you can just take an report example with required formatting and insert iteration tags (it also have condition tags). JXLS use JEXL as expression language so we can insert quite complex expressions and call java methods on objects.
So, everything works fine, I provide the data to JXLS, write output to OutputStream and was able to update formatting on the fly without any coding.

The only thing make me unhappy – my report require generation of dynamic sheets but this feature just failed in the latest JXLS release (0.9.8). It throws ClassCastException during sheet copy operation java.lang.ClassCastException: org.apache.poi.hssf.util.CellRangeAddress cannot be cast to java.lang.Comparable
It looks like nobody check this feature (never run this code) since CellRangeAddress is NOT Comparable. As I got from inspecting the code in subversion it was refactored to work with new POI API and TreeSet was used as a container for CellRangeAddress (maybe just mechanic error)
So, that sad issue may be easily fixed if all TreeSet instances created in the “net.sf.jxls.util.Util.java” will be changed to HashSet

    public static void copyRow(HSSFSheet sheet, HSSFRow oldRow, HSSFRow newRow) {
        //TreeSet --> HashSet
        Set mergedRegions = new HashSet();
    }
    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow) {
        //TreeSet --> HashSet
        Set mergedRegions = new HashSet();
    }

    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, String expressionToReplace, String expressionReplacement) {
        //TreeSet --> HashSet
        Set mergedRegions = new HashSet();
    }

You can apply that patches on your own, by downloading jxls with sources and call “mvn clean install” from source code folder.

Another issue I’ve found about JXLS – is perfomance, on my reporting example (which has about 32 sheets) template processing was quite long (about 3-5 seconds) which is not good if your reports are frequently generated. I have a plan to profile and fix that performance issue in my free time.

Happy templating with JXLS, it’s really good tool and this article is my attempt to support that library and the efforts which JXLS developers put on it.
Thank You Guys !

Advertisements

https://www.facebook.com/achorniy

Tagged with: , , , ,
Posted in Software Development, Tips and Tricks
21 comments on “Creating Excel reports from java is easy
  1. Anil sadineni says:

    Hi andrey,

    thanks for the post. I am trying to use jxls in my grails app. When I try to read data from xls file using groovy objects with jxls I am not getting data back. Am I missing something? Any ideas?

  2. Anil Sadineni says:

    Please ignore my previous comments. Everything works fine as expected. I added wrong configurations in xml mapping file. Thanks !!!

  3. Albert says:

    Thank you very much!!! It worked really well for me!!

  4. […] I got a question about the jXLS performance, in short it was like “jXLS is a kind of slow for my big reports”. I mentioned that in my previous post (Creating Excel reports from java is easy) […]

  5. […] I got a question about the jXLS performance, in short it was like “jXLS is a kind of slow for my big reports”. I mentioned that in my previous post (Creating Excel reports from java is easy) […]

  6. The article is very helpful! Thank you, I’ll be sure to use your advice. Personally, I had some problems converting excel files over to PDF. I got so annoyed from manually moving the information (such as commission reports, invoices, etc.) over to another document, but then I found this program at my work that quickly and painlessly converts my excel documents into PDF and ready to go templates and reports, saving me aggravating hours upon hours. It provides my business a simple way to make individualized statements for our business associates, taking our invoices and seamlessly placing the information in the awesome templates. Check it out here.

  7. Hi, i using the JXLS for generating report by template. I use the tags in the template. But i can’t find the tag else! bcs i need to put the value to cell depending on some parameter like:

    good

    but if flag = false, i need to put BAD!!! how can i do it ?

  8. Andrey, i didn’t miss docs by templates. I’m using template and highlight the cell using template. Try to explain as:

    u have a table :

    HEADERS: PX_LAST1 | PX_LAST2 | PX_LAST3

    ${trade.pxLast1}${trade.pxLast2}${trade.pxLast3}

    i need to lighr all negative in the red and positive in green! if i do it with jx:if i’ll miss the columns in the header;like

    0}”> lightGreen<jx:if test="${trade.pxLast1 lightRED so on…..

  9. also if i use jxls+jexl 1.0 “${a > 0 ? “GOOD” : “BAD”} – doesn’t support! if i try to use jxls + jexl 2.0 they are incompartible with each other!

    What to do ?

  10. Mayank says:

    Hi Andrey,

    I have following requirement :
    I want to use Excel template functionality for exporting information from our application.Basic idea is that we will have pre-defined excel templates present on server machine. When client hit the server for downloading data in Excel format we should use some API to read the predefined template and then create temp file on server using template and data selected by user .Then this temp file can be used to download at client’s machine.

    Basic need for Excel Export is:
    1) A excel template should be defined which application will read on run time using some api.
    2) We will use api to update the template as save its copy with different name as a Temp excel file.
    3) We will read this Temp file using Java IO api to get the FileInputStream which should be passed to open file on client machine.

    Can JXLS help us achieve this requirement? I have tried using Moyosoft’s Java Excel Connector (JEC) but it supports for only standalone system.We deploy our project as Service using Java Service Wrapper.Due to limitation in JEC we am looking for another alternative for implementing this requirement.

    Please suggest best possible option for Excel Export using Templates.

    Regards,
    Mayank

    • Andrey Chorniy says:

      I think everything that you described can be done. I’m not sure if you really need to save your template as temp-file, I just generate the XLS-file on the fly and write it directly to HttpServletRequest output-stream. But of course you can save to the file if you need that.

      • novarse says:

        Hello Andrey,
        Could you please tell me how you generate the XLS-file on the fly because I want to write it directly to the HttpServletRequest output-stream?
        Thanks
        Stephen

    • hung says:

      I think that using the library simreport in jsimreport.com help you to solve your issues. It can generate reports based on predefined template or generate at run-time. You can see more details in some examples in jsimreport.com.

  11. hallowen says:

    I see that POI library or other libraries requiring you many time to make a report, ’cause you take many time to config and understand many concepts involved, write many code lines. I get used with library in jsimreport.com and feel it is easy to make an excel report. try it.

  12. tom says:

    I’ve developed many projects which generate many excel reports. I’ve used many libraris, tools to make reports. And at last I’ve found that simreport library in jsimreport.com satisfies me a lot. it is easy to start and easy to make a report

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: