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.cfcOutput:
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
...Read More
<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>
::
8
comments
Posts
