Solved

Excel not being created from component

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
css, html 5 43
How to make footer stick to bottom 9 31
alert(innerHTML); 8 20
Html Table looping 4 13
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

831 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