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).

Colter

(Edit) Thanks for posting. In keeping with SO's Q&A format, could you break it up into a separate "Question", then post the solution separately as an "Answer"? (I know it is a little weird since you are both asking and answering, but that seems to be the preferred method of answering your own question / creating a how to :-) – Gash

This is pretty awesome! Definitely do what @Gash suggested though. – Noelnoelani

Thanks a lot! Will be useful for me. – Ulric

Sorry about that. I fixed the format. – Colter

Great, thanks. (A happy side effect is that folks can now vote on both the question and answer :) – Gash

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">

Colter

This is super clever. I'm planning on changing some of our Excel functionality to use this instead of another stupid hack that we've been using. – Noelnoelani

That sounds great. – Colter