POI Examples - Part III (Text Extraction, Shapes, Outlines, ...)

A few more POI translations from the Busy Developers' Guide to HSSF Features. Note: I wanted the examples to be complete and self contained. So you may notice they contain more code than the examples in the POI guide.

Pre-requisites:

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



Styling Shapes

<h1>Styling Shapes</h1>
<cfscript>
 pathToOutputFile = "c:/StylingShapes.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 an anchor to position the shape in the sheet
     HSSFClientAnchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
     a = HSSFClientAnchor.init( 0, 0, 1023, 255, 1, 0, 1, 0 );

     // create a simple shape using the anchor
     s = patriarch.createSimpleShape( a );

     // create an oval shape
     HSSFSimpleShape = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFSimpleShape");
     s.setShapeType( HSSFSimpleShape.OBJECT_TYPE_OVAL );

     // set the line and fill color using r,g,b (red, green, blue) values
     s.setLineStyleColor( 10, 10, 10 );
     s.setFillColor( 90, 10, 200);
     // use a dotted line with a 3pt thickness for this shape
     s.setLineWidth( HSSFSimpleShape.LINEWIDTH_ONE_PT * 3 );
     s.setLineStyle( HSSFSimpleShape.LINESTYLE_DOTSYS );

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

Outlining

<h1>Outlining</h1>
<cfscript>
 pathToOutputFile = "c:/Outlining.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();
     sheet1 = wb.createSheet("new sheet");
  
     sheet1.groupRow( 5, 14 );
     sheet1.groupRow( 7, 14 );
     sheet1.groupRow( 16, 19 );

     sheet1.groupColumn( 4, 7 );
     sheet1.groupColumn( 9, 12 );
     sheet1.groupColumn( 10, 11 );

      //To collapse (or expand) an outline use the following calls:

     sheet1.setRowGroupCollapsed( 7, true );
     sheet1.setColumnGroupCollapsed( 4, true );
              
              
     // 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>

Iterate over rows and cells

<h1>Iterate over rows and cells</h1>
<cfscript>
 pathToOutputFile = "c:/iteratorData.xls";

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

 try {

     // create a sample file to use for this example
     wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
     sheet = wb.createSheet("new sheet");
  
     // populate a few rows and cells with data
     row = sheet.createRow( 0 );
     row.createCell( 0 ).setCellValue("Name");
     row.createCell( 1 ).setCellValue("Location");
     row = sheet.createRow( 1 );
     row.createCell( 0 ).setCellValue("Alice");
     row.createCell( 1 ).setCellValue("East");
     row = sheet.createRow( 2 );
     row.createCell( 0 ).setCellValue("Bob");
     row.createCell( 1 ).setCellValue("South");
     row = sheet.createRow( 3 );
     row.createCell( 0 ).setCellValue("Alan");
     row.createCell( 1 ).setCellValue("North");
    
     // save the file to disk
     fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
     wb.write( fileOut );
     fileOut.close();

     // read the input file back in
     fileIn = createObject("java", "java.io.FileInputStream").init( pathToOutputFile );
     wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init( fileIn );

     // Iterate through cell values and print to screen
     sheet = wb.getSheetAt(0);
     rit = sheet.rowIterator();
     while (rit.hasNext()) {
         row = rit.next();
         cit = row.cellIterator();

         while (cit.hasNext()) {
             cell = cit.next();
             // display the cell value, row and column index
             WriteOutput("row["& row.getRowNum() &"]col["& cell.getCellNum() &"]=");
             WriteOutput(cell.getRichStringCellValue().toString() &"<br>");
         }

     }


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

Text Extraction

<h1>Text Extraction</h1>
<cfscript>
 pathToInputFile = "c:\textExample.xls";

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

 try {
     fileIn = createObject("java", "java.io.FileInputStream").init( pathToInputFile );
     wb = javaLoader.create("org.apache.poi.hssf.usermodel.HSSFWorkbook").init( fileIn );
     extractor =javaLoader.create("org.apache.poi.hssf.extractor.ExcelExtractor").init( wb );

     // Iterate through cell values and print to screen
     extractor.setFormulasNotResults( true );
     extractor.setIncludeSheetNames( false );
     text = extractor.getText();
  
     WriteOutput( text );      
     WriteOutput("<br>Finished!");
 }
 catch (Any e) {
     WriteOutput("ERROR: "& e.message &" "& e.detail);
 }
 // always close the stream
 if ( structKeyExists(variables, "fileIn") ) {
     fileIn.close();
 }
</cfscript>