^ pop-out codeShare ^
  1. <cffunction name="queryToCsv2" access="public" output="false" returntype="string">
  2.      <!--- required arguments --->
  3. <cfargument name="query" required="true" type="query">
  4. <!--- optional arguments --->
  5. <cfargument name="headers" required="false" type="string" default="">
  6. <cfargument name="cols" required="false" type="string" default="">
  7. <!--- delimiter: comma, tab, pipe, or semicolon --->
  8. <cfargument name="delimiter" required="false" type="string" default="comma">
  9. <!--- TODO: maybe add qualifier --->
  10. <cfscript>
  11.             /**
  12.             * Convert the query into a CSV format using Java StringBuffer Class.
  13.             *
  14.             * @argument query      The query to convert. (Required)
  15.             * @argument headers      A list of headers to use for the first row of the CSV string. Defaults to all the columns. (Optional)
  16.             * @argument cols      The columns from the query to transform. Defaults to all the columns. (Optional)
  17.             * @argument delimiter comma, tab, pipe, or semicolon. Defaults to comma (optional)     
  18.             * @return Returns a string in CSV format.
  19.             * @author Qasim Rasheed (qasimrasheed@hotmail.com), modified by Aaron Longnion
  20.             * @version 1, March 23, 2005
  21.             * @version 2, April 18, 2007
  22.             */
  23. var csv = createObject('java', 'java.lang.StringBuffer');
  24.             var i = 1;
  25.             var j = 1;
  26.             var endOfLine = chr(13) & chr(10);
  27.             var delim = "";
  28.             if (arguments.delimiter IS "comma") {
  29.              delim = ",";
  30.             } else if (arguments.delimiter IS "tab") {
  31.                 delim = chr(9);
  32.             } else if (arguments.delimiter IS "pipe") {
  33.                 delim = "|";
  34.             } else if (arguments.delimiter IS "semicolon") {
  35.                 delim = ";";
  36.             }
  37. if (ArrayLen(arguments) gte 2) {
  38.              headers = arguments[2];
  39.             }
  40.             if (ArrayLen(arguments) gte 3) {
  41.                 cols = arguments[3];
  42.             }
  43.             if (not len( trim( cols ) ) ) {
  44.                 cols = arguments.query.ColumnList;
  45.             }
  46.             if (not len( trim( headers ) ) ) {
  47.                 headers = cols;
  48.             }
  49.             headers = ListToArray( headers );
  50.             cols = ListToArray( cols );
  51. for (i = 1; i lte ArrayLen( headers ); i = i 1) {
  52.                 csv.append( '"' & headers[i] & '"#delim#' );
  53.             }
  54.             csv.append( endOfLine );
  55. for (i = 1; i lte arguments.query.RecordCount; i= i 1) {
  56.                 for (j = 1; j lte ArrayLen( cols ); j=j 1) {
  57.                     csv.append( '"' & arguments.query[cols[j]][i] & '"#delim#' );
  58.                 }
  59.                 csv.append( endOfLine );
  60.             }
  61.             return csv.toString();
  62.         </cfscript>
  63. </cffunction>