Skip to main content
D
djpr0ject
Participating Frequently
November 1, 2012
Question

ColdFusion Search with Excel Sheet creation

  • November 1, 2012
  • 1 reply
  • 1246 views

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)

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
November 1, 2012

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.

D
djpr0jectAuthor
Participating Frequently
November 1, 2012

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>

D
Dan_Bracuk
Inspiring
November 2, 2012

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices