How do I return a query from an Excel file?

NOTE: To use the below solution, your ColdFusion server must allow you to create Java objects. Many shared hosts do not allow this.

Java's JDBC ODBC allows you to connect to a Microsoft Excel file. Then using Java's SQL classes we are able to query this file and return certain tables to a ColdFusion query object.

This function returns the ColdFusion query object so that you may use the methodology of "Query of Queries" to return the data that you need.

A possible location for this function would be inside a CFC labeled MicrosoftUtil.cfc or something equally descriptive. 

<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>

To use the function simply use the following syntax: 

getExcelSheet("myFile.xls","productsSheet")

Where myFile.xls is a relative path to your Excel file and productsSheet is the name of the sheet inside the Excel file that you would like to return.

This question was written by Kyle Hayes
It was last updated on February 9, 2007.

Categories

Database / SQL
File and Directory Access

Comments

comments powered by Disqus