Spreadsheets Made Even Easier in Railo

First and foremost, I want to give a huge shout out and thanks to Andy Jarrett (@andyj and Blog ) for his work on the tag & functions extension for Railo. His work on this extension, and making it publicly available for all of us, just made my life infinitely easier.

The Problem

My client wanted to get a list of all members on his site who have logged in over the last year. He wanted to know how many times each user logged in each month over the previous year (previous year being determined by the current date). He wanted the data presented in an XLS file, sorted from the most logons to the least.

The Solution

We all know that there are plenty of ways to go about creating a spreadsheet from CFML and none of them are too awful. However, I needed something that would be very easy and fast because I knew the data was going to be a bit of a bear to work with and time was limited. The new CF9 spreadsheet functionality fit the bill, however, it has not yet been incorporated into the Railo core.

At the database level, I had some fun creating a couple of UDFs and a View so that I could easily call for this data from CFML. No problems here. Okay, I’ll confess that the view and udfs were a bit tricky … but they were fun problems to solve (NERD ALERT :)!

Enter Andy’s extension. To start, I had to install the extension on Railo (duh). Like the update process for Railo, it’s incredibly easy to add extensions to your Railo server. Since Andy wrote up all the basics needed for installation on his blog, you should check out his post http://www.andyjarrett.co.uk/blog/index.cfm/2011/¼/cfspreadsheet-for-Railo-update for installation/setup details.

Once the extension was installed and the server restarted, which took a few minutes tops, all of CF9’s spreadsheet functions and the cfspreadsheet tag are now at my disposal in Railo.

From here, it took me ~10 minutes to write and test the code that would generate and output my XLS file … and this is all the code it took!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 
// 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>
</pre>

CFMLove

I love the fact that CFML (and Andy’s extension!) allowed me to spend the majority of my time working on the data and, once I had that sorted, I did not have to eff around trying to figure out how to present it. That part was easy and just freaking worked!

If you need to do some spreadsheet output and you’re on Railo, be sure to checkout the extension. I’m pretty sure you’ll be as stoked about it as I am!

Posted via email from self.is_a? Blog

Comments