Solved

Excel not being created from component

Posted on 2014-10-15
5
198 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
Building a website can seem like a daunting task to the uninitiated but it really only requires knowledge of two basic languages: HTML and CSS.
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now