POI Examples - Create/Read Cell Comments

Here is another quick example from the Busy Developers' Guide to HSSF Features. This one demonstrates how to create or read cell comments.

I did run into one issue with the example. The java code uses an unknown method named get(). I could not find this method in the HSSFSheet class. Fortunately, the guide shows two options for reading cell comments. So I just commented out the problematic one.

// cannot find sheet.get(...)
HSSFCell cell = sheet.get(3).getColumn((short)1);

Pre-requisites:

The following examples use the JavaLoader.cfc. See How to install POI on ColdFusion using the JavaLoader.cfc


Output:



Create Cell Comments

<h1>Create Cell Comments</h1>
<cfscript>
pathToOutputFile = "c:\cellComments.xls";

// get a reference to the javaLoader stored in the server scope
javaLoader = server[ application.myJavaLoaderKey ];

try {
 wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
 sheet = wb.createSheet("Cell comments in POI HSSF");

 // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
 patr = sheet.createDrawingPatriarch();

 // create reusable objects
 RichTextString = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFRichTextString");
 HSSFClientAnchor = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFClientAnchor");

 //create a cell in row 3
 cell1 = sheet.createRow( 3 ).createCell( 1 );
 cell1.setCellValue( RichTextString.init("Hello, World") );

 //anchor defines size and position of the comment in worksheet
 comment1 = patr.createComment( HSSFClientAnchor.init(0, 0, 0, 0, 4, 2, 6, 5) );

  // set text in the comment
 comment1.setString( RichTextString.init("We can set comments in POI") );

 //set comment author.
 //you can see it in the status bar when moving mouse over the commented cell
 comment1.setAuthor( "Apache Software Foundation" );

 // The first way to assign comment to a cell is via HSSFCell.setCellComment method
 cell1.setCellComment( comment1 );

 //create another cell in row 6
 cell2 = sheet.createRow( 6 ).createCell( 1 );
 cell2.setCellValue( 36.6 );


 comment2 = patr.createComment( HSSFClientAnchor.init(0, 0, 0, 0, 4, 8, 6, 11) );
 //modify background color of the comment
 comment2.setFillColor( 204, 236, 255 );


 //apply custom font to the text in the comment
 HSSFColorRED = javaLoader.create("org.apache.poi.hssf.util.HSSFColor$RED");
 HSSFFont = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFFont");

 string = RichTextString.init( "Normal body temperature" );
 font = wb.createFont();
 font.setFontName( "Arial" );
 font.setFontHeightInPoints( 10 );
 font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD );
 //font.setColor( HSSFColor.RED.index );
 font.setColor( HSSFColorRED.getIndex() );
 string.applyFont( font );

 comment2.setString( string );
 //by default comments are hidden. This one is always visible.
 comment2.setVisible( true );
 comment2.setAuthor("Bill Gates");

 /**
  * The second way to assign comment to a cell is to implicitly specify its row and column.
  * Note, it is possible to set row and column of a non-existing cell.
  * It works, the commnet is visible.
  */
 comment2.setRow( 6 );
 comment2.setColumn( 1 );

 // save the file to disk
 fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
 wb.write( fileOut );
 fileOut.close();

 WriteOutput("Finished!");
}
catch (Any e) {
 WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the stream
if ( structKeyExists(variables, "fileOut") ) {
 fileOut.close();
}
</cfscript>

Read Cell Comments

Note, this example uses the file created in previous example

<h1>Read Cell Comments</h1>
<cfscript>
// File created in example above
pathToInputFile = "c:\cellComments.xls";

// get a reference to the javaLoader stored in the server scope
javaLoader = server[ application.myJavaLoaderKey ];

try {
 // read in the workbook
 fileIn = createObject("java", "java.io.FileInputStream").init( pathToInputFile );
 wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init( fileIn );
 sheet = wb.getSheetAt( 0 );

 // Note: the first option did not work. method
 // sheet.get(..) could not be found
 //
 // cell = sheet.get( 3 ).getColumn( 1 );
 // comment = cell.getCellComment();

 // Alternatively you can retrieve cell comments by (row, column)
 comment = sheet.getCellComment( 6, 1 );
 if ( IsDefined("comment") ) {
       str = comment.getString();
       author = comment.getAuthor();
       WriteOutput("comment author="& author &"<br>");
       WriteOutput("comment text="& str &"<br>");
 }

 WriteOutput("Finished!");
}
catch (Any e) {
 WriteOutput("ERROR: "& e.message &" "& e.detail);
}
// always close the stream
if ( structKeyExists(variables, "fileIn") ) {
 fileIn.close();
}
</cfscript>