ColdFusion Search with Excel Sheet creation

[djpr0ject]

I created a form search with two buttons - one for results on the web page and one to create an excel sheet.

Problem: When a user conducts a search with results on the web page - the user would have to select the criteria again then click the create excel button to create the sheet.

How can I work it, so the user after conducting a search can just click one button to create the excel sheet?

(Using "Post" for the form - will change to get if needed will your help)

[Dan_Bracuk]

Are you sure about this?  While it's simple enough to do you might be presented with complaints about this ugly button on your nice web page.

In any event, you take the search parameters from the first page and make them hidden form fields on the second page.  Add another form field indicating excel.  Add a visible submit button.

[djpr0ject]

I'm still freshman with CF - this means I have a lot of questions.

When I tried using the excel.cfm page I receive: Variable MRESULTS is undefined.

How do I send "mresults" to the excel page?

Search Page Link:

   <a href="javascript:document.

getElementById('yourForm').submit()">Excel</a>
      <form id="yourForm" action="excel.cfm" method="post">
             <!--- save search values sent via POST --->
             <cfoutput>
             <input name="ship" value="#mresults.ship#">
           
             </cfoutput>
      </form>

Excel page:

<cfquery name="MResults"
         datasource="support">
        
        
select *

from table1

<cfform action="/msr/excel.cfm" method="post"> 

<cfset sObj=SpreadsheetNew()>

<!--- Create header row --->
<cfset SpreadsheetAddRow(sObj, "Name,address")>
<cfset SpreadsheetFormatRow(sObj, {bold=TRUE,fgcolor="grey_25_percent", alignment="center"}, 1)>

<cfset SpreadsheetFormatColumn(sObj, {alignment="left", dataformat="mm/dd/yyyy"}, 14)>

<cfset SpreadsheetAddRows(sObj, MResults)>

<cfspreadsheet action="write" name="sObj" filename="C:Orders.xls" overwrite="true">

<cfheader    name="Content-Disposition"
                        value="inline; filename=Orders.xls">
            <cfcontent    type="application/csv"
                        file="C:\Orders.xls"
                        deletefile="yes">

</cfform>

</cfquery>

[Dan_Bracuk]

First, you can't send query results as a form variable.  You can however cache them so that when the query tag runs again the results are instantly available.

Next, your code looks like there might be some repetition between the html and excel pages.  Reuseable code is better than repetitive code.  There are a number of ways to achieve this.  In situations like yours I just have one results page that processes the user inputs, runs the query, does other stuff if necessary and then renders in html or excel depending on what the user requested.

Next, cfform renders as html yet you have code inside that form which outputs an excel spreadsheet.

[djpr0ject]

You can however cache them so that when the query tag runs again the results are instantly available.

Can you give me an example of how to achieve this?

[Dan_Bracuk]

The way I do it is to have the user choose html or excel from a dropdown list.  Something like this:

<select name="ShowAs">

<option value="html" />Web Page</option>

<option value="excel" />Excel</option>

</select>

Then my results page looks like this;

code to validate user input and do other things if necessary

query

code for no records found

<cfif form.ShowAs is "html">

html code

<cfelse>

excel code

</cfif>