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

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:

<!--- Create an instance of the POIUtility.cfc. --->

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

<!--- Get a short hand to the current sheet. --->

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

<!--- Loop over the data query. --->

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

<!--- Create an instance of the POIUtility.cfc. --->

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

<!--- Loop over the data query. --->

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

<!--- Create an instance of the POIUtility.cfc. --->

<cfset objPOI = CreateObject(

"component",

"POIUtility"

).Init()

/>

<!--- Simulate a query object. --->

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

<!--- Set the query into the sheet. --->

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

<!--- Set the sheet name. --->

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

Reader Comments

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.

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

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.

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.

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.

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?

First, Thanks for a great site. This is always my goto blog for CF answers.

I am having some trouble implementing the POIUtility into my application. I have dozens of excel reports that I have been building as spreadsheetml and I am trying to consolidate that process into a single custom tag.

All of the examples that i can find in the sourcecode and blog create the query in the code. I need to be able to pass a query/struct/array similar to the one in the example above to the POI utility. I started customizing the code, and then decided that I am rebuilding something that has probably already been built by better minds than mine. Can you (or anyone) point me to an example that passes in an array similar to the one above.

Thanks, this is exactly what I was looking for. However I can't get it to read Excel 2007 files. It works perfectly with Excel 2003 (or a 2007 file saved in Excel 97-2003 format). For a 2007 file, it throws the error Felix notes above:

Object Instantiation Exception. An exception occurred when instantiating a java object. The cause of this exception was that: .

The error occurred in E:\Inetpub\wwwroot\POIUtility.cfc: line 875Called from E:\Inetpub\wwwroot\index.cfm: line 75Called from E:\Inetpub\wwwroot\POIUtility.cfc: line 875Called from E:\Inetpub\wwwroot\index.cfm: line 75

Hi Ben - I appear to be having a similar problem (I presume), but it's falling at the next line.

I'm getting:

Object Instantiation Exception.An exception occurred when instantiating a java object. The cause of this exception was that: .

The error occurred in /home/website/cliff/imp/poi/POIUtility.cfc: line 883Called from /home/website/cliff/imp/index.cfm: line 49Called from /home/website/cliff/imp/poi/POIUtility.cfc: line 883Called from /home/website/cliff/imp/index.cfm: line 49

As the subject file is generated remotely, I cannot verify it's original Excel version, but have tried opening and re-saving in Excel '03 format. I also tried stripping away most of the tabs, leaving only one - it contains quite a lot of data.

Wouldn't you know it! As soon as I post that, I notice that the sheet has an autofilter - I removed that and it worked fine. I then tried again with the full worksheet, with all it's tabs and data and after removing the autofilter from each tab, it was read in with no problems - the resulting dump is a thing of beauty! (That's a sentence you don't get to use too often! B->)

Now I just need to find a way to macro the removal of the worksheet protection and of the autofilters, then my user can download the file, run a (hopefully) single macro and upload and I can let CF take care of everything else.

Using the Java layer you can read in and edit existing Excel files. With my POI custom tags, you can specify rows/columns to update; but, there is a lot of things I need to change to perhaps make that easier.

hmm, it definitely shows how to read an excel file with multiple sheets in it, but i couldnt reveal how to write multiple sheets to one excel spreadsheet. am i overlooking it? should it use the WriteExcelSheet() function?

Thanks for the reply Cliff, I already did that... it did help in removing the extra spaces. But what I am getting is a document which is not in proper format.... is there any email id of ur's I could send the document to...Thanks,Purnima

I have a quick question related to POI utility. When we use poiutility, is it possible to read the query results directly and display it to user using cfconent? I do not want to to write to excel and then read it.

Question on its use: I'm adding a feature to a data export, a pointer to a file on a server. I'd like to make this a proper hyperlink, but I'm unsure how to do this.

I tried dumping a proper link in, i.e. "&lt;a href='pathtofile'&gt;link text&lt;/a&gt;" but the link syntax shows up in the resulting spreadsheet as is, and doesn't display the text as a hyperlink as desired.

Well meh. Page wouldn't allow me to post the link syntax properly, figured the lt/gt codes would do the trick, but no. So please don't assume my Excel spreadsheet has the lt/gt codes and that's why it doesn't work :)

Hi Ben, Thanks for your library.I have a question,How can I use your stuff for creating excel.I already have code which converted my queries to HTML and then with the <cfdocument format="pdf"> it saved report in particular directory, but for some reports I need to sore second file as excel.Here what I'm saving in pdf:

thanks for the great utility, been using it a while to read data from excel files. So far I've been using xls files but I would also like to use xlsx. So far had no success with it. I have the most recent POI (3.8) and additional components but I am getting an error: "org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)"Just wanted to check in with you on possible solution before I dig into POI itself.Thanks

In case you're interested in expanding the functionality, I added the following lines of code to a local copy of POIUtility.cfc so it would be able to open password-protected XLS files. Here's the code (in context) and an explanation of what else needs to happen for it to work.

I am the co-founder and lead engineer at InVision App, Inc — the world's leading prototyping,
collaboration & workflow platform. I also rock out in JavaScript and ColdFusion 24x7 and I dream about
promise resolving asynchronously.