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