Chad, have you looked into JExcelAPI (http://jexcelapi.sourceforge.net/), it is
fantastic at reading and writing Excel documents.
in fact we moved to JExcelAPI because we had so many problems with reading and
writing data in Excel. sandra was right that it is due to how excel saves the
data in the background. you have encountered one problem with how the data is
stored but there is also many more problems that arise due to this issue.
we have found JExcelAPI very easy to use, supports all our Excel activities and
has good documentation
>How would you go about doing it if you don't have column names to test
>if would be a date value.
>
>The dates are counted from Excel's internally stored date of 1/1/1900.
>
>I did it using the following information. Note I used Ben Nadel's
>POIUtility.cfc instead of using the POI libraries directly <cfloop
>from="1" to="#local.excelsheet.maxcolumncount#" index="column">
> <cfset local.columnname =
>Replace(local.excelsheet.columnnames[column]," ","","ALL")>
> <cfif local.columnName is 'DatePaid'>
> <cfset local.earlydate = createDate(1900,1,1)>
> <cfset local.prelimdate =
>dateAdd('d',evaluate('column#column#'),local.earlydate)>
> <cfset local.date = dateAdd('d',-2,local.prelimdate)>
> <cfset local.struct[#local.columnname#] =
>dateformat(local.date,"mm/dd/yyyy")>
> <cfelse>
> <cfset local.struct[#local.columnname#] =
>evaluate('column#column#')>
> </cfif>
></cfloop>
>
>I am currently using the code below to read in an uploaded excel file.
>
><cfset objWorkBook =
>CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").Init(
> <!--- Create the file input stream. --->
> CreateObject("java","java.io.FileInputStream").Init(
> <!--- Create the file object. --->
> CreateObject("java","java.io.File").Init(
>
>"#trim(SrcDirectory.SrcF_Directory)#\#UploadedFilename#"
> )
> )
> ) />
>
>
>Everything works good except that it is reading date values (7/7/1996)
>as a numeric values and outputting some weird long number. How can I get
>it to produce 7/71996 in the output instead of a numeric value.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323590
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
