Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel not being created from component

Posted on 2014-10-15
5
Medium Priority
?
208 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 2000 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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
Originally, this post was published on Monitis Blog, you can check it here . Websites are getting bigger and more complicated by the day. Video, images and custom fonts are all great for showcasing your product or service. But the price to pay in…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

721 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