Producing Excel Report in ColdFusion

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(
  <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 ;"
      ) />