<!-- 
An example similiar to Sample 6 but we populate the workbook based on data from a database.
-->

<cf_xls_workbook SaveAsName="generate7.xls" >
	
	<cf_xls_font id="default" family="Arial" size="10" weight="normal" />
	<cf_xls_font id="defaultItalic" family="Arial" size="8" weight="Normal" style="italic" />
	<cf_xls_font id="headerFont" family="Geneva" size="8" weight="Normal" style="normal" />
	<cf_xls_font id="footerFont" family="Geneva" size="12" weight="Normal" />

	<cf_xls_style id="header" FontID="defaultItalic" Align="left" WrapText="Yes" BottomBorder="Medium" />
	<cf_xls_style id="data" FontID="default" Align="right" WrapText="No" />
	<cf_xls_style id="labels" FontID="default" Align="left" WrapText="No" />
	<cf_xls_style id="datesStyle" dataformat="m/d/yy" FontID="default" Align="right" WrapText="No" />
	
	
	<!-- For each sheet you need add cf_xls_sheet tags for each sheet.  Each sheet has its own headers,footers,
			printsetup, freezepanes, printareas, mergedcells, etc...
	-->
	<cf_xls_sheet Name="Grades" columnwidths="25,20,10,10,10,10,10,10,10,10,10,10">
		<!-- cf_xls_printsetup sets the orientation and margins.  Also makes the sheet fit on 1 page's width. -->
		<cf_xls_printsetup Orientation="Landscape" fitwidth="1" HeaderMargin="0.25" FooterMargin="0.25" RightMargin="0.5" LeftMargin="0.5" TopMargin="1.0" BottomMargin="1.0" />
		
		<!-- cf_xls_freezepanes makes the area stick/freeze when scrolling -->
		<cf_xls_freezepanes row="1" column="2" />
		
		<!-- Set the header and footer for this sheet. -->
		<cf_xls_header left="Left Header" leftFontID="headerFont" center="Center Header" centerFontID="headerFont" right="Right Header" rightFontID="headerFont" />
		<cf_xls_footer left="Left Footer" leftFontID="footerFont" center="Center Footer" centerFontID="footerFont" right="Right Footer" rightFontID="footerFont" />

		<cf_xls_row>			
			<cf_xls_cell id="hStart" styleID="header">Student Name</cf_xls_cell>
			<cf_xls_cell styleID="header">SSN</cf_xls_cell>
			<cf_xls_cell styleID="header">HW 1</cf_xls_cell>
			<cf_xls_cell styleID="header">HW 2</cf_xls_cell>
			<cf_xls_cell styleID="header">HW 3</cf_xls_cell>
			<cf_xls_cell styleID="header">HW 4</cf_xls_cell>
			<cf_xls_cell styleID="header">Test 1</cf_xls_cell>
			<cf_xls_cell styleID="header">Test 2</cf_xls_cell>
			<cf_xls_cell styleID="header">Test 3</cf_xls_cell>
			<cf_xls_cell styleID="header">Test 4</cf_xls_cell>
			<cf_xls_cell styleID="header">Final</cf_xls_cell>
			<cf_xls_cell id="hEnd" styleID="header">Average</cf_xls_cell>
		</cf_xls_row>
		
		<cfquery name="getStudents" datasource="xlsadaptersampledb" dbtype="ODBC">
			SELECT * FROM Students ORDER BY LastName;
		</cfquery>
		
		<cfoutput query="getStudents">	
			<!-- a label to use as a referenceID so we can add a formula to calculate the avereage grade -->
			<cfset stuLabel = "stu" & StudentID />	
				
			<cf_xls_row>			
				<cf_xls_cell style="align:left;" styleID="data">#LastName#, #FirstName#</cf_xls_cell>
				<cf_xls_cell style="align:left;" styleID="data">#SSN#</cf_xls_cell>
				<!-- this is the first grade, so we label this one, since it will be the first in 
				a list of cells to average -->
				<cf_xls_cell id="#stuLabel#_begin" type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<cf_xls_cell type="number" styleID="data">#HW1#</cf_xls_cell>
				<!-- label this one since it is the last of the cells to average -->
				<cf_xls_cell id="#stuLabel#_end" type="number" styleID="data">#HW1#</cf_xls_cell>
				<!-- now put the formula in, with references to the previous labels.-->
				<cf_xls_cell type="formula" style="dataformat:0.00" styleID="data">AVERAGE(CFXLS:#stuLabel#_begin;:CFXLS:#stuLabel#_end;)</cf_xls_cell>
			</cf_xls_row>
		</cfoutput>
	</cf_xls_sheet>	
	
	
	<cf_xls_sheet Name="Lectures" columnWidths="15,55">
		<cf_xls_row>
			<cf_xls_cell styleID="header" >Date</cf_xls_cell>
			<cf_xls_cell styleID="header" >Lecture</cf_xls_cell>
		</cf_xls_row>	
		
		<cfquery name="getLectures" datasource="xlsadaptersampledb" dbtype="ODBC">
			SELECT * FROM Lectures ORDER BY PresentedDate;
		</cfquery>
		
		<cfoutput query="getLectures">	
		<cf_xls_row>			
			<cf_xls_cell type="date" styleID="datesStyle">#DateFormat(PresentedDate,"mm/dd/yyyy")#</cf_xls_cell>
			<cf_xls_cell styleID="labels">#Title#</cf_xls_cell>
		</cf_xls_row>
		</cfoutput>
	</cf_xls_sheet>	
</cf_xls_workbook>