Create XLS based on different columns each time

[dfwalton]

I have a very wide table that describes jobs done by employees. Created a cool interface to select types of jobs, specific emps, date ranges, etc, and it all goes out to Excel.
Now the users want to be able to select specific columns to include, not all 55. So I set up checkboxes on the selection screen, and the SQL works fine: I create a select string that only includes the column names requested. My query looks like

<cfquery...>Select #SelectList# from [where clause]...

It works great.
I have another string, #outList# to get the output from the query. It looks like: #region#,#empName#,#eventDate# ...etc

The part that fails looks like this:

<cfsetting enablecfoutputonly="yes">

<CFSaveContent Variable="outText">
<cfoutput>#ColHeads# #crlf#</cfoutput>
<cfoutput query="EPDtoXLS">
#outlist#
</cfoutput>


</CFSAVECONTENT>
<cfcontent type="application/msexcel"> <!---WAS text/Tab-Delimited --->
<cfoutput>
<CFHEADer name="Content-Disposition" value="Filename=#Form.csvFilename#">

#outText#</cfoutput>

What I get is a spreadheet with the correct column headings and #outlist# in every cell.

Any bright ideas out there? Thanks a million.

 

[cfSearching]

To get the actual values, you must loop through the column names (inside the outer loop) and use array notation

#queryName[columnName][rowNumber]#

However, I would check cflib.org. They have several pre-built functions for converting queries to CSV strings:

http://cflib.org/udf/CSVFormat

----------------------------------

http://cfsearching.blogspot.com/