Populating An Existing, Formatted Excel Document Using ColdFusion And POI : Kinky Solutions : A Student's Perspective by Ben Nadel

Populating An Existing, Formatted Excel Document Using ColdFusion And POI

Posted February 19, 2007 at 8:47 AM

Last week, I was tackling the problem of providing a simple way to format Excel documents using ColdFusion and POI. In the comments to that post, Alan Johnson suggested that instead of trying to create a completely new Excel workbook, that I use an existing Excel workbook with pre-formatted cells. This was absolutely genius. Genius I tell you! I can't believe I never even thought of this.

This kind of solution would be great for reporting systems where you want to create highly customized reports that need to get run on a regular basis. Something repeatable, something whose data is dynamic but whose layout is not. To test this idea, I created a "blank" Excel document and formatted the cells/columns/rows that way I wanted them to end up in the final report:

As you can see, the header and data cells are already formatted complete with background colors, borders, text formatting, text alignment, text wrapping, and there's even a formula that averages the song ratings. Now, it's just a matter of populating them:

Launch code in new window » Download code as text file »

<!---
Create an array to hold data that we are going to use to
populate the existing Excel sheet. This array will be an
array of arrays representing rows of columsn.
--->
<cfset arrData = ArrayNew( 1 ) />
<!---
Populate the data array. To do this, we will just convert
several lists to arrays. Each of these lists will represent
the columns within a given row.
--->
<cfset arrData[ 1 ] = ListToArray(
"Artist,Song,Rating,Length,Play List"
) />
<cfset arrData[ 2 ] = ListToArray(
"Over The Rhine,All I Ever Get For Christmas Is Blue,9.5,4:25,Blue Night"
) />
<cfset arrData[ 3 ] = ListToArray(
"Over The Rhine,Darlin' (Christmas Is Coming),8.0,3:35,Easy Afternoon"
) />
<cfset arrData[ 4 ] = ListToArray(
"Nina Simone,In The Dark,9.0,2:15,Makeout Deluxe"
) />
<cfset arrData[ 5 ] = ListToArray(
"Norah Jones,Turn Me On,9.0,2:33,Makeout Deluxe"
) />
<cfset arrData[ 6 ] = ListToArray(
"Peter Gabriel,In Your Eyes,10.0,5:29,Break Up"
) />
<!---
Now that our data arary is populated we can go about
creating the Excel workbook. When we create this Workbook,
we are going to read in an existing Excel sheet that
already has set formatting. Read in the Excel file using
a File Input Stream.
--->
<cfset objWorkBook = CreateObject(
"java",
"org.apache.poi.hssf.usermodel.HSSFWorkbook"
).Init(
CreateObject(
"java",
"java.io.FileInputStream"
).Init(
CreateObject(
"java",
"java.io.File"
).Init(
ExpandPath( "./pre_formatted.xls" )
)
)
) />
<!---
Now that we have read the existing Excel file into the
WorkBook, let's get the first sheet. This is the sheet
to which we will be writing data, but mainting the
current format.
--->
<cfset objSheet = objWorkBook.GetSheetAt(
JavaCast( "int", 0 )
) />
<!---
Loop over the rows in the data array to start populating
the Excel file with data.
--->
<cfloop
index="intRow"
from="1"
to="#ArrayLen( arrData )#"
step="1">
<!---
Get a pointer to the current row. This will make
referencing it easier as we make are way through
the data. Remember that our ColdFusion array is
one-based, but the rows index is Java and
zero-based.
--->
<cfset objRow = objSheet.GetRow(
JavaCast( "int", (intRow - 1) )
) />
<!---
Loop through the "column" values in our data array
(for this row index).
--->
<cfloop
index="intColumn"
from="1"
to="#ArrayLen( arrData[ intRow ] )#"
step="1">
<!---
Get the cell object whose value we want to set.
Remember that while ColdFusion is one-based,
the index of the cell is zero-based.
--->
<cfset objCell = objRow.GetCell(
JavaCast( "int", (intColumn - 1) )
) />
<!---
For this example, we know that one of the columns
is numeric. We could have just set the value based
on the column index, but I have chosen to go with
a simple numeric check.
--->
<cfif IsNumeric( arrData[ intRow ][ intColumn ] )>
<!---
Set the numeric value. We are setting it as a
float, but the API and the existing Excel
formatting will take care of the display.
--->
<cfset objCell.SetCellValue(
JavaCast(
"float",
arrData[ intRow ][ intColumn ]
)
) />
<cfelse>
<cfset objCell.SetCellValue(
JavaCast(
"string",
arrData[ intRow ][ intColumn ]
)
) />
</cfif>
</cfloop>
</cfloop>
<!---
Now that we have populated our existing Excel file with
data, let's write the updated Excel data to a new data
file. We do NOT want to overwrite the pre-formatted file
as we want to be able to use that again to created
pre-formatted Excel sheets.
--->
<cfset objWorkBook.Write(
CreateObject(
"java",
"java.io.FileOutputStream"
).Init(
CreateObject(
"java",
"java.io.File"
).Init(
ExpandPath( "./pre_formatted_data.xls" )
)
)
) />

Doing this produces this new Excel document:

Notice that all the formatting was kept exactly as it was in the original document. Also, if you look at the code, notice that the outputted document is a different physical file that than the pre-formatted, input document. This allows us to keep a standard format document and then repeatedly generate new documents.

This is very cool! I can already see where this would come into use. I'm thinking corporate logos, freeze panes. I know that this sort of stuff can be done using the POI interface... but why do it if you don't have to? Of course, I still want to come up with a way to easily format Excel documents using some sort of API, but this solution is pretty badass.

Thanks Alan Johnson!

Download Code Snippet ZIP File

Related Blog Entries

Completely Awesome: Whack Your Boss

First Attempt At Excel Document Formatting Hooks Using ColdFusion And POI

Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

I'm blushing Ben. Thanks.

I'm just glad it all worked ;*) .... Cheers!

Posted by Alan Johnson on Feb 19, 2007 at 2:44 PM

Hi Ben
I think the most powerful use of using an existing xls as a template is that you get to include charts. There's no way to use the poi stuff to create a chart, but you are supposed to be able to load data into the right places so that existing charts display with your dynamic series data.
Antony

Posted by Antony on Feb 19, 2007 at 10:34 PM

Antony,

Yeah, that is some seriously cool stuff. Charts and graphics. It's very cool!

Posted by Ben Nadel on Feb 19, 2007 at 11:32 PM

Ben, thanks for all your work on the POI interface. I have found another Java package that takes the idea of populating an existing, formatted Excel Template. It is called jXLS and seems to work pretty well. It uses the Jakarta POI package. It is a separate java class install (i.e., copy a file to a CF server directory), but is also free.

I have written 2 blog posts about jXLS. The first is just what and why and references your work: http://mattw.mxdj.com/populating_an_existing_formatted_excel_document_using_coldfu.htm

The second is how to install and some sample usage code.
http://mattw.mxdj.com/using_jxls_and_jxlsutiltiycfc_to_export_excel_files.htm

Although it seems to solve a different need than what you have been working on, some of your readers may find it interesting.

Posted by Matt Williams on Mar 3, 2007 at 6:40 PM

This looks pretty cool, but I have a question for you, I am in need of something liek this, however, in my template, I need to create a dynamic amount of formatted items...

Is there a way to create extra formatted rows?

Posted by David on May 25, 2007 at 11:33 AM

@David,

Try looking at this:

http://www.bennadel.com/index.cfm?dax=blog:484.view

It talks about using the POI Utility to apply basic CSS to the query record set. It is not perfect but it is the best I have to offer just yet. But, don't download that version of the POI, use this one, it is the most up to date:

http://bennadel.com/index.cfm?dax=blog:624.view

Posted by Ben Nadel on May 25, 2007 at 11:39 AM

I need to add a row to the spreadsheet every time some data is submitted. I want to put it at the next available row, but how do I know where that is?

Posted by Lee on Jun 8, 2007 at 2:42 PM

It doesn't seem that Ben has included it yet, but the java method to get the last row number is
getLastRowNum()

Hope that helps.

Posted by Matt Wiliams on Jun 8, 2007 at 2:49 PM

That's great that I found this POI useful to populate values into existing formatted excel report.

However, if the worksheet has an image and I tried to run the script you wrote, the image disappeared together with the formatting. I want the image to remain there.

Excel error occurred:
------------------------------------------------------------------------

Microsoft Office Excel File Repair Log

Errors were detected in file '\\server2\website\_poi\poi\pre_formatted_data.xls'
The following is a list of repairs:

Damage to the file was so extensive that repairs were not possible. Excel attempted to recover your formulas and values, but some data may have been lost or corrupted.

I could not figure out why Microsoft Office Excel File Repair Log

------------------------------------------------------------------------

Posted by Eileen on Oct 4, 2007 at 4:59 AM

@Eileen,

I have not worked with images in Excel and POI yet. I will post anything that I find.

Posted by Ben Nadel on Oct 4, 2007 at 7:18 AM

@Eileen,

We also found this to be the case with CF7... however, for some reason it would still work in CF6. Not all images, but it could still handle most of them.

I haven't found any workaround for this in the later versions of Cold Fusion.

alan

Posted by Alan Johnson on Oct 4, 2007 at 10:11 AM

FYI... Also we've noticed that the best xls format to work with POI is Excel version 97-2003/2005

Posted by Alan Johnson on Oct 4, 2007 at 10:57 AM

This is awesome man thanks. Current project was giving me a huge headache and this is exactly the solution I needed.

Thanks a million!

Posted by Ryan on May 9, 2008 at 11:19 AM

@Ryan,

Glad to help out. Make sure you check out the most updated project for POI. It has some cool tag-based output:

http://www.bennadel.com/projects/poi-utility.htm

Posted by Ben Nadel on May 9, 2008 at 12:29 PM

I get an 'Object Instantiation Exception' when try to load my current excel file. It works fine with a simple excel file but my file contains macros or something else that this seems to have a problem with. I get the error on

'ExpandPath( "./new_report_template.xls" )'.

The stack trace indicates

'Caused by: org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null'.

Any ideas for a non-java programmer?

Posted by Bret on Jul 10, 2008 at 2:44 PM

@Bret,

Sorry, no suggestions from me. I have only dealt with less complicated documents.

Posted by Ben Nadel on Jul 10, 2008 at 2:59 PM

@Bret

What version of Excel is your template saved as? Try to keep it Excel 97-2005 version, since we've found that is the most agreeable to be used with POI.

Posted by Alan Johnson on Jul 10, 2008 at 3:02 PM

Alan - After reading one of your earlier posts I saved the file as Excel 97 - 2003 & 5.0/95 Workbook. It doesn't appear to help my situation. Thanks though.

Posted by Bret on Jul 10, 2008 at 3:54 PM

If you have images in the excel doc, you might want to start by stripping those out first. Macros are usually okay to have in the file. Images (since CF7 and above) seem to cause issues.

Posted by Alan Johnson on Jul 10, 2008 at 3:59 PM

I'm using CF8 dev edition and when I use the example code I get an error saying the variable objcell is undefined. It definitely is defined. I think it has something to do with the javacast(). I'm not sure. Can anybody help? pls. thx.

Posted by Ana on Jul 28, 2008 at 10:23 PM

Great info Ben. But I got a problem. In your sample you build a data array of data for your input. I need to use a db query. I'm not quite sure how to do that but I found some other code to create an array from my query. But I'm getting an error: Variable OBJCELL is undefined.

This is my code:
<cfquery dbtype="query" name="makexcel">
Select site_id,request_type,part_number,part_desc,detail_number,receipt_date,Ship_date,ship_via,waybill from getpr
</cfquery>


<cfloop query="makexcel">
<cfset arrData[CurrentRow][1]=site_id>
<cfset arrData[CurrentRow][2]=request_type>
<cfset arrData[CurrentRow][3]=part_number>
<cfset arrData[CurrentRow][4]=part_desc>
<cfset arrData[CurrentRow][5]=detail_number>
<cfset arrData[CurrentRow][6]=receipt_date>
<cfset arrData[CurrentRow][7]=ship_date>
<cfset arrData[CurrentRow][8]=ship_via>
<cfset arrData[CurrentRow][9]=waybill>
</cfloop>

The rest of the code is from your example. Any ideal what I'm doing wrong? Thanks, Jim

Posted by Jim Papaleo on Jul 31, 2008 at 9:55 AM

@Jim,

You might find it easier to use the POI Tags that I have added to the latest versions of this project:

Populating An Existing, Formatted Excel Document Using ColdFusion And POI : Kinky Solutions : A Student's Perspective by Ben Nadel