Monday, August 11, 2008

POI Examples - Part II (Drawing Shapes)

So I was happily translating some java examples from the POI Quick Guide, when a blogger named Dave comments that he had already done that ... four years ago! Now while I am not familiar with his site, that did dampen my enthusiasm a bit ;-) Though I cannot say I am surprised. People learn about different topics at varying times and rates. So some things that are new to me, may well be 'old hat' to other developers and vice versa. On the up side, at least someone is learning something new.

Anyway, since POI Quick Guide was updated in the last four years, I decided to post a few of the newer examples. Now, if someone else has already done that too .. oh well. Just file it under the 'more information is better' category and move on to the next blog ;-)

applyFont .. sometime this year?
I did have one problem with the styled text example. The sample code worked without error. However, when I tried to open the file and MS Excel stopped responding. Yegor Kozlov mentioned this may be a bug with HSSFRichTextString.applyFont. So I added the suggested fix to my code which seemed to resolved the problem. All is well that ends well.

Drawing Shapes
Pre-requisites: The following examples use the JavaLoader.cfc. See How to install POI on ColdFusion using the JavaLoader.cfc.


<h1>Drawing Shapes - Simple Shape</h1>
<cfscript>
pathToOutputFile = "c:/drawingSimpleShape.xls";

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

try {
// create a new workbook and add a sheet
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
sheet = wb.createSheet("new sheet");

// WARNING:: This will clear out any existing shapes for the worksheet.
patriarch = sheet.createDrawingPatriarch() ;

// example 1: create a simple shape
HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
a = HSSFClientAnchor.init( 0, 0, 1023, 255, 1, 0, 1, 0 );
shape1 = patriarch.createSimpleShape( a );
HSSFSimpleShape = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFSimpleShape");
shape1.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );

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


<h1>Drawing Shapes - Simple Textbox</h1>
<cfscript>
pathToOutputFile = "c:/drawingSimpleTextBox.xls";

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

try {
// create a new workbook and add a sheet
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
sheet = wb.createSheet("new sheet");

// WARNING:: This will clear out any existing shapes for the worksheet.
patriarch = sheet.createDrawingPatriarch() ;

//create a text box
HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
textbox1 = patriarch.createTextbox( HSSFClientAnchor.init(0,0,0,0,1,1,2,2) );
HSSFRichTextString = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFRichTextString");
textbox1.setString( HSSFRichTextString.init("This is a test") );

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


<h1>Drawing Shapes - Styled Textbox</h1>
<cfscript>
pathToOutputFile = "c:/drawingStyledTextBox.xls";

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

try {
// create a new workbook and add a sheet
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
sheet = wb.createSheet("new sheet");

// WARNING:: This will clear out any existing shapes for the worksheet.
patriarch = sheet.createDrawingPatriarch() ;

//use different fonts to style parts of the text in the textbox
HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
textbox = patriarch.createTextbox( HSSFClientAnchor.init(0,0,0,0,1,1,2,2) );
font = wb.createFont();
font.setItalic( true );
HSSFFont = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFFont");
font.setUnderline( HSSFFont.U_DOUBLE );
HSSFRichTextString = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFRichTextString");
string = HSSFRichTextString.init("Woo!!!");
// this line is bug fix: http://www.mail-archive.com/user@poi.apache.org/msg01129.html
string.applyFont( 0 );
string.applyFont( 2, 5, font );
textbox.setString( string );

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

<h1>Drawing Shapes - Grouped Shapes</h1>
<cfscript>
pathToOutputFile = "c:/drawingGroupedShapes.xls";

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

try {
// create a new workbook and add a sheet
wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
sheet = wb.createSheet("new sheet");

// WARNING:: This will clear out any existing shapes for the worksheet.
patriarch = sheet.createDrawingPatriarch() ;

//example 4: group shapes together
// WARNING: Any group you create should contain at least two other shapes or subgroups.

// Create a shape group.
HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
HSSFShapeGroup = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFShapeGroup");
group = patriarch.createGroup( HSSFClientAnchor.init(0,0,900,200, 2,2,2,2) );

// Create a couple of lines in the group.
HSSFChildAnchor = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFChildAnchor");
HSSFSimpleShape = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFSimpleShape");
shape1 = group.createShape( HSSFChildAnchor.init(3,3,500,500) );
shape1.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );
shape1.getAnchor().setAnchor( 3,3,500,500 );
shape2 = group.createShape( HSSFChildAnchor.init( 1,200,400,600) );
shape2.setShapeType( HSSFSimpleShape.OBJECT_TYPE_LINE );



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

2 comments:

Somebody August 12, 2010 at 10:06 PM  

Hi!

Have you found some way to insert object in Excel doc. e.g. I want to insert a txt file in an xls file using POI

Please suggest. If you can provide some example. it will be great

cfSearching August 13, 2010 at 1:44 PM  

I am not sure about inserting objects. You may have better luck asking over on the POI user list.

-Leigh

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep