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