Support for XSSFWorkbook and SXSSFWorkbook (xmlx format; POI 3.9+) [SPR-6898]

[spring-projects-issues]

Matteo Zardini opened SPR-6898 and commented

I think it's a good improvement if buildExcelDocument can use the new XSSFWorkbook class, XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
Many people are beginning to use this new format, this is a default with Office 2007.

---

Affects: 3.1.1

Attachments:

• AbstractPOIExcelView.java (2.75 kB)
• AbstractPOIExcelViewThreeWorkbookTypes.java (2.92 kB)

Issue Links:

• add support of xlsx format to JasperReportsMultiFormatView [SPR-12843] #17441 add support of xlsx format to JasperReportsMultiFormatView

Referenced from: commits 76cf5be

9 votes, 10 watchers

[spring-projects-issues]

Kevin Zogg commented

I found this issue while searching for a way to use XSSF with the built-in AbstractExcelView. After a while I decided to modify the AbstractExcelView, so that it would support both formats at once. The attached file is the result of my work with it.

Explanations:

• I removed some utility methods, because I felt like it would just not fit there. You have (at least I did) to write your own Util class anyway to keep the code clean and easy.
• I added the method 'createWorkbook' which you have to override. This is where you decide if you want HSSF or XSSF.
• Content type will be set automatically.
• I removed the whole template stuff. As the caller is now responsible to create the workbook, it's easy to create one based on a template file (new XSSFWorkbook(resource.getInputStream());)

I hope this helps someone. Feel free to change anything you want :)

[spring-projects-issues]

Rossen Stoyanchev commented

The current version of POI we build against 3.0.2, now almost 4 years old does not have XSSFWorkbook, so it would be a good time for an upgrade of that dependency.

[spring-projects-issues]

Al Scherer commented

Thanks, Kevin! I was looking for the same functionality and yours was a nice template. I'd like to see this get incorporated into the framework.

[spring-projects-issues]

Thomas Turrell-Croft commented

I'm trying to create a workbook with more than 65536 rows, this is not possible with HSSF. XSSF allows 1,048,576 rows.

[spring-projects-issues]

Fredrik Tuomas commented

I attached a version (based on Kevin Zogg's work) that also support SXSSFWorkbook.

[spring-projects-issues]

Juergen Hoeller commented

Based on your suggestions, I've introduced AbstractXlsView, AbstractXlsxView and AbstractXlsxStreamingView variants, designed to work as closely as possible with POI 3.8+'s arrangements. The traditional (pre POI 3.5) AbstractExcelView has been marked as deprecated, just like AbstractJExcelView already is.

I went with separate classes (in a hierarchy) primarily for minimum dependencies imposed, i.e. no poi-ooxml dependency needed when just using HSSF. As a side benefit, the content type can also be determined in the constructor then, and the SXSSFWorkbook.dispose call can be applied in a cleaner fashion.

This will be available in the upcoming 4.2 snapshot. Feel free to give it a try...

Juergen

[spring-projects-issues]

Juergen Hoeller commented

Note that AbstractXmlxStreamingView requires POI 3.9+ due to the pretty important SXSSFWorkbook.dispose() method only being available there.

We recommend the use of POI 3.10+ where we support close() calls for existing resources in a worksheet. We do that conditionally based on an instanceof Closeable check in order to preserve POI 3.5+ compatibility in AbstractXslView and AbstractXlsxView.

Juergen