️This article has been over 2 years since the last update.

This article describes how to use a third library to generate Excel and profile the performance problems of POI Excel generation with JVisualVM.

Writing Data with spreadsheet

Here contains a list data I need to export with .xslx format.

1
2
3
4
5
6
7

[{
  "name": "prod1",
  "sku": [
    {"memory":"4G","size":"5.6"},
    {"memory":"8G","size":"6"}
  ]
}]

The data need to be converted like this

As saw above, a rowspan is required for one-many relationship object.

While in POI, the rowspan is implicated with addMergedRegion, I found a third party solution called spreadsheet on Github. The spreadsheet uses declarative language with Java8 DSL Closure which saves a lot of your time.

1
2
3
4
5
6
7

s.row(r -> {
       r.cell("Value 1");
       r.cell(c -> {
           c.value("Rows");
           c.rowspan(3);
       });
   });

Profiling slow POI Excel with JVisualVM

After developed, I found the cost of generation was too long. Now we can use JVisualVM to see why it was slow.

Just open the JVisualVM app, Select your application, and go to Profiler tab.

For IDEA users, you can install the plugin for better integration.

Click the Settings checkbox, and input the class to be filtered.

1
2

org.apache.poi.**,
builders.dsl.spreadsheet.**

Save the settings and click CPU to start the profile.

And then run your Java code(like a generation request from curl), you will find the Method execution time was estimated. Now you can see how your time is costly. In my code, I found the following method took much time.

1

org.apache.poi.xssf.usermodel.XSSFSheet.addMergedRegion

Then I searched addMergedRegion slow on Google, and found the answer here, and my slow problem solved by changing addMergedRegion to addMergedRegionUnsafe.

Conclusion

Profile with JVisualVM is easy

• input the class to get filtered.
• sort and find the most time of the method execution.
• Google/StackOverflow with ${methodName} slow