POI 3.5 beta 4 - Supports Excel 2007 / XLSX Format

I was pleased to discover there is a new beta version of POI that supports the Excel 2007 xlsx format. The beta version introduces a new SS UserModel that supports both HSSF (xls files) and the new XSSF packages (xlsx files).

While I have only tested some of the updated examples in the Busy Developers' Guide to HSSF and XSSF Features, so far I am pleased. The syntax for most basic tasks seems the same. Except you now use the new XSSF classes when working with xlsx files. For example, using the JavaLoader.cfc, you simply change the workbook class:

<cfscript>
...
// XLS workbook
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();

// XLSX workbook
wb = javaLoader.create("org.apache.poi.xssf.usermodel.XSSFWorkbook").init();
</cfscript>

There are also some new helper classes that provide generic ways of performing common tasks with both formats. To open an existing workbook (of either format) you now use the new WorkBookFactory class. It automatically determines the file format, and returns a workbook object of the correct type:

<cfscript>
// Use the JavaLoader.cfc to read in either an XLS or XLSX workbook
WorkbookFactory = javaLoader.create("org.apache.poi.ss.usermodel.WorkbookFactory");
inp = createObject("java", "java.io.FileInputStream").init( pathToFile );
wb = WorkbookFactory.create(inp);
</cfscript>

Another new convenience class is CreationHelper. It is a useful class that lets you generically create objects (like RichTextStrings, HyperLinks, ecetera) . Meaning you can use the same code with both formats. The CreationHelper is accessible from any workbook object. Just call the new method: workbook.getCreationHelper().

<cfscript>
...
createHelper = workbook.getCreationHelper();
textString = createHelper.createRichTextString("this is a richTextString");
</cfscript>

There are obviously some differences in the formats. So I imagine certain areas will need separate code for each format. But there also some improvements in the new XSFF packages. For example, the old way of creating cell comments was a bit "klunky". The new XSFF method is much simpler. As described in the documentation, each Sheet has a list of its comments, and they can be added much like other cell properties.


Testing the Updated Examples
To get familiar with the new version, I tested many of the updated examples in the Busy Developers' Guide. For the most part, the new code worked with either format. Usually, a simple change of file extension and workbook class was all that was needed. However, I did run into a few issues.

To test compatibility, I used the Office 2007 converter to view all xlsx files. So some of the issues may have been due to problems with the converter. Though I would not rule out my own inexperience with the new XSSF packages either ;-)

Examples - Busy Developers' Guide to HSSF and XSSF Features

Code worked with both formats

• How to create a sheet
• How to create cells
• How to create date cells
• Working with different types of cells
• Getting the cell contents
• Aligning cells
• Working with borders
• Fills and color
• Merging cells
• Working with fonts
• Reading and writing
• Create user defined data formats
• Set page numbers on the footer of a sheet
• Shift rows
• Set the zoom magnification for a sheet
• Repeating rows and columns
• TextExtraction (Update 02/07/2009) Using the ExtractorFactory, the same code for both formats
  

Issues/Differences

• How to create a new workbook - Code worked with both formats. Though an error occurred unless one or more sheets was added to the new workbook.

• Text Extraction - Required HSSF and XSSF specific extractor objects

• Custom colors - Required HSSF and XSSF specific color objects

• Use newlines in cells - Code works with both formats. However, "\n" did not seem to work with CF, so #chr(10)# was used instead

• Fit Sheet to One Page - Code works with both formats. But the "Fit to One Page" setting did not work when the XLSX file was opened with the Office 2007 converter

• Set print area for a sheet - Code works for both formats. But "Print Area" did not seem to work for XLS files
  

• Create split and freeze panes - Code works for both formats. But the "pane" was not visible in Sheet 4, when the XLSX file was opened with the Office 2007 converter
  

• How to set cell comments - Required HSSF and XSSF specific code. Also, the Office 2007 converter would not open XLSX files with comments

I am still learning about the new format, so comments and corrections are welcome.

Related Links:

1. How to install the POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc
2. Download the Updated CF examples