8

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).
Bob Marley
  • 238
  • 1
  • 9
  • 2
    (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](http://stackoverflow.com/help/self-answer) / creating a how to :-) – Leigh Apr 08 '16 at 19:41
  • This is pretty awesome! Definitely do what @Leigh suggested though. – Chris Peters Apr 08 '16 at 21:58
  • Thanks a lot! Will be useful for me. – Thorsten Apr 12 '16 at 09:49
  • Sorry about that. I fixed the format. – Bob Marley Apr 12 '16 at 18:36
  • Great, thanks. (A happy side effect is that folks can now vote on both the question *and* answer :) – Leigh Apr 13 '16 at 14:19

1 Answers1

3

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">
Bob Marley
  • 238
  • 1
  • 9