d-ross.org

Busy ColdFusion Developers' Guide to HSSF Features

JMR broke our comments, they're fixed now (he gets 200+ spams a day, I get 1 a month). Anyways, I mentioned that I would translate the Busy Developers' Guide to HSSF Features into CFML. I figure the more documentation for stuff like this that gets written, the better. I was suprised to search google for "CFMX + LDAP" and see that my posts were on top. Anyways, all this code has been tested on CFMX 6.1, with poi-2.5-final-20040302.jar and poi-contrib-2.5-final-20040302.jar in CFMX's classpath. All sheets have been verified with MS-Excel 2000. All comments have been left intact (credit goes to original author).

New Workbook:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

New Sheet:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet1 = wb.createSheet("new sheet")/>
<cfset sheet2 = wb.createSheet("second sheet")/>
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Creating Cells:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(0)/>

<!---  Create a cell and put a value in it --->
<cfset cell = row.createCell(0)/>
<cfset cell.setCellValue(1)/>

<!--- Or do it on one line. --->
<cfset row.createCell(1).setCellValue(javacast("double","1.2"))/>
<cfset row.createCell(2).setCellValue("This is a string")/>
<cfset row.createCell(3).setCellValue(true)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Creating Date Cells:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(0)/>

<!---  Create a cell and put a date value in it.  The first cell is not styled as a date. --->
<cfset cell = row.createCell(0)/>
<cfset cell.setCellValue(now())/>

<!---  we style the second cell as a date (and time).
 It is important to create a new cell style from the workbook otherwise you can end up
 modifying the built in style and effecting not only this cell but other cells. --->

<cfset cellStyle = wb.createCellStyle()/>
<cfset cellStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("m/d/yy h:mm"))/>
<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue(now())/>
<cfset cell.setCellStyle(cellStyle)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Demonstrates various alignment options:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>

<cfset cellStyleStatic = createObject("java","org.apache.poi.hssf.usermodel.HSSFCellStyle")/>

<cfset createCell(wb,row,0,cellStyleStatic.ALIGN_CENTER)/>
<cfset createCell(wb,row,1,cellStyleStatic.ALIGN_CENTER_SELECTION)/>
<cfset createCell(wb,row,2,cellStyleStatic.ALIGN_FILL)/>
<cfset createCell(wb,row,3,cellStyleStatic.ALIGN_GENERAL)/>
<cfset createCell(wb,row,4,cellStyleStatic.ALIGN_JUSTIFY)/>
<cfset createCell(wb,row,5,cellStyleStatic.ALIGN_LEFT)/>
<cfset createCell(wb,row,6,cellStyleStatic.ALIGN_RIGHT)/>
 
<cffunction name="createCell" returntype="void" hint="Creates a cell and aligns it a certain way.">
    <cfargument name="wb" required="Yes" type="Any"
        hint="the workbook (must be of type:  org.apache.poi.hssf.usermodel.HSSFWorkbook)"/>
    <cfargument name="row" required="Yes" type="Any" 
        hint="the row to create the cell in (must be of type: org.apache.poi.hssf.usermodel.HSSFRow)"/>
    <cfargument name="column" required="Yes" type="numeric"
        hint="the column number to create the cell in"/>
    <cfargument name="align" required="Yes" type="Any"
        hint="the alignment for the cell (must be an alignment property of: org.apache.poi.hssf.usermodel.HSSFCellStyle )"/>

    <cfset var cell = arguments.row.createCell(arguments.column)/>
    <cfset var cellStyle = arguments.wb.createCellStyle()/>
    <cfset cell.setCellValue("Align It")/>
    <cfset cellStyle.setAlignment(arguments.align)/>
    <cfset cell.setCellStyle(cellStyle)/>

</cffunction>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Working with borders:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>
<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue(4.0)/>

<!--- Style the cell with borders all around. --->
<cfset style = wb.createCellStyle()/>
<cfset style.setBorderBottom(style.BORDER_THIN)/>
<cfset style.setBottomBorderColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$BLACK").getIndex())/>
<cfset style.setBorderLeft(style.BORDER_THIN)/>
<cfset style.setLeftBorderColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$GREEN").getIndex())/>
<cfset style.setBorderRight(style.BORDER_THIN)/>
<cfset style.setRightBorderColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$BLUE").getIndex())/>
<cfset style.setBorderTop(style.BORDER_MEDIUM_DASHED)/>
<cfset style.setTopBorderColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$BLACK").getIndex())/>
<cfset cell.setCellStyle(style)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Fills and colors

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>

<!--- Aqua background --->
<cfset style = wb.createCellStyle()/>
<cfset style.setFillPattern(style.BIG_SPOTS)/>
<cfset style.setFillBackgroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$AQUA").getIndex())/>
<cfset cell = row.createCell(1)/>
<cfset cell.setCellStyle(style)/>
<cfset cell.setCellValue("X")/>

<!--- Orange "foreground", foreground being the fill foreground not the font color. --->
<cfset style = wb.createCellStyle()/>
<cfset style.setFillPattern(style.BIG_SPOTS)/>
<cfset style.setFillForegroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$ORANGE").getIndex())/>
<cfset cell = row.createCell(2)/>
<cfset cell.setCellStyle(style)/>
<cfset cell.setCellValue("X")/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Merging cells:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>

<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue("This is a test of merging")>

<!--- create a region(rowFrom,cellFrom,rowTo,cellTo) --->
<cfset region = createObject("java","org.apache.poi.hssf.util.Region").init(2,1,2,6)/>
<cfset sheet.addMergedRegion(region)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Working with fonts:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>

<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue("This is a font test")>

<cfset font = wb.createFont()/>
<cfset font.setFontHeightInPoints(24)/>
<cfset font.setFontName("Courier New")/>
<cfset font.setItalic(true)/>
<cfset font.setStrikeout(true)/>

<!--- create a style and set our font to it --->
<cfset style = wb.createCellStyle()/>
<cfset style.setFont(font)/>

<cfset cell.setCellStyle(style)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\extemp\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Custom colors:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!---  Create a row and put some cells in it. Rows are 0 based. --->
<cfset row = sheet.createRow(2)/>

<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue("Default Palette")/>

<!---   apply some colors from the standard palette,
  as in the previous examples.
  we'll use red text on a lime background  --->

<cfset style = wb.createCellStyle()/>
<cfset style.setFillForegroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$LIME").getIndex())/>
<cfset style.setFillPattern(style.SOLID_FOREGROUND)/>

<cfset font = wb.createFont()/>
<cfset font.setColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$RED").getIndex())/>
<cfset style.setFont(font)/>

<cfset cell.setCellStyle(style)/>

<!--- save with the default palette --->
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\default_palette.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

<!--- now, let's replace RED and LIME in the palette
    with a more attractive combination
    (lovingly borrowed from freebsd.org) --->

<cfset cell.setCellValue("Modified Palette")/>
<cfset palette = wb.getCustomPalette()/>
<!--- replacing the standard red with freebsd.org red --->
<cfset palette.setColorAtIndex(createObject("java","org.apache.poi.hssf.util.HSSFColor$RED").getIndex(),
      153,
      0,
      0)/>
<!--- replacing lime with freebsd.org gold --->
<cfset palette.setColorAtIndex(createObject("java","org.apache.poi.hssf.util.HSSFColor$LIME").getIndex(),
      255,
      204,
      102)/>

<!---  save with the modified palette
  note that wherever we have previously used RED or LIME, the
  new colors magically appear --->
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\modified_palette.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Reading and Rewriting Workbooks:

<cfset fileIn = createObject("java","java.io.FileInputStream").init("C:\workbook.xls")/>
<cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn)/>
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>
<cfset sheet = wb.getSheetAt(0)/>
<cfset row = sheet.createRow(0)/>
<cfset cell = row.getCell(3)/>
<!--- try/catch to check if cell is null - CFMX cannot assign a null value --->
<cftry>
    <cfset isNull = cell/>
    <cfcatch>
        <cfset cell = row.createCell(3)/> 
    </cfcatch> 
</cftry>
<cfset cell.setCellType(cell.CELL_TYPE_STRING)/>
<cfset cell.setCellValue("a test")/>
<!--- Write the output to a file --->
<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset  fileOut.close()/>

Using newlines in cells:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>
<cfset style = wb.createCellStyle()/>
<cfset font1 = wb.createFont()/>
<cfset font2 = wb.createFont()/>
<cfset style.setFont(font1)/>
<!--- Word Wrap MUST be turned on --->
<cfset style.setWrapText(true)/>

<cfset row = sheet.createRow(2)/>
<!--- row height is in twips (1/20th of a pixel) --->
<cfset row.setHeight(550)/>

<cfset cell = row.createCell(2)/>
<cfset cell.setCellType( cell.CELL_TYPE_STRING )/>
<cfset cell.setCellValue( "Use \n with word wrap on to create a new line" )/>
<cfset cell.setCellStyle( style )/>
<!--- set the column width (in units of 1/256th of a character width)  --->
<cfset sheet.setColumnWidth(2, 5000 )/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Data Formats:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>
<cfset format = wb.createDataFormat()/>
<cfset row = sheet.createRow(2)/>
<cfset cell = row.createCell(2)/>
<cfset cell.setCellValue(javacast("double","11111.25"))/>
<cfset style = wb.createCellStyle()/>
<cfset style.setDataFormat(format.getFormat("0.0"))/>
<cfset cell.setCellStyle(style)/>
<cfset row = sheet.createRow(3)/>
<cfset cell = row.createCell(2)/>
<cfset cell.setCellValue(javacast("double","11111.25"))/>
<cfset style = wb.createCellStyle()/>
<!--- double up on pound signs: --->
<cfset style.setDataFormat(format.getFormat("##,####0.0000"))/>
<cfset cell.setCellStyle(style)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Fit Sheet to One Page:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<cfset ps= sheet.getPrintSetup()/>
<cfset sheet.setAutobreaks(true)/>
<cfset ps.setFitHeight(1)/>
<cfset ps.setFitWidth(1)/>

<!--- fill some stuff in, something with loop counters forces use of javacast() --->
<cfloop from="1" to="50" index="r">
    <cfset row = sheet.createRow(javacast("int",r))/>
    <cfloop from="1" to="25" index="c">
        <cfset cell = row.createCell(javacast("int",c))/>
        <cfset cell.setCellValue(rand())/>
    </cfloop>
</cfloop>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Set Print Area:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<!--- fill some stuff in, something with loop counters forces use of javacast()--->
<cfloop from="1" to="50" index="r">
    <cfset row = sheet.createRow(javacast("int",r))/>
     <cfloop from="1" to="25" index="c">
         <cfset cell = row.createCell(javacast("int",c))/>
         <cfset cell.setCellValue(rand())/>
    </cfloop>
</cfloop>

<cfset wb.setPrintArea(0, "$A$1:$C$2")/>
<!--- Alternatively: wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)  --->

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Set Page Numbers on Footer:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<cfset footer = sheet.getFooter()/>
<cfset footer.setRight( "Page #footer.page()# of #footer.numPages()#")/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Using the Convenience Functions:

<!--- The convenience functions live in contrib and provide utility features
  such as setting borders around merged regions
  and changing style attributes without explicitly creating new styles.  --->
 
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet1 = wb.createSheet("new sheet")/>

<cfset row = sheet1.createRow(2)/>
<cfset row2 = sheet1.createRow(4)/>

<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue("This is a test of merging")>

<!--- create a region(rowFrom,cellFrom,rowTo,cellTo) --->
<cfset region = createObject("java","org.apache.poi.hssf.util.Region").init(2,1,2,6)/>
<cfset sheet1.addMergedRegion(region)/>

<!--- Set the border and border colors --->
<cfset borderMediumDashed =  createObject("java","org.apache.poi.hssf.usermodel.HSSFCellStyle").BORDER_MEDIUM_DASHED/>
<cfset aquaIndex = createObject("java","org.apache.poi.hssf.util.HSSFColor$AQUA").getIndex()/>
<cfset regionUtil = createObject("java","org.apache.poi.hssf.usermodel.contrib.HSSFRegionUtil").init()/>
<cfset regionUtil.setBorderBottom( borderMediumDashed, region, sheet1, wb )/>
<cfset regionUtil.setBorderTop( borderMediumDashed, region, sheet1, wb )/>
<cfset regionUtil.setBorderLeft( borderMediumDashed, region, sheet1, wb )/>
<cfset regionUtil.setBorderRight( borderMediumDashed, region, sheet1, wb )/>
<cfset regionUtil.setBottomBorderColor(aquaIndex, region, sheet1, wb)/>
<cfset regionUtil.setTopBorderColor(aquaIndex, region, sheet1, wb)/>
<cfset regionUtil.setLeftBorderColor(aquaIndex, region, sheet1, wb)/>
<cfset regionUtil.setRightBorderColor(aquaIndex, region, sheet1, wb)/>

<!--- Shows some usages of HSSFCellUtil --->
<cfset style = wb.createCellStyle()/>
<cfset style.setIndention(4)/>
<cfset cellUtil = createObject("java","org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil").init()/>
<cfset cellUtil.createCell(row, 8, "This is the value of the cell", style)/>
<cfset cell2 = cellUtil.createCell( row2, 8, "This is the value of the cell")/>
<cfset cellUtil.setAlignment(cell2, wb, style.ALIGN_CENTER)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Shift rows up or down on a sheet:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<cfset row = sheet.createRow(7)/>

<cfset cell = row.createCell(1)/>
<cfset cell.setCellValue("This is a test of shifting")/>

<cfset sheet.shiftRows(javaCast("int",5), javaCast("int",10), javaCast("int",-5))/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Set a sheet as selected:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<cfset sheet.setSelected(true)/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Set the zoom magnification:

<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet = wb.createSheet("new sheet")/>

<cfset sheet.setZoom(3,4)/> <!--- 75% magnification --->

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Splits and freeze panes:

<!---
There are two types of panes you can create; freeze panes and split panes.
A freeze pane is split by columns and rows. You create a freeze pane using the following mechanism:
 sheet1.createFreezePane( 3, 2, 3, 2 );
The first two parameters are the columns and rows you wish to split by.
The second two parameters indicate the cells that are visible in the bottom right quadrant.

Split pains appear differently. The split area is divided into four separate work area's.
The split occurs at the pixel level and the user is able to adjust the split by dragging it to a new position.
Split panes are created with the following call:
 sheet2.createSplitPane( 2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT );
The first parameter is the x position of the split. This is in 1/20th of a point. A point in this case seems to equate to a pixel.
The second parameter is the y position of the split. Again in 1/20th of a point.
The last parameter indicates which pane currently has the focus. This will be one of HSSFSheet.PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.
 --->
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>

<cfset sheet1 = wb.createSheet("new sheet")/>
<cfset sheet2 = wb.createSheet("second sheet")/>
<cfset sheet3 = wb.createSheet("third sheet")/>
<cfset sheet4 = wb.createSheet("fourth sheet")/>

<!---  Freeze just one row --->
<cfset sheet1.createFreezePane( 0, 1, 0, 1 )/>
<!---  Freeze just one column --->
<cfset sheet2.createFreezePane( 1, 0, 1, 0 )/>
<!---  Freeze the columns and rows (forget about scrolling position of the lower right quadrant). --->
<cfset sheet3.createFreezePane( 2, 2 )/>
<!--- Create a split with the lower left side being the active quadrant --->
<cfset sheet4.createSplitPane( 2000, 2000, 0, 0, sheet4.PANE_LOWER_LEFT )/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Repeating rows and columns:

<!---
It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the HSSFWorkbook class.
This function Contains 5 parameters.
The first parameter is the index to the sheet (0 = first sheet).
The second and third parameters specify the range for the columns to repreat.
To stop the columns from repeating pass in -1 as the start and end column.
The fourth and fifth parameters specify the range for the rows to repeat.
To stop the columns from repeating pass in -1 as the start and end rows.
--->
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>

<cfset sheet1 = wb.createSheet("new sheet")/>
<cfset sheet2 = wb.createSheet("second sheet")/>

<!--- Set the columns to repeat from column 0 to 2 on the first sheet --->
<cfset wb.setRepeatingRowsAndColumns(javacast("int",0),javacast("int",0),javacast("int",2),javacast("int",-1),javacast("int",-1))/>
<!--- Set the the repeating rows and columns on the second sheet. --->
<cfset wb.setRepeatingRowsAndColumns(javacast("int",1),javacast("int",4),javacast("int",5),javacast("int",1),javacast("int",2))/>

<cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>
<cfset wb.write(fileOut)/>
<cfset fileOut.close()/>

Headers and Footers:

<!--- Example is for headers but applies directly to footers. --->
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>

<cfset sheet = wb.createSheet("new sheet")/>
<cfset header = sheet.getHeader()/>
<cfset header.setCenter("Center Header")/>
<cfset header.setLeft("Left Header")/>
<cfset header.setRight(header.font("Ste

10/19/2004 10:40 A - Doug T said... The following line: " <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>" gives an object instantiation error every time I run it.  Any ideas? I am just cutting and pasting your code for reading/rewriting a sheet (changing filename and path of course).

10/19/2004 01:00 P - Dave Ross said... what happens if you dump out fs before the call, e.g. <cfdump var="#fs#">? also, do you have a existing sheet  at C:\workbook.xls ?

10/19/2004 01:22 P - Doug T said... Found the issue (sort of).  I removed all of the data validation from cells on one sheet in the workbook (not even the sheet that I am trying to update) and it works now. BUT, I loose all my formatting on the front sheet that I am not updating. For example, I have a "dashboard" type of function with macros, data validation and formulas that call out to data on other sheets on the first sheet, then the data resides on other sheets.  I can now get the data into the other sheets, but my dashboard is REALLY messed up and looses all it's formulas. *sigh* I thought HSSF just might break through my problems.... Maybe you can suggest a better solution?  I looked at "ExtenXLS" but it is really pricey!

10/19/2004 01:27 P - Dave Ross said... yeah there are certain limitations to the hssf implementation that make complex excel sheets difficult to work with. Most of my needs are met because excel is mainly used for bulk transfer of data from end user to customer. I would keep playing... it sounds like you are close.

10/19/2004 02:56 P - Doug T said... My next attempt will be to VBA script in the elements I need so that it applies them when the sheet is opened. A real pain, but it should work.

11/10/2004 12:48 P - Jonathan Nickle said... First of all, thank you for posting these CF examples.  They really helped me debug a few problems.  However, I do have one more question.  Do you have an example of creating a workbook, outputting it, then updating it later and adding more data (sheets)?

11/10/2004 05:25 P - Jonathan Nickle said... After much fussing, I finally figured out which class needs to be called and the process for opening and editing an existing file.  For me, this allows me to generate very large workbooks, one sheet at a time.  POI is memory intesive, and I was having a problem before with recordsets over 60,000.  Please feel free to <a href="mailto:jon_nickle@yahoo.com">email me</a> if you would like some example code.  Again, thanks for all the references in your blog!!  -Jon

11/10/2004 06:32 P - Dave Ross said... glad to hear you made out ok Jon... so what ended up being different vs. the Reading and Rewriting Workbooks section of this document?

11/17/2004 11:36 A - Douglas knudsen said... How is the performance of this approach? Say I have a 3000 line report 25 columns wide, will this POI handle it quickly? Also, what about adding images to the excel doc, possible?

11/17/2004 11:43 A - Jonathan Nickle said... Doug, I have been working with this now for a couple of weeks.  I am using this to generate reports in excess of 50,000 with 15 columns.  It seems to process about 1000 rows every 2 or 3 seconds.  Once you start working with this, I think you will find this to be the most flexible and powerful Excel file generator option. I know you can do graphs, but I would check the Jakarta site for inserting images.  Send me an email if you get stuck, maybe my pain in ramping up can save you some time:  jonathan.nickle@pearson.com -Jon

11/17/2004 11:45 A - Dave Ross said... I haven't done any real performance testing (e.g. load testing), but it's always been fast enough for my purposes. The image thing is complicated, I've never sucessfully done it myself, but it *can* be done I believe, it just may not be implemented with the high-level API. Check out this thread for some reading on it (that was 2003 so I'm sure things have changed) http://article.gmane.org/gmane.comp.jakarta.poi.devel/7238

11/17/2004 11:51 A - Jonathan Nickle said... DAVE ROSS - I am trying to find your email to let you know of a little issue I am having with your site and to send you some example code.  Can you post your email or send it to me??

11/17/2004 06:39 P - Warren Koch said... I am having a terrible time with memory usage.  I am creating large workbooks using HSSF and CFMX.  I get back about 120k rows of data which I am breaking up into 12 10k record books in the hopes of minimizing the impact on the JVM heap.  No luck -- I have to have a 1.5gb heap. Has anyone run into this and solved it?  I'd be happy to share/email  my code.... By the way -- your eaxample helped me out a lot.

11/17/2004 06:47 P - Jonathan Nickle said... Warren, Send me an email and I can tell you what I have found.  I have been working on the same issue for weeks. jonathan.nickle@pearson.com

11/17/2004 07:14 P - Dave Ross said... i really don't see how a 120k row excel sheet is usable, but have you tried doing it directly from java? Keep in mind that a 120k row recordset is difficult to deal with on it's own, let alone pumping it out to an excel sheet with a high level api like this.  You may find that using CF is introducing a lot of memory overhead. If you need help writing the equivalent java classes, let me know.

11/17/2004 07:24 P - warren koch said... All these rows are a pain but that's what the user wants (and he really does have the need strange as it may be).  He doesn't care, however, if the data is broken up into individual workbooks though.  I have gotten all this to work -- I just keep using up progressively more memory as each workbook is written out.  I don't know how to release it.  Problem is I am Java stupid so this seems like my only choice.  I will send you a code packet -- maybe I'm just doing something overtly stupid !!

11/17/2004 07:33 P - warren said... your email address webmaster@d-ross.org is bouncing back undeliverable.  can you email me from somewhere different so that I can send the code?

11/17/2004 07:51 P - Dave Ross said... just post it here... I'll change the webmaster email to be my real email addy.

11/17/2004 08:07 P - warren said... Here is some of the code I am using.   I omitted all the extraneous query stuff and other things I use to do count rows, etc.  I REALLY appreciate the help.  I have been fighting this for weeks.  Background: I get about 100,000 rows of data and am dumping them into pre-built workbooks. I thought I'd be clever and break this up into 10 workbooks of 10,000 rows each in the hopes that I would use less JVM memory. Run the query, create the workbook using a pre-built template, dump the rows into it, write the workbook out to the hard disk, and move on to the next workbook. The (simplified) CF code I am using is: <CFLOOP --  THIS RUNS 10 TIMES over the Query --- Simplified code>   <CFSET CreateWB()>   <!--- POPULATE CELLS WITH QUERY DATA  <CFSET CreateRowAt = sheet.getLastRowNum() + 1>  <CFSET TargetRow = sheet.createRow(javacast("int",CreateRowAt))>     <CFSET wbMakeCell(TargetRow,0,#BMI_Category#)>  <CFSET wbMakeCell(TargetRow,1,#BMI#)>  <CFSET wbMakeCell(TargetRow,2,#BMI_Name#)>  <CFSET wbMakeCell(TargetRow,3,#BMI_Information#)>  <CFSET wbMakeCell(TargetRow,4,#BMI_CAGE#)>--->  <CFSET WriteWB(WorkBook,OutputFileName)> </CFLOOP> <CFFUNCTION name="CreateWB" output="false"> <!--- Workbook is pass by reference)   <CFARGUMENT name="SourceFile" type="string" default="#APPLICATION.RootServerPath#ExcelTemplates\StdDataFile.xls">   <CFSET var fileIn = createObject("java","java.io.FileInputStream").init(SourceFile)>   <CFSET WorkBook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fileIn)>   <CFSET WorkBookDateUtil = createObject("java","org.apache.poi.hssf.usermodel.HSSFDateUtil")>   <CFSET fileIn.close()>   <!--- more code goes here to define cells, sheets, etc.---> </CFFUNCTION> <CFFUNCTION name="WriteWB" output="false">   <CFARGUMENT name="wb" type="any">   <CFARGUMENT name="OutputFile" type="string">   <CFSET var fileOut = createObject("java","java.io.FileOutputStream").init(OutputFile)>   <CFSET wb.write(fileOut)>   <CFSET fileOut.close()> </CFFUNCTION> <CFFUNCTION name="wbMakeCell" output="false">  <!---  Fills a specific cell in a row.   WARNING -- this function uses PASS BY REFERNECE  --->  <CFARGUMENT name="RowTarget" type="any">  <CFARGUMENT name="CellTarget" type="numeric">  <CFARGUMENT name="CellData" type="any">  <CFARGUMENT name="CellFormat" type="string" default="TEXT">  <CFIF (CellTarget GTE 0) AND (CellTarget LTE 255)>   <CFSET cell = RowTarget.createCell(javacast("int",CellTarget))>  <CFELSE>   <CFSET cell = RowTarget.createCell(javacast("int",0))>  </CFIF>  <CFSWITCH expression="#UCASE(CellFormat)#">   <CFCASE value="TEXT">    <CFSET cell.setCellValue(CellData)>    <CFSET cell.setCellStyle(DataStyleText)>   </CFCASE>   <CFCASE value="NUMBER">    <CFTRY>     <CFSET cell.setCellValue(javacast("double",CellData))>     <CFSET cell.setCellStyle(DataStyleNum)>     <CFCATCH>      <CFSET cell.setCellStyle(DataStyleText)>      <CFSET cell.setCellValue(CellData)>     </CFCATCH>     </CFTRY>   </CFCASE>   <CFCASE value="DATE">    <CFTRY>     <CFSET cell.setCellValue(WorkBookDateUtil.getExcelDate(CellData))>     <CFSET cell.setCellStyle(DataStyleDate)>     <CFCATCH>      <CFSET cell.setCellStyle(DataStyleText)>      <CFSET cell.setCellValue(CellData)>     </CFCATCH>     </CFTRY>   </CFCASE>   <CFDEFAULTCASE>    <CFSET cell.setCellStyle(DataStyleText)>    <CFSET cell.setCellValue(CellData)>   </CFDEFAULTCASE>  </CFSWITCH> </CFFUNCTION> The Problem: I am eating memory like candy. I used the same workbook object each time in the loop in the hopes that I am re-using the same memory space. This does not appear to be the case --- the memory utilization increasing each time. We have set the java heap size (max & min) to 1024 mb. Anything less than that and it won't work. We are looking into increasing the JVM garbage collector rates and upping the heap size to 2gb.

01/05/2005 01:16 P - Dave Ross said... fyi - it looks like some of this post may have gotten lost when the server died. I'll try to find the rest of it asap

01/13/2005 11:32 A - Michael Dawson said... First, this is an excellent resource.  Just one day after I found this page, I had a need to create an Excel spreadsheet with pre-populated formulas.  This saved my life, practically. Any update on finding the "missing" part of this page?

01/13/2005 11:35 A - Dave Ross said... Michael, After looking at a google-cached version, I'm only missing about the last three lines. I pasted them in and they got truncated. When I get some time, I'll try to track down the problem. However, I'm very glad to hear that this helped out!

03/02/2005 12:07 P - Rob Kolosky said... I'm getting the same error as Doug T ... CF is throwing an Instantiation error. But it seems that FileInputStream isn't reading the file, because it says in the stack trace : Caused by: java.io.IOException: Unable to read entire header; -1 bytes read; expected 512 bytes When I dump #fs#, I get an object, but I don't know if it's reading the file or not.

03/02/2005 01:08 P - Dave Ross said... Is the file in an old version of excel? I found this which might shed some light on your problem.

03/29/2005 03:44 P - Aaron Rouse said... How is the newline feature supposed to be used?  If I just copy and paste the code directly from here it does not make a new line.  I'd thought it would encounter the /n and make a new line but instead it just displays the /n character with the other text.

03/29/2005 03:57 P - Dave Ross said... Aaron, Try using \n ... that should work. -Dave

03/29/2005 04:05 P - Aaron Rouse said... Dave, I guess I just do not follow your example, more than likely me making an assumption where I should now.  How would the \n be applied?  The way I read this line: <cfset cell.setCellValue( "Use \n with word wrap on to create a new line" )/> Is that it would show the word "Use" then a line break so the other words are on a new line.  However when I copy and paste the example code above that does not happen for me.

04/15/2005 05:30 P - scott stuart said... I'm having trouble populating cells with Unicode characters via ColdFusion.  Adapting examples from pure Java does not seem to work, ie: <CFSET cell.setEncoding(cell.ENCODING_UTF_16)> <CFSET cell.setCellValue("\u0422\u0435\u0441\u0442")>; Has anyone gotten this to work? -scott

10/31/2005 01:04 P - Joel said... Is there a way to format money or numbers with commas every thousand. (example: $1,000,000)

10/31/2005 01:29 P - Dave Ross said... Joel, Yes, see the section in this entry labeled "Data Formats". You would simply call getFormat(5) or whatever you need as seen here: http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html hope that helps...

11/04/2005 02:41 P - Matthew said... can you post the rest of the header and footer section?  it appears it got chopped off.

12/15/2005 11:46 A - Eric Roberts said... I have a question in regards to looping through a query to create multiple sheets.  I essentailly copied the above code and multiple sheets were not created.  The first sheet is a summary witht he names of students that will be hyperlinks that link to the appropriate sheet that contains individual information on that student.  First question: how do I use the hypoerlink function in excel?  do i treat it as a formula intha java code, or is this just not supported yet.  Second Question: How do i address the cf code within a cfoutput from a query to create new worksheets?  Is the a way to deconstruct the object so I can reuse the variable names?  It looks like I am overwriting the data written out inthe previous loops.  It's been abpout 10 years since I have done any java programming, so i am a bit rusty.  Any help on this would be very greatly appreciated.

12/15/2005 11:59 A - scott stuart said... To create a hyperlink, use the HYPERLINK function, ie: this.cell.setCellFormula('HYPERLINK("#theURL#","#urlval#")');

12/15/2005 01:58 P - Eric Roberts said... Thanks Scott...

12/15/2005 02:56 P - Eric Roberts said... I figured out what my problem was...  In copying the code i duplicated the constructor, which would recreate the object and write a blank workbook, thus deleteting the previously created sheets.  Something should be annotated in the above code snippets about this.  Thanks for all your help and thank for converting this over to CF for us.  This java class is a godsend! (thanks to the Apache Jakata team as well ;-) ) Eric

12/16/2005 09:59 A - Matthew said... I am generating an excel document for about 5,000 records and I have worksheets being created for every section. the problem is it takes around 30 seconds to generate the excel file and prompt them to download it.  Any pointers to make it speed up the process?

12/16/2005 12:00 P - Eric Roberts said... I'd like to know that too...I am pulling about 20k records and CF times out before it finishes processing the java classes. Also...how do you set a font color?  in looking at the above excamples and the examples given ont he Apache Jakarta page, I tried the following: <cfset font.setColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$RED").getIndex())/> <cfset font.setColor((short)0xc)/> niether worked... Any ideas? Eric

01/06/2006 02:12 P - Stacey said... I basically copied/pasted your code as below.  I just want to read in an excel spreadsheet and parse the data to load into my database.  I unpredictably get the following errors: 1) I have drop down box for month/year on the spreadsheet.  The error 'Object Instantiation Exception. An exception occurred when instantiating a java object' occurs for some month/year (not always the same month/year though).  Sometimes I just go to the spreadsheet, type something, save it, go back to delete what I typed and saved again and it'll work. 2) The spreadsheet has 96 rows of data and then a few blank rows before the TOTAL row.  It seems to have problem with those blank lines.  For a new spreadsheet, I get the error 'deletejava.lang.NullPointerException' unless I open up the spreadsheet to type in something in those blank rows and then delete and save the xls. HELP!!! <cfset xlsfile=#App.XlsDir# & "tc145_010306_unhidden.xls"> <cfset fileIn = createObject("java","java.io.FileInputStream").init(xlsfile)/> <cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn)/> <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>

01/06/2006 02:38 P - Dave Ross said... for your first problem, I'm not sure what's causing the problem but it's probably some underlying bug between MS and the POI library. Are you using the latest POI stuff out there (keep in mind I wrote this article over a year ago). For your second problem, just wrap your attempt to read the data in a try/catch block, and ignore the rows that throw exceptions. Hope that helps! -Dave

01/06/2006 03:51 P - Stacey said... Dave, thanks for the quick response.  I use poi-2.5.1-final-20040804.jar and poi-contrib-2.5.1-final-20040804.jar.  Is that the latest?  The weird thing is that when I select the month and save the xls, I'll get the error.  But if I open up the xls again, modify any cell and save it, then it'll work. With my 2nd problem, how do I tell it to ignore the rows that throw exceptions?  Is there a way to tell it to read only certain number of rows from the worksheet?

01/06/2006 03:57 P - Dave Ross said... those are the latest stable... you could try the 3.0 alpha. so the second error occurs immediately opne instantiation of org.apache.poi.hssf.usermodel.HSSFWorkbook ?

01/06/2006 04:08 P - Stacey said... here's what I see: - With a new spreadsheet coming in, the 2nd error will occur if there's blank rows in between data rows.  The error points to line <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/> - I fix it by editing something on any cell and saving the spreadsheet - If I open the xls again, change the month/year, then I'll get the first error. - I go in to edit/save again and it's fine.

01/06/2006 04:46 P - Stacey said... Using 3.0alpha seems to fix my 1st error (I'll keep praying :D) Now I just need to see if I can tell it to ignore the blank rows as you suggested

01/24/2006 01:18 A - jonathan nickle said... who the hell stole my name? give it back.

02/01/2006 02:47 A - mito said... Hi, does anybody know, if there are some issues when using ColdFusion MX 7??? I have made some tests, and it seems not to work properly as it did on MX 6... Bye, mito ;o)

02/01/2006 11:35 P - Eric Roberts said... I used it on 7 with no probs... Eric

02/06/2006 02:58 P - Neelima Kaja said... I am trying to use "\n" to get a new line in the text of a cell. But this doesn't seem to work. I was looking at this chain of comments, but I didn't see this answered. Could please let me know how to do it.

02/07/2006 11:03 A - katie said... I'm not able to display the date in the correct format when I generate an Excel spreadsheet . Dataformat= CreateObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat"); datestyle.setDataFormat(dataFormat,getBuiltinFormat("mm/dd/yyyy")); Any Suggestions?? I have a dealine to get it completed today ---thanks bunch

02/10/2006 09:26 A - Luis Azevedo said... Anyone have a java sample to add an image to a Excel sheet? Thanks for any help.

02/10/2006 09:43 A - Dave Ross said... Luis, It looks like HSSF does now support some basic drawing/imaging stuff. See here: http://jakarta.apache.org/poi/hssf/quick-guide.html#Images

02/10/2006 06:00 P - Rich Rein said... Just a quick piece I thought I would pass on to add to this.  Here are my code snippets for creating bold text and underlining, as well as hyperlinking.  I have wrapped this all inside of a set of nested custom tags, so that I pass a few attributes (and a query to be displayed on one or more tabs), and have the tag handle doing basic excel exports with customized formatting options for our reporting tool (CFMX 6.1)... <cfscript> workBook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(); <!--- ... ---> // column header style (with bold text) cellHeaderStyle = workBook.createCellStyle(); headerFont = workBook.createFont(); headerFont.setFontHeightInPoints(8); headerFont.setFontName("Verdana"); headerFont.setBoldweight(createObject("java","org.apache.poi.hssf.usermodel.HSSFFont").BOLDWEIGHT_BOLD); cellHeaderStyle.setFont(headerFont); // simulate an html hyperlink, with blue text underlined cellLinkStyle = workBook.createCellStyle(); dataFont = workBook.createFont(); dataFont.setFontHeightInPoints(8); dataFont.setFontName("Verdana"); dataFont.setColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$BLUE").getIndex()); dataFont.setUnderline(true); cellLinkStyle.setFont(dataFont); <!--- ... ---> // in my code processing section, here is a piece of text in bold cell.setCellValue(JavaCast("string","My column heading")); cell.setCellStyle(cellHeaderStyle); <!--- ... ---> // in my code processing section, here is a hyperlink being created cell.setCellFormula('HYPERLINK("ABSOLUTE_URL","VALUE TO DISPLAY")'); cell.setCellStyle(cellLinkStyle); </cfscript>

02/13/2006 01:36 P - Julie Johnson said... I need to read a specific worksheet from an Excel spreadsheet, then create a new spreadsheet containing just the information from that tab.   I'm using ColdFusion and I can get it to read the original spreadsheet and write the same spreadsheet with a new name but I don't know how to tell it to only read the one tab of information and write that to a new spreadsheet.  Here's the code I have so far: <!--- File you are reading: ---> <cfset fileIn = createObject("java","java.io.FileInputStream").init("#application.FilePath#idoc.xls")/> <!--- Pass open InputStream for Excel file: ---> <cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn)/> <!--- Pass filesystem to constructor: ---> <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/> <!--- Specify the sheet you want: ---> <cfset sheet = wb.getSheet("Infoweb")/> <!--- Write the output to a file: ---> <cfset fileOut = createObject("java","java.io.FileOutputStream").init("#application.FilePath#Infoweb.xls")/> <cfset wb.write(fileOut)/> <cfset  fileOut.close()/> Any help is appreciated.

02/24/2006 01:19 P - Matt Jones said... How do you reliably determine if a cell value is a date vs a number?

02/24/2006 05:13 P - Matt Jones said... doh, nevermind, I can look at getCellStyle().getDataFormat()

05/02/2006 03:37 P - Detarmstrong said... HSSFFormulaEvaluator I downloaded the dev HSSF so I could use the formula evaluator but I am having issues instantiating the object; I get an error saying Unable to find a constructor for class org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator that accepts parameters of type ( ). Any suggestions on getting it working?

05/15/2006 03:27 P - Dave said... Does this work differently on a UNIX/Solaris setup? Do I just neet to adjust my "#application.FilePath#" (as seen above)? If this does work, can someone give an example of reading in a single tabbed excel spread sheet and dumping the contents to something like a query? Thanks -- Dave :+)

05/16/2006 09:07 A - Dave said... These two lines <cfset fileIn = createObject("java","java.io.FileInputStream").init("/opt/coldfusionmx7/wwwroot/xceltest/5000-journals.xls")/> <CFDUMP VAR="#fileIn#"> <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/> <CFDUMP VAR="#wb#"> both dump out like: object of java.io.FileInputStream Methods available (returns int) close (returns void) getChannel (returns java.nio.channels.FileChannel) getFD (returns java.io.FileDescriptor) read (returns int) read (returns int) read (returns int) skip (returns long) mark (returns void) reset (returns void) markSupported (returns boolean) hashCode (returns int) getClass (returns java.lang.Class) wait (returns void) wait (returns void) wait (returns void) equals (returns boolean) notify (returns void) notifyAll (returns void) toString (returns java.lang.String) *** Im assuming that they are "installed" correctly, but something is wrong in that I still can not use the functions :(

06/07/2006 05:39 A - diwakar Gupta said... will this multi tab excel functionality work with CF5?

07/06/2006 10:39 A - sweeper said... Guys, I downloaded and tried to use this but... I had problem with Russian and Turkish characters.                 HSSFWorkbook wb = new HSSFWorkbook();                 HSSFSheet s = wb.createSheet();                 wb.setSheetName( 0, "SomeUnicodeName", HSSFWorkbook.ENCODING_UTF_16 );                 // for cell value                 HSSFRow r = s.createRow( 0 );                 HSSFCell c = r.createCell( (short)0 );                 c.setCellType( HSSFCell.CELL_TYPE_STRING );                 c.setEncoding( HSSFCell.ENCODING_UTF_16 );                 c.setCellValue( "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F" ); That kinda code I found in documents but it doesn't seem to work with CF. Any ideas about encoding / charset setting?

07/12/2006 07:02 A - sweeper said... Guys, solution for my above mentioned problem is: <cfscript>  workBook.setSheetName( 0, "someProblemName", workBook.ENCODING_UTF_16);  .... .... .... .... celln.setEncoding( celln.ENCODING_UTF_16 ); celln.setCellValue(JavaCast("string","someProblemText")); </cfscript>

08/02/2006 11:28 A - Joel said... 2 questions... 1) Is it possible to reference a cell in another workbook? 2) Is it also possible to be able to get the actual cell value once the cell value have been calculated by a formula? Thanks

08/25/2006 04:26 P - matthew said... Is there any way to have the width autoset depending on the content in the cells?  I am having to figit with the width until i get it right and i wish i could set it to setWidth(auto)... and it will find the correct width.

09/01/2006 03:42 P - Eric O. said... As A trial I attempted to use the following code in CFMX7: <cfset wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/> <cfset fileOut = createObject("java", "java.io.FileOutputStream").init("C:\"&form.progno&".xls")/>                <cfoutput>               <cfset sheet = wb.createSheet(#trim(form.Progno)#&"_Projects")/>                          <cfset rowCount = 0/>         <cfloop index="i" from="1" to="10">             <cfset rowCount = val(rowCount)/>             <cfset row = sheet.createRow(rowCount)/>             <cfset cell = row.createCell(0)/>             <cfset cell.setCellValue("yes")/>                           <cfset rowCount = rowCount + 1/>         </cfloop>                                 </cfoutput> It worked fine when I hard coded the value of 0(zero) in the createRow method but when I tried to pass rowCount as a variable it I received error message that the createRow method was not available.   Is there something wrong with the code or can you not pass variables in the method call? Any suggestions would be helpful. Thanks in advance. Eric O.

09/01/2006 04:00 P - Joel said... Eric, use javacast when you are creating a row... Example: <cfset row = sheet1.createRow(javacast("int", "#rowcount#"))/>

09/01/2006 04:08 P - Eric O. said... Thanks Joel!

10/29/2006 03:57 P - Steve Durette said... So, does anybody know if it is possible to output an excel spreadsheet directly to the browser using only cfcontent and displaying the object? All of the examples I've seen write out the file, then provide a link to the file that got written. Thanks, Steve

12/08/2006 07:27 A - di_wakar said... Hi, using these API can i read an excel sheet that contain few null cell...any idea or pice of code?

12/11/2006 04:28 A - di_wakar said... Hi every one, Regarding to read/upload an excel sheet, I got the exact solution…without paying any one cent or without creating DSN, I am able to read all row & cell of .xls file, also I am able to validate value & null cell also. If any one want help or sample code feel free to contact me.   Thanks & Regards Diwakar Gupta Lead Engineer HCLT-India

12/12/2006 12:01 P - Joel said... my numeric data returned from my sql queries are being stored as text when exporting.  tried the javacast("double,var) and CELL_TYPE_NUMERIC with no luck.  help?

12/15/2006 12:24 A - di_wakar said... Hi all, We have many methods to read an excel sheet… A. Save it as .csv & read as file (but coding is too much & if you cell have any comma separated value it cause a problem) B. Create ODBC & than DSN inside of CF admin (it is also painful if you want to read different type of excel.) C. There is some paid custom tag available on net (you need to pay much amount & some limitations are there) D. Last & final method is use Jakarta POI (some already guess about it) I use Jakarta POI…the problem is that with these POI that there is not enough material or documentation is available on net…I am lucky I did much R &D & get the solution. With using these POI our most of requirement' s get fulfill…see the sample code to read an excel sheet without creating a DSN <cfset fileIn = createObject( "java","java. io.FileInputStre am").init ("#file_url# ")/> <cfset wb = createObject ("java","org. apache.poi. hssf.usermodel. HSSFWorkbook" ).init ("#fileIn#") /> <cfset sheet = wb.getSheetAt( 0)/> //this is sheet number you can read multiple sheet.. <cfset Last_Row = sheet.getLastRowNum ()>// you will get number of last row to validate number of row <cfloop index="i" from="1" to="#Last_Row# "> <cfset row = sheet.getRow( javacast( "int",#i# ))> <cfloop index="j" from="0" to="18"> <cfoutput> <cfset Cell_len=#len( row.getCell( javacast( "int",j)) ) #> <cfif #Cell_len# GT 0>//to validate null value(blank cell) <cfset cell_Add=row. getCell(javacast ("int",j))> <cfset "c#j#"=cell_ Add.getNumericCe llValue() >// to read numenric value <cfset "c#j#"=cell_ Add.getStringCel lValue() >// to read string value <cfelse> <cfset "c#j#"="NULL" > </cfif> </cfoutput> </cfloop> </cfloop> Now a tip: use CFdump for wb, sheet, row you will able to see all related function. I hope this small description will provide you enough help…if any one have any question feel free to contact me… Thanks & Regards Diwakar Gupta

12/15/2006 03:12 A - Gary said... Well, I'm trying to reproduce Ben Nadel's post http://www.bennadel.com/index.cfm?dax=blog:427.view using HSSF.  But I'm stuck on trying to get the number of columns from a worksheet.  There are 2 classes (MulBlankRecord and MulRKRecord) that have a method "getNumColumns()", but I can't get it to work.

12/15/2006 04:05 A - di_wakar said... HI Gary, you need to create object of row & than you can use this function: getLastCellNum ().it will return the last column number.

01/22/2007 12:53 P - Robin Jacob said... Hi ,   My name is robin ....i am using an java web based application for  reporting.   Application : Retriving data from a database(65000 rows & 64 Columns) --> open a workbook (HSSFWorkbook) -->Open a sheet  (HSSFSheet sheet = wb.createSheet                      ("Sheet1");) --> Create rows and Columns on iteration with the data using the resultset                                       HSSFRow row = null;                    HSSFCell  cell = null;                    while(sqlBean1.getNextRecord()){  //itrating the result set from database (65500 itrations)                            i+=1;                            row = sheet.createRow((int)i);                            createCell( row, (short) 0, ""+(sqlBean1.getAsStringNA(1)));                            ..about 64 same line (createCellInt)                    }                                       private void createCell(HSSFRow row, short column,String value){                               cell = row.createCell(column);                               cell.setCellValue(value);                               row = null;                               value = null;                               cell = null;                   }                     After this i got an excel file of about 50MB and i am dowloading it   Problem : Its taking more memory of about 315 MB is showing in Task manager (java.exe). I am testing in Tomcat 5.5  and i incerase the JVM memory of about                  512MB using (Xms and Xmx).                   This memory issue is comming exactly on the itration part its using much memory in result set because i replaced the while loop with for loop of 65500                   itrations and put dummy data and checked then also the same memory is used. So i concluded the memory usage is comming exctly in  (row =                   sheet.createRow((int)i); createCell( row, (short) 0, ""+(sqlBean1.getAsStringNA(1)));) these two lines                   For one user this works fine....But if two users coming then memory out exception is throwing and server will get shutdown..my application is purelly                  webbased so i cannot identify no of users and incerase the memry of server...i am wondering how that perticular lines may consume this much amount                 of memory even i used the System.gc() and nullify all objects but no progress...   POI version :  poi-scratchpad-3.0-alpha2-20060616                       poi-contrib-3.0-alpha2-20060616                       poi-3.0-alpha2-20060616    JDK : 1.5   Tomcat : 5.5   Will any one please identify the exact problem or suggest me a solution for the problem i am facing.... following i paste the complete code   private HSSFCell  cell = null;   public HSSFWorkbook loadingFeDrillDown_Condor_TL(VB_Condor_FE vB_Condor_FE)throws Exception{         File fl = new File("");         FileInputStream fs = new FileInputStream(fl);         POIFSFileSystem fileRead=new POIFSFileSystem(fs);         HSSFWorkbook wb = new HSSFWorkbook(fileRead);//new HSSFWorkbook();         String param1 = null;         String param2 = null;         String param3 = null;         String param4 = null;         String param6 = null;                 param1 = ((vB_Condor_FE.getInt_yr() == 0)?""+2006:""+vB_Condor_FE.getInt_yr());          param2 = ((vB_Condor_FE.getInt_qtr() == 0)?null:""+vB_Condor_FE.getInt_qtr());         param3 = ((vB_Condor_FE.getInt_mn() == 0)?null:""+vB_Condor_FE.getInt_mn());         param4 = ((vB_Condor_FE.getInt_wk() == 0)?null:""+vB_Condor_FE.getInt_wk());         param6 = ((vB_Condor_FE.getCn() == null)?null:"'"+vB_Condor_FE.getCn()+"'");                 UB_Condor_Headers uB_Condor_Headers = new UB_Condor_Headers();         uB_Condor_Headers.setSQLGetRecordBean(sqlBean);         VB_Condor_Headers vB_Condor_Headers = uB_Condor_Headers.getCompleteData("type = 'Condor_FE_Drilldown' and header1 = 'Condor_TL'");         ////System.out.println("vB_Condor_Headers : "+vB_Condor_Headers);         HSSFSheet sheet = wb.createSheet("FEDDD_TL");         HSSFRow row1 = sheet.createRow((short)0);         HSSFCellStyle cellStyle = wb.createCellStyle();                 HSSFFont f=wb.createFont();         f.setFontHeightInPoints((short) 12);         f.setColor( (short)0xc );         f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         cellStyle.setFont(f);                 for(int i=0;i<vB_Condor_Headers.getInt_id_data().length;i++){             sheet.setColumnWidth((short) (i), (short) ((50 * 8) / ((double) 1 / 20)));             HSSFCell cell=row1.createCell((short) (i));             cell.setCellStyle(cellStyle);             cell.setCellValue(vB_Condor_Headers.getStr_header2_data()[i]);         }         try{             sqlBean1.makeConnection();                         System.out.println("call Condor_FE_DrillDown_ColTotal "+param1+", "+param2+", "+param3+", "+param4+","+param6+"");             if(sqlBean1.getRecord("{call Condor_FE_DrillDown_ColTotal("+param1+", "+param2+", "+param3+", "+param4+","+param6+")}")){             HSSFRow row = null;             int i = 0;             while(sqlBean1.getNextRecord()){                 if(i>65500){break;}                 i+=1;                 row = sheet.createRow((int)i);                 createCell( row, (short) 0, ""+(sqlBean1.getAsStringNA(1)));                 createCell( row, (short) 1, ""+(sqlBean1.getAsStringNA(2)));                 createCell( row, (short) 2, ""+(sqlBean1.getAsStringNA(3)));                 createCell( row, (short) 3, ""+(sqlBean1.getAsStringNA(4)));                 createCell( row, (short) 4, ""+(sqlBean1.getAsStringNA(5)));                 createCell( row, (short) 5, ""+(sqlBean1.getAsStringNA(6)));                 createCell( row, (short) 6, ""+(sqlBean1.getAsStringNA(7)));                 createCell( row, (short) 7, ""+(sqlBean1.getAsStringNA(8)));                 createCell( row, (short) 8, ""+(sqlBean1.getAsStringNA(9)));                 createCell( row, (short) 9, ""+(sqlBean1.getAsStringNA(10)));                 createCell( row, (short) 10, ""+(sqlBean1.getAsStringNA(11)));                 createCell( row, (short) 11, ""+(sqlBean1.getAsStringNA(12)));                 createCell( row, (short) 12, ""+(sqlBean1.getAsStringNA(13)));                 createCell( row, (short) 13, ""+(sqlBean1.getAsStringNA(14)));                 createCell( row, (short) 14, ""+(sqlBean1.getAsStringNA(15)));                 createCell( row, (short) 15, ""+(sqlBean1.getAsStringNA(16)));                 createCell( row, (short) 16, ""+(sqlBean1.getAsStringNA(17)));                 createCell( row, (short) 17, ""+(sqlBean1.getAsStringNA(18)));                 createCell( row, (short) 18, ""+(sqlBean1.getAsStringNA(19)));                 createCell( row, (short) 19, ""+(sqlBean1.getAsStringNA(20)));                 createCell( row, (short) 20, ""+(sqlBean1.getAsStringNA(21)));                 createCell( row, (short) 21, ""+(sqlBean1.getAsStringNA(22)));                 createCellInt( row, (short) 22, sqlBean1.getAsIntNA(23));                 createCellInt( row, (short) 23, sqlBean1.getAsIntNA(24));                 createCellInt( row, (short) 24, sqlBean1.getAsIntNA(25));                 createCell( row, (short) 25, ""+(sqlBean1.getAsStringNA(26)));                 createCellInt( row, (short) 26, sqlBean1.getAsIntNA(27));                                 }             }         }catch(Exception e){System.out.println(e);}            return wb;     }     private void createCellInt(HSSFRow row, short column,int value){             cell = row.createCell(column);             cell.setCellValue((double) value);             row = null;             cell = null;     }         private void createCell(HSSFRow row, short column,String value){             cell = row.createCell(column);             cell.setCellValue(value);             row = null;             value = null;             cell = null;     }     Thanks and Regards Robin Jacob

01/30/2007 03:55 A - sweeper said... Hi guys, did anybody try inserting comments on the cells with the new features of poi-3.0-alpha3-20061212.jar ?

01/31/2007 06:19 A - sweeper said... Sorry, 20061212.jar does not contain cell comments ability the latest nightly build is at: http://encore.torchbox.com/poi-svn-build/

03/29/2007 11:39 A - Benoit said... Génération d'un fichier excel 'à la volée' en CFM + POI pour ceux qui semblent intéréssés: <cfscript>     context = getPageContext();     context.setFlushOutput(false);     response = context.getResponse().getResponse();     out = response.getOutputStream();     response.setContentType("application/msexcel"); wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(); sheet1 = wb.createSheet("new sheet"); sheet2 = wb.createSheet("second sheet"); row = sheet1.createRow(0); cell = row.createCell(0); cell . setCellValue(1); row.createCell(1).setCellValue(javacast("double","1.2")); row.createCell(2).setCellValue("This is a string"); row.createCell(3).setCellValue(true); wb.write(out);     out.flush();     out.close(); </cfscript> Attention c'est un gros hack (response.getResponse().getResponse() issu de http://weblogs.macromedia.com/cantrell/archives/2003/06/using_coldfusio.cfm

04/05/2007 04:53 P - Brian Oeding said... I have used a number of the POI methods for Excel successfully, but I have been searching for a couple more or their equivalent without success.  1) Essentially I'm trying to do a copy and paste on a range of cells. The Excel Macro equivalent is:     Range("D1:Q28").Select     Selection.Copy     Range("C1").Select     ActiveSheet.Paste 2) Remove Columns (like the RemoveRows method example shown below) // Delete the first row which is the header row. LOCAL.SheetData.Query.RemoveRows( JavaCast( "int", 0 ), JavaCast( "int", 1 ) ); Any suggestions?

04/05/2007 05:47 P - benoit said... Sorry for the french comment below, essentially it"s an example of streaming the generated HSSF object to the browser without using a temporary file. As for response.getResponse().getResponse() it's as i said below a big hack i found on another blog.

04/25/2007 03:34 A - celebrity said... Absolutely right comments means suggestion of some words but some people write a article bigger than the orignal post.

05/02/2007 04:15 P - Nath said... Hi Stacey, I saw that you have done one excel which has dropdown in one cell. My program is : i have to prepare one excel sheet with about 60 columns, out of which some are dropdowns and the values in dropdown have to be brought in from database and prepare excel template and provide it to the users to download it. Once the user uploads that excel template with data, i have to read all the values in the excel and save in database. Can some one help me in preparing the excel template, my special concern is with creating those dropdown columns. Please provide me an example if someone has created excel with dropdown columns. Thanks Nath

06/04/2007 04:52 A - Queena said... Dear di_wakar, I had put the poi-2.5.1-final-20040804.jar file into the class path. Done the setting in Coldfusion administrator and restart the service. I have paste your source code and try to run but got the error : Just in time compilation error Invalid parser construct found on line 10 at position 63. ColdFusion was looking at the following text: . Invalid expression format. The usual cause is an error in the expression structure. The error is from this code: <cfset fileIn = createObject("java","java.io.FileInputStream").init("./eSurveySample.xls")/> Anyone can tell me anything wrong? Hope to hear the response soon. Thanks. Queena

06/12/2007 04:29 A - li said... Jxcell spreadsheet component is also a good choice to deal with excel files.

06/12/2007 04:58 P - Lee said... Hi all - All I need to know before I venture into this POI stuff, can a CF program write to an xls file that is shared with other users, i.e, if another user has full permission to the workbook and has it open first and CF writes to that workbook, will it work properly or cause an error.  I know that a shared workbook is possible using EXCEL by itself, but combining write access in CF with EXCEL write access, will it perform the necessary share/lock scenario to keep the data intact?  Or just not possible?

06/27/2007 05:18 A - Ambar said... Hi,     I am trying to read an excel using POI. My problem is that the excel sheets I have to read have lot of drop downs and check boxes....POI is throwing exception in creating Record for it.... can somebody plz help me with this.

07/30/2007 04:29 A - Catcha said... Hi,    I'm trying to read a spesific sheet, row and cell in the excel file and output at html. I encountered a problem when trying to get the format of the cell from the Excel file. How can i determine what format index(eg. 0,1,2...) the cell is using in the excel file so that i can output correctly? Please help. Thanks.

07/30/2007 09:16 P - Catcha said... Hi Diwakar,       Thanks for your reply. Really appreciate that. The sample code that you have provided is very helpful in getting the type of value in a cell. What I was trying to do is to get the format of the data from the cell.     I have figured out the way to do it as below: <cfset temp = wb.createDataFormat()>  <cfset CellFormat = temp.getFormat(cell.getCellStyle().getDataFormat())> Once output the "CellFormat", we can get the format of the data in the cell.

08/06/2007 11:52 A - Zdenek said... Hi, I have figured out there is a small mistake using \n in *Using newlines in cells:*. #chr(10)# is needed in CF.

08/08/2007 01:25 P - Brian said... I have a workbook that I edit a particular sheet i.e. 'DataSheet' using POI successfully with CF7.02 and CF8. The problem is I have a chart on another sheet in the workbook with a formula referencing 'DataSheet' i.e. 'Chart Title=DataSheet!$AA$2'. Every time I use POI to modify 'DataSheet', my formulas convert the previous value to text e.g., Chart Title= [blank], but the real value showing up on the chart is 'Summary' for instance. There is a Macro and the chart itself and their references are both fine, just the standalone formula references. Any ideas why this occurs or how to fix it without recreating the formulas each time?

09/24/2007 09:10 A - Brian said... Who should I contact to request help with this Excel Chart bug I found with HHSF I mentioned on 8/8/07 above?

11/02/2007 12:11 P - Stephanie said... I get the following error from the following code.  Does anyone have any ideas why? Object Instantiation Exception. An exception occurred when instantiating a java object. The cause of this exception was that: . Object Instantiation Exception. An exception occurred when instantiating a java object. The cause of this exception was that: . at setting wb <cfset fileIn = createObject("java","java.io.FileInputStream").init("D:\workbook.xls")/> <cfset fs = createObject("java","org.apache.poi.poifs.filesystem.POIFSFileSystem").init(fileIn)/> <cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fs)/>

11/02/2007 12:22 P - Joel LaTondress said... Make sure you don't have the worksheet you are trying to write to open....that is usually when I see that error.

11/02/2007 12:32 P - Stephanie said... Nope, not open.:>(

11/02/2007 01:15 P - Stephanie said... the first row is a filtering row with drop downs. If I remove that it works. Of course, I need the user to be able to have these so....

11/02/2007 01:21 P - Joel L said... I did experience that using an auto-filter on the template - unfortunately I did not find a work-around besides removing them.

11/02/2007 01:47 P - Stephanie said... I see stacy said above that her errors went away when she put the new versions of poi-3.0.....jar in the lib on the server but I can add these files but not delete the old ones....

11/02/2007 03:19 P - Stephanie said... I got the files deleted and now it works! Thanks guys/gals!

11/05/2007 11:25 A - Matt said... I have 5 coldfusion templates. I'd like to be able to export each template onto an individual worksheet in excel. They have a ton of data on them so I want to just export them the way there are rather than creating a row/cell in excel. Is there a way to do this? Thanks so much!!!!

04/20/2009 03:11 P - Patiman said... I am attempting to get POI to dump more than 65536 records... is that possible? I ran a test and looped over and had it generate 100,000 rows - didn't get any errors, but then when I open the XLS file in any version of Excel including 2007 it will only show the first 65535 rows... Any way to have it generate more than this?

06/04/2009 03:57 P - Dorthy said... When using the following code it attempts to write it to the server.  How do you correct this? <cfset fileOut = createObject("java","java.io.FileOutputStream").init("C:\workbook.xls")/>

07/24/2009 11:08 A - Naveen said... Is there any way to set selected columns as read-only on data download.

11/04/2009 09:54 A - Carlos M. said... Any idea how to access the build in format that uses the Euro symbol. I'm creating the following style: <cfset dollarStyle = workbook.createCellStyle()/> <cfset dollarStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("($##,####0.00_);[Red]($##,####0.00)")) /> <cfset dollarStyle.setFont(normalFont)/> and later using is like: <cfset cell.setCellStyle(dollarStyle)/> I'd like to do the same with the Euro format, but can't seem to figure that one out...

03/01/2010 12:40 P - Patrick Whittingham said... Do these java libraries read/write powerpoint sldes?

04/13/2010 03:21 P - Dana said... Any ideas on how to have the output as landscape instead of Portrait?

Post a comment:

Name:
Email:	(required, will not be displayed)
Website:
Subscribe:
Comment:
Remember me?
Enter the text you see in the box below it:
You will be sent an email asking you to validate your comment.