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 :
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">
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With