MDCFUG Maryland ColdFusion User Group 3/14/2000 Downloading CF Data into Excel presented by Joan Falc

1
MDCFUGMaryland ColdFusion User Group 3/14/2000
Downloading CF Data into Excelpresented by
Joan Falcão, NISTjoan.falcao_at_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?
• ltcfcontent type"application/excel"gt
• Select import format
• html or tab/carriage return
• Engineer white space
• ltcfsetting enablecfoutputonly"yes"gt
• Careful placement of ltcfoutputgt and lt/cfoutputgt
• Plan ahead for next screen
• Post the Excel sheet from the next screen
• Configure browsers to open Excel automatically

5
Excel_tab.cfm

• ltcfsetting enablecfoutputonly"yes"gt
• ltcfset AwardNumber "6H2000"gt
• ltcfset RecipientName "TeraTech, Inc"gt
• lt!--- QUERIES FOR GRANTS AND ACCRUALS DATA AND
  LAST UPDATED DATE---gt
• ltcfquery name"GetAccruals" datasource"Grants"gt
• SELECT FY, PP, Accrual FROM AllYearAccruals
• WHERE AwardNumber'AwardNumber' AND Accrual ltgt
  0
• lt/cfquerygt
• lt!--- START OF EXCEL OUTPUT. ---gt
• lt!--- Delimited by TAB and RETURN. ---gt
• lt!--- APPLICATION.CFM white space will be
  included. ---gt
• lt!--- So be sure to use cfsetting
  enablecfoutputonly ---gt
• lt!--- EVERYTHING PRINTED MUST FALL WITHIN
  CFOUTPUT TAGS ---gt
• ltcfcontent type"application/excel"gt
• lt!--- HEADING DATA ---gt

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)

• ltcfsetting enablecfoutputonly"yes"gt
• ltcfset AwardNumber "6H2000"gt
• ltcfset RecipientName "TeraTech, Inc"gt
• lt!--- QUERIES FOR GRANTS AND ACCRUALS DATA AND
  LAST UPDATED DATE---gt
• ltcfquery name"GetAccruals" datasource"Grants"gt
• SELECT FY, PP, Accrual FROM AllYearAccruals
• WHERE AwardNumber'AwardNumber' AND Accrual ltgt
  0
• lt/cfquerygt
• lt!--- START OF EXCEL OUTPUT. ---gt
• lt!--- EXCEL SPREADSHEET WILL READ/IMPORT HTML
  FORMATTING. ---gt
• lt!--- EVERYTHING PRINTED MUST FALL WITHIN
  CFOUTPUT TAGS ---gt
• ltcfcontent type"application/excel"gt
• lt!--- GRANTS DATA AND ASOFDATE DISPLAYED ---gt
• ltcfoutputgt
• lthtmlgt

8
Excel_html.cfm (2 of 3)

• ltbodygt
• lttable border"2" bordercolor"red"gt
• lttrgt
• lttd align"LEFT" bgcolor"Aqua"gt
• ltfont face"Arial Narrow"gtltbgtAward
  Numberlt/bgtlt/fontgt
• lt/tdgt
• lttd align"left" colspan2gt
• ltfont face"Arial Narrow"gtAwardNumberlt/fontgt
• lt/tdgt
• lt/trgt
• lttrgt
• lttd align"left" bgcolor"Aqua"gt
• ltfont face"Arial Narrow"gtltbgtRecipient
  Namelt/bgtlt/fontgt
• lt/tdgt
• lttd align"left" colspan2gt

9
Excel_html.cfm (3 of 3)

• lt!--- DYNAMICALLY RENDERED ACCRUALS DATA. ---gt
• ltcfoutput query"GetAccruals"gt
• lttrgt
• lttd align"center"gtltfont face"Arial
  Narrow"gtFYlt/fontgtlt/tdgt
• lttd align"center"gtltfont face"Arial
  Narrow"gtPPlt/fontgtlt/tdgt
• lttd align"right"gtltfont face"Arial
  Narrow"gtDollarFormat(Accrual)lt/fontgtlt/tdgt
• lt/trgt
• lt/cfoutputgt
• lt!--- END OF EXCEL OUTPUT ---gt
• ltcfoutputgt
• lt/tablegt
• lt/bodygt

10
Called from NextScreen.cfm

• lthtmlgt
• lttitlegtNext Screenlt/titlegt
• ltcfif IsDefined("GenerateExcel")gt
• ltbody onLoad"document.excel.submit()"gt
• ltcfelsegt
• ltbodygt
• lt/cfifgt
• lt!--- Contents of next screen ---gt
• Hello World!
• ltcfif IsDefined("GenerateExcel")gt
• lt!--- hidden form to pass variables to Excel
  spread sheet ---gt
• ltform name"excel" action"Excel_tab.cfm"
  method"post"gt
• ltinput type"hidden" name"AwardNumber"
  value"6H2000"gt
• ltinput type"hidden" name"RecipientName"
  value"TeraTech, Inc"gt
• lt/formgt

11
Application.cfm

• Ensure that application.cfm does not generate
  white space especially with tabcarriage
  return format
• ltcfsetting enablecfoutputonly"yes"gt
• lt!--- Your Application.cfm code goes here ---gt
• ltcfsetting enablecfoutputonly"no"gt

12
Configure Each Browser(Netscape notes)

• If youre 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 its 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