Performance problems after upgrading to JXLS 2.2.3

Question

I recently upgraded to the new version of JXLS (from 1.0.2) and found that it caused a significant performance hit. My use case is writing excel reports based on a template. Previously, the reports were written out in a matter of 4 minutes, but now it takes more than 60 minutes to write out. I am using the

JxlsHelper.getInstance().processTemplate(is, os, context);

call to process the template (following the tutorial here). I tried using both the Transformers (poi and jexcel), but neither gave me the performance that the previous version of JXLS did.

Wondering if anyone else has had a similar experience or has resolved a similar performance issue after upgrading.

Answer 1

In general case jxls-2 should perform better than jxls-1. Possibly you have some specific template where it is not true. If you could share it or create an example we could investigate the root cause of the issue. You can create the issue at bitbucket issue tracker.

Besides that check the following examples in jxls-demo project which output many data rows

• org.jxls.demo.StressXlsxDemo - generates 30000 employees in about 6 seconds
• org.jxls.demo.StressDemo - generates 30000 employees in 1 second using POI SXSSF

SXSSF transformer is the fastest if you need to generate millions of records but it works only with simple templates.

Also if you have a lot of formulas in your template try to disable formula processing and see if it helps. You can do it with setProcessFormulas(false) method of JxlsHelper.

If nothing helps just file an issue in issue tracker.

Answer 2

For tiny spreadsheets (30,000 rows) it's great.

I'm struggling with v2.2.5 with a spreadsheet that has over 1 million rows across all sheets. I'm using: setProcessFormulas(false) and expect < 60 seconds. It seems to spin forever and I haven't seen one complete.

Even though I'm using an InputStream I see very little cpu usage as if its operating only on a single thread. I've used other xls writing strategies that have handled much larger spreadsheets without much issue so it would appear to be something internal.

protected void workbookToStream(Map beanParams, OutputStream os) throws IOException {
      try(InputStream is = new ByteArrayInputStream(templateBytes)) {
         JxlsHelper helper = JxlsHelper.getInstance();
         helper.setProcessFormulas(false);
         helper.processTemplate(is, os, new Context(beanParams));
      }
}