MXimize by Carlos Gallupa, thoughts about and experiments with coldfusion, css, sql, components and javascript

Reading Excel 95 files with Coldfusion, part I

First of all, let me introduce myself since I hadn't done so in my first post.

Hi, I'm Jax, a friend of Tjarko, and I have been a Coldfusion user for the last decade or so. I am also a fairly experienced Java programmer.


I  am currently working on a project that involves a bit of data harvesting from various sources.
One of the data sources is an Excel file hosted on ( or actually: generated by) another webserver. So I needed something that could read and parse a true Excel file (so not a csv file with an xls extension). It would be nice if it could also use the Excel file straight from the web as it's source, without me having to locally save the file first.

Coldfusion installs with a version of the POI libraries which can handle all this just fine.  And to top it off Ben Nadel wrote a utility component that interfaces this library.
"Great !' I thought, 'Someone else already did all the dirty work (Thanks Ben!), and all I have to do is write a few coldfusion lines and I'm off!'.  A good thing, because data harvesting is only a small part of the the project I'm working on and I had other, bigger fish to fry.

I started testing with his component but I couldn't succesfully parse any of my Excel files.  As it turned out POI does not support Excel '95 (or older) files. And my datasources where all, you guessed it, Excel '95 files.

So... back to the drawing board it was. I did some research and came across the JExcelApi which could handle Excel '95 files.

Hence, the JExcelApiUtility.cfc was born.


    <cfcomponent displayname="JExcelApiUtility" output="false" hint="Utility class, interfacing the JExcelApi. Reads Excel files.">
   
    <cfscript>
    // START: access through setters and getters   
    variables.workBookLoaded = false;
    variables.workBook = "";
    // END: access through setters and getters
    </cfscript>

    <cffunction name="init" access="public" output="false" returntype="JExcelApiUtility" hint="Constructor">
        <cftry>
            <cfset variables.workBook = createObject("java","jxl.Workbook")> <!--- default constructor will suffice --->
            <cfcatch type="object">
                <cfthrow message="Could not create Instance of JExcelApi (jxl.WorkBook): #cfcatch.detail#" >
            </cfcatch>
        </cftry>   
        <cfreturn this>
    </cffunction>   
   
    <cffunction name="loadWorkBook" access="public" returntype="void" output="false" hint="Reads an Excel file.">
        <cfargument name="excelFile" type="any" required="true" hint="path to excel">
        <cfset var aFile = "">
        <cfset variables.workBook = "" >
        <cfset setWorkBookLoaded(false)>
             <cftry>
                 <cfset aFile = createObject("java","java.io.File").init(arguments.excelFile)>
                 <cfset variables.workbook = variables.workbook.getWorkBook(aFile)>
                 <cfset aFile.close()>
                 <cfset setWorkBookLoaded(true)>
                 <cfcatch type="any">
                     <cfthrow message="Could not load workbook with arguments: #arguments.excelFile#,   detail: #cfcatch.detail#" >   
                 </cfcatch>
             </cftry>   
        <cfreturn>
    </cffunction>

    <cffunction name="isWorkBookLoaded" access="public" output="false" returntype="boolean">
        <cfreturn variables.workBookLoaded>
    </cffunction>   
   
    <cffunction name="setWorkBookLoaded" access="private" output="false" returntype="void">
        <cfargument name="workBookLoaded" required="true" type="boolean">       
        <cfset variables.workBookLoaded = arguments.workBookLoaded>
    </cffunction>

    </cfcomponent>
NB. 

1. Allthough the above code works, it is far from complete. The next parts in this series will feature a more usuable version, so stay tuned.
2. Working with this component requires you to have the jxl.jar installed in coldfusion's classpath. You can find the jar sitting in the zip download available from JExcelApi.sourceforge.net.

There are a few things worth noting here.

I created a setter and getter method ( in this case, the getter method is more of a convenience type) that will only tell us if the JExcelApiUtility object
holds a workbook or not. It's use will become more obvious in the next parts.

    <cfset aFile = createObject("java","java.io.File").init(arguments.excelFile)>
    <cfset variables.workbook = variables.workbook.getWorkBook(aFile)>
    <cfset aFile.close()>



I created an instance of java.io.File that is instantiated with the path to the Excel file. This instance ('aFile') will create a File object that the jxl.workbook instance can use to, in turn, return a workbook object. We can't simply feed jxl.workbook the path, because the path is an object of type String and jxl.workbook can't convert a string into a workbook. And while we are at it, you also can't initiate jxl.workbook with a cfhttp.filecontent either, because cfhttp.filecontent is of type java.io.ByteArrayOutputStream.

It will however accept an inputStream and that is what I will discuss in  part II.

8462 viewed | Your opinion...  | del.icio.us | Digg it | Jax @ 07/08/07 16:06 cet