1 / 12

MDCFUG Maryland ColdFusion User Group 3/14/2000 Downloading CF Data into Excel presented by Joan Falcão, NIST joan.falc

MDCFUG Maryland ColdFusion User Group 3/14/2000 Downloading CF Data into Excel presented by Joan Falcão, NIST joan.falcao@nist.gov. Quick and Dirty. Save your ColdFusion web page in html Open Excel Open the html file from the Excel File/Open menu Html tables translate into Excel tables

sumi
Download Presentation

MDCFUG Maryland ColdFusion User Group 3/14/2000 Downloading CF Data into Excel presented by Joan Falcão, NIST joan.falc

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MDCFUGMaryland ColdFusion User Group3/14/2000Downloading CF Data into Excelpresented by Joan Falcão, NISTjoan.falcao@nist.gov

  2. Quick and Dirty • Save your ColdFusion web page in html • Open Excel • Open the html file from the Excel File/Open menu • Html tables translate into Excel tables • Form objects translate into Excel objects

  3. “Seamless” Integration • Code a template with cfcontent tag • Invoke the template from the following template • Configure each browser to associate “application/excel” with the Excel executable via the “Pick App” button • Embellish with Excel macro or add-in (another configuration step)

  4. ColdFusion Code for Excel • Use cfcontent Tag • Enable CFCONTENT in CF Server Basic security? • <cfcontent type="application/excel"> • Select import format: • html or tab/carriage return • Engineer white space • <cfsetting enablecfoutputonly="yes"> • Careful placement of <cfoutput> and </cfoutput> • Plan ahead for next screen • Post the Excel sheet from the next screen • Configure browsers to open Excel automatically

  5. Excel_tab.cfm <cfsetting enablecfoutputonly="yes"> <cfset AwardNumber = "6H2000"> <cfset RecipientName = "TeraTech, Inc"> <!--- QUERIES FOR GRANTS AND ACCRUALS DATA AND LAST UPDATED DATE---> <cfquery name="GetAccruals" datasource="Grants"> SELECT FY, PP, Accrual FROM AllYearAccruals WHERE AwardNumber='#AwardNumber#' AND Accrual <> 0 </cfquery> <!--- START OF EXCEL OUTPUT. ---> <!--- Delimited by TAB and RETURN. ---> <!--- APPLICATION.CFM white space will be included. ---> <!--- So be sure to use cfsetting enablecfoutputonly ---> <!--- EVERYTHING PRINTED MUST FALL WITHIN CFOUTPUT TAGS ---> <cfcontent type="application/excel"> <!--- HEADING DATA ---> <cfoutput>Award Number: #AwardNumber# Recipient Name: #RecipientName# </cfoutput> <!--- DYNAMICALLY RENDERED ACCRUALS DATA. PRINTED ROW-BY-ROW. ---> <!--- Engineer your white space carefully. ---> <!--- Pay careful attention to placement of cfoutput tags ---> <cfoutput query="GetAccruals"> #FY# #PP# #DollarFormat(Accrual)#</cfoutput> <cfsetting enablecfoutputonly="no">

  6. Excel_tab.cfm OUTPUT Award Number: 6H2000 Recipient Name: TeraTech, Inc 1997 23 $629,972.00 1997 3 $525,761.00 1997 11 $741,652.00 1997 16 $1,142,713.00 1998 25 $170,008.00 1998 25 $935,902.00 1998 5 $201,170.00 1998 10 $1,366,523.00 1998 16 $1,832,299.00

  7. Excel_html.cfm (1 of 3) <cfsetting enablecfoutputonly="yes"> <cfset AwardNumber = "6H2000"> <cfset RecipientName = "TeraTech, Inc"> <!--- QUERIES FOR GRANTS AND ACCRUALS DATA AND LAST UPDATED DATE---> <cfquery name="GetAccruals" datasource="Grants"> SELECT FY, PP, Accrual FROM AllYearAccruals WHERE AwardNumber='#AwardNumber#' AND Accrual <> 0 </cfquery> <!--- START OF EXCEL OUTPUT. ---> <!--- EXCEL SPREADSHEET WILL READ/IMPORT HTML FORMATTING. ---> <!--- EVERYTHING PRINTED MUST FALL WITHIN CFOUTPUT TAGS ---> <cfcontent type="application/excel"> <!--- GRANTS DATA AND ASOFDATE DISPLAYED ---> <cfoutput> <html> <head> <title>Accruals History</title> </head>

  8. Excel_html.cfm (2 of 3) <body> <table border="2" bordercolor="red"> <tr> <td align="LEFT" bgcolor="Aqua"> <font face="Arial Narrow"><b>Award Number:</b></font> </td> <td align="left" colspan=2> <font face="Arial Narrow">#AwardNumber#</font> </td> </tr> <tr> <td align="left" bgcolor="Aqua"> <font face="Arial Narrow"><b>Recipient Name:</b></font> </td> <td align="left" colspan=2> <font face="Arial Narrow">#RecipientName#</font> </td> </tr> </cfoutput>

  9. Excel_html.cfm (3 of 3) <!--- DYNAMICALLY RENDERED ACCRUALS DATA. ---> <cfoutput query="GetAccruals"> <tr> <td align="center"><font face="Arial Narrow">#FY#</font></td> <td align="center"><font face="Arial Narrow">#PP#</font></td> <td align="right"><font face="Arial Narrow">#DollarFormat(Accrual)#</font></td> </tr> </cfoutput> <!--- END OF EXCEL OUTPUT ---> <cfoutput> </table> </body> </html> </cfoutput> <cfsetting enablecfoutputonly="no">

  10. Called from NextScreen.cfm <html> <title>Next Screen</title> <cfif IsDefined("GenerateExcel")> <body onLoad="document.excel.submit();"> <cfelse> <body> </cfif> <!--- Contents of next screen ---> Hello World! <cfif IsDefined("GenerateExcel")> <!--- hidden form to pass variables to Excel spread sheet ---> <form name="excel" action="Excel_tab.cfm" method="post"> <input type="hidden" name="AwardNumber" value="6H2000"> <input type="hidden" name="RecipientName" value="TeraTech, Inc"> </form> </cfif> </body> </html>

  11. Application.cfm Ensure that application.cfm does not generate white space – especially with “tab&carriage return” format <cfsetting enablecfoutputonly="yes"> <!--- Your Application.cfm code goes here ---> <cfsetting enablecfoutputonly="no">

  12. Configure Each Browser(Netscape notes) If you’re lucky, Netscape will prompt you • In the “Unknown File Type” box, • click the “Pick App” button, and • browse for/SELECT the Excel executable on your PC If unlucky, (blank screen or Netscape insists on saving): • On Netscape menu bar, • edit/preferences/category/navigator/applications • search each description for “file type details” entry with MIME type = “application/excel” • it may have “description” of “download” (from Norton) • delete it, since it’s not working • regenerate the Excel file and respond to prompt For security reasons, do not uncheck the box: “Always ask before opening this type of file”

More Related