Aug 29, 2014

POI Compatibility Issues with ColdFusion


In this post I will discuss with Issues coming with the latest POI 3.10 in ColdFusion 9/10..

Recently I worked on Import/Export features of Excel documents in my project. Moreover, this excel documents are too much complex with Multiple trigger and rich with Macros.

In result, I faced lots of Issue in reading such files and faced n no of errors with POI.

Let's discuss How ColdFusion reads excel sheets  ?

What is POI ?
- POI is open source java library used to read Microsoft documents.

Why POI in ColdFusion ?
- ColdFusion ships with POI to read Excel sheets. CF also ships with Open Office, by default excel read/manipulation handle by POI. If you want to use Open Office then Configure local/remote Open Office directories under CF Admin. ColdFusion 10 comes with POI 3.6 jar files.

POI jar files present under lib directory of ColdFusion: (Below jar files comes up with POI 3.10)
  1. dom4j-1.6.1.jar
  2. poi.jar
  3. poi-ooxml.jar
  4. poi-ooxml-schemas.jar
  5. poi-scratchpad.jar
  6. stax-api-1.0.1.jar
  7. xmlbeans-2.6.0.jar
Now let's discuss some errors which I have got while reading those XLS/XLSX files using ColdFusion 10. 

Some Errors while reading those XLS/XLSX files with ColdFusion 9/10 using inbuilt tags/function available in ColdFusion :
  • (with XLS) java.lang.NullPointerException at org.apache.poi.hssf.model.LinkTable.getExternalBookAndSheetName  
  • (with XLS)Duplicate PageSettingsBlock record (sid=0x89c) 
  • (with XLS) Unexpected celltype (5)  
    • This error may comes when you have some dirty data or cell value starting with #.
      But everything fine in my case.
  • (with XLS) coldfusion.excel.ExcelProcessException: An exception occurred while using action=read. NullPointerExceptioN.
    • There was nothing wrong in my cfspreadhseet syntax
  • (with XLSX) The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. Is it really an excel file?
    • Some of the XLSX files are failing
  • (with XLSX) The Supplied spreadsheet seems to be encrypted .xlsx file. It must be decrypted before use by HSSF

What I have tried so far with inbuilt Tags/Function available in CF :    Sucess/Failure ?
  • As all issues are pointing towards POI issue. So I download the latest version of POI 3.10 and extracted all jar files and put it under \lib directory.

    Success: 6/5 issues with 100% success rate with  XLS solved using updated POI except encrypted error coming up with some XLSX file.

    Adverse effects (Failures):  
    • Formatting rows/columns/cells of Excel Sheets with styles like fonts and color are not working and throw an error "Java: no Method error with fonts and color".
      • Cause: ColdFusion expecting Font.getCharSet to return a byte, while the latest version of POI returns an int. Stackoverflow thread
    •  Some of the XLSX file which was easily readable using POI 3.6 is now unreadable and through
      "org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet.getColsList()Ljava/util/List
      java.lang.NoSuchMethodError"
In the next post I will discuss some workaround for it.