POIUtility.cfc Examples For Reading And Writing Excel Files In ColdFusion : Kinky Solutions : A Student's Perspective by Ben Nadel

POIUtility.cfc Examples For Reading And Writing Excel Files In ColdFusion

Posted July 26, 2007 at 9:27 AM

Some people have been asking me to actually write some documentation and examples for my POIUtility.cfc ColdFusion component (documentation? What's documentation???). Anyway, this morning, I have added a new build to my POI Utility project. The build does not actually contain any new functionality in the POIUtility.cfc, but it does come bundled with some demos of how the reading and writing takes place. I have also put these examples below.

The following Read actions (full workbook and single sheet reads) are done on the following Excel sheet:

Reading All Sheets Of A Given Workbook

The POIUtility.cfc has a ReadExcel() function. This can read both an entire workbook as well as a single sheet. There is also a ReadExcelSheet() function, but PLEASE, do NOT use this unless you know more about POI. If you want to read a single sheet, just use ReadExcel() and pass in a zero-based sheet index (SheetIndex param). Here is the multi-sheet read example:

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

<cfset objPOI = CreateObject(
"component",
"POIUtility"
).Init()
/>
<!---
Read in the Exercises excel sheet. This has Push, Pull,
and Leg exercises split up on to three different sheets.
By default, the POI Utilty will read in all three sheets
from the workbook. Since our excel sheet has a header
row, we want to strip it out of our returned queries.
--->
<cfset arrSheets = objPOI.ReadExcel(
FilePath = ExpandPath( "./exercises.xls" ),
HasHeaderRow = true
) />
<!---
The ReadExcel() has returned an array of sheet object.
Let's loop over sheets and output the data. NOTE: This
could be also done to insert into a DATABASE!
--->
<cfloop
index="intSheet"
from="1"
to="#ArrayLen( arrSheets )#"
step="1">
<cfset objSheet = arrSheets[ intSheet ] />
<!---
Output the name of the sheet. This is taken from
the Tabs at the bottom of the workbook.
--->
<h3>
#objSheet.Name#
</h3>
<!---
Output the data from the Excel sheet in a table.
We know the structrure of the Excel, so we can
use the auto-named columns. Also, since we flagged
the workbook as using column headers, the first
row of the excel was stripped out and put into an
array of column names.
--->
<table border="1">
<tr>
<td>
#objSheet.ColumnNames[ 1 ]#
</td>
<td>
#objSheet.ColumnNames[ 2 ]#
</td>
<td>
#objSheet.ColumnNames[ 3 ]#
</td>
</tr>
<cfloop query="objSheet.Query">
<!---
It is possible that the query read in read in
blank rows of data. For our scenario, we know
that we HAVE to have an exercise name.
Therefore, if there is no exercise name returned
(in Column1), then this row is not valid - skip
over it.
--->
<cfif Len( objSheet.Query.column1 )>
<tr>
<td>
#objSheet.Query.column1#
</td>
<td>
#objSheet.Query.column2#
</td>
<td>
#objSheet.Query.column3#
</td>
</tr>
</cfif>
</cfloop>
</table>
</cfloop>

This will output the following screen data:

And, just for your reference, here is what at ReadExcel() function returns - an array of Sheet objects:

Notice that the last sheet has a bunch of empty cells. This is because there is no logic in the POIUtility.cfc to disregard empty rows; you have to do that in your business / output logic - who am I to decide what is valid data. Eventually, I will probably put in some sort of a flag that will exclude empty rows.

Reading A Single Sheet Of A Given Workbook

To read a single sheet out of a workbook, use the ReadExcel() function, like above, but this time, pass in the SheetIndex argument. For some reason, I decided to make this index zero-based (starting at zero) rather than one-based like the rest of ColdFusion. I'll probably change that at some point to be more ColdFusion-compatible.

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

<cfset objPOI = CreateObject(
"component",
"POIUtility"
).Init()
/>
<!---
Read in the Exercises excel sheet. This has Push,
Pull, and Leg exercises split up on to three different
sheets. Instead of reading in all the sheets, we are
going to read in just the Push sheet (currently the
sheet indexes are zero-based). And, since our excel
sheet has a header row, we want to strip it out of our
returned queries.
--->
<cfset objSheet = objPOI.ReadExcel(
FilePath = ExpandPath( "./exercises.xls" ),
HasHeaderRow = true,
SheetIndex = 0
) />
<!---
Since we provided a sheet index, the ReadExcel() has
returned a single Sheet object. Let's loop over the
single sheet and output the data. NOTE: This could be
also done to insert into a DATABASE!
--->
<!---
Output the name of the sheet. This is taken from
the Tabs at the bottom of the workbook.
--->
<h3>
#objSheet.Name#
</h3>
<!---
Output the data from the Excel sheet in a table.
We know the structrure of the Excel, so we can
use the auto-named columns. Also, since we flagged
the workbook as using column headers, the first
row of the excel was stripped out and put into an
array of column names.
--->
<table border="1">
<tr>
<td>
#objSheet.ColumnNames[ 1 ]#
</td>
<td>
#objSheet.ColumnNames[ 2 ]#
</td>
<td>
#objSheet.ColumnNames[ 3 ]#
</td>
</tr>
<cfloop query="objSheet.Query">
<!---
It is possible that the query read in read in
blank rows of data. For our scenario, we know
that we HAVE to have an exercise name.
Therefore, if there is no exercise name returned
(in Column1), then this row is not valid - skip
over it.
--->
<cfif Len( objSheet.Query.column1 )>
<tr>
<td>
#objSheet.Query.column1#
</td>
<td>
#objSheet.Query.column2#
</td>
<td>
#objSheet.Query.column3#
</td>
</tr>
</cfif>
</cfloop>
</table>

Write A Sheet To A Given Workbook

When writing a sheet to a given workbook, you are creating a new file. Right now, the WriteExcel() function does not have the ability to work with an existing workbook, unless you use the WriteExcelSheet(), but again, I wouldn't do this until you have a better understanding of the POI library. When writing a sheet, you can pass in a single Sheet object or an array of Sheet objects. If you pass in an array, you will simply create a workbook with more than one sheet. You can get a new sheet object (which is really just a struct), call the GetNewSheetStruct() function.

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

<cfset objPOI = CreateObject(
"component",
"POIUtility"
).Init()
/>
<cf_makequery name="qGirl">
name|hair|best_feature
Julie|Blonde|Forearms
Lydia|Brunette|Eyes
Cynthia|Blonde|Eyes
</cf_makequery>
<!---
Create a sheet object for this query. This will
return a structure with the following keys:
- Query
- ColumnList
- ColumnNames
- SheetName
--->
<cfset objSheet = objPOI.GetNewSheetStruct() />
<cfset objSheet.Query = qGirl />
<!---
Define the order of the columns (and which
columns to include).
--->
<cfset objSheet.ColumnList = "name,hair,best_feature" />
<!---
We want to include a header Row in our outputted excel
workbook. Therefore, provide header values in the SAME
order as the column list above.
--->
<cfset objSheet.ColumnNames = "Name,Hair,Beast Feature" />
<cfset objSheet.SheetName = "Girls" />
<!---
Now, let's write the sheet to a workbook on the file
sysetm (this will create a NEW file). When doing so, we
have the option to pass either a single sheet object (as
we are donig in this example, or an array of sheet
objects). We can also define header and row CSS.
--->
<cfset objPOI.WriteExcel(
FilePath = ExpandPath( "./girls.xls" ),
Sheets = objSheet,
HeaderCSS = "border-bottom: 2px solid dark_green ;",
RowCSS = "border-bottom: 1px dotted gray ;"
) />

When writing, you have the ability to define CSS for the header row, every row, and the alternating row. The CSS capabilities are limited, but they tend to do what I need to do. When writing cell data, the POIUtility.cfc is looking at the data type of the query column. Therefore, it is important (or rather, essential) to have a query with explicitly typed columns.

Hope that helps a little bit.

Download Code Snippet ZIP File

Related Blog Entries

FLV 404 Error On Windows 2003 Server

Learning ColdFusion 8: CFZip Part V - Deleting Zip File Archives

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

Reader Comments

Hi Ben -- IF I haven't told you in a while, you Rock.

One wish list item here that I think could really add tremendous value to your tool would be to not only tell which worksheet to read/write to, but what cell range to read from. (ie. Read cells B15:E25)... Likewise with the write, to set a location. -- Then you could totally strip data out of an excel spreadsheet. regardless of where it is.

Posted by Alan Johnson on Jul 26, 2007 at 9:42 AM

Very cool.

As you know I built something similar using the JExcelApi. Only the reading Excel part that is. Pretty soon I encountered problems like, a header being present allright, but not on the first row. And columns that had data, but no header while the other columns had, or double headers (same name used twice for a column).

It turns out to become quite a headache when you try to deal with all these problems. :-)

Posted by jax on Jul 26, 2007 at 10:44 AM

@Alan,

Thanks. I am hoping to beef it up a bit more soon. I just haven't had the time, plus the fact that it works fairly well for my needs right now... you know what I mean :)

@Jax,

Exactly. The balance is how to make the interface for the object powerful without just duplicating the actual POI API (otherwise, whey even bother creating a wrapper). I have some ideas. Once, I formulate them a bit more, I will pass them along as they might be relevant to your jExcel stuff.

Posted by Ben Nadel on Jul 26, 2007 at 11:10 AM

Great set of functions. Add this to your wish list -- a function to just return the sheet names. If we have a big XLS with several sheets, and all I want to do is display the sheet names, I have to load in all the data and ignore it. We ended up having to write one to extend your CFC a bit.

Thanks for all the improvements!
Tim

Posted by Tim on Jul 27, 2007 at 3:31 PM

@Tim,

Good one :) I will add that to the next release.

Posted by Ben Nadel on Jul 28, 2007 at 5:26 PM

Hi Ben,

great functions! I'm playing around with the function ReadExcel().
It works very good with my own Excel files. But if i read a Excel file which i get from my customer every day by email i get an error.
Error:
Object Instantiation Exception.
An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

If i open the file and save it under the same name with no changes, your function can read it.

Any idea what i can do?
Thanks for help!

Felix

Posted by Felix on Sep 26, 2007 at 8:12 PM

@Felix,

Since opening and saving it works, then my guess is there is something funny about the original file. Either:

1. The file is not a true XLS file; maybe it is a CSV file or an HTM file that someone sends with an XLS file extension.

2. The file IS an XLS file, but perhaps too new a version to be read by POI. Then, you opening and saving it converts to a more compatible type.

What version of Excel are you using to open and close the file?

Posted by Ben Nadel on Sep 27, 2007 at 7:14 AM

Awesome, powerful utility. I seem to have an issue when i get around the 4-5 sheet area with lots of data. I get an error message from Excel stating that no new fonts may be applied. Depending on how much data is contained, i lose font styles toward the later sheets, often mid-sheet. What could I be doing wrong?

Posted by Chris Dunbar on Apr 9, 2008 at 4:53 PM

@Chris,

Someone else was getting that issue as well. I have not figured out why that happens just yet. Are you in Excel 97?

Posted by Ben Nadel on Apr 10, 2008 at 7:43 AM

Ben,
I'm using CF8 and opening in Excel 2003.
Chris

Posted by Chris Dunbar on Apr 10, 2008 at 9:13 AM

@Chris,

Hmmm. Not sure. If I find anything, I will post it here.

Posted by Ben Nadel on Apr 10, 2008 at 9:20 AM

Post Comment | Ask Ben

POIUtility.cfc Examples For Reading And Writing Excel Files In ColdFusion : Kinky Solutions : A Student's Perspective by Ben Nadel