ColdFusion and Excel 2007 Integration
Bookmark and Share
 

Posted by Rahul in ,

There has been a lot of resources available already for ColdFusion and Excel Integration but yet I couldn't find anything to make use for my requirement of processing xlsx files.
This gave me an opportunity to role my own version, using POI which primarily has the following features :

  • Support for xlsx files i.e. it can consume files created with Microsoft Excel 2007
  • Support for reading large files using the POI Event API than the normal user API which is limited by the memory

The creation of Excel documents which is done using HSSF and XSSF APIs for xls and xlsx documents respectively, can still be an issue as the memory requirement of files is so large that it can completely overwhelm the server and whilst this is not really the fault of the API - the BIFF8 file structure demands that the complete file be built in memory before it can be saved to disk.

I used POI 3.5 beta 4 which has support for new Office Open XML file formats, such as XLSX and PPTX introduced with Office 2007.

I am using using the JavaLoader Utility to load the POI 3.5 and also the custom jar used to read Excel document. . Refer this post to find to how to install POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc

This is not a pure ColdFusion solution and wrote a custom library to

  • utilize the POI Event API library for processing
  • make the utility also available for Java program

Importance of using java for the utility is much as ColdFusion natively doesn’t support batch processing and I feel that in case we are using this utlity to consume large data it makes more sense to load the processed data using batch. Though we can leverage JDBC batch processing in ColdFusion we could just do the same directly in Java without involving ColdFusion.

The installation instruction are pretty simple which have been added to the Readme.txt file. All the details about the usage are also in the Readme.txt file.

I would like to thank Ben Nadal whose POIUtility.cfc is the base of almost the complete write Excel functionality which I tried to use as is and adding support for creating Office 2007 (xlsx files).

I am naming this utility as ExcelProcessor and is still in development. Please let me know if anybody is interested in the source code and can contribute in improving the same. I would like to hear feedback, suggestions, comments from people who take the opportunity to try it.

Download ExcelProcessor (zip)

Note : If you receive a error java.lang.ClassNotFoundException: org.apache.xerces.parsers.SAXParser while running ExcelReader just inlcude Xerces parser in the classpath

5 comments

chaitanya sagar- Excel Expert   says 4/09/2009 10:09 AM

I have done as you said mate, it worked pretty well, you have done good job. I am looking for more of such useful posts.

office   says 4/14/2009 10:11 PM

We are running CFMX 7, in looking over the code, it should work for CFMX 7, unless I missed something, right?

Rahul   says 4/14/2009 10:17 PM

Yes it should work on CFMX7, you might just need to make a few syntactical changes introduced with CF8 like structure creation, refactoring loops etc

Henweigh   says 11/27/2009 1:33 AM

Thanks for creating this however I've been struggling with installing this to see if your utility can read my large excel files.

I followed your instructios to the best of my ability by downloading POI 3.5 final (not in beta any more) and I placed it in your customlib folder.

I created a custom mapping to javaloader in the CF administrator.

However when I attempted to run your sample readExcel.cfm I get a java error message:

java.lang.NoClassDefFoundError: org/openxml4j/opc/Package
at com.adobe.www.excel.XLSXReader.process(XLSXReader.java:45)
at com.adobe.www.excel.ExcelReader.processFile(ExcelReader.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at coldfusion.runtime.java.JavaProxy.invoke(JavaProxy.java:87)
at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2272)
at cfExcelProcessor2ecfc800959545$funcREADEXCEL.runFunction(C:\inetpub\wwwroot\ExcelProcessor\ExcelProcessor\ExcelProcessor.cfc:20)
etc...

Did I install it incorrectly? Or is there a step or 2 that I'm missing? Thanks for your help with this.

Henweigh   says 11/27/2009 1:35 AM

Hello,
Thanks for creating this utility but I'm having a real nightmare trying to install it and getting it to work.

I followed your installation instructions as best as I could:
1.Downloaded POI (not in beta anymore so I downloaded 3.5 final)
2.Extracted everything in your zip file to wwwroot
3. Created custom mapping in Administrator to JavaLoader
4. Copied JOI to the customlib folder

When I run readexcel.cfm I get the following error message:

java.lang.NoClassDefFoundError: org/openxml4j/opc/Package
at com.adobe.www.excel.XLSXReader.process(XLSXReader.java:45)
at com.adobe.www.excel.ExcelReader.processFile(ExcelReader.java:24)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at coldfusion.runtime.java.JavaProxy.invoke(JavaProxy.java:87)
at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:2272)
at cfExcelProcessor2ecfc800959545$funcREADEXCEL.runFunction(C:\inetpub\wwwroot\ExcelProcessor\ExcelProcessor\ExcelProcessor.cfc:20)
etc...

Did I get something wrong in the install? Or is there something else I need to do to make this work?

Post a Comment

Newer Post Older Post