ColdFusion and Excel 2007 Integration

 

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)