Export to Excel (as a REAL excel file)

[insuractive]

I've been exporting data to excel for ages now, so I know the drill - build the data as an HTML table or MSOFFICE XML,set the <cfcontent type="application/vnd.ms-excel"> and then output the result. At the moment, I am trying to generate an excel file to import into another application (Microsoft MapPoint, to be exact).

The problem seems to be that MapPoint won't recognize the generated file as being an excel file. My guess is its because the file is not an excel file, per se, but a format that excel recognizes and can convert into a spreadsheet. If I load the CF-generated file into excel and then save as a new file, then the new file imports just fine into MapPoint. So my question is this:

Does anyone know of a way to export data from ColdFusion as a real XLS file? I have some experience working with Jakarta POI, so I'm thinking that's where this fun little journey is going to wind up. But I thought I'd send it out there and see if anyone in the community has any better suggestions.

thanks a lot, all!

[TSB]

Try http://cfregex.com/cfcomet/Excel/index.cfm?ArticleID=B5ED33FB-5CB1-4ACC-899689A15A0E1539 it looks like it is a archive of the old cfcomet site. I use to visit cfcomet all the time to learn new tricks.

Trevor

[Kronin555]

Ben wrote a nice CFC wrapper around POI.
http://www.bennadel.com/index.cfm?dax=blog:474.view

[insuractive]

Thanks for your help TSB and Kronin. I actually just wond up rolling my own POI code to create the excel file. It seems to work great. I'll include the code below for anyone else who is interested:

[pelican]

I have successfully implement some POI code from another message in the forum, but how do I adjust the format/style of the cells, change the column width, etc? Most of this POI is beyond me. Thanks.

[insuractive]

Take a look at the jakarta POI site. They have a lot of good example code:

http://jakarta.apache.org/poi/hssf/quick-guide.html

[insuractive]

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.

[insuractive]

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]

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.

[insuractive]

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.

[cf_dev2]

Another thing to watch is java package names are case sensitive.

<!--- correct case. this works --->
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()>
<!--- wrong case . this does NOT work --->
<cfset wb = createobject("java","ORG.APACHE.poi.hssf.USERMODEL.HSSFWorkbook").init() />

[pelican]

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);

[insuractive]

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>

[cf_dev2]

quote:

---

Originally posted by: insuractive
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>

---

The colors AQUA and ORANGE are special cases. If you check the API, you'll see they are inner (or nested) classes.
http://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html

So you cannot just instantiate the outer class and refer to them as objColor.AQUA and objColor.ORANGE. You need a special syntax to create an instance of the nested class. Though technically I suppose its not truly instantiated but that's another question :)


Instead of ...
<cfscript>
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);
</cfscript>

You'll need to use:
<cfscript>
aqua = CreateObject("Java", "org.apache.poi.hssf.util.HSSFColor$AQUA");
objStyle = wb.createCellStyle();
objStyle.setFillBackgroundColor(aqua.index);
</cfscript>

... OR
<cfscript>
aquaIndex = CreateObject("Java", "org.apache.poi.hssf.util.HSSFColor$AQUA").index;
objStyle = wb.createCellStyle();
objStyle.setFillBackgroundColor(aquaIndex);
</cfscript>

[insuractive]

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

[cf_dev2]

quote:

---

Originally posted by: insuractive
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

---

Could .. but I wouldn't recommend it 😉 As soon you hard code the number, you know they'll go and change it to 491 😉

Besides, it completely defeats the purpose of those nice human readable statics they created.

[insuractive]

Agreed.

[pelican]

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.

[cf_dev2]

>> like the code is being ignored or something.
You're right. The API says "It is necessary to set the fill style in order for the color to be shown in the cell." Since you're not setting a fill style its ignored.

It may sound backwards, but for a solid color use setFillForegroundColor:

<cfset row = sheet.createRow(0) />
<cfset first_cell = row.createCell(0) />
<cfset objstyle = workbook.createCellStyle() />
<cfset objstyle.setFillForegroundColor(aqua.index) />
<cfset objstyle.setFillPattern(objstyle.SOLID_FOREGROUND)>
<cfset first_cell.setCellValue("Fleet") />
<cfset first_cell.setCellStyle(objstyle) />

quote:

---

<cfset aqua = createobject("Java", "org.apache.poi.hssf.util.HSSFColor$AQUA").init() />

---

You don't actually need to call init() there because you're only using the class's static variables (ie aqua.index ) and not its methods (ie. aqua.getIndex() ). The code should work either way, but init() is not needed.

[pelican]

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.