Spring Framework
  1. Spring Framework
  2. SPR-6898

Support for XSSFWorkbook and SXSSFWorkbook (xmlx format; POI 3.9+)

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Complete
    • Affects Version/s: 3.1.1
    • Fix Version/s: 4.2 RC1
    • Component/s: Web
    • Labels:
      None
    • Last commented by a User:
      false

      Description

      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.

        Issue Links

          Activity

          Hide
          Kevin Zogg added a comment -

          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

          Show
          Kevin Zogg added a comment - 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
          Hide
          Rossen Stoyanchev added a comment -

          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.

          Show
          Rossen Stoyanchev added a comment - 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.
          Hide
          Al Scherer added a comment -

          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.

          Show
          Al Scherer added a comment - 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.
          Hide
          Thomas Turrell-Croft added a comment -

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

          Show
          Thomas Turrell-Croft added a comment - I'm trying to create a workbook with more than 65536 rows, this is not possible with HSSF. XSSF allows 1,048,576 rows.
          Hide
          Fredrik Tuomas added a comment -

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

          Show
          Fredrik Tuomas added a comment - I attached a version (based on Kevin Zogg's work) that also support SXSSFWorkbook.
          Hide
          Juergen Hoeller added a comment -

          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

          Show
          Juergen Hoeller added a comment - 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
          Hide
          Juergen Hoeller added a comment -

          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

          Show
          Juergen Hoeller added a comment - 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

            People

            • Assignee:
              Juergen Hoeller
              Reporter:
              Matteo Zardini
              Last updater:
              Juergen Hoeller
            • Votes:
              9 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                6 weeks, 1 day ago