I am still kicking alive… have been very busy with work and family to post here haiz..
Found great solution to my long term problem using cfx excel. Tried to format the date display so many times or make the format cell working till like failed! here’s the good solution at this url!
Solution:
1) Make sure the cells for CreatedDate column are formatted in the excel template before writing. example custom > dd-mmm-yy. For CreatedTime column, set format cell to display custom > hhmmL
2) here’s the ultimate solution.
<cfset columnList = “Name,CreatedDate,CreatedTime”>
<cfset columnTypeList = “VarChar,Integer,Decimal”>
<cfset dataQuery = QueryNew(columnList,columnTypeList)>
<cfset tmp = QueryAddRow(dataQuery,1)>
<cfset tmp = QuerySetCell(dataQuery,”Name”,”Eileen”>
<cfset tmp = QuerySetCell(dataQuery,”CreatedDate”,”#DateFormat(now(),’yyyy/mm/dd’)#”>
<cfset tmp = QuerySetCell(dataQuery,”CreatedTime”,”#TimeFormat(now(),HH:mm:ss)#”>
3) use cfx excel to write the query
4) Instead of displaying mm/dd/yyyy, the cell displays dd-mmm-yy at last! coz of the “Dates are numbers!”. For time, it should use decimal. It displays the datetime as hhmmL in the excel.
http://www.eggheadcafe.com/conversation.aspx?messageid=33575936&threadid=33575933
