export data into Excel

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