POI Examples - Part I

Update (02/13/2009): The POI 3.5 beta now supports Excel 2007 format. See also the updated examples for xlsx format.

The POI Quick Guide, on poi.apache.org, has some great introductory examples on using POI. Of course the examples are in java. Since I have seen several POI questions lately, I thought a ColdFusion translation of some of the basics might be helpful for those new to using POI with ColdFusion.

On a side note..
Interestingly, a houseoffusion.com subscriber named Tyler Fitch mentioned there is a version of POI bundled with ColdFusion 8. It might also be bundled with MX7. But I am not certain about that. In any case, this means it is possible to use POI under ColdFusion 8 without installing any new jars.

But before you get too excited, the version that ships with ColdFusion 8 is not the latest. An adobe forum member named BKBK recently pointed out that the built-in jar is version 2.5.1. It is also a partial version only. It does not contain some packages like HWPF (Horrible Word Processor Format). HWPF is used to work with MS Word Files. Still, it is neat to know that you can use POI right out of the box.

If you plan on doing any serious work with POI, you will probably want to use the latest version. In which case you can use the JavaLoader.cfc. It allows you load a newer version of POI without breaking ColdFusion. See How to install POI on ColdFusion using the JavaLoader.cfc


Anyway, here are the first few examples from the Quick Guide. I will add more as I get time.

Source: Apache Busy Developers' Guide to HSSF Features
HSSF Examples:

• New Workbook


• New Sheet


• Creating Cells


• Creating Date Cells


• Working with different types of cells

New Workbook

<h1>New Workbook</h1>
<cfscript>
pathToOutputFile = "c:/newWorkbook.xls";

try {
   // create a workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
   // create an output stream for saving the file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   // save the workbook to disk
   wb.write( fileOut );
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>             

New Sheet

<h1>New Sheet</h1>
<cfscript>
pathToOutputFile = "c:/newSheet.xls";

try {
    // create a workbook object
    wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    // add new worksheets
    sheet1 = wb.createSheet("new sheet");
    sheet2 = wb.createSheet("second sheet");
    // create an output stream for saving the file
    fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
    // save the workbook to disk
    wb.write( fileOut );
    WriteOutput("Finished!");
}
catch (Any e) {
    WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
    fileOut.close();
}
</cfscript>

Creating Cells

<h1>Creating Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingCells.xls";

try {
   // create a workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    sheet = wb.createSheet("new sheet");

   // Create a row and put some cells in it. Rows are 0 based.
   row = sheet.createRow( 0 );
   // Create a cell and put a value in it.
   cell = row.createCell( 0 );
   cell.setCellValue( 1 );

   // Or do it on one line.
   row.createCell( 1 ).setCellValue( 1.2 );
   row.createCell( 2 ).setCellValue( "This is a string" );
   row.createCell( 3 ).setCellValue( true );

   // Write the output to a file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   wb.write( fileOut );
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>

Creating Date Cells

<h1>Creating Date Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingDateCells.xls";

try {

   // create a new workbook object
   wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
   // add a new sheet
   sheet = wb.createSheet("new sheet");

   // Create a row and put some cells in it. Rows are 0 based.
   row = sheet.createRow( 0 );

   // Create a reusable date object
   JavaDate = createObject("java", "java.util.Date");

   // Create a cell and put a date value in it.
   // The first cell is not styled as a date.
   cell = row.createCell( 0 );
   cell.setCellValue( JavaDate.init() );

   // 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.
   cellStyle = wb.createCellStyle();
   HSSFDataFormat = createObject("java", "org.apache.poi.hssf.usermodel.HSSFDataFormat");
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
   cell = row.createCell( 1 );
   cell.setCellValue( JavaDate.init() );
   cell.setCellStyle( cellStyle );

   // Write the output to a file
   fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
   wb.write(fileOut);
   WriteOutput("Finished!");
}
catch (Any e) {
   WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
   fileOut.close();
}
</cfscript>

Working with different types of cells

<h1>Working with different types of cells</h1>                
<cfscript>
pathToOutputFile = "c:/differentTypesOfCells.xls";

try {
    // create a workbook object
    wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
    // create a new worksheet and add a row
    sheet = wb.createSheet("new sheet");
    row = sheet.createRow( 2 );

    // create different types of cells
    HSSFCell = createObject("java", "org.apache.poi.hssf.usermodel.HSSFCell");
    JavaDate = createObject("java", "java.util.Date");
    row.createCell( 0 ).setCellValue( 1.1 ); // numeric
    row.createCell( 1 ).setCellValue( JavaDate.init() ); // date (not formatted)
    row.createCell( 2 ).setCellValue( "a string" ); // string
    row.createCell( 3 ).setCellValue( true ); // boolean
    row.createCell( 4 ).setCellType( HSSFCell.CELL_TYPE_ERROR );

    // Write the output to a file
    fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
    wb.write(fileOut);
    WriteOutput("Finished!");
}
catch (Any e) {
    WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the output stream
if ( structKeyExists(variables, "fileOut") ) {
    fileOut.close();
}
</cfscript>