Solved

Excel not being created from component

Posted on 2014-10-15
5
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. 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 : Go t…

617 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