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