Created
March 23, 2011 22:07
-
-
Save imageaid/884140 to your computer and use it in GitHub Desktop.
The ease of using cfspreadsheet (or the spreadsheet cfscript functions) to generate an XLS from a CF query.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// the CFC I use to grab the data | |
component displayname="MemberActivity" hint="I generate reports on Member Activity"{ | |
public MemberActivity function init(){ | |
return this; | |
} | |
public query function getMemberLogonActivity(){ | |
var result = ""; | |
var q_service = new query(); | |
q_service.setDatasource(MYDSNVAR); | |
q_service.setName("getMemberLogonActivity"); | |
q_service.setSQL( | |
"SELECT first_name, last_name, email, state, registration_date, personality_factor, | |
month_1_logons, month_2_logons, month_3_logons, month_4_logons, month_5_logons, month_6_logons, | |
month_7_logons, month_8_logons, month_9_logons, month_10_logons, month_11_logons, month_12_logons, | |
total_annual_logons | |
FROM vw_MemberLogonActivity | |
WHERE total_annual_logons > 0 | |
ORDER BY total_annual_logons DESC" | |
); | |
result = q_service.execute(); | |
return result.getResult(); | |
} | |
} | |
// In the calling template: | |
<cfscript> | |
service = createObject("component","MemberActivity").init(); | |
activity = service.getMemberLogonActivity(); | |
// first we create the spreadsheet object | |
spreadsheet = spreadsheetNew("memberactivityreport"); | |
// next we add the header row | |
spreadsheetAddRow(spreadsheet,"first_name, last_name, email, state, registration_date, personality_factor, month_1_logons, month_2_logons, month_3_logons, month_4_logons, month_5_logons, month_6_logons, month_7_logons, month_8_logons, month_9_logons, month_10_logons, month_11_logons, month_12_logons, total_annual_logons"); | |
// I want to format my headers so that they're bold and centered | |
spreadsheetFormatRow(spreadsheet,{bold=true,alignment="center"},1); | |
// Ah, CFML how I love you. Just use the spreadsheetAddRows method to add your entire query to the spreadsheet | |
spreadsheetAddRows(spreadsheet,activity); | |
// finally, write the file to the server/file system | |
spreadsheetWrite(spreadsheet,"/path/to/file/member_activity_#dateFormat(now(),'mm_dd_yyyy')#.xls"); | |
</cfscript> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Would you be able to just provide a spreadsheetRead to read the file and .xml data?