For those of us running on ColdFusion 8, Ben Nadel’s wrapper to the Apache POI classes are still the best way of working with native Excel files.
That said, the POI that ships with CF 8 is old … very old … 2004 old in fact and as such it has to be treated very gently and chokes on the slightest bit of Excel cleverness. The most common example of this that I’ve come across occurs when trying to import a sheet that has Auto Filter / Sort enabled. The Java layer explodes in a mess of unhandled exceptions and steadfastly refuses to read the file.
Fortunately for us, the POI development team didn’t consider this acceptable and proceed to fix this (and one assumes, a host o other bugs) in their later releases. And fortunately for us, ColdFusion’s happy co existence with Java classes mean we can upgrade the POI in place to take advantage of the features and fixes provided in the newer versions.
It’s simple enough to do:
- Jump onto the POI download site and grab the latest (3.6.20091214 at time of writing) binary in your preferred archive format
- Check the MD5Sums of the downloaded files (especially important if this is going onto a production server)
- Assuming they’re ok extract the files to a tmp folder
- Navigate to the lib directory of your ColdFusion install (e.g. c:coldfusion8lib or /opt/coldfusion/lib)
- Backup the existing POI files (poi-2.5.1-final-20040804.jar and poi-contrib-2.5.1-final-20040804.jar)
- Stop the ColdFusion service
- Delete the existing POI files (poi-2.5.1-final-20040804.jar and poi-contrib-2.5.1-final-20040804.jar)
- Copy the new files (poi-3.6-20091214.jar and poi-contrib-3.6-20091214.jar) into your lib directory
- Start the ColdFusion service
Assuming ColdFusion starts up correctly you should now be able to attempt opening those problematic Excel files and, hopefully, should have much more luck working with them.
ColdFusion 9
From what I can see the CF9 version of POI is much more current but is still not the latest release. You could also attempt this process on a CF9 server if you desperately needed a feature or fix that is only available in the latest POI release but bear in mind CF9 has much greater office compatibility out of the box so be very careful when upgrading.
As ever, this information is provided as is and without warranty. Always backup and test in development / staging before trying this on production kit!
any chance you encountered this error after upgrading?
500 org/apache/poi/hssf/usermodel/HSSFWorkbook (Unsupported major.minor version 49.0)
Hi Deake,
No I haven’t come across that error in either our dev or production servers. I can only suggest trying a slightly older version of the POI libraries (the whole archive is available for download) and see if that makes it more stable.
I upgraded the JVM and all is well now.. well almost! Getting a few of these errors but at least I know how to fix them!
…ColdFusion can’t decipher reliably. If this is a Java object and you verified that the method exists, you may need to use the javacast function to reduce ambiguity.
thanks for the helpful info on how to upgrade the POI environment. I thought I had been running 3.0 for years and only just found out I was running 2.5
Hi Deake,
Glad you’ve got it mostly working. I was amazed at just how many issues this upgrade solved and was equally amazed to see just how old the CF8 version of POI is!!! It was 3 years out of date when CF8 was released which is a bit embarrassing for Adobe.