Saturday, August 30, 2008

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>

8 comments:

Murray,  December 16, 2008 at 3:16 AM  

Hi CFSearching.

I am using the POI jars and when I run the first example on this page I get the error "ERROR: The selected method createCell was not found. Either there are no methods with the specified method name and argument types, or the method createCell is overloaded with arguments types that ColdFusion can't decipher reliably. If this is a Java object and you verified that the method exists, you may need to use the javacast function to reduce ambiguity."

It may be that I havent created my javaloader properly.

Do you know if there is a POI function call to return the current version of the jars so I can see what version CF is linking in? iText has that functionality and I was wondering whether POI did too. I have trawled through the api docs and cant find a method.

I am using version poi-bin-3.2-FINAL-20081019

Do you know of such a method?

Thanks, Murray.

cfSearching December 16, 2008 at 7:35 AM  

Hi Murray,

It is probably because the createCell() method is overloaded. So CF does not know which which method to call: createCell(int) or createCell(short). Try using javacast to reduce the ambiguity.

cell1 = sheet.createRow( javacast("int", 3) ).createCell( javacast("int", 1) );

> Do you know if there is a POI
> function call to return the
> current version of the jars so I > can see what version CF is
> linking in?

An old, but very useful tip from Brandon Purcell's blog shows how to do this for any class:

Identifying Which Jar file a class was loaded from in ColdFusion MX

Example using POI:
<cfscript>
resourceClass = "org/apache/poi/hssf/usermodel/HSSFSheet.class";
resourceURL = getClass().getClassLoader().getResource(resourceClass);
if ( IsDefined("resourceURL") ) {
WriteOutput(resourceClass &"="& resourceURL);
}
else {
WriteOutput("could not resolve resource");
}
</cfscript>

cfSearching December 16, 2008 at 7:43 AM  

> which which method

Well drat. My edit did not take ;-)

Leigh

Murray,  December 16, 2008 at 12:39 PM  

Hi Ben,

Now I am really confused! :-)

When I ran your script I got

jar:file:/C:/CFusionMX7/runtime/../lib/poi-2.5.1-final-20040804.jar!/org/apache/poi/hssf/usermodel/HSSFSheet.class

ie the builtin version.

Thinking that was strange I ran the following code to test my iText jars. First, I ran an app that uses iText classes and methods that are not in the cf mx7 itext (eg pdfptable) to make sure that the javaloader was working correctly. All tested OK.

Then I ran the following script (using Brandon's code) and got the results listed below.

(Sorry about some missing < in the code below - your comment entry window wouldnt allow tags)

cffunction name="resolveResource" returnType="string">
cfset var resourceURL = getClass().getClassLoader().getResource(resource)>
cfif isDefined("resourceURL")>
cfreturn resourceURL>
/cfif>
cfreturn "could not resolve resource">
/cffunction>

cfoutput>

cfset resource="org/apache/poi/hssf/usermodel/HSSFSheet.class">
p>#resource#: [#resolveResource(resource)#]

cfset resource="com/lowagie/text/pdf/PdfPTable.class">
p>#resource#: [#resolveResource(resource)#]

/cfoutput>

Results:

org/apache/poi/hssf/usermodel/HSSFSheet.class: [ jar:file:/C:/CFusionMX7/runtime/../lib/poi-2.5.1-final-20040804.jar!/org/apache/poi/hssf/usermodel/HSSFSheet.class]

com/lowagie/text/pdf/PdfPTable.class: [ jar:file:/C:/CFusionMX7/runtime/../lib/iText.jar!/com/lowagie/text/pdf/PdfPTable.class]

Now, my javaloaded jars are in C:\CFusionMX7\wwwroot\Sites\CFLib\cfc\javaclass and I double checked to make sure they werent in the path reported above.

Apart from all that, I used the javaCast as you suggested on your example script on this page and still got the original error (method createCell was not found).

I am using MX7.

This is strange. As a final test I tried Brandon's method on one of my own test java classes that I am javaLoading. The class is there because this works:

testObj = variables.javaLoader.create("itextTests.test1").init();

result = testObj.echo("testing");

WriteOutput("
test1= "&result);

but when I run this:
cfset resource="itextTests/test1.class">
p>#resource#: [#resolveResource(resource)#]

I get:
itextTests/test1.class: [ could not resolve resource]

So, I just wanted to check that Brandon's method does work with javaLoaded classes and that my path "itextTests/test1.class" has the correct syntax.

Thanks,
Murray

Murray,  December 16, 2008 at 1:33 PM  

and I called you Ben instead of Leigh!!! I had also been browsing Ben Nadel's blog.

My apologies - a senior moment, I guess.

Murray

cfSearching December 16, 2008 at 1:46 PM  

@Murray,

{smacks forehead}

Brandon's method does work. But I forgot that it checks the built in CF classLoader. Since we are using the JavaLoader, we should check the one the JavaLoader uses.

Try this. It worked for me with MX 7:

<cfscript>
// get javaLoader reference
javaLoader = server[ application.myJavaLoaderKey ];
// create test workbook object
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();

// get the class name (without the package) ie HSSFWorkbook.class
className = listLast( wb.getClass().getName(), ".") &".class";
resourceURL = wb.getClass().getResource( className );
WriteOutput(resourceURL &"
");
</cfscript>


> I used the javaCast as you
> suggested on your example script > on this page and still got the
> original error


I tried createCell and it did work for me with MX7. Even without the JavaLoader. What happens if you split the code into two lines?

// use javacast
row = sheet.createRow( 3 );
cell1 = row.createCell( 1 );

Murray,  December 16, 2008 at 2:16 PM  

Yes, using the javaLoader getClass worked fine. Thanks for clearing that up.

Now it is my turn to smack my forehead....

Your suggestion of using the javaCast was correct all along! I just failed to notice that the createCell() method was used twice and I only changed the first one. That's what I get for pushing on when I should be sleeping!!!

Thanks again for your help.

Cheers,
Murray

cfSearching December 16, 2008 at 3:07 PM  

@Murray,

You are welcome!

> and I called you Ben instead of
> Leigh!!! I had also been browsing > Ben Nadel's blog.

No problem at all. That is exactly what I figured ;-)

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep