Anybody using POIUtility cfc to import from Excel?

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?

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

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.

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

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

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

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