POI Excel 2007

[Cold_Drink]

I've been using the java POI built into coldfusion (CFMX7) to read excel files. I can read 93-2003 files fine but I can't read Excel 2007 files. Is there a way to update the apache POI that is built into coldfusion so that it can read Excel 2007 files?

[-__cfSearching__-]

I would not disturb built in classes. It might accidentally break something in CF. Try using the JavaLoader.cfc to load a newer version of the POI jar. Store it in the server scope to avoid memory leaks.

http://javaloader.riaforge.org/
http://www.compoundtheory.com/?action=displayPost&ID=212

[Cold_Drink]

Ok, thanks, so if I load a new apache POI jar but use the same java calls like this:

newWorkBook = CreateObject(
"java",
"org.apache.poi.hssf.usermodel.HSSFWorkbook"

How will Coldfusion know to use the jar from the javaloader rather than the POI built into ColdFusion?

Sorry if its a dumb question, just starting to use Java within coldfusion.

[-__cfSearching__-]

No problem. To create objects you would use an instance of the JavaLoader.cfc, instead of createObject.

When you create an instance of the JavaLoader.cfc, you pass in an array of jar paths. So the JavaLoader knows exactly which jars it should use.

javaLoader = createObject("component", "javaloader.JavaLoader").init(arrayOPathsToTheJarFilesYouWantLoaded)

Then when you need to create an object, use the javaLoader.create function, instead of createObject.

newWorkBook = javaLoader .create("org.apache.poi.hssf.usermodel.HSSFWorkbook")...

But as I mentioned earlier, you should store the javaloader instance in the server scope to avoid memory leaks.

[Kronin555]

POI can't read Office 2007 documents. Not even the newest version of POI. However, once you're on version 3 or newer, it at least throws a meaningful error message when you do try to read in a 2007-format file.

http://poi.apache.org

[-__cfSearching__-]

Kronin555,

I have not tried this with Office 2007 documents. So I will take your word for it 😉 But one of their pages says it does. It might well be a typo, but here is the mention of it

"HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format."
http://poi.apache.org/hssf/index.html

EDIT Hm. I may have incorrectly assumed Cold Drink was not talking about the OOXML file format. Because it clearly states POI does not support that.

[-__cfSearching__-]

Cold Drink,

Are you trying to read OOXML files, or just ones created in 2007 and saved in an older format? If it is the former, then obviously Kronin555 is correct: you cannot do this with POI.

[Cold_Drink]

Yeah, I'm trying to read a .xlsx Excel 2007 file. If I open an .xlsx file and save it in 97-2003 format I'm able to then read it using the POI. So, it looks like I'm out of luck trying to read .xlsx, unless you know of a way to convert an .xlsx as an .xls without using the Java POI (other than opening excel and saving the file)?

[-__cfSearching__-]

You may be out of luck for now. I do not think any of the open source projects (POI, JExcel, ..) can read this new format yet. I have seen mention of reading 2007 using OLEDB, which might be a possibility here. Though I have never tried it myself. So I could not say if that would work.

[klmanion]

This thread is old, but I notice that Apache now has a version of POI that's compatible with Excel's OOXML (.xlsx) file format.  Support for this appears to have started with version 3.5 from what I can tell.  I am running CF8 with Java version 1.6.0_4.  Does anyone know if upgrading to the latest version of Java will give me access to the newer POI jar files that support Excel 2007?

I'm trying to use Ben Nadel's POIUtility.cfc to read Excel 2007 (xlsx) files, but I'm getting the dreaded "Object Instantiation Exception" exception because of this issue.  Any advice would be greatly appreciated.

Thanks,

Kevin

[klmanion]

Looks like I found the information I needed in Dirk's post on th

is thread of Ben Nadel's site:  http://www.bennadel.com/blog/624-ColdFusion-POIUtility-cfc-Updates-And-Bug-Fixes.htm

[-__cfSearching__-]

>> Looks like I found the information I needed in Dirk's post

>> "... and of course you should remove the old poi class

>> files from /lib ..."

Just be aware that CF uses the POI jars too. So it is possible you could break something by swapping out the jars.

Another option is to use the javaLoader to run both versions side by side.

http://javaloader.riaforge.org/

[klmanion]

Thanks for the advice.  I'll look into using javaloader.

[klmanion]

Great advice cfSearching!  Following these steps, I was able to successfully read an Office 2007 xlsx file into ColdFusion:

I used your instructions at http://cfsearching.blogspot.com/2009/02/how-to-install-poi-35-beta-on.html to install the latest Apache POI 3.7 files and then use JavaLoader to load them into the Server scope.  Then I updated Ben Nadel's POI Utility as follows:

changes in POIUtility.cfc in function ReadExcel()
replace these lines:
LOCAL.ExcelFileSystem = CreateObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).Init( LOCAL.FileInputStream );
LOCAL.WorkBook = CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").Init(LOCAL.ExcelFileSystem);

with this:
LOCAL.FileInputStream = CreateObject( "java", "java.io.FileInputStream" ).Init( ARGUMENTS.FilePath );
javaLoader = server[application.myJavaLoaderKey];

LOCAL.WorkBookFactory = javaLoader.create("org.apache.poi.ss.usermodel.WorkbookFactory");
LOCAL.WorkBook = LOCAL.WorkBookFactory.create(LOCAL.FileInputStream);

Thanks again for the help!!!

[-__cfSearching__-]

LOCAL.WorkBookFactory =

Great! Good use of the WorkBookFactory .. and thanks for posting the code.

-Cheers

Leigh