In CF 5 there should be an option of 'Excel' when you define
a data source. In CFMX since it uses JDBC you must first create a system ODBC
connection, then in CFMX set it up as an ODBC bridge connection. One that is in
place you can query the excel file like this:
<cfquery name="queryname" datasource="excelDSN">
select * from `sheet1$`
</cfquery>
A main thing is that the quotes around the sheet name MUST BE LIKE THIS: `
and NOT LIKE THIS: '. To make a JDBC connection to excel, first setup a MS Access datasource (doesn't matter if it has any tables)
<cfquery name="qryXLSdata"
datasource="myAccessPassthrough">
SELECT *
FROM [Sheet1$]
IN 'c:\path\to\file.xls' 'EXCEL 5.0;'
</cfquery>
this is a pretty good work around for the lack of dynamic connect strings in CFMX. it works for other file types as well... CSV, DBF, XLS, MDB.Because recent Excel products support HTML table format, getting ColdFusion to generate and Excel file can be as simple as creating your HTML tables and then using the <cfcontent> tag to set the mime type of your newly generated Excel file.
The following code sample taken from the CF 7 documentation shows a sample of using <cfheader>
and <cfcontent> to push a dynamic Excel file to the browser (prompt the user whether to save Excel
the file or open it in a browser).
<cfheader name="Content-Disposition" value="inline; filename=acmesalesQ1.xls">
<cfcontent type="application/vnd.msexcel">
<table border="2">
<tr><td>Month</td><td>Quantity</td><td>$ Sales</td></tr>
<tr><td>January</td><td>80</td><td >$245</td></tr>
<tr><td>February</td><td>100</td><td>$699</td></tr>
<tr><td>March</td><td>230</td><td >$2036</td></tr>
<tr><td>Total</td><td>=Sum(B2..B4)</td><td>=Sum(C2..C4)</td></tr>
</table>
Learn about excel specific styles like mso-number-format (dd\/mm\/yyyy,....)
Recent Comments