Create an excel file export with non standard column names (with spaces) using cfwheels, coldfusion and cfspreadsheet

Question

This is more of a how-to than an actual question. (I searched and couldn't find a solution, so I came up with this)

I needed to create an excel file export that would allow users to :

1. filter the data using a form, from the original table
2. Export the results to an excel file, from the original table.
3. Allow non standard column names with spaces and some special characters.
4. Format the exported data in some columns, while keeping the original table values (for filtering).

Answer 1

I searched and couldn't find a solution, so I came up with this:

Using sample table "Salary"

CREATE TABLE [dbo].[Salary](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [employee_id] [varchar](36) NULL,
    [salary] [decimal](18, 0) NULL,
    [createdat] [datetime] NULL,
    [updatedat] [datetime] NULL,
    [updated_by] [varchar](36) NULL,
    [created_by] [varchar](36) NULL )

First create a special model for pulling the excel data. Example "export.cfc"

models\export.cfc

<cfcomponent extends="Model" output="false">
    <cffunction name="init">   
      <cfset table("Salary")/>
       <!--- defined properties to allow spaces in column names via [] alias.--->
      <cfset property(sql="employee_id", name="[Employee ID]")>
      <cfset property(sql="dbo.getName(employee_id)", name="[The Employee Name]")>
      <cfset property(sql="salary", name="[He gets paid what?]")>
      <cfset property(sql="CONVERT(VARCHAR, createdAt, 101)", name="[Date Created]")>
    </cffunction>   
</cfcomponent>

Then just pull the specific columns you need for the excel export. ([] are required)

<cfset columns = "id,[employee id],[The Employee Name],[He gets paid what?],[Date Created]"/>

<cfset excelData = model("export").findAll( 
                                        select=columns,
                                        parameterize=false
                                         ) />
<cfspreadsheet 
        action = "write"  
        filename="#expandpath('files')#\export.xls" 
        query="excelData" 
        overwrite="true">