Getting stats from Soundings using cfspreadsheet
I'm hoping to post this over on soundings.riaforge.org later but I am having trouble with the site. Plus also thought good to throw out here.
Below I have hacked at the Sounding app by Ray (ColdFusion Jedi) Camdon to use cfspreadsheet to create the Excel stat sheet. All this code I have put in the IF block stating "form.format is 'excel'". It requires the query "getSurveyTakers" which should be just before it.
<cfset colList = ['Survey_Taker_ID', 'Survey_Taken' ]/><cfloop query="questions"> <cfset colName = reReplace( question, "[^A-Za-z0-9 _]", "", "all" ) /> <cfset colName = reReplace( colName, "(amp)| ", "_", "all" ) /> <cfset arrayAppend(colList, colName ) /></cfloop><cfset reportQry = queryNew( arrayToList(colList) )/><cfset QueryAddRow(reportQry, getSurveyTakers.recordCount +1)> <cfset QuerySetCell(reportQry, colList[1], "Survey Taker ID", 1)> <cfset QuerySetCell(reportQry, colList[2], "Survey Taken", 1 )><cfloop query="questions"> <cfset thisRow = questions.currentRow+2 /> <cfset QuerySetCell(reportQry, colList[thisRow], questions.question, 1)> </cfloop> <cfloop query="getSurveyTakers"> <cfset answerRow = getSurveyTakers.currentRow + 1> <cfset oid = ownerid> <cfset QuerySetCell(reportQry, colList[1], ownerid, answerRow)> <cfset QuerySetCell(reportQry, colList[2], "#dateFormat(completed,"mm/dd/yy")# #timeFormat(completed,"h:mm tt")#", answerRow )> <cfloop query="questions"> <cfset i = questions.currentRow+2 /> <cfset QuerySetCell(reportQry, colList[i], htmlEditFormat(application.survey.getAnswerResult(id,oid)), answerRow )> </cfloop> </cfloop> <cfscript> //Use an absolute path for the files. ---> theFile = expandPath( "report.xls" ); //Create empty ColdFusion spreadsheet objects. ---> theSheet = SpreadsheetNew("CourseData"); //Populate object with a query. ---> SpreadsheetAddRows(theSheet,reportQry); </cfscript> <cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheetname="courses" overwrite=true> <cfheader name="content-disposition" value="attachment;filename=report.xls"><cfcontent type="application/msexcel" file="#theFile#" deletefile="true" reset="true"> <cfabort>There might be more efficient (read: quicker) ways of doing the above so always happy for feedback!