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>
-----Original Message-----
From: Chad McCue [mailto:[email protected]]
Sent: Wednesday, June 17, 2009 8:51 AM
To: cf-talk
Subject: Reading Excel Files with Coldfusion
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:323572
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
