Export to Excel (as a REAL excel file)

Thanks very much. That works for me. Now that I have a working example of how to implement styles, I think I'll be okay.

Thanks for all your assistance with this. I'm still having problems making the style work though. Here's the code I'm trying. Everything works except the parts where it involves style:

<cfset workbook = createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init() />
<cfset aqua = createobject("Java", "org.apache.poi.hssf.util.HSSFColor$AQUA").init() />
<!---Create a sheet for the workbook:--->
<cfset sheet = workbook.createSheet() />
<cfset workbook.setSheetName(0,"birthdates") />
<cfset row = sheet.createRow(0) />
<cfset first_cell = row.createCell(0) />
<cfset objstyle = workbook.createCellStyle() />
<cfset objstyle.setFillBackgroundColor(aqua.index) />
<cfset first_cell.setCellValue("Fleet") />
<cfset first_cell.setCellStyle(objstyle) />
<cfloop query="dategrid">
<cfset row = sheet.createRow(currentrow) />
<cfset first_cell = row.createCell(0) />
<cfset first_cell.setCellValue(fleet) />
</cfloop>
<cfset fos = createobject("java","java.io.FileOutputStream").init("e:\NameThisFile.xls") />
<cfset workbook.write(fos) />
<cfset fos.close() />

I think I have the syntax correct to interpret from cfscript to cfset, and I don't get any error at all on my page. But the background color simply isn't there, like the code is being ignored or something. Does the version of Excel make a difference? We're using Office 2007 here.

Agreed.

Gotta love those nested classes. Conversely, you can probably also replace the call to HSSFColor.AQUA.index with a hardcoded "49":

http://poi.apache.org/apidocs/constant-values.html#org.apache.poi.hssf.util.HSSFColor.AQUA.index

It would go something like this:

<cfscript>
// Create Color object (to reference static properties)
objColor = CreateObject("Java", "org.apache.poi.hssf.util.HSSFColor");
// assuming wb workbook object has already been created
objStyle = wb.createCellStyle();
objStyle.setFillBackgroundColor(objColor.AQUA.index);
objStyle.setFillPattern(objStyle.BIG_SPOTS);
// assuming row object has already been created
objCell= row.createCell(JavaCast("int",1));
objCell.setCellValue("X");
objCell.setCellStyle(objStyle);
</cfscript>

Would you be able to help and convert a couple lines of Java code for me? Only a couple to help get me going. After I have the workbook created, and the sheet, and I have a query populating the rows and cells, what is the proper CF syntax for these lines? If you're able to help, thank you very much.

// Aqua background
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);

The code you are looking at is JAVA code. Using Java code in CF can be a little tricky at first, but once you get used to it, it actually seems pretty simple. I bet you can find some good resources if you check google for Java in CF.

The core thing to understand is that if you are loading in java objects into CF, you need to create them using either CreateObject("Java", "myOrganization.myPackage.myClass") or <cfobject>

Also, keep in mind that most java files import other objects / libraries and then treat them as local objects (i.e. they don't neccessarily prefix them with their entire "address"). In addition, in order to call an objects constructor (the java function that initializes most objects), you would replace myJavaObject() with myJavaObjectInCF.init()

I.E.,
JAVA
===========
HSSFWorkbook wb = new HSSFWorkbook();

CF
===========
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()>

Learning how to leverage existing java code in CF is probably one of the most important skills to learn as a CF developer. It drastically increases (by many orders of magnatude) the amount of pre-built code you have at your disposal. Personally, I like to keep my code as much in CF as I can, but its hard to ignore Java projects like iText and Jakarta POI that take an almost impossible task in CF and reduce it to a few lines of implementing Java Code.

I'd rather create a REAL Excel file as well. I can create the file no problem, but I'd like to know how to style the cells using POI and HSSF. I checked out quite a lot of the docs from the POI site, and found some very helpful information on HSSF, but when it came to implementing the code examples into Coldfusion, it didn't recognize the code at all. I don't think the examples are setup for using with Coldfusion, or maybe the files have to be placed in a certain location or something. For example, here's some style code from the HSSF examples:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Aqua background
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);
// Orange "foreground", foreground being the fill foreground not the font color.
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell = row.createCell((short) 2);
cell.setCellValue("X");
cell.setCellStyle(style);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
But I had to change the syntax to create the sheet from wb.createsheet to workbook.createsheet, and then I kept getting an error that the HSSFColor variable was in error.
I basically need help understanding how to implement the code inside Coldfusion.

For anyone else following this thread, the post I was talking about is here:

http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=7&threadid=1172038

pelican,
take a look at the first message you got your POI code from. I just wrote out a description of how to use the <cfcontent> method for creating and serving an excel file from CF. If you are uncomfortable using the POI java code in CF, you can create an "excel" file using HTML Tables (or XML) + <cfcontent>. The reason I put "excel" in quotes is because what you actually create is an HTML table that excel knows how to open and treat as a normal file. This is the way most people normally do this sort of thing (CF to Excel) but because my initial post was about importing an excel-formated file into another 3rd party piece of software I needed the file to be in the actual excel file format.

One (of the many) advantages of using the HTML Table method is that you can specify colors and styles and excel will maintain them when you import the data.