There are two ways of doing this:

1. The hard way
2. The POI Utility way

I didn’t do the easy way until now, because I didn’t think it will be that easy.

This is how I did it the hard way:

<cfset lColumns = "job_ref,job_title,firstname,lastname,email,contact_preferred,TrafficLight,location,area,jobType,industry,Occupational">
<cfloop query="qJobs">
     <cfoutput>
     <cfset index = 0>    
     <cfloop list="#lColumns#" index="columnName">
        <cfset thisColumn = "qJobs." & columnName>
        <cfset columnValue = Replace(Evaluate(thisColumn),",","|","ALL")><!--- escape all commas --->
        <cfif index gt 0>,</cfif>'#columnValue#'
        <cfset index = index + 1>
     </cfloop>
     </cfoutput>
</cfloop> 

The template will output the string on the browser

• I will then copy the output, open Notepad and save it as a CSV file.
• Close Notepad, opens the CSV file using M$ Excel and saves it as an Excel file and then I am done.

Ben’s POI Utility does all the above, and it only took me 5 minutes or so to modify his example file, it’s just too easy. Below is the code:

<cfset objPOI = CreateObject(
      "component",
      "POIUtility"
      ).Init()
      />
  <cfset objSheet.Query = qManpower />
  <cfset objSheet.ColumnList = lColumns />

  <cfset objSheet.ColumnNames = "Job Reference,Job Title, Firstname, Lastname, Email, Contact Preferred, Traffic Light, Location, Area, Job Type, Industry, Occupational Area" />
  <cfset objSheet.SheetName = "Sheet 1" />
  <cfset objPOI.WriteExcel(
      FilePath = ExpandPath( "./Sheet1.xls" ),
      Sheets = objSheet,
      HeaderCSS = "border-bottom: 2px solid dark_green ;",
      RowCSS = "border-bottom: 1px dotted gray ;"
      ) />