Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2011 (Minneapolis, MN) with:

ColdFusion POIUtility.cfc Updates And Bug Fixes

Posted by Ben Nadel
Tags: ColdFusion

I have finally gotten around to fixing the known bugs in my original ColdFusion POIUtility.cfc (small ColdFusion wrapper for the POI library). For those of you who don't know what POI is, it is a Java library for reading and writing Excel files. This library ships with ColdFusion but is HUGE and complicated. My POIUtility.cfc is a ColdFusion wrapper that creates a very simple interface to the POI library that is much easier to deal with (but less powerful). It allows you to read an Excel file into a set of queries or to write a set of queries to an Excel file.

I wanted to give some special thanks to the all the people who helped me debug this, but especially to Jeremy Knue who helped me figure out how to stop the file system from locking the Excel files (close File Input stream), to Richard J Julia, Charles Lewis, and John Morgan who pointed out that NULL cell / row values bombed out, and to Sophek Tounn who pointed out that empty sheets were returning non-query objects. These have all been fixed.

Here is the updated POIUtility.cfc (POIUtility.cfc.2007.04.04.txt).

I am going to be continuing to work on this and hopefully improve it. I have already created a alternate version that can deal with some more CSS, but that is totally not polished.

Thanks to everyone (mentioned or otherwise) who helped me improve this.




Reader Comments

Yeah, I hear you... I tend to just convert dates to strings for these reports:

SELECT
CONVERT( Char( 10 ), date_created, 101 ) AS date_created
FROM []

This will convert the date_created date/time stamp to a formatted string which will format as Text in the Excel. Not the best solution, but the best one I have at this time.

Reply to this Comment

The only problem I have with this awesome utility is, I have a column I'm reading that contains leading 0's. The utility is removing those leading 0's but I don't need it to do that.

Any suggestions?

Thanks much,
Will

Reply to this Comment

@Will,

Yeah, from what I can remember, it reads all fields in a strings, so nothing should be stripped (I think).

Reply to this Comment

I made a couple tweaks so that we could use this with 6.1:

ReadExcelSheet() -- removed the datatype argument from QueryAddColumn().

WriteExcelSheet() -- replaced GetMetaData() with code that force-feeds case insensitivity and varchar type to all columns:

--------
// Get the meta data of the query to help us create the data mappings.
// LOCAL.MetaData = GetMetaData( ARGUMENTS.Query );

LOCAL.arrMetaData = ArrayNew(1);
LOCAL.qryColumnList = ARGUMENTS.Query.columnList;

for(i=1; i lte listLen(LOCAL.qryColumnList); i=i+1){
LOCAL.arrMetaData[i] = StructNew();
LOCAL.arrMetaData[i].IsCaseSensitive = "NO";
LOCAL.arrMetaData[i].Name = lCase(listGetAt(LOCAL.qryColumnList,i));
LOCAL.arrMetaData[i].TypeName = "VARCHAR";
}

LOCAL.MetaData = LOCAL.arrMetaData;
--------

Reply to this Comment

Awesome! Is there a simple way to zip the xls file(s) up so I can mail the bad babies to a client. This will enable me to automate a monthly set of reports I've been doing in DTS. DTS isn't so bad, but I have to modify my query every time to account for the monthly date range. In CF, I can set up the date range dynamically.

Reply to this Comment

@macbuoy,

While I don't have any Zip code on hand, I know there is a ton of ColdFusion zip data out there including custom tags and UDFs. Just give a quick search on Google.

Glad you like the POI stuff :)

Reply to this Comment

I've got this working on my Dev machine. I found one problem and I'm not sure if I'm missing something or if my workaround is the only way around:

I just run a query and call my poiUtility object's WriteSingleExcel() method.

My Excel sheet kept showing the wrong data in the wrong columns.

I was sending the columnNames parameter as a list of the fields in the same order that I called them in my query.

What I found was that CF is reordering my column order alphabetically in my query object.

So, my workaround is to create a local variable for the column list, do a listSort(columnList, "textNoCase"), pass the local variable to WriteSingleExcel() for the columnList param and get the spreadsheet in an order that is undesirable but properly aligned.

Have I missed some other, built-in way to solve this?

Reply to this Comment

. . .correction. In my setup, I'm using the columnNames parameter NOT the columnList parameter in WriteSingleExcel().

This is why I think I'm missing something built-in. The ColumnList param has a weird comment:

<blockquote>"This is list of columns provided in custom-order."</blockquote>

hmmmm. MAYBE I should experiment with the parameter instead of bugging everyone with my rant. . .

Will report.

Reply to this Comment

. . .OK. So, I figured out how to properly use the columnList and columnNames parameters in the writeSingleExcel() method.

Maybe Ben can elaborate, but I basically passed the same list in the same order to each of these parameters and got both my data AND the header row in the order I wanted.

I'm assuming that one refers, simply, to the header row data and order and the other instructs the method how to order the columns in the passed-in query.

Sorry if that was painfully obvious to others. ;-P

Reply to this Comment

As one of the method arguments, you should be able to pass in the column list - this will be the delimited list of columns in a given order (the order that gets output to the file).

Reply to this Comment

getting error on write...
Element TYPENAME is undefined in a CFML structure referenced as part of an expression.

simple test code.
<!--- fake data --->
<cfscript>
MyQuery = querynew('name,address,phone');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','james blanard');
querysetcell(MyQuery,'address','1223 N. Foster Ave');
querysetcell(MyQuery,'phone','517-484-4444');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','alan arkin');
querysetcell(MyQuery,'address','24 Maple Dr. Apt 4');
querysetcell(MyQuery,'phone','414-484-4444');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','Zoe Zimbabewha');
querysetcell(MyQuery,'address','1000 one thousand way');
querysetcell(MyQuery,'phone','555-484-4444');
</cfscript>

<!---
write them to a new Excel file.
--->
<cfset objPOIUtility.WriteSingleExcel(
FilePath = ExpandPath( "query2excel.xls" ),
Query = MyQuery,
ColumnList = "column1,column2,column3",
ColumnNames = "name,address,phone",
SheetName = "my contacts"
) />

Full Error Info:

Element TYPENAME is undefined in a CFML structure referenced as part of an expression.

The error occurred in C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc: line 1540
Called from C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc: line 1405
Called from C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc: line 1894
Called from C:\CFusionMX7\wwwroot\poiutility\writeexcel2.cfm: line 32

1538 :
1539 : // Map the column name to the data type.
1540 : LOCAL.DataMap[ LOCAL.MetaData[ LOCAL.MetaIndex ].Name ] = LOCAL.MetaData[ LOCAL.MetaIndex ].TypeName;
1541 : }
1542 :

Reply to this Comment

@James,

You have to provide the query with data type in your QueryNew() method:

querynew(
'name,address,phone',
'CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR'
);

Notice the second argument. Without this, I guess ColdFusion doesn't make that available (even though it has the ability to guess data types).

Reply to this Comment

Thx ben - and wow speedy reply....

hate to be pain, but now im getting index issues....you're sample
arry/query is pretty straight ahead...what am i missing

new error.

[Table (rows 3 columns name, address, phone): [name:
coldfusion.sql.QueryColumn@126b669] [address:
coldfusion.sql.QueryColumn@91bea7] [phone:
coldfusion.sql.QueryColumn@7beb29] ] is not indexable by column1

The error occurred in C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc:
line 1714
Called from C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc: line 1405
Called from C:\CFusionMX7\wwwroot\poiutility\POIUtility.cfc: line 1894
Called from C:\CFusionMX7\wwwroot\poiutility\writeexcel2.cfm: line 32

1712 : LOCAL.CellValue = ARGUMENTS.Query[
1713 : LOCAL.Columns[ LOCAL.ColumnIndex ]
1714 : ][ LOCAL.RowIndex ];
1715 :

Reply to this Comment

Are you calling the WriteExcelSheet() directly? Or are you letting it get called by the WriteExcel() method? WriteExcelSheet() can work if called directly, but it was designed as a helper method?

Reply to this Comment

calling it directly - an instantiation of the object on the page is the only thing left off that first example....

Reply to this Comment

You might want to try calling WriteExcel() instead. The arguments are very similar. The main difference (for a single sheet Excel file) is that WriteExcel() generated the Workbook for you and passes it to the WriteExcelSheet() method. This shouldn't make a difference, but maybe we just aren't catching the real issue.

Reply to this Comment

same error:
calling thru writeexcel

<cfset objPOIUtility = CreateObject("component", "POIUtility").Init() />

<!--- Create mock query to dump to SS --->
<cfscript>
MyQuery = querynew('name,address,phone','CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','james blanard');
querysetcell(MyQuery,'address','1223 N. Foster Ave');
querysetcell(MyQuery,'phone','517-484-4444');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','alan arkin');
querysetcell(MyQuery,'address','24 Maple Dr. Apt 4');
querysetcell(MyQuery,'phone','414-484-4444');
queryaddrow(MyQuery,1);
querysetcell(MyQuery,'name','Zoe Zimbabewha');
querysetcell(MyQuery,'address','1000 one thousand way');
querysetcell(MyQuery,'phone','555-484-4444');

arrSheets[ 1 ] = objPOIUtility.GetNewSheetStruct();
arrSheets[ 1 ].Query = MyQuery ;
arrSheets[ 1 ].SheetName = "My Contacts";
arrSheets[ 1 ].ColumnList = "column1,column2,column3";
arrSheets[ 1 ].ColumnNames = "name,address,phone";
</cfscript>

<cfset objPOIUtility.WriteExcel(
FilePath = ExpandPath( "query2excel.xls" ),
Sheets = arrSheets
) />

Reply to this Comment

Ahhh, i see :)

This is the problem line:

arrSheets[ 1 ].ColumnList = "column1,column2,column3";

You are treating the columns just like the column from the ReadExcel() method. What you need to do is actually send through the columns as they exist in the passed in query:

arrSheets[ 1 ].ColumnList = "name,address,phone";

If you look at the CFArgument tag for the ColumnList it looks like this:

<cfargument
name="ColumnList"
type="string"
required="false"
default="#ARGUMENTS.Query.ColumnList#"
/>

It actually defaults to the column list of the passed query. So why pass in the column list yourself? To dictate the order of the column output.

Hope that helps a bit.

Reply to this Comment

I just tested your POIUtility.cfc the very first time. My DB is Oracle 9 and I do have a query resultset where some columns are of type DATE and for some rows and this specific column the value is NULL.

The function WRITEEXCELSHEET throws an error in line 1736: The value "" cannot be converted to a number.

Two kinds of workaround may solve this:
a) SQL conversion like TO_CHAR(my_date_column)
b) alter the cfc in line 1736 in a way like else if REFindNoCase( "date|time", LOCAL.DataMapValue )){ LOCAL.DataMapCast = "string"; }

But the Excel cell format is TEXT then and it is hard to switch it to DATE ...

Do you have any idea how to pass values of type DATE to an Excel sheet?

Michael

Reply to this Comment

@Michael,

Unfortunately, I do something like your first suggestion. I pass in my dates as strings:

CONVERT(
CHAR( 10 ),
YOUR_DATE,
101
)

... or something like that. That way, by the time it gets to POI, it is a string, not an actual date object. I never got around to figuring out how to mess with dates well.

Reply to this Comment

Dear Ben,

Thanks for your great works.

Just want to share my case, i need output chinese characters. So, i added some line to your POIUtility for suitable for work.

...
// Create a cell for this query cell.
LOCAL.Cell = LOCAL.Row.CreateCell(
JavaCast( "int", (LOCAL.ColumnIndex - 1) )
);

LOCAL.Cell.setEncoding(ARGUMENTS.WorkBook.ENCODING_UTF_16);
...

Reply to this Comment

@Gordon,

Thanks - that is good stuff to know. We have to deal with a lot of foreign characters here at work with international law firms. This hasn't come up yet, but I am sure it will one day :)

Reply to this Comment

This is a great start for what I am looking for. very impressive. one of the columns I am retrieving from the excel file is an ID that matches what I had in a database. Based on this ID, I want to query the database to update one of the fields with new values from the database. How can I modify your code to query my database based on that first column that has the unique ID?

Thank you
Feras

Reply to this Comment

Starting to use POIUtility. Lovely. Got a password protected workbook. I'm not going to be able to get into it with this, right? Currently, I get a Object Instantiation Exception on the attempt to create an org.apache.poi.hssf.usermodel.HSSFWorkbook object.

Reply to this Comment

@Christopher,

I have not done much work with password protected work books. Me and another guy were trying to get password protecting on individual sheets, but I don't think we ever figured that out.

Reply to this Comment

Hello,

This utility look awesome, I just stumbled over it and wanted to check it out. I created a test page for reading *.xls files but get this error and wondered if anyone could tell me what I'm doing wrong? Thanks!

Paul

=============
<!--- Create a new instance of the POI utility. --->
<cfset objPOIUtility = CreateObject(
"component",
"POIUtility"
).Init()
/>

<!--- Get the path to our Excel document. --->
<cfset strFilePath = ExpandPath( "./testList.xls" ) />

<cfset arrExcel = objPOIUtility.ReadExcel(
FilePath = strFilePath,
HasHeaderRow = true
) />
============================

The error occurred in C:\ColdFusion8\wwwroot\CFIDE\test\POIUtility.cfc: line 871
Called from C:\ColdFusion8\wwwroot\CFIDE\test\upload_action.cfm: line 52
Called from C:\ColdFusion8\wwwroot\CFIDE\test\POIUtility.cfc: line 871
Called from C:\ColdFusion8\wwwroot\CFIDE\test\upload_action.cfm: line 52

869 :
870 : // Create a file input stream to the given Excel file.
871 : LOCAL.FileInputStream = CreateObject( "java", "java.io.FileInputStream" ).Init( ARGUMENTS.FilePath );
872 :
873 : // Create the Excel file system object. This object is responsible

Reply to this Comment

Oops, forgot the error message :P

An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

Reply to this Comment

Almost embarrassed to say... :)

The problem was nothing to do with the utility, I just had the path to my test.xls file pointed one directory higher then it should have been... whoops. Too much Redbull and not enough sleep :P

Great utility, thanks for making it available.

Paul

Reply to this Comment

I really need (re: desperate!) to be able to create a new sheet in an existing workbook, enter data in that sheet, and then save the workbook under a different name. Is that possible with your utility?

Reply to this Comment

Has anyone tested this with CF8? I upgraded our server to CF8 last week and now it appears to be broken. Here's the error I'm receiving now.

Object Instantiation Exception.
An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

The error occurred in C:\TricoHome\CRDMS\POIUtility.cfc: line 883
Called from C:\TricoHome\CRDMS\ReadCSMData.cfm: line 31
Called from C:\TricoHome\CRDMS\POIUtility.cfc: line 883
Called from C:\TricoHome\CRDMS\ReadCSMData.cfm: line 31

881 : "org.apache.poi.hssf.usermodel.HSSFWorkbook"
882 : ).Init(
883 : LOCAL.ExcelFileSystem
884 : );
885 :

Reply to this Comment

Whoops. It was just a coincidence that we got the error after upgrading to CF8 last week. Turns out it was a problem with the Excel file being read in. The business user had left some blank lines and then a row with this (Copyright © 2007 CSM Worldwide, Inc.) in it at the bottom. Removing those rows fixed our issue.

Reply to this Comment

Great utility. Still had problems with it throwing an exception with null values in number-type columns. I added a try at line 1760'ish to catch those and default the Local.CellValue to 0 in the JavaCast function- kind of a big assumption, I know- but it works for my needs for now. Any additional thoughts? Thanks for the excellent code!

Reply to this Comment

Is there any way to get dates to work when I am reading in from an xls file. All I get is dates in decimals now. I did some research and looks like xls stores dates as dates since the start of the century.. Is there a function or something I can run that will get me a nicely formatted date?

Reply to this Comment

@Greg,

In ColdFusion, you should be able to use DateFormat() to format the decimal once you import it:

DateFormat( excel_date_value )

I am working on improving the import process.

Reply to this Comment

Ya know... this thing works great... except, two things. It throws errors about ColumnList when you call WriteExcel() which I can fix/workaround... and the formatting is HEINOUS. So far I've brought it down from 1910 lines to 1081 lines and I'm still formatting this so it's in a more manageable form. Where the ---- did you learn to format code?! I mean, thanks... but, ... sigh.

Reply to this Comment

@Josh,

The number of lines means nothing to me. That is how I learned to format code after dealing with people's no-white-space-outlook on life. I find a certain amount of white space makes the code much more readable and allows me to concentrate on one small chunk of code at a time (separation of concerns). Redoing it, I am going to remove the CFScript tags that I have as I feel that mixing CF tags and script hurt readability and debugging.

Can I ask why you are even taking time to reformat it? As CF is compiled, you are not going to gain any performance by reformatting?

Reply to this Comment

@Ben's response,

I'm aware it becomes compiled and all formatting essentially becomes moot to speed/efficiency after the first access. That is not the reason for formatting, however.

As mentioned, I want it in a more manageable form. My reason for caring what the source looks like is this: I plan to add functionality that's specific to me and my employer's needs and would like it in a form that's much more standard. Also, as this is obviously an incomplete work, I plan to add some of the missing functionality -- such as importing everything appropriately. I haven't gotten a chance to look at all the inner workings yet, but I plan to optimize it "as much as possible" because of the huge datasets I deal with.

My biggest gripes are that single function calls take up to 5 lines. cfarguments take up to 7 lines. You have trailing and opening parentheses/braces ALL over the place. These things, in my opinion, extremely hurt readability. This doesn't affect readability -- but, you have comments that state the obvious:

// Set alternate row style.
local.Cell.SetCellStyle(local.AltRowStyle);

Except, I think you had it formatted like so:

// Set alternate row style.
local.Cell.SetCellStyle(
local.AltRowStyle
);

That's pretty much the equivalent of:
<!--- Set a variable labeled x to the numeric value 3 --->
<cfset var x = 3/>

As far as removing cfscript tags goes -- I wouldn't. It doesn't hurt readability. It enhances it, in my opinion. The only tags you use are cfcomponent, cffunction, cfargument, and cfscript... I wouldn't convert the entire thing to cfscript... nor would I convert the entire thing to tags. Converting it to tags would probably double the file size again... don't know about you, but I hate digging through code to find what I'm looking for.

Reply to this Comment

@Josh,

Obviously, if you are going to be updating the code, then by all means, make it readable for yourself :)

Reply to this Comment

Ok so I am pretty new to CF and am looking some basics of how to get this cfc up and running. I have downloaded the code I need for reading a single spreadsheet and put it into my .CFM file. I have downloaded the POIutility.cfc also but am getting the following error when I run.

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

The error occurred in D:\tobermore_SC_1207\www_root\POIUtility.cfc: line 875
Called from D:\tobermore_SC_1207\www_root\uploadArchitectsPOI.cfm: line 50
Called from D:\tobermore_SC_1207\www_root\POIUtility.cfc: line 875
Called from D:\tobermore_SC_1207\www_root\uploadArchitectsPOI.cfm: line 50

873 : // Create the Excel file system object. This object is responsible
874 : // for reading in the given Excel file.
875 : LOCAL.ExcelFileSystem = CreateObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).Init( LOCAL.FileInputStream );
876 :
877 :

Any suggestion (or putting me straight!) for ease of implementation would be appreciated .

Cheers.

Reply to this Comment

@Neil,

This error usually happens when someone passes in a file path that is not valid. Are you passing in a fully expanded path name (starting with the drive letter)?

Reply to this Comment

I'm getting the same error as the others:

Object Instantiation Exception.
An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

The error occurred in E:\...\POIUtility.cfc: line 875
Called from E:\...\import.cfm: line 34
Called from E:\...\POIUtility.cfc: line 875
Called from E:\...\import.cfm: line 34

873 : // Create the Excel file system object. This object is responsible
874 : // for reading in the given Excel file.
875 : LOCAL.ExcelFileSystem = CreateObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).Init( LOCAL.FileInputStream );
876 :
877 :

I'm sure that the path is valid because it was just uploaded via a CFFile tag a few lines earlier. Any thoughts?

Reply to this Comment

Do you have any filtering or drop down menus inside of the Excel document? I have been told that this can cause instantiation errors as well.

Reply to this Comment

@Ben

I don't think I have either of those in the file. I don't know it 100%, but it seems very unlikely (the program that generated the file is pretty low-tech).

The plot thickens, though; if I open the file and immediately save it, I can open the file just fine. I'm beginning to wonder if some permissions are being screwed up on upload or something like that.

Reply to this Comment

@Chris,

Hmm, very strange. There must be some sort of functionality that is causing an issue. Sorry I am not more help.

Reply to this Comment

@Ben

It turns out it's all due to a ColdFusion bug. I used <cfhttp> to upload my Excel file, and if a file is the last parameter to <cfhttp> it gets messed up (bytes are added to the end of the file). The trick is to add an extra, unused parameter to the <cfhttp>. It's working beautifully now.

Thanks for your time, and thanks for POIUtility.

Reply to this Comment

Absolutely extraordinary interface. Thanks very much.

Not surprised, but a little disappointed to see it does not read/parse Excel 2007 files. This is probably a limitation of POI and not your utility.

Reply to this Comment

Ben,
I wanted to let you know that I am getting the same error as many above (the Object Instantiation Exception at line 875). I also have the "phenomena" of loading the file in excel and saving it and then have things work properly. I am using cffile to upload it.
To add some intrigue, I am fairly sure that the excel file is using an old version of Excel - when loading it into Excel 2003 SP3 the file gives errors (file error: data may be lost) - it does not gives errors loading into Excel 2003 SP2 - which I believe is a known bug in newer versions of Excel.

When your tool works, it works great - it makes alot of things possible. However, not being able to load these particular excel files is bit of a crimp in the style.

Reply to this Comment

Great stuff Ben -

Say i'm looking to integrate this into my methods where I'm already returning a query recordset from an excel import (since msoft does not support 64 bit odbc drivers we're looking to POI as an alternative). I notice the column names are 'colum1' 'column2' etc -

I'd prefer to use the first row as column name values for this in my application. I'm looking at modifying the POIUtility to allow this functionality.

Am I correct in saying that custom column naming from first row headers is NOT supported from the 'ReadExcel' method call?

I'd pass you the POIUtility back if I go this route of course, i'm planning on implementing it as a flag on method call of 'useColumnNamesFromHeader' boolean argument to ReadExcelSheet.

Let me know what you think.

Reply to this Comment

@Kevin,

Currently, there is no dynamic naming. You can modify it if you like. I have always been opposed to it because I feel it puts too much power to "do wrong" into the hands of the person providing the Excel sheet. Now, you are dependent on them to give you the right name. Sure, it might be their job, but I just don't trust them :)

Reply to this Comment

I hear ya Ben -

I have the file modified - I think it fits well into your arch - lmk if you want me to shoot it over to you.

Reply to this Comment

Hi Ben,

I was having problems with the builtin POI java jars throwing a #Value! error in the xls file when using the COUNTIF() excel function.

So, I used the JavaLoader to load the most recent POI jars then slightly amended your custom tags which fixed that problem.

Here is my blog entry:

http://murrayhopkins.wordpress.com/2008/12/17/value-error-when-using-builtin-poi-in-coldfusion-mx7/

Thanks for your great POI Utility!

Cheers,
Murray

Reply to this Comment

Hi Ben,

Thanks for this library... another great contribution to the community :) It allows much simpler handling of spreadsheet data, especially such boring issues as handling line feeds in CSVs, etc.

I am hoping to use it for my Mum's church website, so the Vicar can maintain content using Google Spreadsheets and Forms, and CF can download them to display on the site.

It works great on my dev machine, but my shared host seems to have most Java object creation disabled. I can create the POI objects, but the java.io.filesystem object shoots blanks... object [unknown type]

Do you think it would be possible to use CFFILE in place of the java.io stuff, and pass that to POI?

Thanks,
-Rob

Reply to this Comment

In case anyone has the same problem using the POI stuff, I was able to get around the original issue I had with line breaks in CSV files. A little Googling led me .... well ... right back here!

http://www.bennadel.com/blog/991-CSVToArray-ColdFusion-UDF-For-Parsing-CSV-Data-Files.htm

... is a great little library to take care of converting the CSV to an array. From there it's a little more wrangling and you have a query object. Thanks again Ben!

-Rob

Reply to this Comment

A client of mine is getting same error as some of the others:

Object Instantiation Exception.
An exception occurred when instantiating a Java object.

Now, here's a clue or two...

I'm dealing with email lists, so I'm looking at 4 columns sheets. What's being imported shouldn't be that complex.

From an admin page on my site, I can also EXPORT an Excel sheet from the database using the simplest method possible: <cfcontent type="application/msexcel">

My client can EXPORT a list, and then try to import it back in, and get the error. It's been driving me nutz because I get screamed at every time this happens to him.

Now I hate MS Office, and am a huge OO fan, so I don't use MS Excel. I can do the same export on my XP machine here of the same list, save it as an Excel 97/2000/XP .xls file from OO, and it will import back in *EVERY TIME* with no error.

The client can send me a copy of the Excel sheet that isn't working for him, and if I just open it in OO, and then simply save it again as an Excel 97/2000/XP .xls file, it works with no error.

So, what might be present in the Excel file that OO *isn't* adding when saving as Excel that might be causing the problem???

That help anybody? I'm a little afraid to play with the .jar files on the server, and hope there might be a less drastic solution.

Reply to this Comment

i want to create excel sheet with expend collapse option( group by option) using coldfusion.if any one have idea, plse help me

Reply to this Comment

I have used your POI Utility Great stuff.

I was trying to do something with dates. So I changed code of POIUtility a little.

if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_NUMERIC) {

// Get numeric cell data. This could be a standard number,
// could also be a date value. I am going to leave it up to
// the calling program to decide.
cellFormatStyle = LOCAL.Cell.getCellStyle();
formatType = cellFormatStyle.getDataFormat();
if (formatType == 14) {
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else if(formatType == 15){
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else if(formatType == 22){
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else if(formatType == 165){
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else if(formatType == 166){
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else if(formatType == 167){
LOCAL.CellValue = DateFormat(LOCAL.Cell.GetNumericCellValue(),'d-mmm-yy');
}else{
LOCAL.CellValue = LOCAL.Cell.GetNumericCellValue();
}
} else if (LOCAL.CellType EQ LOCAL.Cell.CELL_TYPE_STRING){

//rest code as it is..

Reply to this Comment

As always you are the man Ben. I was sitting here using your POI cfc to rip data from an excel for an important client meeting tomorrow and pulling my hair out.. I then found your update.. Legend! Greetings from Ireland - great work bud.

Reply to this Comment

thank you very very very much for developing this component!!! It works great!!!
You're my Developer of the month! ;-)

Reply to this Comment

Re: Element TYPENAME is undefined in a CFML structure referenced as part of an expression error.

I experienced this because I created the query from JSON data using an arrayOfStructuresToQuery function and of course, as a result, the output query (newQry) didnt have any datatypes.

However, I did a QoQ:
<CFQUERY name="local.rQry" dbtype="query">
SELECT newQry.* from newQry
</CFQUERY>

then passed local.rQry to the POIUtility. All was happy because the QoQ process added the missing datatypes.

Cheers,
Murray

Reply to this Comment

Thanks for the tools! In trying to use a template,
I get this error:
Object Instantiation Exception.
An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.

79 : "java",
80 : "java.io.File"
81 : ).Init(ExpandPath( "/MSR-RAD_CatalogTemplate_Master.xls" ))
82 : )
83 : ) />


You mentioned something above about dropdowns and other formatting that could cause the problem. Do you know what the are and if there are any work arounds.

Reply to this Comment

@Les Mizzell

Was the doc you were using a .xlsx perhaps? 3.5 POI utility NOW supports office 2007 document types - which I'm checking on now - doesn't seem to work out of the gate just yet as I'm getting a 'Object Instantiation Exception' when attempting to 'ReadExcel' on a 2k7 doc. More when I find out what's up.

Reply to this Comment

i do hav a big problem ! .. somebody help me ?

it goes like this .. i have read an excel file .. and my problem is on how to display the contents of it ? .. is there a such thing on displaying the contents ?

Reply to this Comment

Owww. I had some problems with different Excel files, and I could not understand that.

Object Instantiation Exception.
Line 871

But using another XML it runs wells!

Doo ya know somethin about?!

Reply to this Comment

@Roberto,

It might be that the Excel file you used was not compatible with the current POI package. Things like:

Excel 2007
Some kinds of formulas

... are not supported yet.

Reply to this Comment

Hi Ben, long time listener, first time caller, love your site.
I'm having a problem with the datatypes. I'm having the same problem as James, but these aren't handmade queries, they're coming from the db and the columns(and therefor the datatypes) depend on user input.
If I have a column with a number in it I get the
"...is undefined in a CFML structure referenced as part of an expression..." error. cfdumping the metadata shows the datatype, Murray's solution doesn't work for me. Am I supposed to re-add the metadata back to the query? If so, how?
thanx

Reply to this Comment

well figured out what was wrong. I used another bit of code from your site to rename some columns. I didn't know that a query has the column names in at least 2 places, so I renamed the columns, but not the metadata.
So in POIUtility.cfc around line 1718 or so it calls for a cell location of:
LOCAL.DataMapValue = LOCAL.DataMap[ LOCAL.Columns[ LOCAL.ColumnIndex ] ];

I replaced this line with:
LOCAL.DataMapValue = LOCAL.DataMap[ LOCAL.MetaData[ LOCAL.ColumnIndex ].Name];
so it references the metadata column name, not the column name I replaced it with.

I'm not exactly sure why doing a QoQ didn't fix this.
I'm curious why you use the column name instead of metadata name, you actually use metadata references earlier.

Now my new dilemma:
how do I get this to open a dialog instead of automatically saving to wherever expandPath() is set to?

Reply to this Comment

@Beth,

When I first built this, I was afraid that I could not depend on people using valid (or unique) column names; as such, I was determined to use auto-generated column names. And, to be honest, I've never really had an issue with it. But, enough people seem to want to the named-columns - I should probably update the code.

As far as using a save dialog (what I think you are talking about), you need to stream the binary data to the client (browser) and tell it the given file data is an attachment.

Something like this:

<cfheader
name="content-disposition"
value="attachment; filename=data.xls"
/>

<cfcontent
type="application/excel"
variable="#YOUR_EXCEL_DATA_VARIABLE#"
/>

Here, the "variable" attribute would contain the binary data of the Excel file. Or, if you already saved the file to disk, you could use the "file" attribute rather than the "variable" attribute.

Ultimately, though, it's the CFHeader tag / "attachment" setting that gets the browser to prompt for a save.

Reply to this Comment

Hello,

I really like your POIUtility.cfc. Is there any effort being spent to do the cell formula evaluator? Many of my formulas are not being created in the Excel file. As a workaround, I desire to skip the entire cell, but I can't do that either.

Any suggestions?

Reply to this Comment

@Tasha,

I think the earlier versions of the POI JAR file don't fully support formulas. You might want to try updating your POI JAR library; that might help out.

Reply to this Comment

I'm running into the instantiation java error I've seen listed above suddenly. It was working on 3 different CF installs with no issue, but once I migrated to the test server I can't load in any of the files that are working on the other servers fine.

The only two differences are file storage is handled with a UNC path instead of a local drive, and 2/3 other servers are running CF9 not 8.

Any thoughts?

Reply to this Comment

Hey Ben,

Awesome utility :)

Would you have any objections to me adapting this into a plugin for the Wheels framework?

Best,
Andy

Reply to this Comment

@Dana,

I think CF9 has an updated POI JAR package, so if you're getting unexpected errors on a CF8 box, you might just be trying to use something not supported in the previous JAR version. If you're getting errors on a CF9 box, however, it might be the pathing issue - I'm not too familiar with UNC paths (or how they might affect file access).

@Andy,

Not at all - I'd be flattered. I haven't worked on this in a while; I wish I had now :) I have some cool ideas on improvements, just never got around to trying them out.

Reply to this Comment

@Paul,

Regarding this error: "An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error:"

I wanted to point out that it also happens if I attempt to ReadExcel() an xls file saved in this format: Microsoft Excel 5.0/95 Workbook(*.xls).

Todd

Reply to this Comment

Hi Ben,

I am trying POI utility to read an Excel file create using Excel 2007 but saved as "Excel 1997 - 2003 Workbook". The worksheet am trying to read has drop-downs.

The read behavior is inconsistent. Sometimes it reads the file and other times it throws the following error:

Object Instantiation Exception.

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

The error occurred in C:\WWF\compdb\_cfc\POIUtility.cfc: line 883
Called from C:\WWF\compdb\import_relationships.cfm: line 22
Called from C:\WWF\compdb\_cfc\POIUtility.cfc: line 883
Called from C:\WWF\compdb\import_relationships.cfm: line 22
881 : "org.apache.poi.hssf.usermodel.HSSFWorkbook"
882 : ).Init(
883 : LOCAL.ExcelFileSystem
884 : );
885 :

Any thoughts what might be causing this and ideas on how to fix the issue?

Cheers,
D.

Reply to this Comment

Excellent utility Ben.

When I use it with a CF8 Server it works and reads excel files fine.
However when I use it on a Railo Server I get this error:

"can not load class through its string name, because no definition for the class with the specifed name [org.apache.poi.hssf.usermodel.HSSFWorkbook] could be found"

Any ideas?

Reply to this Comment

add XLSX support (tested with CF8)

the poi api wich comes with CF8 is very old.
download new poi api from http://poi.apache.org/download.html

copy all jar-files (also from subdirectories) direct to [cfusion]/lib

restart coldfusion

changes in POIUtility.cfc in function ReadExcel()
replace these lines:
LOCAL.ExcelFileSystem = CreateObject( "java", "org.apache.poi.poifs.filesystem.POIFSFileSystem" ).Init( LOCAL.FileInputStream );
LOCAL.WorkBook = CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").Init(LOCAL.ExcelFileSystem);

with this:
LOCAL.FileInputStream = CreateObject( "java", "java.io.FileInputStream" ).Init( ARGUMENTS.FilePath );
LOCAL.WorkBookFactory = CreateObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").Init();
LOCAL.WorkBook = LOCAL.WorkBookFactory.create(LOCAL.FileInputStream);

Reply to this Comment

Just FYI on the error ...

"Has anyone tested this with CF8? I upgraded our server to CF8 last week and now it appears to be broken. Here's the error I'm receiving now.

Object Instantiation Exception.
An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Error: ''.
"

I was getting this, and noticed that it was the client adding filtering, once removed the error went away.

Reply to this Comment

Hello, nice utility first of all! I've a little problem when using "HasHeaderRow = true" with more than 10 columns... As the array of columnNames is sorted numeric natural (1,2,3,4...) And the query is sorted numeric alfabetical(1,10,11,12,13[...]19,2,20). So the columnNames in the array do not correspond to the column numbers in the query... eg: arr index 1 = column 1, BUT arr index 2 = column 10. Is it possible to get the query columns returned numeric natural order? Thanks!

Reply to this Comment

Hello, nice utility first of all! I've a little problem when using "HasHeaderRow = true" with more than 10 columns... As the array of columnNames is sorted numeric natural (1,2,3,4...) And the query is sorted numeric alfabetical(1,10,11,12,13[...]19,2,20). So the columnNames in the array do not correspond to the column numbers in the query... eg: arr index 1 = column 1, BUT arr index 2 = column 10. Is it possible to get the query columns returned numeric natural order? Thanks!

Reply to this Comment

@Christophe,

what you want? read or write an excel? i have never problems with the columorder. i use queries/excelfiles with 50+ columns and use POI to read and write them.
keep in mind that every usage of queryname.ColumnList returns columnnames in alphanumerical order.

Reply to this Comment

howdy Ben! (yes, i'm from texas) :)

so i've been using your utility for quite some time and i use them extensively for excel (works great btw! and thanks for all the awesome work/assistance on your utilities). the problem i have now is a new business rule i have to implement/deliver. the customer has an unGodly 500+ columns report they want to export to excel. but the cell.cfm only allows for 255. do you have an updated version that will handle this new request?

Reply to this Comment

With Dirk's xsls support, I could create a file in my pc (instance.xlsx) which I can open clicking on it, modify and save ok. But when I try to open this file from the code (I need to show Save/Open dialog box) the format is not valid and the file can't be opened (though the saved file is ok). These are the lines I added to open from code:

  • <cfheader
  • name="content-disposition"
  • value="attachment; filename=Instance.xlsx"
  • />
  •  
  • <cfcontent
  • type="application/ms-excel"
  • variable="./Instance.xlsx"
  • />

Any suggestion? maybe the excel mimetype? (I have tried application/ms-excel, application/vnd.ms-excel.sheet.macroEnabled.12, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)

Thanks.
Mila.

Reply to this Comment

Is there a way to protect a column using the POI Utility when writing an excel file?

Reply to this Comment

Dirk,

The changes you made ReadExcel() were only for reading .xlsx files. Do you have the equivalent for writing .xlsx files?

Thanks,
David

Reply to this Comment

Ben,

I've been lurking on your blog for some time and you've saved my bacon more than once. I finally had to comment in response to Josh Olsen's comments from Feb of 2008. It may be a moot point, but I just have to throw my hat into the ring on your coding style:

I love it!

I am so tired of trying to maintain CF code that is laid out horizontally instead of vertically! I am maintaining some pages that have lines that extend out to over 600 characters wide!

Granted, CF does a horrible job removing whitespace - even with CFSILENT and other techniques. It's still no reason to make code harder to maintain, read, and spot obvious errors.

I'd much rather see this:

  • <cfif var eq 1>
  • <cfset newvar = "some value">
  • <cfelse>
  • <cfset newvar = "some other value">
  • </cfif>

Than this:

  • <cfif var eq 1><cfset newvar="some_value"><cfelse><cfset newvar="some_other_value"></cfif>

Then, when coders mix HTML and CF in a single line, I just want to pull out my precious few remaining hairs!

I think white space gives mental breathing room and allows the brain to digest the code in a more meaningful way.

Commenting is a matter of style and taste. I tend to comment as if talking to myself six months into the future - because that's how it usually works out. I try to document things that I will need to know in the future and not just make sure every line has an associated comment. I focus on the WHY I am doing something instead of the WHAT. I can deduce what is going on but I may totally forget why I was doing it in the first place.

Your exchange with Josh was far more professional and restrained than I think I would have been in a similar situation.

Thank you for sharing so much knowledge and code over the years. I for one, appreciate what you do as well as THE WAY you do it! :)

Regards,

murmeister

Reply to this Comment

Ben,

The POIUTILITY cfc missing the super and subscripts part.

Ex: Co2, H20, 1st, 2nd etc...

for the above cases the super and subscripts missing.

Can you please help me in this read.

Regards,
Varun Reddy

Reply to this Comment

first off, love your POI Utility, it gets used daily. I got the most recent version, but was still having a problem with dates getting rendered as floating point. To get around that, I made the following mod, starting at line 1792, right after an if/else to set the cell style.

  • // attempt to set data format because for whatever reason,
  • // dates occasionally get rendered as numbers.
  • // So, I will look at the DataMapValue, and if it is a date type,
  • // I will manually set the cell style.
  • // Note, this does not take into effect the existing RowStyle or AltRowStyle.
  • // I leave that to others to contend with.
  • if (REFindNoCase( "smalldatetime|date|time", LOCAL.DataMapValue )){
  • LOCAL.CellStyle = ARGUMENTS.WorkBook.CreateCellStyle();
  • LOCAL.CellStyle.setDataFormat(ARGUMENTS.WorkBook.createDataFormat().getFormat("m/d/yy hh:mm"));
  • LOCAL.Cell.SetCellStyle(LOCAL.CellStyle);
  • }

Reply to this Comment

I am using the POI Utility for writing a monthly sales report. I am needing help with breaking the data out by Salesman Name. Meaning I need to total the sales for each Salesman, then have a grand total of all sales. Can anyone help?

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.