Notice: With the launch of Adobe Cookbooks, this site will no longer be accepting new entries or posting new content. Thanks to everyone who submitted content!

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 at 11:08:03 AM EST.

CFML Referenced

<cfscript>
<cffunction>

Categories

Database / SQL, File and Directory Access

Comments

Comment made by Martijn van der Woud on October 13, 2006 at 1:59 PM
"NOTE: To use the below solution, your ColdFusion server must allow you to create Java objects. Many shared hosts do not allow this."

An alternative is to use the Microsoft Jet Engine to redirect a query from an existing MS Acces datasource to an excel file on the server.

Ben Forta discusses this possibilty in chapter 27 of his book "Advanced ColdFusion MX 7 Application Development".

For an example, download the zip file from http://www.forta.com/books/0321292693/0321292693_27.zip and open "ReadExcel.cfm"

Note: the comments in the example and the text in the book state that the "proxy" datasource must be empty, but I have tried this with a non-empty datasource and it works fine.

Comment made by Kyle Hayes on October 18, 2006 at 6:34 PM
Yes, this is indeed true and was a solution that I looked at when I came across my issue. However, my department is phasing out all use of Access databases and using only Sql Server and Oracle databases. Thanks for the note.

Comment made by Raul Riera on November 2, 2006 at 12:32 AM
Anyone else getting this error?

Context validation error for tag cfscript. The start tag must have a matching end tag. An explicit end tag can be provided by adding </cfscript>. If the body of the tag is empty you can use the shortcut <cfscript .../>.

Comment made by Raymond Camden on November 2, 2006 at 9:49 AM
Ensure you cut and pasted right. I do see a closing cfscript in the tag above.

Comment made by Raul Riera on November 2, 2006 at 10:00 AM
I did, twice... still the same error

Comment made by Raul Riera on November 2, 2006 at 10:05 AM
Hmmmm nevermind that "//" comment command was making the } commented as well... works fine now.. thanks

Comment made by Raymond Camden on November 2, 2006 at 10:10 AM
Ah - missing } at the end. I'll edit it now.

Comment made by Dustin on November 7, 2006 at 5:55 PM
How do you get the output of the function into a query so you can return individual columns?

I can see the output in cfdump, but keep getting a "Complex object types cannot be converted to simple values" whenever I attempt to return the query.

Comment made by Michael on March 7, 2007 at 1:27 AM
Has anyone found out a way to discover what the name of the Sheets are before I query one? It would be nice to display to the user first.

Comment made by sLive on September 10, 2007 at 2:23 PM
Thank you for that!

http://fikra.sohbetlive.com http://www.birakma.com http://www.ircmedya.com http://www.aniden.net http://www.sohbetlive.net http://www.alpella.net http://www.ucundan.com http://www.yerlifm.com http://www.laklaka.net http://zurna.sohbetlive.com http://www.youtubesitesi.com