Microsoft Jet

Microsoft Jet, the database engine commonly associated with Microsoft Access, provides ColdFusion developers the ability to read and write data from and to Excel files as if they were databases. This means Excel files can be manipulated using techniques and code that all ColdFusion developers are already familiar with.

To begin using the Jet technique, we'll need a data source. The Administrator can create a data source to a single Excel file within the ColdFusion Administrator using the ODBC Socket driver. However, that will not fulfill most developers' requirements for reading and writing to multiple files with the filename provided at run time. ColdFusion 5 did include this capability through dynamic connection strings, but with the migration to a Java code base, that option had to be removed. The workaround in ColdFusion MX 7 is to create a data source to an empty Microsoft Access database and then tell Jet to redirect the command to an Excel file within the <cfquery> tag. Here is an example:

 <!---   "proxy" is a datasource pointing to an empty Microsoft Access database ---> <cfquery name="excelData" datasource="proxy">   SELECT Salesperson, SalesAmount   FROM "Excel 8.0; DATABASE=#tempFile#; HDR=YES".[Sales$] </cfquery> 

Notice the FROM clause. This format tells Jet to redirect the command to an Excel document. Here is a breakdown of the clause:

• Excel 8.0 specifies that we're reading from an Excel file. The version number should be 8.0, which corresponds to Microsoft Excel 2000, even if versions 2002 or 2003 are installed.

• DATABASE= provides the filename of the Excel document. This must be the full path to the document.

• HDR=YES specifies that the first line in the document represents headers for each column. These headers are used as field names.

• [Sales$] is the name of the worksheet that contains the data. This can also be a named range or a cell range.

Review Listing 27.3 for a complete example that processes an uploaded Excel document.

Listing 27.3. ReadExcel.cfmProcess an Uploaded Excel File

 <!---   Filename: ReadExcel.cfm   Purpose: Reads data from an uploaded Excel file and displays it on screen   Requires: A datasource "proxy" pointing to an empty Access database ---> <!--- accept an uploaded Excel file ---> <cfset tempFile = GetTempFile(GetTempDirectory(), "xls")> <cffile   action="upload"   fileField="fileName"   destination="#tempFile#"   nameConflict="overwrite"> <!--- select data using standard query with special FROM clause ---> <cfquery name="excelData" datasource="proxy">   SELECT Salesperson, SalesAmount   FROM "Excel 8.0; DATABASE=#tempFile#; HDR=YES".[Sales$] </cfquery> <!--- headers for our data table ---> <table border="0" cellpadding="0" cellspacing="0">   <tr>     <th>Sales Person</th>     <th>Sales this period</th>   </tr>   <!--- loop through and output the data --->   <cfoutput query="excelData">     <tr>       <td>#excelData.salesPerson#</td>       <td>#excelData.salesAmount#</td>     </tr>   </cfoutput> </table> 

NOTE

The example code can be tested as-is against the "files/Sales.xls" Excel document included with this chapter. Using against other Excel files may require modifying worksheet and field names.

Although this technique can work very well in some situations, particularly when the developer has full control over the Excel source file, it is also very prone to user error. In Listing 27.3 we accepted a file uploaded by the user and are depending on the file's having the correct worksheet nameSalesand the correct field namesSalesPerson and SalesAmount.

Data types are another issue that can become a problem when reading from Excel files. Excel is not inherently a database and as such does not provide fields of data but rather individual cells of data. The result is that when we map individual columns to query fields, the Jet driver must identify the correct data type to use. By default, Jet will scan the first eight rows of data in order to determine the data type. However, if the first eight rows of data happen to have data that is not consistent with the rest of the column, then an incorrect mapping is made and data will be lost. The data is lost because when Jet encounters a mismatch after it has determined the data type, the mismatched value is returned as NULL. The number of rows to scan can be customized via the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows Registry entry.