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>