After over 2 years, cfHSSF has been updated. It has been done by Steven Durette and he did a really great job!

From his email:

I made a couple more additions and I set it up so that it would degrade gracefully if the version of POI isn't high enough to support the features.
I also made a change to the way values are set in cells. Before a row was created every time a cell value was set. With newer versions of POI if a cell already existed in a row, it was wiped out when another cell in the same row was set. The new method first checks to see if the row exists and uses that. If the row doesn't exist then it creates it.
The ability to put JPEG and PNG images into workbooks was added. This works with the POI included with CF9. If someone tries to use it that doesn't have a new enough version, it takes the cell where the image was to reside and places a message that the POI version doesn't support it.
You can now stream the workbook directly to the browser instead of having to create a file first by using one of the new functions.

Basicaly there has been done the following changes:

1. Added options for XLSX (Office 2007) files if the POI is the right version.
2. Added getRow function. This function will get a row or create it if it doesn't already exist.
3. Added getCell function. This function will get a cell or create it if it doesn't already exist.
4. Changed multiple functions to use getRow and getCell functions.
5. Added AddImage function to place an image in the spreadsheet.
6. Added AddJPGImage function to put a JPEG image in the spreadsheet.
7. Added AddPNGImage function to put a PNG image in the spreadsheet.
8. Added createBookVar to allow for streaming workbook to the browser without creating a file.
9. Updated create region to allow creation of regions in xlsx workbooks.

If anyone interested, you may download it here.

WOOHOO!!!!!!!!!!!!!!!!!!!!!

6000 downloads of my projects. Not too bad.

There was a request for some cfHSSF usage examples, so here are a couple:


EXAMPLE 1: <cfobject component="cfHSSF" name="exc" />
<cfset wb = exc.readBook("c:\workbook1.xls")>
<cfset sh = exc.readSheet(workbook=wb, sheetname="sh2")>
<cfset cv = exc.getCellValue(sh, 0, 0)>
<cfdump var="#cv#">

EXAMPLE 2: <cfobject component="cfHSSF" name="exc" />
<cfset theArr = ArrayNew(2)>
<cfset theArr = exc.worksheet2array(BOOKNAME="d:\Reports.xls", sheetName="Sheet1")>
<cfdump var="#theArr#">

EXAMPLE 3: <cfobject component="cfHSSF" name="exc" />
<cfquery name="getA" datasource="#request.dsn#">
   Select *
   From states
</cfquery>
<cfset exc.easyQuery2Excel(BOOKNAME="c:\wb.xls", sheetName="sh", titles=getA.columnList, data=getA)>

EXAMPLE 4: <cfobject component="cfHSSF" name="exc" />
<cfset wb1 = exc.init()>

<cfset sh1 = exc.createSheet(wb1, "sh2")>

<cfset cell1 = exc.setCellValue(sh1, 3, 4, "asd")>
<cfset exc.setCellValue(sh1, 0, 0, "1")>
<cfset exc.setCellValue(sh1, 0, 1, "3")>

<cfset exc.setCellDataFormat(workbook=wb1, sheet=sh1, rownum=2, cellnum=2, format="m/d/yy h:mm", val=Now())>

<cfset exc.setRowHeight(sh1, 0, 1000)>
<cfset exc.setCellWidth(sh1, 0, 5000)>

<cfset exc.setCellAlign(workbook=wb1, cell=cell1, align="right", wrap="true")>
<cfset exc.setCellFormula(sheet=sh1, frml="A1+B1", rownum=1, cellnum=1)>
<cfset exc.setCellBorder(workbook=wb1, cell=cell1, border="Thin", color="red")>
<cfset exc.setCellPattern(workbook=wb1, cell=cell1, pattern="BIG_SPOTS", color="red")>

<cfset exc.createDateCell(wb1, sh1, 1, 0, "h:mm")>

<cfset exc.setCellFontStyle(workbook=wb1, cell=cell1, bold="false", underline="doubLE_ACCOUNTING")>

<cfset region = exc.mergeRegion(sh1, 4, 4, 10, 11)>
<cfset exc.setCellPattern(workbook=wb1, cell=exc.setCellValue(sh1, 4, 4, "asd"), pattern="BIG_SPOTS", color="red")>

<cfset exc.setPageNum(sh1)>

<cfset exc.fitSheet(sh1)>

<cfset exc.setFooters(sheet=sh1, lfooter="left", cfooter="center", rfooter="right", lfooterfont="Verdana", lfooterfontstyle="italic", rfooterfontsize=30)>

<cfset fo = exc.createBook(wb1, "C:\workbook1.xls")>
<cfset exc.close(fo)> Hope it helps

While i will be trying to submit it to RIAForge, for now, cfHSSF.cfc can be downloaded from here. Enjoy.

There was talk lately about handling XLS files using ColdFusion and POI and it made me remember that i've done something on it back in 2004 when POI was not part of CF yet. As well as i remember, i was inspired by Busy Developers' Guide to HSSF Features and Dave Ross's article. Thought it may help someone, so i am going to upload it to RIAForge soon. Stay tuned.