ColdFusion: Query to Excel Function

I have a project at work where I needed to take a result set from a user's search and give them the option to download those results to excel, but the problem didn't stop there. On the same page they are allowed also to call a totally different function and get back a result set a bit different than the first. So I had two options. I could send them each to a CF page where they could take results and output them to Excel, or I could write a function that will allow any method to be called, any query result set to be put into Excel regardless of the result set. I chose the latter, because I have another project it could also be useful for.

Let me first give a quick overview of how the app is set up. I am using Model-Glue as my framework, with a SQL backend, CF as my development language. I admit that this is somewhat limited in the sense that you must be using an object oriented type methodology to make this work.

So, the first thing we do is add a bit of JavaScript to our page, which will allow for the submission of a hidden form which will call our function.

If you notice there are a few things that are very important about this form. We have four items that are vital to the entire process. These four items tell the Excel Function what data to go get for the Excel file.

1. The name/path to the CFC you need to get your data from.
2. The method of the CFC to call which returns a query result set of the data.
3. Any arguments that you know the method takes as a hidden field with an "arg|" before it so our function knows that it is an argument. (If you have more than one argument, then you'll have more of these fields.)
4. The fields that you want to show from your query result set, often you may bring back more fields then you want in your result set, so this allows you to give the fields you want, the order in which you want them, and the alias of the fieldname for your spreadsheet with the original db fieldname pipe delimited with the alias fieldname. (ie: fieldname|My Field Name)

Next we'll insert our JavaScript link which submits this form to our Excel method.

Our form when submitted goes to an event called "queryToExcel", this event calls our method "getExcelData" (below) in our controller passing it the form values.

The first thing our controller does is set a couple of variables, one being the path to our CFC. We then use cfInvoke to make our "dynamic" method call, passing in any arguments which we loop through the form variables to get anything with our "arg|" prefix, then we get the data that we want back in a query result set, that result is then set in the viewState and sent on to our "rendering page" which you can see below.

In our processing file which I call dspExportData.cfm, we are setting our query to a variable as well as the fields in the previous form submission that we want to show. If the "showFields" form value was blank we set our columnList to output to the query.columnList, otherwise we grab the passed in column names.

Next, we check to make sure we're dealing with a query, we then set our content output to Excel. To output the data, we first output our column names then move on to our query. We do a bit of double looping to make this work, first looping from 1 to our recordCount, inside of that loop we loop over our column list and refer to our query result set like an array outputting our results and coming out with what we wanted all along.

I hope this was useful for you, this is just the way I thought to do it, there are probably better ways to do this but never-the-less this is what worked for me! If you think of better ways to do this, let me know I would love the feedback!