1

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.

subodh
  • 337
  • 2
  • 6
  • 18

2 Answers2

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

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.

Leonid Vysochyn
  • 1,254
  • 1
  • 7
  • 12
  • We actually generate a few 100 reports on each run, the template used is not very complicated just 3-4 tabs and some jx:each commands in each of them. I have reverted back to the previous version of jxls for now will come back and look at the new version when I get time later. – subodh Aug 15 '15 at 04:33
0

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));
      }
}
Mike Samaras
  • 376
  • 2
  • 13
  • It's a very simple spreadsheet with no formulas, but has a lot of rows. – Mike Samaras Sep 08 '15 at 14:43
  • I was running StressXlsxDemo with 500000 rows and it worked fine finishing in about 2 minutes. But this demo has only a few columns so the overall processing size is not that big (~21Mb result file). There should be enough heap size defined though (this example requires 4Gb). – Leonid Vysochyn Dec 29 '15 at 20:35
  • Overall for such big sizes one should use SXSSF transformer to reduce the memory requirements – Leonid Vysochyn Dec 29 '15 at 20:36