Hello Kabbi,
Hopefully, you have remedied this issue, but if not, there is
a solution. It may seem overwhelming, but once you do it, you'll
see how easy it is.
The only requirements will be Java and Apache's POI which you
can get here:
http://www.apache.org/dyn/closer.cgi/jakarta/poi/
POI is an API to Microsoft formats.
Unzip the file and place the JAR file under {cfusion
home}/lib and restart the ColdFusion server. That's pretty much it
for the hard part.
Now, we'll move on to making the actual Excel file;
This is the only call you'll make to Apache's POI. This will
create the main workbook object.
<cfset workbook =
createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()
/>
Create a sheet for the workbook:
<cfset sheet = workbook.createSheet() />
You'll need to give the sheet a name so POI will know which
one to work with:
<cfset workbook.setSheetName(0,"Name it whatever you
want") />
Start a new row, cell and set the cell name.
<cfset row = sheet.createRow(0) />
<cfset cell = row.createCell(0) />
<cfset cell.setCellValue("Cell One") />
Think of the Cells and Rows as an array starting with 0 and
stopping at however many cells/rows you need. We'll do a few just
for kicks.
<cfset second_row = sheet.createRow(1) />
<cfset second_cell = row.createCell(1) />
<cfset second_cell.setCellValue("Cell Two") />
<cfset third_row = sheet.createRow(2) />
<cfset third_cell = row.createCell(2) />
<cfset third_cell.setCellValue("Cell Three") />
<cfset fourth_row = sheet.createRow(3) />
<cfset fourth_cell = row.createCell(3) />
<cfset fourth_cell.setCellValue("Cell Four") />
<cfset fifth_row = sheet.createRow(4) />
<cfset fifth_cell = row.createCell(4) />
<cfset fifth_cell.setCellValue("Cell Five") />
<cfset sixth_row = sheet.createRow(5) />
<cfset sixth_cell = row.createCell(5) />
<cfset sixth_cell.setCellValue("Cell Six") />
Now, we'll begin adding our data to the new Excel sheet.
<cfloop query="YourQuery">
<cfset row = sheet.createRow(currentrow) />
<cfset cell = row.createCell(0) />
<cfset cell.setCellValue(QueryColumnName) /> <!---
The values will be the actual names of your columns --->
<cfset second_row = sheet.createRow(currentrow) />
<cfset second_cell = row.createCell(1) />
<cfset second_cell.setCellValue(QueryColumnName) />
...
<cfset sixth_row = sheet.createRow(currentrow) />
<cfset sixth_cell = row.createCell(5) />
<cfset
sixth_cell.setCellValue(dateformat(DateColumnForFun,"yyyy-mm-dd"))
/>
</cfloop>
Now, we'll need to call Java's file output stream class so we
can write the Excel file to disk.
<cfset fos =
createobject("java","java.io.FileOutputStream").init("NameThisFile.xls")
/>
The actual writing of the file to disk:
<cfset workbook.write(fos) />
And finally, close the Java FOS to free up some memory... or
something like that.
<cfset fos.close() />
I know this may look like a lot, but it's really not. It's
pretty simple after the first time you do it.
I hope this helps... or is at least somewhat close to your
original question.
Defuse
8
Replies
AdChoices