Import and Export Data From Excel Using ColdFusion
page 2 of 5
by Debjani Mallick
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 48731/ 90

Reading data from an excel sheet
<cffunction name="readExcelSheet" access="public" 
  output="false" returntype="query">
      <cfargument name="fileName" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
      <cfscript>
            // Declaration of variables
            var objInst = '';
            var stmnt = '';
            var sheetData = '';
            var sql = "Select * from [#sheetName#$]";
            var qryData = QueryNew('');
 
            // Processing the excel sheet to read the data
            if(len(trim(arguments.fileName)) and fileExists(arguments.fileName))
            {
                  CreateObject("java", 
                    "java.lang.Class").forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  objInst = CreateObject("java", 
                    "java.sql.DriverManager").getConnection(
     "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & arguments.fileName);
                  stmnt = objInst.createStatement();
                  sheetData = stmnt.executeQuery(sql);
                  qryData = CreateObject('java', 
                    'coldfusion.sql.QueryTable').init(sheetData);
                  objInst.Close();
            }
            return qryData;
</cfscript>
</cffunction>

Explanation

 

In the above function, we have two arguments – fileName which is the absolute path to the excel sheet along with the excel sheet name and sheetName is the name of the worksheet present in the excel sheet whose data we want to read. In Java, all classes are defined, self-referentially, as the instance of the class java.lang.Class. In order to load the java.lang.Class instance of a particular Java class, the forName method of java.lang.Class can be used. Every class of Java maintains a ProtectionDomain which in turn maintains a CodeSource object pointing to the location from which code for the particular domain was loaded. ProtectionDomain performs the functionality of defining the security access to the domain to which the class belongs. In the above function, we are creating an instance of the JdbcOdbcDriver. Then we are creating an object instance to refer to the driver manager. All those lines of code are being executed with the purpose of connecting to the target datasource. For connecting to a database, we need two things to be done. First one is the loading of the driver which is accomplished by class.forName method. When we call this method, an instance of a driver is automatically created and registered with the DriverManager. So its not required to create an instance of the class. After the driver is loaded, it can used to make a connection to the DBMS. So now the second step in establishing a connection with the target database is to have an appropriate driver connect to the DBMS. The DriverManager works closely with the Driver interface in order to determine the drivers available to the JDBC client. When a connection is requested by the client, the DriverManager takes the responsibility of finding the driver that recognizes the URL provided by the client and uses it to connect to the corresponding data source. In the above function, the getConnection method is used to establish a connection. This method requires a 'connectionstring' which depends on the type of database to be connected. The two common examples of connectionstring are:

Microsoft SQL
 "jdbc:odbc:DRIVER={SQL Server};Database="dbname";Server=ip;", "username", "password"
 
 Microsoft Access
 "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\websites\folder\mydatabase.mdb;Uid=;Pwd=;" 

If the JDBC URL specified as the connection string in the getConnection method is recognized by one of the drivers loaded, then a connection is established by the driver with the DBMS specified in the URL. And here the DriverManager manages all the connection details. The connection returned by the getConnection method is an open connection which can be used to create JDBC statements which would pass the SQL statements to the DBMS. After the connection is made, we are executing the query. Now after the query is executed, how to return a ColdFusion query object from a Java class using a JDBC result set (java.sql.ResultSet). This can be accomplished by the use of the coldfusion.sql.QueryTable class. This class comes with ColdFusion MX and located in the cfusion.jar file. After the work is done, we are closing the connection by calling the close function.


View Entire Article

User Comments

Title: Mr.   
Name: John Glynn
Date: 2011-09-14 10:45:00 AM
Comment:
Thank you Debjani Mallick!
This is a terrific tutorial that worked right out of the box. I was wondering though, is there a way to return the table columns in a different order than alphabetical, order by current column order or something like that?

Thanks again!
John
Title: readExcelSheet   
Name: HP
Date: 2011-06-13 11:43:57 AM
Comment:
I got this error when trying to use the readExcelSheet function. NOt sure why I got this error. Any idea?
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Title: missing columns after read op'n   
Name: sooraj
Date: 2010-08-24 10:10:15 AM
Comment:
hi Debjani,
great stufff for cf'ers...
I have an interesting issue when I used ur functioanlity to read excel file, the problem is my file contyains 4 columns/fields and after upload and read operation, dumping shows only 3 columns...why this happens?

Any ideas..Is I am missing anything...

thanks
Title: Hi   
Name: Smith
Date: 2010-03-31 11:54:50 AM
Comment:
Yes waiting for it
Title: Where R ur Articles???   
Name: Abhinash
Date: 2010-03-31 10:57:19 AM
Comment:
Waiting for ue next big article
Title: Data not being written to new columns   
Name: Chuck
Date: 2010-01-14 3:49:00 PM
Comment:
We are trying method 2 and we are not writing data to new columns, instead its writing to a new line for every column. Also it's sorting the data in alphabetical order.
Title: instance, a FileOutputStream or ServletOutputStream   
Name: Lance
Date: 2009-06-17 3:00:53 PM
Comment:
Do have an example of how to use ServletOutputStream
Title: Reply: How do you specify sheetname   
Name: Debjani Mallick
Date: 2009-06-11 12:16:21 PM
Comment:
For specifying the arguments using the second write method, the syntax is:
writeExcelSheet('table name','datasource name','path to the excel sheet with sheet name');
This will create a sheet with the default name. Using the second write method, its first of all not possible to specify worksheet names and secondly, its also not possible to write to multiple tabs of an excel sheet through this method. But the first method specified, accomplishes both the tasks.
Title: How do you specify sheetname   
Name: jtansey
Date: 2009-06-11 9:19:14 AM
Comment:
So, in example two of writing to an excel worksheet, it says to specify the fileName as the full path along with the excelsheetname. I need to write to multiple sheets. How exactly do I specify the sheetname?
Title: Good one   
Name: Abx-68
Date: 2009-05-07 4:26:03 AM
Comment:
Good one!! Keep Going
Title: Helpful article   
Name: John
Date: 2009-04-22 11:15:24 AM
Comment:
Thank you for the article. It is really helping.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-08-25 3:12:39 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search