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

Question

This is more practical than real. (I searched and could not find a solution, so I came up with this)

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

• filter data using the form from the source table
• Export the results to an excel file from the source table.
• Allow custom column names with spaces and some special characters.
• Format the exported data in some columns, keeping the original table values ​​(for filtering).

Answer 1

I searched and could not find a solution, so I came up with the following:

Using the Salary Sample Table

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 custom model for pulling excel data. Example "export.cfc"

model \ 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 on the specific columns needed to export excel. ([] 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">