asked on

Excel not being created from component

I have a component which is supposed to create an Excel file from any table.

Parameters passed to the component are table and datasource name.

The component is NOT creating the Excel file.

However, when I copy the component in a .CFM and execute the .CFM, the Excel file is created.

Can you take a look into this for me ?

testComponent.cfm
=================
<cfset structInParam = structnew()>
<cfset structInParam.dataSource = 'myDataSource'>
<cfset structInParam.tableName = 'login'>

<cfinvoke component="myComponent" method="crtExcelFile">
      <cfinvokeargument name="structInParam" value="#structInParam#">
</cfinvoke>

myComponent.cfc
===============

<cfcomponent>

<cffunction name="crtExcelFile" access="public" returntype="void" output="false" displayname="crtExcelFile" hint="">
      <cfargument name="structInParam" required="true" type="struct">

<cfquery name="getColumns" datasource="#structInParam.dataSource#">
SELECT Column_Name
FROM INFORMATION_SCHEMA.Columns
WHERE Table_Name = '#structInParam.tableName#'
       ORDER BY
             Column_Name
</cfquery>

<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=#structInParam.tableName#.xls">

<table border="1">
<tr>
      <cfloop query="getColumns">
       <td><cfoutput>#column_name#</cfoutput></td>
      </cfloop>
</tr>

<cfquery name="getData" datasource="#structInParam.dataSource#">
SELECT #ValueList(getColumns.Column_Name)#
FROM #structInParam.tableName#
</cfquery>

<cfoutput query="getData">
<tr><cfloop list="#getData.columnList#" index="columnName">
<td>#getData[columnName][currentRow]#</td>
</cfloop>
</tr>
</cfoutput>

</cffunction>

</cfcomponent>

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Errol Farro

ASKER

Setting output = "true" fixed the problem. Thank you very much.

Can you provide example on how to scope the variables and localize any function local variables

_agx_

#structInParam# is a function argument so it resides in the ARGUMENTS scope. Inside the function, you should prefix it with "ARGUMENTS" whenever you reference it. It'll work even if you don't, but it's a good practice and improves readability of the code. Example:

Use #ARGUMENTS.structInParam.tableName#
... instead of #structInParam.tableName#

>> localize any function local variables

Depends on your version. CF9+ uses the LOCAL scope. You can either pre-declare the variables at the top of the function

         
          <cfset LOCAL.getData = "">
          <cfset LOCAL.getColumns = "">
          <cfset LOCAL.columnName= "">

Open in new window

... or do it inline. For example, prefix those 3 variables with "LOCAL." when they are declared/used

<cfquery name="Local.getData" ...>

CF8 and earlier use the VAR keyword. In CF8 all var statements MUST be placed at the top of the function - just after the cfarguments.

<cffunction name="crtExcelFile" access="public" returntype="void" output="false" displayname="crtExcelFile" hint="">
      <cfargument name="structInParam" required="true" type="struct">

          <cfset VAR getData = "">
          <cfset VAR getColumns = "">
          <cfset VAR columnName= "">
          ....

Open in new window

Errol Farro

ASKER

Excellent tips !!!!! Thank you very much. Appreciate it

_agx_

Anytime :)