Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Anybody using POIUtility cfc to import from Excel?
Author: Tom Chiverton
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#308106
> If I can figure out how to upgrade/install/whatever files on the server
> (CF7 running on IIS), maybe I'm in business.
You can use the JavaLoader CFC to load newer versions of a JAR into CF,
without mucking about with replacing CF's files.
--
Tom Chiverton
****************************************************
This email is sent for and on behalf of Halliwells LLP.
Halliwells LLP is a limited liability partnership registered in England and Wales
under registered number OC307980 whose registered office address is at Halliwells
LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is
available for inspection at the registered office. Any reference to a partner in
relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The
Solicitors Regulation Authority.
CONFIDENTIALITY
This email is intended only for the use of the addressee named above and may be
confidential or legally privileged. If you are not the addressee you must not
read it and must not use any information contained in nor copy it nor inform any
person other than Halliwells LLP or the addressee of its existence or contents.
If you have received this email in error please delete it and notify Halliwells
LLP IT Department on 0870 365 2500.
For more information about Halliwells LLP visit
www.halliwells.com.
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#308070
Tyler Fitch wrote:
> There's a version of the POI library buried in the CF installation and you
> could replace it with the newer version from the Apache group. But I
> wouldn't recommend that as it could unexpectedly break some of CF in built
> functionality.
Yea - I sorta already figured that out.
I've been able to recreate the problem with a 2003 Excel file though.
the cfc will error out when trying to type a NULL cell - and I've not
figured out a way around this yet...
I've got code on the other side that will take care of it, but the I'm
still looking at the cfscript in the cfc itself. (without much luck so far)
Thanks,
LEs
Author: Tyler Fitch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#308028
There's a version of the POI library buried in the CF installation and you
could replace it with the newer version from the Apache group. But I
wouldn't recommend that as it could unexpectedly break some of CF in built
functionality. Personally I've used Mark Mandel's JavaLoader to reference
the newer version 3.0 version of the POI lib. This requires a little
tweaking of Ben Nadel's POIUtility.cfc's instantiation of the POI Java
classes, but it gives you ability to read the latest and greatest Excel
files which was worth it to me.
The good news is that I was talking with some engineers from the CF team at
CFUnited last week and they've already updated the POI library that comes
with CF to be the latest and greatest. So in the future this will be less
of a problem, but that's the future and not now.
t
----- Excess quoted text cut - see Original Post for more -----
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#308012
>> After upgrading to the newer 3.0.2 version I had less issues.
Heh - gotta love this:
"The Jakarta POI (Poor Obfuscation Implementation) API is a fantastic
way for Java programmers to access Microsoft document formats. The most
mature API from Jakarta POI is the HSSF (Horrible Spreadsheet Format)
API, which accesses Microsoft Excel documents."
If I can figure out how to upgrade/install/whatever files on the server
(CF7 running on IIS), maybe I'm in business.
I'm still Googling, but if anybody has a good reference on how to
install this, point me in the direction...
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#308011
Tyler Fitch wrote:
> Hi Les,
>
> Are you working with Excel 2007 files?
Yea, darnit! Weird - one file will import fine, the next will toss an
error. I can create all the spreedsheet files I want in Open Office,
save them as Excel files and every single one of them works. Go figure.
Client has been importing huge list, and this one list with only 10 rows
throws an error. Sheesh...
I though about attempting a utility that would let SQL Server do the
work, but I'm not that great with complex SQL Server stuff and don't
think I could pull it off. Plus, the formatting of the Excel sheets
*vary*, and I've got CF code that accounts for that.
Today was the first sheet they couldn't import. Once they gave it to me,
I got it to work by just putting "something" in the blank cells (spaces).
> I've had issues with reading the
> latest version of Excel files using the version of the POI library included
> in CF. After upgrading to the newer 3.0.2 version I had less issues.
I can upgrade the library? Not my server, but I have access. I'll check
that out.
Author: Tyler Fitch
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#307996
Hi Les,
Are you working with Excel 2007 files? I've had issues with reading the
latest version of Excel files using the version of the POI library included
in CF. After upgrading to the newer 3.0.2 version I had less issues.
HTH,
t
----- Excess quoted text cut - see Original Post for more -----
Author: Les Mizzell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56819#307994
I've got it working pretty darned good - except for one problem. If
there's a NULL cell in the Excel sheet, it's throwing an error
"Element CELL is undefined in LOCAL."
LOCAL.CellType = LOCAL.Cell.GetCellType();
If I go into my Excel sheet and just enter a space in the cells, no error.
So, I'm trying a try/catch block to force the cell type to string (1),
if it's NULL since I know that's what all the values are in the sheets
being used anyway:
for (
LOCAL.ColumnIndex = 0 ;
LOCAL.ColumnIndex LT LOCAL.ColumnCount ;
LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
){
LOCAL.Cell = LOCAL.Row.GetCell( JavaCast( "int", LOCAL.ColumnIndex ));
// Get the type of data in this cell.
try {
LOCAL.CellType = LOCAL.Cell.GetCellType();
} catch(any excpt)
{
LOCAL.CellType = 1
}
But, that doesn't seem to be working either.
Anybody got a clue?
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
