Sunday, August 3, 2008

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

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

4 comments:

dave August 3, 2008 at 5:33 PM  

You should do some searching on the Internet before doing a post like this - primarily since I did exactly the same thing for folks almost 4 years ago!!!

(It's the 4th google hit for "coldfusion poi")


http://www.d-ross.org/index.cfm?objectid=9c65ecec-508b-e116-6f8a9f878188d7ca

cfSearching August 3, 2008 at 6:21 PM  

Haha. Yes, I have learned solely from the java examples. So I have never seen that page. But I can see you did thorough job translating them. About the only difference is error handling.

Sorry if you took offense, but people blog about what they are interested in and well .. that leads to a lot of duplication on the internet. Nature of the beast I guess. Personally, I think the more information and knowledge available the better. But thanks for pointing out such a complete list of examples.

Anonymous,  August 21, 2008 at 2:59 AM  

Thanks so much! I'm looking for a word specific example but your post really helped.

Much appreciated!

-Mike

cfSearching August 21, 2008 at 4:42 PM  

@Mike,

Your are very welcome. I am glad you found it useful!

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep