Hopefully this post will help people who are experiencing performance problems with jExcel inside of a web application environment like Apache Tomcat, Websphere, and the like. The short version of the post is this: if you’re experiencing a lot of slow-down while jExcel is being called set this switch in your web-application environment: jxl.nogc=true. If you’re using Tomcat you’d modify the CATALINA_OPTS setting to be something like this:
CATALINA_OPTS=”…. -Djxl.nogc=true ….”
CATALINA_OPTS=”…. -Djxl.nogc=true ….”
Now, the full explanation:
I use jExcel to create Excel documents on-the-fly. It works really well, is simple to use, and is extremely fast; however, we were running into issues after creating the first Excel document. The time spent creating the Excel spreadsheets to be written increased more than seven times after the first spreadsheet document was created. Of course, this unacceptable, so I was tasked with finding the solution.
I loaded up the jExcel source code and began tracing through it figuring out how Excel documents are created within jExcel. While I was searching, I was sent the following tidbit of information by one of my coworkers who was looking at the high level and stumbled upon it through this website (I would have eventually found this bit comment, but I’m glad he did since it saved me a lot of time). The important bit of information is this in the WorkbookSettings class:
/**
* Flag to indicate whether the system hint garbage collection
* is enabled or not.
* As a rule of thumb, it is desirable to enable garbage collection
* when reading large spreadsheets from a batch process or from the
* command line, but better to deactivate the feature when reading
* large spreadsheets within a WAS, as the calls to System.gc() not
* only garbage collect the junk in JExcelApi, but also in the
* webservers JVM and can cause significant slowdown
* GC deactivated using -Djxl.nogc=true on the JVM command line
* Activated by default or by using -Djxl.nogc=false on the JVM command line
*/
The switch jxl.nogc was set to deactivate the manual call to Java’s garbage collection and the time spent setting up the Excel workbooks dropped by a factor of six from when we first tested the application. Without the nogc activated jExcel performs a full garbage collection after certain operations are done within the jExcel code. Which, as you can guess, full garbage collection while handling a request destroys any sort of quick response time that you might have been hoping for.
Cheers,
Ujjwal Soni