CODEWITHSUNDEEP
coldfusioncoldfusion-10
weggie
weggie

Reputation: 447

How to properly format dates with SpreadsheetFormatColumns

Server: CF10 Platform: Windows and Linux (Win in DEV windows in PROD)

So I'm generating an excel file for a client, but having trouble getting date fields to behave properly. Everything else seems to work just fine but when I send the data to excel to be generated, excel treats it as just text. So when the field gets sorted, it is handled as such.

More info: The column I'm trying to configure is called Arrival Date it is arriving formatted as mm/dd/yyyy. (I have tried to format is as m/d/yy, but when it arrives in the sheet that doesn't work as well.)

Code:

<cfset filename = expandPath("./TDYData_(#DateFormat(now(),'mmddyy')#).xls")>
<!--- Make a spreadsheet object --->
<cfset s = spreadsheetNew("TDYData")>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "TDY Phase,Full Name,Employment Category,Gender,Originating Agency,Agency Comments,Originating Office,Office Comments,Originating Country,TDY Request Received,Mission Office Supported,Type of TDY Support,eCC Submission,eCC Approval,eCC Point of Contact,Date of Departure from Originating Country,Arrival Date,Departure Date,Accomodation Type,Accomodation Comments,Assigned Desk,Local Mobile Number,TDY Comments")>

<!--- format header --->
<cfset spreadsheetFormatRow(s, {bold=true,fgcolor="lemon_chiffon",fontsize=10}, 1)>
<!--- Add query --->
<cfset spreadsheetAddRows(s, myExcel)>
<cfset SpreadSheetAddFreezePane(s,0,1)>

<cfset SpreadsheetFormatColumn(s, {dataformat="m/d/yy"}, 17) />
<cfset SpreadsheetFormatColumn(s, {alignment="right"}, 16) />


<cfheader name="content-disposition" value="attachment; filename=TDY_Data_(#DateFormat(now(),'mmddyy')#).xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Any ideas?

Thanks

Upvotes: 0

Views: 561

Answers (1)

weggie
weggie

Reputation: 447

So I have a workable solution:

<cfset therow = 0>
<cfoutput query="myExcel" startrow="1">
  <cfset therow = myExcel.currentrow + 1>
  <cfif len(eCCSubDate) GT 0>
  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##eCCSubDate##Chr(34)#)",therow,13)>
  </cfif>
  <cfif len(eCCApproved) GT 0>
  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##eCCApproved##Chr(34)#)",therow,14)>
  </cfif>
  <cfif len(DateDepartCntry) GT 0>
  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateDepartCntry##Chr(34)#)",therow,16)>
  </cfif>
  <cfif len(DateArrive) GT 0>
  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateArrive##Chr(34)#)",therow,17)>
  </cfif>
  <cfif len(DateDepart) GT 0>
  <cfset SpreadsheetSetCellFormula(s,"DATEVALUE(#Chr(34)##DateDepart##Chr(34)#)",therow,18)>
  </cfif>         


</cfoutput>

So I set a variable for a counter then loop back over the query and check to make sure its not an empty cell then apply a formula to the cell the chr(34) are quotes then the data value. By using this, (After the data is written) it will at least behave like a real date field.

Upvotes: 0

Related Questions