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 !
https://www.facebook.com/achorniy
Developer Notes 
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?
Please ignore my previous comments. Everything works fine as expected. I added wrong configurations in xml mapping file. Thanks !!!
Thank you very much!!! It worked really well for me!!
[…] 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) […]
[…] 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) […]
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.
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 ?
It depends on what you need
you can write two tags
jx:if test="a>0"andx:if test="a <= 0"or if it is just a content in a single row user ternary oprator just inside the cell (JEXL expression)
"${a > 0 ? "GOOD" : "BAD"}http://commons.apache.org/jexl/reference/syntax.html#Operators
ok good, but also i need to hightlight the cell in different color, like if BAD – RED, GOOD – GREEN.
also does exist some way when i interate via collection to put some variable and assignt the value to it ?
i mean how in the best way hightlight the value in the red if it’s <= 0 ???
Ok, I think you missed the general JXLS documentation. The answer is simple – “highlight it in your Excel file template” and show/hide it conditionally with jx:if tag
see the examples from jxls http://jxls.sourceforge.net/samples/tagsample.html
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…..
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 ?
about this comment i read u previous article and now it works for me! Thanks
Not sure what you did, I think it should work with Jexl-2.0, you just need to use corresponding version of JXLS.
in JXLS-1.0-RC-2 pom.xml have dependency on commons-jexl 2.0.1
However my previous experience shows that JXLS-0.9.9 was faster (with previous jexl version) https://achorniy.wordpress.com/2011/02/14/faster-excel-export-jxls/
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
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.
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
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.
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.
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