Solved

Excel not being created from component

Posted on 2014-10-15
5
202 Views
Last Modified: 2014-10-16
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>
0
Comment
Question by:Errol Farro
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 40384181
>> output="false"

I don't normally use cfheader/cfcontent from within a function. But I'm guessing output="false" would prevent the function from returning the Excel data to your browser - and that's why the download doesn't work.

Try setting output="true".

Edit:  Nothing to do with your question, but it's good to scope the variables when possible ie arguments.variableName vs variableName.  

Also be sure to localize any function local variables, including query names and cfloop index variables.
0
 

Author Comment

by:Errol Farro
ID: 40384692
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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40384742
#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

0
 

Author Comment

by:Errol Farro
ID: 40384796
Excellent tips !!!!!  Thank you very much. Appreciate it
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40384927
Anytime :)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Technology Resume 7 66
Copying table data to one another 15 36
send email part1 9 39
ASP.net Markup for Sub Menu 2 39
This article describes how to create custom column layout styles for Bootstrap. The article uses 5 columns to illustrate the concept, but the principle can be extended to any number of columns.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question