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.