I do like spreadsheets. They're basically glorified csv files and talk about the lowest-common-denominator for data - I'm pretty sure after the nuclear holocaust, the last two things left on earth will be cockroaches and data stored in spreadsheets... There are many ways to work with spreadsheets, but it's not uncommon to be pulling data into ColdFusion queries, massaging the data, and then stashing it. This is the sort of utility script work that I get asked to do on client sites like it's my job. Oh, wait... yeah yeah...
Like most data structures, ColdFusion queries are great for some jobs and stink for others. But then there are things about queries that should work well but they simply don't. If you have ever tried to generically output a query and you used the "queryname.columnlist" property, you know what I'm talking about. That column list is in alphabetic order, not in the order it appeared in a select statement, or in this case, in the order of the spreadsheet columns. Oh, and its translated to upper case, which is seriously ugly. This is not always a problem, however, when working with spreadsheets, it often is. Spreadsheet users usually put their columns in an order for a reason - and if you are working with a script to read a spreadsheet into a query, do a little dance and then write a new spreadsheet back out, generically (i.e. without hard-coding the column names in your script) - then using that columnlist property for the headers in the output spreadsheet is trouble.
Solution? use the getmetadata function on your query to get an array of structs that describe the columns in the query - and yes, that's an array, which means ordered, in this case from left-to-right in the spreadsheet. Nice! Also, the case of the values is completely preserved. Extra Nice!
One trick is that you now have an array of structs and what you usually want (for your output to the spreadsheet) is just a common-delimited list of the column names. The column names are all in the key "name" in each structure item in the array. Oh, look what i found (and by found i mean wrote) http://www.cflib.org/udf/arrayofStructsToList
So, drop that <cffunction /> into a page and sprinkle in some of this sample code: note, you'll also need a source xls file
<cfscript> fpath = expandPath("."); sep = request.vars.sep; source = listappend(fpath,"source.xlsx",sep); dest = listappend(fpath,"dest.xls",sep); </cfscript> <!--- pull the source spreadsheet into a query object ---> <cfspreadsheet action="read" src="#source#" query="q" headerrow="1" rows="2-9999" /> <cfoutput>ugly stuff:#q.columnlist#</cfoutput><br /> <cfoutput>much better:#arrayofStuctsToList(getmetadata(q),'name')#</cfoutput><br /> <!--- image you wanted to modify the data and do some calculations ---> <cfset queryAddColumn(q,"check",[]) /> <cfloop query="q"> <cfscript> isok = "BAD"; // do whatever you need to do here if (true) isok = "OK"; querySetCell(q,"check",isok,currentrow); </cfscript> </cfloop> <!--- note the newly created column comes back in both calls. thats good! ---> <cfoutput>ugly stuff:#q.columnlist#</cfoutput><br /> <cfoutput>much better:#arrayofStuctsToList(getmetadata(q),'name')#</cfoutput><br /> <!--- create an output spreadsheet for the new data ---> <cfset out = spreadsheetNew()> <!--- Add header ---> <cfset spreadsheetAddRow(out, arrayofStuctsToList(getmetadata(q),"name"))> <!--- format header if you like ---> <cfset spreadsheetFormatRow(out,,1)> <!--- drop in the data ---> <cfset spreadsheetAddRows(out, q)> <cfset spreadsheetWrite(out, dest, true)>