How do I return a query from an Excel file?
There are two three ways of doing this. The first one is using dynamic datasources, where you can define a dsn in the ColdFusion Administration, and then override the location of the file in runtime. This works for Access and Excel, and it's well explained in the Adobe site.
The second option JDBC drivers directly, and this is a good time to remind everyone about the ColdFusion Cookbook, which is yet another project that Ray's leading. It's explained here.
JIC you want to know what it looks like, here's a UDF.
<cffunction name="getExcelSheet" access="public" output="false" returntype="query">
<cfargument name="filename" required="true" type="string" />
<cfargument name="sheetName" required="true" type="string" />
<cfscript>
var c = "";
var stmnt = "";
var rs = "";
var sql = "Select * from [#sheetName#$]";
var myQuery = "";
arguments.filename = expandPath(arguments.filename);
if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
try{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
stmnt = c.createStatement();
rs = stmnt.executeQuery(sql);
myQuery = CreateObject('java','coldfusion.sql.QueryTable').init(rs);
}catch(any e){
// error-handling code }
}
return myQuery;
</cfscript>
</cffunction>
<cfargument name="filename" required="true" type="string" />
<cfargument name="sheetName" required="true" type="string" />
<cfscript>
var c = "";
var stmnt = "";
var rs = "";
var sql = "Select * from [#sheetName#$]";
var myQuery = "";
arguments.filename = expandPath(arguments.filename);
if(len(trim(arguments.filename)) and fileExists(arguments.filename)){
try{
CreateObject("java","java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
c = CreateObject("java","java.sql.DriverManager").getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.filename );
stmnt = c.createStatement();
rs = stmnt.executeQuery(sql);
myQuery = CreateObject('java','coldfusion.sql.QueryTable').init(rs);
}catch(any e){
// error-handling code }
}
return myQuery;
</cfscript>
</cffunction>
The third option is to use Sean Corfield's Java CFX tags, part of the OpenXCF project. I couldn't find extensive documentation, but it looks pretty straight forward:
<cfx_ExcelQuery action="read" file="#xlsfile#" variable="myQuery" />
<cfx_ExcelQuery action="write" file="#xlsfile#" query="myQuery" />
<cfx_ExcelQuery action="write" file="#xlsfile#" query="myQuery" />
