Trouble with cfspreadsheet and excel files

hi,

 

i have request client generate excel spreadsheet query. have query kicking out fields , can generate excel file without hitch. problem comes when client takes excel file , tries manipulate it. majority of trouble comes fields should marked currency or dates. am, struggle able generate "real" date field. before excel not sorting dates properly.

 

i able call excel formula using this:

<cfset spreadsheetsetcellformula(s,"datevalue(#chr(34)##replacement_etd##chr(34)#)",therow,9)>

 

date value forces excel acknowledge real date field. however, fails when file manipulated thru excel. next problem currency field. can't excel acknowledge currency. comes custom. when set, sum function won't work in excel. can add fields individually a1+b1+c1 = total. won't helpful when there 200 rows.

 

i able suggestion cf programmer had similar situation. generated excel file first proper headings , set columns proper fields such date , currency etc.

 

the next step fill in fields row row , should formatted.

 

code:

 

<cfset filename = expandpath("./reports/arrivals.xlsx")>

<cfspreadsheet  action="read" src = "#filename#"  name = "s" >

          <cfset therow = 0>

          <cfoutput query="myexcel" startrow="1">

               <cfset therow = myexcel.currentrow + 1>

               <cfset spreadsheetsetcellvalue(s, incumbent, therow, 1)>

               <cfset spreadsheetsetcellvalue(s, section, therow, 2)>

               <cfset spreadsheetsetcellvalue(s, position_number, therow, 3)>

               <cfset spreadsheetsetcellvalue(s, position_title, therow, 4)>

               <cfset spreadsheetsetcellvalue(s, incumbent_emplyment_type, therow, 5)>

               <cfset spreadsheetsetcellvalue(s, incumbent_etd, therow, 6)>

               <cfset spreadsheetsetcellvalue(s, tour_comments, therow, 7)>

               <cfset spreadsheetsetcellvalue(s, replacement, therow, 8)>

               <cfset spreadsheetsetcellvalue(s, replacement_eta, therow, 9)>

          </cfoutput>   

<cfheader name="content-disposition" value="attachment; filename=departures_(#dateformat(now(),'mmddyy')#).xls">

<cfcontent type="application/msexcel" variable="#spreadsheetreadbinary(s)#" reset="true">

 

the data in cells has been formatted. when file generated , streamed user columns not formatted expected.

 

does else know if method work or have better suggestion on getting cf generate proper date , currency field excel acknowledge?

 

adobe coldfusion v10 running on rhel 5

 

thanks

for reason, have format twice - once before data inserted, , once after.

 

this true if have data values strictly integers.  these, if format cell ahead of time 'text', still align right, integers do.  however, if reapply formatting after data inserted, should format properly.

 

hth,

 

^_^

 

ps.. thank taking time code (cf)spreadsheetsetcellvalue!!  many people lazy , chuck query object @ cfspreadsheet tag.

More discussions in ColdFusion

adobe