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)
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.getCharSetto 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