There are two ways of doing this:
- 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")>
<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 ;"
) />