Home / Generating Excel spreadsheets from CFMX
On Ben Forta's blog, many have been asking for "Blackstone" (next version of CFMX) to be able to export data to .xls format. Over the past few years, there have been a variety of ways to do this, however none of them have been able to unlock most of the features of the Excel workbook file format. One easy way is to export the data to a comma-separated or tab-delimited format, which would typically open in Excel on the end user's workstation. Another way is to generate an html table and save it with a .xls extension, which excel will also open. Neither method gives you full control over excel's features, but there are other ways. One option is to install excel on the server and automate the spreadsheet generation using COM. Should you not be able (or want) to do this, another option (which is becoming the common theme on this blog) is to use Java, more specifically the Apache Jakarta Project's POI, a "Java API To Access Microsoft Format Files".
The POI API is quite large, and includes many sub-projects. The section we are interested in is called HSSF, a "Java API To Access Microsoft Excel Format Files". HSSF provides a way to create, modify, read and write XLS spreadsheets. Using HSSF from CFMX is more or less a snap. The first step is to download the POI library and place it somewhere in CFMX's classpath. I typically use <cfusionmx-dir>/wwwroot/WEB-INF/lib.
The code to write an excel spreadsheet is pretty straight forward.
First, we create an instance of HSSFWorkbook.
<cfset workBook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
Should you be browsing the POI documentation, the package we will be using most often to generate excel spreadsheets is org.apache.poi.hssf.usermodel. At this point, we are free to use the HSSF libary to write whatever we want into our new excel workbook. On Ben's blog, Hien asked "In generating content to an Excel spreadsheet, will it be possible to have the output stored in different worksheets (tabs) within the same workbook?". In POI-HSSF's case, you can't even do anything until you add a worksheet to the workbook, and you can add as many as you want, so the answer is YES.
<cfset newSheet = workBook.createSheet()/>
If you want to name your new sheet, you can reference it by the order it was added to the workbook (starting with 0):
<cfset workBook.setSheetName(0, "My First Sheet")/>
Creating rows in a sheet is really easy too, the number you pass into createRow is the zero-based index of the row you want to create. HSSF needs a row created in order to add data to the spreadsheet. For instance, if you wanted to add information to the third row in the sheet, you would do this:
<cfset row = newSheet.createRow(3)/>
Cells work in the same fashion: in order to add information to a cell, you must first use the HSSFRow's createCell function.
<cfset cell = row.createCell(0)/>
You can place a variety of information in the cell, from strings:
<cfset cell.setCellValue("hello world!")/>
numbers:
<cfset cell.setCellValue(1.25145285)/>
even formulas:
<cfset cell.setCellFormula("A1 * A2")/>
(keep in mind that formulas should be exactly as they were if you were putting them in the cell sheet, e.g. you must reference other cells using the A1, B4 etc. format. Also, make sure you omit the "=" in the formula)
Setting the style of cells is a bit harder, you first need to create a style (which can later be applied to any number of cells):
<cfset uglyStyle = workBook.createCellStyle()/>
Then you can set properties of the cellStyle:
<cfset uglyStyle.setBorderBottom(uglyStyle.BORDER_MEDIUM)/>
Setting colors is even more difficult, I guess because excel has some complex underlying color implementation. To set the background color of a cell, you have to do this: (notice that you use the dollar sign to access an inner class of a java object, like HSSFColor)
<cfset green = createObject("java","org.apache.poi.hssf.util.HSSFColor$LIGHT_GREEN").init()/>
<cfset uglyStyle.setFillBackgroundColor(green.getIndex())/>
Now, we can set the style of a cell like this:
<cfset cell.setCellStyle(uglyStyle)/>
Finally, to write our workbook out to a file, we need an output stream for the written file:
<cfset fileOutStream = createObject("java","java.io.FileOutputStream").init("C:\cfusionmx\wwwroot\#CreateUUID()#.xls")/>
Then we write our workbook to this stream:
<cfset workBook.write(fileOutStream)/>
And close the stream:
<cfset fileOutStream.close()/>
As you can see, there's a lot you can do in terms of generating .xls format documents using POI-HSSF. While no excel export implementation is perfect, HSSF probably is one of the most robust and easiest to use from CFMX.
