ColdFusion POIUtility.cfc Updates And Bug Fixes

Only had time for a quick look today but this is very impressive.

This helped fixed the bugs I experienced. Thanks!

I wish this worked with dates, everything else is fantastic..

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

Oops, ignore my last comment. I have bad data to start with. There's no problem.

Thanks,
Will

Yeah dude, it's handlin' everything correctly. I was havign a goof ball moment. :)

Thanks again Ben!

Will

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

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.

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?

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

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

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 :

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 :

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

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

that did it - thanks

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

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

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

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.

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

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

Ahhh figured it out :)

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

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?

Nevermind! :)

I created a new function that edits a spreadsheet in an existing workbook.

Thanks!!

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 :

Works fine on CF8 for me..

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.

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!

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?

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.

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

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.

@Ben

Cheers for the help Ben much appreciated.

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?

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

@Ben

No, it's a regular .xls file.

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

What's the license agreement to use this code?

Excellent, so it's free to use for any application!

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.

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.

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.

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.

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

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

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!

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

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.

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

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

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.

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

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

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.

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

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 ?

Thanks....

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

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

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?

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?

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?

Hey Ben,

Awesome utility :)

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

Best,
Andy

@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

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.

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?

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

and of course you should remove the old poi class files from [cfusion]/lib

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.

You'll have to make sure the jar file is in place - then restart service guessing.

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!

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!

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

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?

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)

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

Dirk,

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

Thanks,
David

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

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

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);
}

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?

Sorry but I'm just a newbie in ColdFusion....Does POIUtility.cfc script compatible with ColdFusion 7?