Advertisement
jamo

CF9/10 CFSpreadsheet Dataformat Test

Jun 3rd, 2011
1,509
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <CFPARAM NAME="URL.DataFormat" DEFAULT="m/d/yy h:mm">
  2.  
  3. <CFOUTPUT>
  4. <h1>CF9/10 CFSpreadsheet Dataformat Test</h1>
  5. <b>Preferred date format:</b> "m/d/yy h:mm"<br>
  6. <b>Problem:</b> Query date/times not properly formatted when converting to Excel.<br>
  7.  
  8. <form action="#CGI.Script_name#" method="get">
  9. <b>Data Format:</b> <input type="text" name="DataFormat" value="#URL.DataFormat#" size=25>
  10. <input type="submit" name="Go" value="Generate Excel File">
  11. </form>
  12.  
  13. <fieldset><legend>"built-in" Date formats</legend><pre>m/d/yy
  14. d-mmm-yy
  15. d-mmm
  16. mmm-yy
  17. h:mm AM/PM
  18. h:mm:ss AM/PM
  19. h:mm
  20. h:mm:ss
  21. m/d/yy h:mm
  22. mm:ss
  23. [h]:mm:ss
  24. mm:ss.0</pre></fieldset>
  25. </CFOUTPUT>
  26.  
  27. <CFIF NOT StructKeyExists(URL, "Go")>
  28.     <CFEXIT>
  29. </CFIF>
  30.  
  31. <cfset q = queryNew("Name,Date", "varchar,date")>
  32. <cfloop index="x" from="1" to="10">
  33.     <cfset queryAddRow(q)>
  34.     <cfset querySetCell(q, "Name", "Name #x#")>
  35.     <cfset querySetCell(q, "Date", now())>
  36. </cfloop>
  37.  
  38. <cfset dfStyle = {dataformat = trim(URL.DataFormat)}>
  39.  
  40. <cfset o = spreadsheetNew('dateTest', true)>
  41. <cfset spreadsheetAddRows(o, q)>
  42. <cfset spreadsheetFormatColumn(o, dfStyle, 2)>
  43.  
  44. <cfset bin = spreadsheetReadBinary(o)>
  45.  
  46. <cfheader name="Content-Disposition" value="attachment; filename=dateTest_#dateFormat(now(), 'ddmmmyyyy')#.xls">
  47. <!--- or try using "application/vnd-ms.excel" as cfcontent type --->
  48. <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#bin#" reset="true">
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement