Export to Excel - Hidden Field not generating results

Question

I've a simple CFC file that contains the different functions for different queries & a separate function that displays the reports dynamically based on the queries.

All the queries work except one which returns approx. 50k rows. Its just a blank screen & I get no error. When I dump the query results, they do get dumped on the screen but while displaying it in a tabular report it gives nothing.

I've a another CFM file that returns 100k rows & works fine.

Below is CFC code that is not working.

<cfcomponent>
    <cfparam name="qry1" default="">
<cffunction name="showqry1" access="remote">
    <cfquery name="qry1" dataSource="myds"  cachedwithin="#CreateTimeSpan(0, 2, 0, 0)#">
        <!--- myquery --->
    </cfquery>
    <cfset Display()>
</cffunction>
<cffunction name="showqry2" access="remote">
    <cfquery name="qry1" dataSource="myds"  cachedwithin="#CreateTimeSpan(0, 2, 0, 0)#">
        <!--- myquery --->
    </cfquery>
    <cfset Display()>
</cffunction>

<cffunction name="Display" access="private">
    <cfdump var="#rptQry#" top="20">   
    <cfsavecontent variable="myrpt">
        <table>
                <!--- make a tabular report here using cfloop over the query--->
        </table>
    </cfsavecontent>

   <cfform action="test.cfm" method="post" name="ExcelData">
        <cfoutput>#myrpt#</cfoutput>
        <cfinput type="hidden" name="excel_data" value="#myrpt#"/><!---This is giving the error. --->
        <cfinput type="submit" name="test" value="Export" />
    </cfform>

</cffunction>  
</cfcomponent>

Any idea why CFM works fine but CFC doesn't? I need my CFC to work & dont want it to convert it to CFM...

UPDATE: I've added a comment("This is giving the error") in the above code that is cause of the error. Irrespective of CFC/CFM this doesn't work.

I use the hidden field to pass data to another file which exports data to excel. Any alternate suggestions??

Any help is highly appreciated.

Thanks

We cannot comment on your code unless you actually *show us* your code. Suggested reading: http://cfmlblog.adamcameron.me/2013/09/short-self-contained-correct-compilable.html — Adam Cameron, Oct 11 '13 at 09:38
You should be passing your data as an argument into your function and also scoping your variables — Matt Busche, Oct 11 '13 at 12:02

score 3 · Answer 1 · answered Oct 11 '13 at 10:24

You still need to read that doc I put in the comment about how to ask questions clearly.

However you are putting your recordset into a variable qry1, but trying to dump a variable rptQry. But that would just error, unless there's some code you're not showing us that populates rptQry.

Also, from a coding practice POV, you shouldn't really be outputting stuff in your functions: that's best done in a CFM page. Get your data with a CFC method; display it with a CFM.

I also recommend you read up on how to do OO with CFML (or in general). Perhaps get Matt Gifford's book "Object-Oriented Programming in ColdFusion"

score 0 · Answer 2 · answered Oct 11 '13 at 10:23

0

Your Display function has cfsavecontent with tabular data and you are putting it into a cfform inside a cfc. I don't know why you are doing that. Insted, simply do an ajax call which return that cfsavecontent and then show it in the cfm.

Else, I guess you may have to output the cfform in the Display function. I may be wrong, but I don't think you can simply place a cfform inside a cfc and expect it to show up on the browser. CFC is not for browser rendering, it should be in a cfm.

answered Oct 11 '13 at 10:23

nasaa

2,701
8
47
76

I used cfform for exporting data to excel. NO browser rendering is needed but just to pass the data to the export to excel feature... – Crash OR Oct 11 '13 at 10:29
I dont know what are you doing here. If it is for exporting it to excel file then save your cfsavecontent's into an xls or csv file. Why are you using a cfform is not understandable. You can simply cfoutput the column name and then loop over the query and output there values and the save them into a file. – nasaa Oct 11 '13 at 10:46
This is because I need to export to excel on click of a button for which I'd need cfform. Can you suggest some alternate to this functionality? – Crash OR Oct 11 '13 at 11:32

score 0 · Answer 3 · answered Oct 11 '13 at 12:57

Regarding the comment, "This is because I need to export to excel on click of a button for which I'd need cfform. Can you suggest some alternate to this functionality?", I will give you some things to think about.

First, you talk about recordsets containing several thousand rows and you have code where you attempt to display that in a browser. Quite simply, that will take an enormous amount of time to render. So, it's a bad idea.

Next, your code has functions for various queries but just one display function. Unless that's a cleverly written function that figures out the column names, it will only work if all the queries have the same columns. If that's the case, maybe you only need one query and some variables.

My suggestion is to start with a form where the user sends the appropriate information which determines what sql gets written. This form should also include a way for them to choose whether they want the results rendered in excel or html. If they choose html, do something to ensure that the data being returned does not overwhelm their browser.

By the way, re-useable code for displaying query results is a good idea. However, a custom tag might be a more conventional way to do it.

Export to Excel - Hidden Field not generating results

3 Answers3