ColdFusion POIUtility.cfc Updates And Bug Fixes
Posted April 4, 2007 at 8:54 AM
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.
Comments (68) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
Only had time for a quick look today but this is very impressive.
Posted by Sam Farmer on Apr 4, 2007 at 9:47 PM
This helped fixed the bugs I experienced. Thanks!
Posted by Brian Oeding on Apr 5, 2007 at 5:49 PM
Sweeet. Please let me know if any other bugs pop up.
Posted by Ben Nadel on Apr 5, 2007 at 5:52 PM
I wish this worked with dates, everything else is fantastic..
Posted by Thomas on Apr 6, 2007 at 3:03 PM
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.
Posted by Ben Nadel on Apr 6, 2007 at 3:34 PM
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
Posted by Will Tomlinson on Apr 29, 2007 at 10:49 PM
Oops, ignore my last comment. I have bad data to start with. There's no problem.
Thanks,
Will
Posted by Will Tomlinson on Apr 29, 2007 at 11:37 PM
@Will,
Yeah, from what I can remember, it reads all fields in a strings, so nothing should be stripped (I think).
Posted by Ben Nadel on Apr 30, 2007 at 7:30 AM
Yeah dude, it's handlin' everything correctly. I was havign a goof ball moment. :)
Thanks again Ben!
Will
Posted by Will Tomlinson on Apr 30, 2007 at 8:45 AM
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;
--------
Posted by Austen on May 2, 2007 at 10:25 AM
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.
Posted by macbuoy on May 4, 2007 at 4:33 PM
@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 :)
Posted by Ben Nadel on May 4, 2007 at 4:47 PM
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?
Posted by macbuoy on May 11, 2007 at 9:56 AM
. . .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.
Posted by macbuoy on May 11, 2007 at 10:00 AM
. . .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
Posted by macbuoy on May 11, 2007 at 10:13 AM
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).
Posted by Ben Nadel on May 11, 2007 at 10:33 AM
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 :
Posted by james on May 15, 2007 at 2:26 PM
@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).
Posted by Ben Nadel on May 15, 2007 at 2:31 PM
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 :
Posted by james on May 15, 2007 at 3:04 PM
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?
Posted by Ben Nadel on May 15, 2007 at 3:08 PM
calling it directly - an instantiation of the object on the page is the only thing left off that first example....
Posted by james on May 15, 2007 at 3:11 PM
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.
Posted by Ben Nadel on May 15, 2007 at 3:14 PM
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
) />
Posted by james on May 15, 2007 at 3:16 PM
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.
Posted by Ben Nadel on May 15, 2007 at 3:29 PM
that did it - thanks
Posted by james on May 15, 2007 at 4:07 PM
@James,
No problem dude, glad to help.
Posted by Ben Nadel on May 15, 2007 at 4:09 PM
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
Posted by michael on Jun 19, 2007 at 9:16 AM
@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.
Posted by Ben Nadel on Jun 19, 2007 at 9:22 AM
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);
...
Posted by Gordon on Aug 8, 2007 at 5:06 AM
@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 :)
Posted by Ben Nadel on Aug 8, 2007 at 7:16 AM
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
Posted by Feras Nabulsi on Aug 8, 2007 at 3:31 PM
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.
Posted by Christopher on Sep 14, 2007 at 8:14 PM
@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.
Posted by Ben Nadel on Sep 17, 2007 at 6:59 AM
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
Posted by Paul on Nov 20, 2007 at 10:31 AM
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: ''.
Posted by Paul on Nov 20, 2007 at 10:32 AM
Ahhh figured it out :)
Posted by Paul on Nov 20, 2007 at 1:35 PM
What was the problem (in case others come up against the same issue)?
Posted by Ben Nadel on Nov 20, 2007 at 1:39 PM
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
Posted by Paul on Nov 21, 2007 at 2:44 PM
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?
Posted by Ed Martin on Dec 7, 2007 at 2:46 PM
Nevermind! :)
I created a new function that edits a spreadsheet in an existing workbook.
Thanks!!
Posted by Ed Martin on Dec 7, 2007 at 3:35 PM
@Ed,
Nicely done. Hopefully I will find time to update this stuff soon :)
Posted by Ben Nadel on Dec 7, 2007 at 3:37 PM
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 :
Posted by Chad on Jan 24, 2008 at 3:01 PM
Works fine on CF8 for me..
Posted by Will on Jan 24, 2008 at 3:09 PM
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.
Posted by Chad on Jan 25, 2008 at 10:43 AM
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!
Posted by Darin Vercillo on Feb 5, 2008 at 5:14 PM
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?
Posted by Greg d on Feb 19, 2008 at 3:48 PM
@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.
Posted by Ben Nadel on Feb 19, 2008 at 3:54 PM
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.
Posted by Josh Olson on Feb 20, 2008 at 1:05 PM
@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?
Posted by Ben Nadel on Feb 20, 2008 at 1:25 PM
@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.
Posted by Josh Olson on Feb 20, 2008 at 1:56 PM
@Josh,
Obviously, if you are going to be updating the code, then by all means, make it readable for yourself :)
Posted by Ben Nadel on Feb 20, 2008 at 2:01 PM
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.
Posted by Neil Grimes on Jul 3, 2008 at 12:14 PM
@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)?
Posted by Ben Nadel on Jul 9, 2008 at 8:47 AM
@Ben
Cheers for the help Ben much appreciated.
Posted by Neil Grimes on Jul 10, 2008 at 5:43 AM
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?
Posted by Chris on Jul 10, 2008 at 10:02 AM
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.
Posted by Ben Nadel on Jul 10, 2008 at 10:22 AM
@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.
Posted by Chris on Jul 10, 2008 at 11:33 AM
@Chris,
Are you generating original file using MS Excel 2007? This is not 2007 compatible yet.
Posted by Ben Nadel on Jul 10, 2008 at 11:42 AM
@Ben
No, it's a regular .xls file.
Posted by Chris on Jul 10, 2008 at 1:19 PM
@Chris,
Hmm, very strange. There must be some sort of functionality that is causing an issue. Sorry I am not more help.
Posted by Ben Nadel on Jul 10, 2008 at 3:02 PM
@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.
Posted by Chris on Jul 10, 2008 at 3:09 PM
@Chris,
That is the strangest error that I have heard of :)
Posted by Ben Nadel on Jul 10, 2008 at 3:22 PM
What's the license agreement to use this code?
Posted by Nadav on Jul 27, 2008 at 11:12 PM
@Nadav,
There is no license agreement.
Posted by Ben Nadel on Jul 28, 2008 at 8:29 AM
Excellent, so it's free to use for any application!
Posted by Nadav on Jul 28, 2008 at 10:13 AM
@Nadav,
Indeed. Enjoy.
Posted by Ben Nadel on Jul 28, 2008 at 10:15 AM
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.
Posted by Jesse Bethke on Aug 21, 2008 at 10:17 PM
@Jesse,
Thanks for the compliments. I am working on some Excel 2007 ideas.
Posted by Ben Nadel on Aug 22, 2008 at 8:32 AM
