|
Import and Export Data From Excel Using ColdFusion
|
by Debjani Mallick
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
36851/
61
|
|
|
Introduction |
In many web-applications development, we come across the
requirement of importing data from tables present in database to excel sheets
and vice-versa. This is basically needed when we are migrating an already
existing application, where we would need the already existing data of the
application to be stored somewhere and then to be imported into database tables
of the new version of the application. Another scenario is when we provide the
end user of an web application to download some details or history, like
transaction history. In such cases, we can have the data saved to an excel
sheet. Though ColdFusion is a tag based language, and we have tags for
performing many complex functionalities without knowing any lower level details
for those, but there is no direct tag to perform the above operations. There
are many solutions to tackle the above issue. One of the easiest solution has
been provided by Ben Nadel, the POI Utility. The POI Utility is a ColdFusion
component that contains functions to read excel sheets into queries thus
making the data compatible with the ColdFusion, and to write database tables
data in the form of queries to excel sheets. This utility provides with the
flexibility to populate multiple worksheets of an excel sheet, thus allowing
the storage of all the required data in one excel sheet instead of multiple
excel sheets. Using this utility, its also possible to provide styling
specifications for the excel sheet data to be written from database table data.
To take all those advantages, we need to download the POI Utility into our
application. But what when we want to perform the above operations without
downloading the POI Utility? The answer to this question is that we have many
other alternatives to perform the above operations. Here I would be describing
some of the alternatives in detail.
|
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.
|
Writing to an excel sheet (method 1) |
<cffunction name="writeExcelSheet" access="public" output="false" returntype="void">
<cfargument name="tableName" required="true" type="string" />
<cfargument name="dsnName" required="true" type="string" />
<cfargument name="fileName" required="true" type="string" />
<cfargument name="sheetName" required="true" type="string" />
<cfscript>
var getRecords = '';
var workBook = '';
var newSheet = '';
var listOfColumns = '';
var count = 0;
var index = '';
var row = 0;
var cell = 0;
var fileOutStream = '';
</cfscript>
<cfquery name="getRecords" datasource="#arguments.dsnName#">
select * from #arguments.tableName#
</cfquery>
<cfif getRecords.RecordCount>
<cfscript
workBook = createObject>("java",
"org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
newSheet = workBook.createSheet();
workBook.setSheetName(0, "#arguments.sheetName#");
listOfColumns = "#getRecords.columnList#";
count = 0;
</cfscript>
<cfloop list="#listOfColumns#" index="index">
<cfscript
row = newSheet>.createRow(0);
cell = row.createCell(count);
cell.setCellValue(index);
count = count + 1;
</cfscript>
</cfloop>
<cfloop query="getRecords">
<cfscript
row = newSheet>.createRow(#getRecords.currentRow#);
count = 0;
</cfscript>
<cfloop list="#listOfColumns#" index="index">
<cfscript
cell = row>.createCell(count);
cell.setCellValue(getRecords[index][currentRow]);
count = count + 1;
</cfscript>
</cfloop>
</cfloop>
<cfscript
fileOutStream = createObject>("java",
"java.io.FileOutputStream").init("#arguments.fileName#");
workBook.write(fileOutStream);
fileOutStream.close();
</cfscript>
</cfif>
</cffunction>
Explanation
For writing to an excel sheet, in the above function, we
need four arguments – tableName which is the name of the table from which we
need to extract the data and save to the excel sheet, dsnName is the datasource
name for connecting to the database where the table is present, fileName is the
full path along with the excel sheet name where the data is to be written, and
sheetName is the name of the worksheet to be created inside the excel sheet. In
the above function we are making the use of the Apache POI model. The POI
project is used for developing pure Java ports of file formats based on
Microsoft's OLE 2 Compound Document Format which is used by Microsoft Office
Documents. This project contains APIs such as HSSF and XSSF responsible for
reading and writing Microsoft Excel 97-2007 and OOXML spreadsheets using (only)
Java. HSSF is the POI Project's pure Java implementation of the Excel
'97(-2007) file format which provides different ways to create, modify, read
and write XLS spreadsheets. As it can be seen in the above function, we are
using the usermodel package of HSSF which maps HSSF low level structures to
familiar workbook/sheet model. Worksheets are created by creating an instance
of org.apache.poi.ss.usermodel.Workbook.
org.apache.poi.hssf.usermodel.HSSFWorkbook creates a concrete class directly.
After creating an instance of Workbook, sheets can be created by calling
createSheet method from the Workbook instance. The created sheet automatically
adds itself in sequence to the workbook. To provide a sheet name to the sheet
created using CreateSheet method, we are taking the help of setSheetName method
which takes three arguments – first one is sheetindex (which in our function is
0, which means the first index or first sheet), second argument is the sheet
name that we want to give, and third one is the encoding which for HSSF is 8bit
per char by default. After creating the instance of the sheet, rows can be
created by calling createRow which takes one parameter which is the row number.
Cells within a row can be created by calling createCell method which takes the
column number and the type. It should be kept in mind that only rows that have
cell values should be added to the sheet and cells that have values should be
added to the row; cells should also have their cell type set to either
Cell.CELL_TYPE_NUMERIC or Cell.CELL_TYPE_STRING depending on whether they
contain a numeric or textual value. To set the value of a cell, setCellValue
method can be called with either a string or double as a parameter. After the
workbook has been generated, it can be written out by calling
write(outputStream) from the instance of workbook created, or by passing it an
OutputStream (for instance, a FileOutputStream or ServletOutputStream). Since
HSSF does not close the OutputStream by itself, in our function we are
explicitly closing the connection by calling the close function. Reading can
also be done by using POI. For this, first we need to create a new instance of
org.apache.poi.poifs.Filesystem, passing in an open InputStream, such as a
FileInputStream for the excel sheet, to the constructor. Then we need to
construct a new instance of org.apache.poi.hssf.usermodel.HSSFWorkbook passing
the Filesystem instance to the constructor. From there we can access all of the
high level model objects through their assessor methods like getSheet, getRow,
etc.
|
Writing to an excel sheet (method 2) |
<cffunction name="writeExcelSheet" access="public"
output="false" returntype="void">
<cfargument name="tableName" required="true" type="string" />
<cfargument name="dsnName" required="true" type="string" />
<cfargument name="fileName" required="true" type="string" />
<cfscript>
var getRecords = '';
var listOfColumns = '';
var tabChar = '';
var newLine = '';
var index = '';
var value = '';
</cfscript>
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getRecords" datasource="#arguments.dsnName#">
select * from #arguments.tableName#
</cfquery>
<cfif getRecords.RecordCount>
<cfsetting showdebugoutput="false">
<cfset listOfColumns = getRecords.columnList>
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<cfsavecontent variable="value">
<cfloop list="#listOfColumns#" index="index">
<cfoutput>#index##TabChar#</cfoutput>
</cfloop>
</cfsavecontent>
<cffile action="write" file="#arguments.fileName#" output="#value#" >
<!--- output query data using cfloop & cfoutput --->
<cfset value = ''>
<cfsavecontent variable="value">
<cfloop query="getRecords">
<cfloop list="#listOfColumns#" index="index">
<cfoutput>#getRecords['#index#'][CurrentRow]##TabChar#</cfoutput>
</cfloop>
<cfoutput>#NewLine#</cfoutput>
</cfloop>
</cfsavecontent>
<cffile action="append" file="#arguments.fileName#" output="#value#">
</cfif>
</cffunction>
Explanation
In the above function, we need three arguments – tableName
which is the name of the table from which we need to extract the data and save
to the excelsheet, dsnName is the datasource name for connecting to the
database where the table is present, fileName is the full path along with the
excelsheet name where the data is to be written. This uses all the ColdFusion
functions to write to an excelsheet. Here we are simply querying the database
and if we have records, we are setting the first row of the sheet with the
column names of the table and then we are looping over the records, and
creating one row for each record. We have used cfsavecontent tag to first save
the contents in a variable and then we are writing the value of the variable to
the sheet. The above method is much simpler and has an easy to understand code
than that for the first method.
|
Conclusion |
After reading this article, I hope that you must have become
comfortable with the requirement of data import and export to excel sheet in
applications. According to our convenience we can make use of any method that
we want in our application to import and export data.
By Debjani Mallick
|
|
|
|
Product Spotlight
|
|