<!--- 
 Filename: FilmsToExcelPrettier.cfm
 Author:  Nate Weiss (NMW)
 Purpose: Outputs film information for Microsoft Excel
--->

<!--- Retrieve information about films --->
<cfquery datasource="ows" name="getFilms">
 SELECT MovieTitle, AmountBudgeted
 FROM Films
 ORDER BY MovieTitle
</cfquery>

<!--- Set the content-type so Excel is invoked --->
<cfcontent type="application/msexcel">
<!--- Suggest default filename for spreadsheet --->
<cfheader name="Content-Disposition" value="filename=FilmBudgets.xls">

<!--- Include "XML Namespace" information to --->
<!--- allow using Excel "extensions" to HTML --->
<html 
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns="http://www.w3.org/TR/REC-html40">
<head><title>Film Budgets</title></head>
<body>
<style TYPE="text/css">
 .rowHeads {
  color:white;
  background:blue; 
 }
 .titleCol {
  width:400px;
  font-style:italic;
  font-family:verdana;
 }
 .priceCol {
  width:170px;
  font-family:verdana;
  color:red;
  mso-number-format:"\0022$\0022\#\,\#\#0\.00"
 };
</style>
<!--- Output ordinary HTML table, which will --->
<!--- be displayed by Excel as a spreadsheet --->
<table>
 <!--- Top row --->
 <tr>
  <th class="rowHeads">Movie Title</th>
  <th class="rowHeads">Amount Budgeted</th>
 </tr>
 
 <!--- Data rows --->
 <cfoutput query="getFilms">
  <tr>
   <td class="titleCol">#MovieTitle#</td>
   <td class="priceCol">#AmountBudgeted#</td>
  </tr>
 </cfoutput>
 
 <!--- Last row, with "total" formula --->
 <cfset firstPriceCell = "B2">
 <cfset lastPriceCell = "B" & getFilms.RecordCount + 1>
 <cfset totalFormula  = "SUM(#FirstPriceCell#:#LastPriceCell#)">
 <cfoutput>
  <tr>
   <td 
    class="titleCol" 
    style="font-weight:bold;background:yellow">Total:</td>
   <td 
    class="priceCol" 
    style="font-weight:bold;background:yellow"
    x:fmla="=#TotalFormula#"></td>
  </tr>
 </cfoutput>
</table>
</body>
</html>
