Coldfusion Excel Export

Question

Fairly new to Coldfusion. Running CF2021 on Windows Server 2016 Datacenter 3 cores/12GB RAM. Running SQL server 2012 (Server 2012 R2; 3 cores/12GB RAM). These are virtual servers as well. Using FW/1; MVC.

I have my code written to download a large set of data (165,000 rows with 34 columns/headers).

In my code, I have the following line started

theFile=GetDirectoryFromPath(getCurrentTemplatePath()) & "newSpreadsheet.xlsx";

which means it is saving to my "views" folder on my server (no big deal). Then I am using spreadsheetAddColumn(.....) to add my headers and spreadsheetAddRows(theSheet,#rc.myquery#); to pull my query from my model view. My problem is, there is so much data that it is pulling down, the page is erroring out with a timeout error. I limited my download to 25k records and it takes about 14 minutes to pull this set of data. I need to figure out how to get all of the records in ONE spreadsheet and what I would like to do is run a scheduledtask in order to run this during down periods and have it readily available to download the spreadsheet when folks start working. Any ideas how to speed this up and/or not have the site time out?

Another problem I am having is my scheduled tasks aren't running due to our server being secured by Siteminder. It requires a user log in upon visiting the site, even though I have anonymous authentication enabled. So my shceduled task will run, but I am getting a 403 error. I have looked into doing this via my code, but have been unsuccessful. So if anyone has any ideas on this as well, I would appreciate any suggestions.

Answer 1

We've experienced both memory and performance issues with the built-in CFSpreadsheet functions over various versions of ACF. If you want to experiment, try the alternative solution - the spreadsheet-cfml library. It uses the latest POI Java libraries and supports the new XML spreadsheet workbook object (which uses the memory-efficient SXSSF streaming format while being populated). It also supports 60+ functions that aren't available in ColdFusion.

https://github.com/cfsimplicity/spreadsheet-cfml

Answer 2

If this is a manual request and not a scheduled task, you should post your scheduled task issue as a separate question for people to target their answers accordingly.

For security reasons, You should never create a file in a publicly accessible folder.

There are two versions in a gist here.

This code requires a <cfquery> data set and will create a uniquely named file in the CF server's temp folder. You can pass in a list of column header names as one argument and a file name that will be used once it's created and presented to the user. You might remove the code that toggles the file extension and only create xlsx files. I used this in an extremely high-load financial application. We restricted data requests to 30k rows as a matter of course.

The bigger problem you're facing is likely more of a database issue. How quickly does that data generate when you run the query directly in SQL Manager? Run that query with the execution plan turned on. That should help you identify bottlenecks in the query.

<cfcomponent output="false" displayname="Spreadsheet Service">
    <cffunction name="init" access="public" output="false" returntype="SpreadsheetService">
        <cfreturn this />
    </cffunction>

    <cffunction name="createFromQuery" access="public" output="false" returntype="void">
        <cfargument name="data" type="query" required="true" />
        <cfargument name="columnnames" type="string" required="false" default=""
            hint="Comma-delimited List.">
        <cfargument name="downloadName" type="string" required="false" default=""
            hint="Final file name sent to the browser." />
        <cfargument name="xlsx" type="boolean" required="false" default="false"
            hint="File extension is xlsx (true) or xls (false)." />
        <cfargument name="excludeHeaderRow" type="boolean" required="false" default="false"
            hint="Show or hide the column header row." />

        <cfset var config= {
            , filename= getTempDirectory() & createUUID()
            , columnnames= arguments.columnnames
            , excludeHeaderRow= arguments.excludeHeaderRow
            , downloadFilename= arguments.downloadName
            } />

        <cfif arguments.xlsx>
            <cfset config.fullname &="xlsx">
            <cfif len(config.downloadName) GT 0>
                <cfset config.downloadFilename &="xlsx">
            </cfif>
            <cfelse>
            <cfset config.fullname &="xls">
            <cfif len(config.downloadName) GT 0>
                <cfset config.downloadFilename &="xls">
            </cfif>
        </cfif>

        <cfset config.full_temp_name= config.temp_path & config.temp_name & "." & config.extension />

        <cftry>
            <cfspreadsheet action="write"
                filename="#config.filename#"
                columnnames="#arguments.columnnames#"
                excludeHeaderRow="#arguments.excludeHeaderRow#"
                query="#arguments.data#" />
            <cfspreadsheet action="read" src="#config.filename#" name="local.xls" />
            <cffile action="delete" file="#config.filename#" />

            <cfif len(arguments.downloadName) GT 0>
                <cfheader name="content-disposition"
                    value="attachment; filename=#config.downloadFilename#" />
                <cfelse>
                <cfheader name="content-disposition" value="attachment; filename=#config.filename#" />
            </cfif>
            <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(local.xls)#"
                reset="true" />
            <cfcatch type="any">
                <cfdump var="#cfcatch#" output="console" />
            </cfcatch>
        </cftry>
    </cffunction>
</cfcomponent>