<cffunction name="writeExcelSheet" access="public"
output="false" returntype="void">
<cfargument name="tableName" required="true" type="string" />
<cfargument name="dsnName" required="true" type="string" />
<cfargument name="fileName" required="true" type="string" />
<cfscript>
var getRecords = '';
var listOfColumns = '';
var tabChar = '';
var newLine = '';
var index = '';
var value = '';
</cfscript>
<cfsetting enablecfoutputonly="Yes">
<cfquery name="getRecords" datasource="#arguments.dsnName#">
select * from #arguments.tableName#
</cfquery>
<cfif getRecords.RecordCount>
<cfsetting showdebugoutput="false">
<cfset listOfColumns = getRecords.columnList>
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<cfsavecontent variable="value">
<cfloop list="#listOfColumns#" index="index">
<cfoutput>#index##TabChar#</cfoutput>
</cfloop>
</cfsavecontent>
<cffile action="write" file="#arguments.fileName#" output="#value#" >
<!--- output query data using cfloop & cfoutput --->
<cfset value = ''>
<cfsavecontent variable="value">
<cfloop query="getRecords">
<cfloop list="#listOfColumns#" index="index">
<cfoutput>#getRecords['#index#'][CurrentRow]##TabChar#</cfoutput>
</cfloop>
<cfoutput>#NewLine#</cfoutput>
</cfloop>
</cfsavecontent>
<cffile action="append" file="#arguments.fileName#" output="#value#">
</cfif>
</cffunction>
Explanation
In the above function, we need three arguments – tableName
which is the name of the table from which we need to extract the data and save
to the excelsheet, dsnName is the datasource name for connecting to the
database where the table is present, fileName is the full path along with the
excelsheet name where the data is to be written. This uses all the ColdFusion
functions to write to an excelsheet. Here we are simply querying the database
and if we have records, we are setting the first row of the sheet with the
column names of the table and then we are looping over the records, and
creating one row for each record. We have used cfsavecontent tag to first save
the contents in a variable and then we are writing the value of the variable to
the sheet. The above method is much simpler and has an easy to understand code
than that for the first method.
