UDF to generate File Drops in CSV, Excel, and Tab delimited formats

At a reader's request, I'm posting a UDF I wrote to generate file drops from queries. The UDF takes as its arguments a query, a format (EXCEL, CSV, or TAB), a list of column names, and a list of column labels. It returns a text string that can be used to either generate a file on the server or can be returned with cfcontent with the appropriate mime type.

<cfscript>
function formatFileOutput(qry, format, columnNames, columnLabels)
{
  // use java string buffer to dramatically improve
  // performance for large queries.
  var output = createObject("java","java.lang.StringBuffer");
  var x = 1;
  var fields = "";
  var row = 1;
  var col = 1;

  var PAGE_HEADER = "";
  var PAGE_FOOTER = "";
  var ROW_HEADER = "";
  var ROW_FOOTER = "";
  var START_OUTPUT = "";
  var END_OUTPUT = "";
  var START_ROW = "";
  var END_ROW = "";
  var START_FIELD = "";
  var END_FIELD = "";

  // generate column label structure
  for (x=1; x lte listLen(columnLabels); x = x + 1)
  {
   columnData[listGetAt(columnNames,x)] = listGetAt(columnLabels,x);
  }

  format = trim(ucase(format));

  // set up output variables
  if (format eq "CSV" or format eq "TAB")
  {
   page_header = "";
   row_header = "";
   for (x=1; x lte listLen(columnNames); x = x + 1)
   {
    row_header = listAppend(row_header, columnData[listGetAt(columnNames,x)]);
   }
   row_header = row_header & chr(13) & chr(10);
   start_output = "";
   end_output = "";
   start_row = "";
   end_row = chr(13) & chr(10);
   start_field = "";
   if (format eq "TAB") {
    row_header = listChangeDelims(row_header," ");
    end_field = " ";
   } else {
    end_field = ",";
   }
   page_footer = "";
  } else if (format eq "EXCEL") {
   page_header = "<HTML;
<head>
<title>Results</title>
<style type=""text/css"">
BODY, TD {
font-family: sans-serif;
white-space: nowrap;
}
TD.header {
font-weight: bold;
background-color: ffff00;
}
</style>
</head>
<body>";
   row_header = "<tr>#Chr(10)#";
   for (x=1; x lte listLen(columnNames); x = x + 1)
   {
    row_header = row_header & " <td class=#chr(34)#header#chr(34)#>#columnData[listGetAt(columnNames,x)]#</td>#CHR(10)#";
   }
   row_header = row_header & "</tr>#Chr(10)#";

   start_output = "<table cellpadding=2 cellspacing=0 border=1>#Chr(10)#";
   end_output = "</table>#Chr(10)#";
   start_row = "<tr>#Chr(10)#";
   end_row = "</tr>#Chr(10)#";
   start_field = " <td valign=top>";
   end_field = "</td>#Chr(10)#";
   page_footer = "</body></html>";
  }
  output.append(PAGE_HEADER);
  output.append(START_OUTPUT);
  output.append(ROW_HEADER);
  for (row=1; row lte qry.recordCount; row = row + 1)
  {
   output.append(START_ROW);
   for (col = 1; col lte listLen(columnNames); col=col+1)
   {
    thisColumn = listGetAt(columnNames,col);
    field = evaluate("qry.#ThisColumn#[row]");
    if (field eq "" and format eq "EXCEL") {
     field=" ";
    }
    output.append(START_FIELD);

    if (format eq "CSV")
    {
     if (field neq "" and not isNumeric(field) )
     {
      field = replace(field,chr(34),"#chr(34)##chr(34)#","ALL");
      field = "#Chr(34)##field##Chr(34)#";
     }
     output.append(field);
    } else {
     output.append(FIELD);
    }
    if (col lt listlen(columnNames,", ")) {
     output.append(END_FIELD);
    }
   }
   output.append(END_ROW);
  }
  output.append(END_OUTPUT);
  output.append(PAGE_FOOTER);
  return output.toString();
}
</cfscript>

Here's an example of how to use it. You could place the following code in a file called "drop.cfm"

<cfparam name="FORMAT" default="TAB">
<cfquery name="myQuery" datasource="WEBREPORTS_MSSQL">
select top 30 * from dbo.TB907
</cfquery>
<cfscript>
if (format eq "EXCEL")
{
  mimetype = "application/msexcel";
  filename = "report.xls";
} else {
  mimetype = "text/plain";
  filename = "report.txt";
}
results = formatFileOutput(myQuery, format, myQuery.columnlist, myQuery.columnlist);
</cfscript>
<cfsetting enablecfoutputonly="Yes">
<cfcontent type="#mimetype#" reset="Yes">
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfoutput>#results#</cfoutput>