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>

...Read More

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>

...Read More

CF8 Tip: Determine path to cmd.exe programmatically

So I was curious if the path to the windows command line interpreter (cmd.exe) could be obtained programatically. I discovered it can with ColdFusion 8. The java.lang.System class contains a method called getenv that can return the value of a specific environment variable. From what I have read, most windows versions store the path to the command line interpreter in a variable named COMSPEC. So I was able to display the path using this snippet:

<cfset pathToExe = createObject("java", "java.lang.System").getEnv("COMSPEC")>
<cfdump var="Path = #pathToExe#">

.. and display all environment variables using:

<cfset environ = createObject("java", "java.lang.System").getEnv()>
<cfdump var="#environ#">

Unfortunately it does not work with MX 7. MX 7 uses and older jvm (version 1.4), which does not contain one of the getenv() methods and the other is marked as deprecated. Fortunately, getenv() was resurrected in time for jvm 1.6, making it easy to read windows environment variables under ColdFusion 8.

...Read More

MS SQL - Selective BULK INSERT without Format Files

Today I found myself with a simple task that had a small twist. Load a series of text files into an MS SQL database. Normally a quick and easy process using BULK INSERT.

Now BULK INSERT works smoothly when both the target table and input file contain the same number of columns. My problem was I needed to load the data into a table containing an extra identity column. Which meant the files contained fewer columns than the target table. Unfortunately, bulk insert gets confused when you try and selectively insert columns. Using format files is one way to handle this. However, I did not want to go that route because the input file delimiters varied. So I wanted to avoid creating a separate format file for every combination of delimiters.

I kept thinking there had to be some other way to do this. While re-reading the documentation, it dawned on me that the documentation states you can bulk insert into a view, not just tables. Now _that_ had possibilities. I realized I could create a view of my table, that contained only the columns I needed. Then bulk insert into the view, instead of the table. I tried it and it worked perfectly.

Since my input files are just gibberish codes, take this example. Let us say you have a table containing six (6) columns and an input file containing two (2) columns. I deliberately used a significantly different number of columns for demonstration purposes.

First create a view containing only the two columns in the input file: FirstName, LastName.

--- TARGET TABLE
CREATE TABLE ContactStaging
(
RecordID int identity(1,1),
FirstName varchar(100) NOT NULL,
MiddleInit char(1) NULL,
LastName varchar(100) NOT NULL,
Phone varchar(20) NULL,
Fax varchar(20) NULL
)


---  VIEW
CREATE VIEW ContactStagingView
AS
SELECT FirstName, LastName
FROM    ContactStaging

Then run the bulk insert command on the view.

BULK INSERT ContactStagingView
FROM  'c:\input.txt'
WITH
(
    FIELDTERMINATOR = '|'
    , ROWTERMINATOR = '*'
    , KEEPNULLS
)


--- INPUT.TXT 
Kira|McConnnel*
Alan|Glover*
Thomas|Young*
Stuart|Long*

As you can see from the results, only the selected columns in the view were affected.


Now, this was special case. I would normally use a format file but decided the view approach was more suitable in this particular situation. It is also nice to know there is an alternative to format files, when you need one.

...Read More