2

I have some problems when I use JXLS to do the excel file export. It is really slow to transform the param list to the excel file when the map size is larger than 5000. Is there any faster solution?

This is the code:

private Workbook generateWorkbook(String templateFilePath, Map<String, Object> contextBeans) {
    try {
        InputStream is = new FileInputStream(templateFilePath);
        XLSTransformer xlsTransformer = new XLSTransformer();
        return xlsTransformer.transformXLS(is, contextBeans);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }
    return null;
}

It takes 7 seconds to generate the Workbook object where the size of contextBeans >5000

Aezio
  • 203
  • 5
  • 18
  • You can try a `BufferedInputStream` and not sure if that time is long. If it is a large file, large objects in your map and your template uses a lot of files it might take some time. – M. Deinum Oct 14 '16 at 06:45
  • @M. Deinum Thank you,I tried but nothing changed.My template just have a '' tag to loop the map,I guess that maybe the map is too large to cost so much time while doing the loop thing. – Aezio Oct 14 '16 at 07:08
  • 1
    Which jXls version are you using? You might want to try at least the most recent version. You are currently also creating a sheet in memory, try a file system one instead (might save memory allocation and gc cycles). – M. Deinum Oct 14 '16 at 07:23
  • @M.Deinum I use [jxls-core 1.0.6] [jxls 2.3.0] now and how to use the file system you said? Sorry I didn't understand. – Aezio Oct 14 '16 at 08:05
  • Write the file to the filesystem, instead of using an in memory one (as you currenlty do). I suggest you do some reading on jxls (also seems like you are combining 2 versions, although not sure haven't worked wth jXLS before just reading the docs). – M. Deinum Oct 14 '16 at 08:24
  • I would use some profiling tool to find out where exactly time is spent, otherwise it's mostly guessing what could be the reason. Then you can either adjust your code or report a bug-report if it is something that can be improved inside one of the third party pieces that are involved. – centic Oct 17 '16 at 08:11
  • @centic I have tried the way you mentioned and also found the highest time-cost method is `xlsTransformer.transformXLS(is, contextBeans)` – Aezio Oct 18 '16 at 01:17
  • That is just the top-level method that you call, you likely need to drill down a bit further to see where the time is actually spent in there. A result usually is a calltree with times for each node. – centic Oct 18 '16 at 14:14
  • @centic Yes,you are right.But the method of the Implements are in a jar file.Maybe I should change my way. – Aezio Oct 19 '16 at 01:17
  • Try a tool like Dynatrace of JVisualVM, they will show where time is spent without needing the sourcecode for all parts. – centic Oct 19 '16 at 05:54
  • @centic thanks for your advice :) – Aezio Oct 19 '16 at 07:29

1 Answers1

0

for jxls 1.x, decrease the if logic in template will improve the performance when data is big.

for jxls 2.x, its performance is better than 1.x.

frank
  • 1,169
  • 18
  • 43