Skip to content

Instantly share code, notes, and snippets.

@imageaid
Created March 23, 2011 22:07
Show Gist options
  • Save imageaid/884140 to your computer and use it in GitHub Desktop.
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.
// 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>
@LinkWorxSeo
Copy link

Would you be able to just provide a spreadsheetRead to read the file and .xml data?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment