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>

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

Friday, August 29, 2008

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

Thursday, August 28, 2008

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

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>

...Read More

Sunday, August 3, 2008

POI Examples - Part I


Update (02/13/2009): The POI 3.5 beta now supports Excel 2007 format. See also the updated examples for xlsx format.


The POI Quick Guide, on poi.apache.org, has some great introductory examples on using POI. Of course the examples are in java. Since I have seen several POI questions lately, I thought a ColdFusion translation of some of the basics might be helpful for those new to using POI with ColdFusion.

On a side note..
Interestingly, a houseoffusion.com subscriber named Tyler Fitch mentioned there is a version of POI bundled with ColdFusion 8. It might also be bundled with MX7. But I am not certain about that. In any case, this means it is possible to use POI under ColdFusion 8 without installing any new jars.

But before you get too excited, the version that ships with ColdFusion 8 is not the latest. An adobe forum member named BKBK recently pointed out that the built-in jar is version 2.5.1. It is also a partial version only. It does not contain some packages like HWPF (Horrible Word Processor Format). HWPF is used to work with MS Word Files. Still, it is neat to know that you can use POI right out of the box.

If you plan on doing any serious work with POI, you will probably want to use the latest version. In which case you can use the JavaLoader.cfc. It allows you load a newer version of POI without breaking ColdFusion. See How to install POI on ColdFusion using the JavaLoader.cfc


Anyway, here are the first few examples from the Quick Guide. I will add more as I get time.

Source: Apache Busy Developers' Guide to HSSF Features
HSSF Examples:

New Workbook

<h1>New Workbook</h1>
<cfscript>
pathToOutputFile = "c:/newWorkbook.xls";

try {
// create a workbook object
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
// 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>


New Sheet

<h1>New Sheet</h1>
<cfscript>
pathToOutputFile = "c:/newSheet.xls";

try {
// create a workbook object
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
// add new worksheets
sheet1 = wb.createSheet("new sheet");
sheet2 = wb.createSheet("second sheet");
// 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>


Creating Cells

<h1>Creating Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingCells.xls";

try {
// create a workbook object
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
row = sheet.createRow( 0 );
// Create a cell and put a value in it.
cell = row.createCell( 0 );
cell.setCellValue( 1 );

// Or do it on one line.
row.createCell( 1 ).setCellValue( 1.2 );
row.createCell( 2 ).setCellValue( "This is a string" );
row.createCell( 3 ).setCellValue( true );

// Write the output to a file
fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
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>


Creating Date Cells

<h1>Creating Date Cells</h1>
<cfscript>
pathToOutputFile = "c:/creatingDateCells.xls";

try {

// create a new workbook object
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
// add a new sheet
sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
row = sheet.createRow( 0 );

// Create a reusable date object
JavaDate = createObject("java", "java.util.Date");

// Create a cell and put a date value in it.
// The first cell is not styled as a date.
cell = row.createCell( 0 );
cell.setCellValue( JavaDate.init() );

// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
cellStyle = wb.createCellStyle();
HSSFDataFormat = createObject("java", "org.apache.poi.hssf.usermodel.HSSFDataFormat");
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell = row.createCell( 1 );
cell.setCellValue( JavaDate.init() );
cell.setCellStyle( cellStyle );

// Write the output to a file
fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
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>


Working with different types of cells

<h1>Working with different types of cells</h1>
<cfscript>
pathToOutputFile = "c:/differentTypesOfCells.xls";

try {
// create a workbook object
wb = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
// create a new worksheet and add a row
sheet = wb.createSheet("new sheet");
row = sheet.createRow( 2 );

// create different types of cells
HSSFCell = createObject("java", "org.apache.poi.hssf.usermodel.HSSFCell");
JavaDate = createObject("java", "java.util.Date");
row.createCell( 0 ).setCellValue( 1.1 ); // numeric
row.createCell( 1 ).setCellValue( JavaDate.init() ); // date (not formatted)
row.createCell( 2 ).setCellValue( "a string" ); // string
row.createCell( 3 ).setCellValue( true ); // boolean
row.createCell( 4 ).setCellType( HSSFCell.CELL_TYPE_ERROR );

// Write the output to a file
fileOut = createObject("java", "java.io.FileOutputStream").init( pathToOutputFile );
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>

...Read More

Adding an image to a worksheet with POI

I saw an interesting post on the adobe forums about POI. A member named MinOP mentioned having trouble adding an image to an Excel file . Here is the problematic section of their code:


<cfset patriarch = newSheet.createDrawingPatriarch()/>
<cfset patriarch.createPicture(
createObject("java", "org.apache.poi.hssf.usermodel.HSSFClientAnchor").init(),
loadPicture("asset/images/logo_e2eA.png", workSheet)
)/>


The code is similar to one of the java examples in the POI Quick Guide: adding images to a worksheet. Now what is confusing about the java example is the use of a method called loadPicture. I remember having this question myself. But could not recall the cause or solution.
So I searched my jar files for loadPicture but found nothing. Eventually I found a thread which explained that loadPicture is a private convenience method contained in one of the example classes. The example classes are located in the org.apache.poi.hssf.usermodel.examples.* package. I checked my jar files. Sure enough the examples package was missing. But even if it were present, the loadPicture method is private. Meaning it cannot be used outside its parent class anyway. At least not without modifying the source code. So that meant I needed to create a ColdFusion function that mimics the loadPicture method.

If you look at the java source code for loadPicture, it is very simple. It reads in an image file then adds the raw bytes of the image to a workbook. As you can see the code uses a FileInputStream object to read the image file. Then the bytes of image are copied into a ByteArrayOutputStream object. Finally, the image bytes are converted to an array and added to the workbook using the HSSFWorkbook.addPicture method.


private static int loadPicture( String path, HSSFWorkbook wb ) throws IOException
{
int pictureIndex;
FileInputStream fis = null;
ByteArrayOutputStream bos = null;
try
{
// read in the image file
fis = new FileInputStream( path);
bos = new ByteArrayOutputStream( );
int c;
// copy the image bytes into the ByteArrayOutputStream
while ( (c = fis.read()) != -1)
bos.write( c );

// add the image bytes to the workbook
pictureIndex = wb.addPicture( bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG );
}
finally
{
if (fis != null)
fis.close();
if (bos != null)
bos.close();
}
return pictureIndex;
}

So I created a ColdFusion function to do the same thing. It is worth noting the java example assumes the image is a PNG. It should be adapted it to accept other image types as needed.


<cffunction name="loadPicture" access="public" returntype="numeric" output="false">
<cfargument name="path" type="string" required="true">
<cfargument name="workbook" type="any" required="true">
<cfset var Local = structNew()>

<cfscript>
Local.index = 0;
Local.errorMessage = "";

try {
Local.fis = createObject("java", "java.io.FileInputStream").init( arguments.path );
Local.bao = createObject("java", "java.io.ByteArrayOutputStream").init();

Local.c = Local.fis.read();
while ( Local.c neq -1 )
{
Local.bao.write( Local.c );
Local.c = Local.fis.read();
}

// Note: Assumes image is PNG format. Adapt if needed
Local.index = arguments.workbook.addPicture( Local.bao.toByteArray(),
arguments.workbook.PICTURE_TYPE_PNG
);
}
catch (Any e) {
Local.errorMessage = e.Message &" "& e.Detail;
}
// always close the input stream
if ( structKeyExists(Local, "fis") ) {
Local.fis.close();
}
</cfscript>

<!--- if an error occurred, rethrow it --->
<cfif len(Local.errorMessage)>
<cfthrow message="#Local.errorMessage#" type="UnableToLoadPicture">
</cfif>

<cfreturn Local.index >
</cffunction>



Let us try this again ..
Now after examining at the java code again, I realized there is a much simpler method. The bulk of the java code just reads in the bytes of a file. In ColdFusion 8 you could simply use the FileReadBinary() function to do this, or for MX7 use <cffile action="readBinary" ...>. It is possible they may do the same thing behind the scenes. But I like the simplicity of using a single function or tag. So the code for loadPicture was simplified to:

imageBytes = FileReadBinary( pathToImage );
pictureIndex = book.addPicture( imageBytes, book.PICTURE_TYPE_PNG );

Here is the complete example below. For convenience I threw together a function called getPOIPictureType. It maps a file extension like "jpg" or "png" to a POI picture type. It is not heavily tested, so no guarantees it is not missing file extensions. Use it at your own risk ;)

Add image to worksheet example
How to install POI on ColdFusion 8 using JavaLoader.cfc



<h1>Adding an image to a worksheet with POI </h1>
<cfscript>
// get a reference to the javaLoader stored in the server scope
javaLoader = server[application.myJavaLoaderKey];

pathToOutFile = "c:\excelFileWithImage.xls";
pathToImage = expandPath("c:\myImage.JPG");

//create a new workbook and worksheet
book = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFWorkbook").init( );
sheet = book.createSheet("Image Test");

// extract the image bytes
// FileReadBinary() is ColdFusion 8 specific. For MX 7 use CFFILE action="readBinary"
imageBytes = FileReadBinary( pathToImage );

// add the image bytes to the workbook
fileExtension = listLast( pathToImage, "." );
pictureType = getPOIPictureType( fileExtension, book );
pictureIndex = book.addPicture( imageBytes, pictureType );

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

// IMAGE1: add the picture at top left
anchor = javaLoader.create( "org.apache.poi.hssf.usermodel.HSSFClientAnchor");
picture = patriarch.createPicture( anchor.init() , pictureIndex );
// set anchor to actual width and height
preferredSize = picture.getPreferredSize();
picture.setAnchor( preferredSize );

// IMAGE2: add the picture at specific location. For details see:
// http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFClientAnchor.html
anchor = anchor.init( 0, 0, 0, 255, 4, 5, 4, 7);
pictureIndex = book.addPicture( imageBytes, pictureType );
picture = patriarch.createPicture( anchor , pictureIndex );
preferredSize = picture.getPreferredSize();
picture.setAnchor( preferredSize );


//write the file to disk
outFile = createObject("java", "java.io.FileOutputStream").init( pathToOutFile );
book.write( outFile );
outFile.close();

WriteOutput("Done!");
</cfscript>

<!---
WARNING: File extension were not heavily tested
--->
<cffunction name="getPOIPictureType" access="public" returntype="numeric" output="false">
<cfargument name="fileExtension" type="string">
<cfargument name="workbook" type="any" required="true">

<cfset var type = "" >
<cfswitch expression="#arguments.fileExtension#">
<!--- PNG format --->
<cfcase value="PNG">
<cfset type = arguments.workbook.PICTURE_TYPE_PNG>
</cfcase>
<!--- JPEG format --->
<cfcase value="JPG,JPEG">
<cfset type = arguments.workbook.PICTURE_TYPE_JPEG>
</cfcase>
<!--- Device independant bitmap --->
<cfcase value="BMP,DIB">
<cfset type = arguments.workbook.PICTURE_TYPE_DIB>
</cfcase>
<!--- Mac PICT format --->
<cfcase value="EMF">
<cfset type = arguments.workbook.PICTURE_TYPE_EMF>
</cfcase>
<!--- Mac PICT format --->
<cfcase value="PICT,PCT">
<cfset type = arguments.workbook.PICTURE_TYPE_PICT>
</cfcase>
<!--- Windows Meta File --->
<cfcase value="WMF">
<cfset type = arguments.workbook.PICTURE_TYPE_WMF>
</cfcase>

<cfdefaultcase>
<cfthrow message="Invalid or unhandled file extension [#arguments.fileExtension#]">
</cfdefaultcase>
</cfswitch>

<cfreturn type>
</cffunction>

...Read More

Friday, August 1, 2008

How to install POI on ColdFusion 8 using JavaLoader.cfc


Update (02/07/2009):

The new POI 3.5 beta also supports ooxml files (xlsx,docx,...). For updated instructions see: How to install the POI 3.5 beta on ColdFusion 8 using JavaLoader.cfc

Update (01/15/2010): Changed server scoped lock to named lock in Application.cfc example


I decided to write up a quick set of instructions for using POI with ColdFusion 8 as a reference. If you are not familiar with the JavaLoader.cfc it is a great tool, written by Mark Mandel, that allows you to dynamically load jar files.


Instructions:

1. Download the latest version of POI from http://poi.apache.org/. Extract the jars into the desired directory.

    Example:
    I created a directory under the webroot named "poi" and placed the jar files there. You can use a different location. So long as ColdFusion has permission to access whatever directory you select. Please note the jar file names tend to be based on version and date. So your file names may differ.

    Jar Locations:
    c:\coldfusion8\wwwroot\poi\poi-3.1-FINAL-20080629.jar
    c:\coldfusion8\wwwroot\poi\poi-contrib-3.1-FINAL-20080629.jar
    c:\coldfusion8\wwwroot\poi\poi-scratchpad-3.1-FINAL-20080629.jar

2. Download and install the JavaLoader.cfc, available at javaloader.riaforge.org

    Example:
    I installed the JavaLoader under the webroot

    JavaLoader Location:
    C:\coldfusion8\wwwroot\javaloader\JavaLoader.cfc
    C:\coldfusion8\wwwroot\javaloader\JavaProxy.cfc
    C:\coldfusion8\wwwroot\javaloader\lib\*.*

3. Instantiate the JavaLoader

Usage
// get a reference to the javaLoader
<cfset javaLoader = server[application.myJavaLoaderKey]>

Application.cfc
<cfcomponent>

<cfset this.name = "POIExamples">
<cfset this.sessionManagement = true>
<cfset this.loginStorage = "session">

<cffunction name="onApplicationStart">
<!--- use a unique hard coded key to store the javaLoader in the server structure ---->
<!--- the xxxx is actually a hardcoded UUID value ---->
<cfset var myJavaLoaderKey = "xxxxxx-xxxxxxxx-xxxxxxxxxx-xxxxxxxxxxx_javaloader">
<cfset var jarPaths = arrayNew(1)>

<!--- if the javaLoader was not created yet --->
<cfif NOT structKeyExists(server, myJavaLoaderKey)>

<!--- these are absolute paths to the POI jar files --->
<cfset arrayAppend( jarPaths, expandPath("./poi-3.1-FINAL-20080629.jar")) >
<cfset arrayAppend( jarPaths, expandPath("./poi-contrib-3.1-FINAL-20080629.jar")) >
<cfset arrayAppend( jarPaths, expandPath("./poi-scratchpad-3.1-FINAL-20080629.jar")) >

<!--- re-verify it was not created yet --->
<cfif NOT structKeyExists(server, myJavaLoaderKey)>
<cflock name="#Hash(myJavaLoaderKey)#" type="exclusive" timeout="10">
<!--- create an instance of the JavaLoader and store it in the server scope --->
<cfset server[myJavaLoaderKey] = createObject("component", "javaloader.JavaLoader").init( jarPaths )>
</cflock>
</cfif>
</cfif>

<!--- store the key in the appliation scope for easy access by other pages --->
<cfset application.myJavaLoaderKey = myJavaLoaderKey>
</cffunction>

</cfcomponent>

...Read More

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep