ObjectToExcel

<cfcomponent output="false">

	<cffunction name="init" access="public" returntype="any" hint="I am the initializer." output="false">
		<cfreturn this/>
	</cffunction>


	<cffunction name="setAutoFilter" returntype="any">
		<cfargument name="autoFilter" default="0">
		<cfset variables.autoFilter = arguments.autoFilter>
		<cfreturn>
	</cffunction>


	<cffunction name="getAutoFilter" returntype="boolean">
		<cfreturn variables.autoFilter>
	</cffunction>


	<cffunction name="processObj" access="public" returntype="any" hint="I take in the data and hand of to the right processor" output="false">
		<cfargument name="rootObj" type="any" required="true" default=""/>
		<cfargument name="wktNames" type="any" required="true" default=""/>
		<cfargument name="autoFilter" type="any" required="false" default="0">

		<cfset var addAutoFilter =  setAutoFilter(arguments.autoFilter)>
		<cfset var excelData = ""/>
		<cfif isQuery(arguments.rootObj)>
			<cfset excelData = processQuery( arguments.rootObj, getWorksheetName( arguments.wktNames, 1 ) )/>
		<cfelse>
			<cfset excelData = processArrayofQueries( arguments.rootObj, arguments.wktNames )/>
		</cfif>

		<cfreturn getXMLHeader() & startWorkbook() & getStyles() & excelData & endWorkBook()/>
	</cffunction>


	<cffunction name="processQuery" access="private" returntype="any" hint="I process a query object into excel" output="false">
		<cfargument name="queryObj" type="any" required="true" default=""/>
		<cfargument name="nodeName" type="any" required="true" default=""/>

		<cfset var columnListed = getColumnList(arguments.queryObj)/>
		<cfset var columnCount = ListLen( columnListed )/>
		<cfset var currVal = ""/>
		<cfset var rendered = ""/>
		<cfset var cellVal = ""/>

		<cfsavecontent variable="rendered">
			<cfoutput>
				#startWorksheet( nodeName )#
					#startTable()#
						#startNewRow()#
							<cfloop from="1" to="#columnCount#" index="curr">
								<cfset currVal = ListGetAt( columnListed, curr ) />
								#renderCell( curr, ListGetAt( columnListed, curr ), 'String' )#
							</cfloop>
						#endRow()#
						<cfloop query="arguments.queryObj">
							#startNewRow()#
								<cfloop from="1" to="#columnCount#" index="curr">
									<cfset currVal = ListGetAt( columnListed, curr ) />
									<cfset cellVal = arguments.queryObj[ currVal ][currentrow] />
									<cfif not IsSimpleValue(cellVal)>
										<cfset cellVal = "" />
									</cfif>
									#renderCell( curr, cellVal, determineType( trim( cellVal ) ) )#
								</cfloop>
							#endRow()#
						</cfloop>
					#endTable()#
				<cfif getAutoFilter()><AutoFilter x:Range="R1C1:R1C#columnCount#" xmlns="urn:schemas-microsoft-com:office:excel"> </AutoFilter></cfif>

				#endWorkSheet()#
			</cfoutput>
		</cfsavecontent>

		<cfreturn rendered/>
	</cffunction>


	<cffunction name="processArrayOfQueries" access="private" returntype="any" hint="I loop through an array of queries and process each query." output="false">
		<cfargument name="queryArray" type="any" required="true" default=""/>
		<cfargument name="workSheetNames" type="any" required="true" default=""/>

		<cfset var excelData = ""/>
		<cfset var finalProduct = ""/>
		<cfloop from="1" to="#ArrayLen(arguments.queryArray)#" index="o">
			<cfset excelData = processQuery( arguments.queryArray[o], getWorksheetName( arguments.workSheetNames, o ) )/>
			<cfset finalProduct = finalProduct & excelData/>
		</cfloop>

		<cfreturn finalProduct/>
	</cffunction>


	<cffunction name="startWorkBook" access="private" returntype="any" hint="I return the start workbook descriptor" output="false">
		<cfreturn "<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">"/>
	</cffunction>


	<cffunction name="EndWorkbook" access="private" returntype="any" hint="I return the end workbook descriptor" output="false">
		<cfreturn "</Workbook>"/>
	</cffunction>


	<cffunction name="startWorksheet" access="private" returntype="any" hint="I return the start worksheet descriptor" output="false">
		<cfargument name="node" type="any" required="true" default=""/>

		<cfset var wks = ""/>
		<cfsavecontent variable="wks"><cfoutput><Worksheet ss:Name="#arguments.node#"></cfoutput></cfsavecontent>

		<cfreturn wks/>
	</cffunction>


	<cffunction name="EndWorksheet" access="private" returntype="any" hint="I return the end worksheet descriptor" output="false">
		<cfreturn "</Worksheet>"/>
	</cffunction>


	<cffunction name="startTable" access="private" returntype="any" hint="I return the start table descriptor" output="false">
		<cfreturn "<Table>"/>
	</cffunction>


	<cffunction name="EndTable" access="private" returntype="any" hint="I return the end table descriptor" output="false">
		<cfreturn "</Table>"/>
	</cffunction>


	<cffunction name="startNewRow" access="private" returntype="any" hint="I return the start row descriptor" output="false">
		<cfreturn "<Row>"/>
	</cffunction>


	<cffunction name="EndRow" access="private" returntype="any" hint="I return the end row descriptor" output="false">
		<cfreturn "</Row>"/>
	</cffunction>


	<cffunction name="renderCell" access="private" returntype="any" hint="I render the cell portion and pass it back" output="false">
		<cfargument name="pos" type="any" required="true" default=""/>
		<cfargument name="data" type="any" required="true" default=""/>
		<cfargument name="bType" type="any" required="true" default=""/>

		<cfset var hCell = ""/>
		<cfsavecontent variable="hCell"><cfoutput><Cell ss:StyleID="#getThisStyle(arguments.data)#" ss:Index="#pos#"><Data ss:Type="#arguments.bType#">#scrubData(trim(arguments.data))#</Data></Cell></cfoutput></cfsavecontent>

		<cfreturn hCell/>
	</cffunction>


	<cffunction name="determineType" access="private" returntype="any" hint="I figure out the type and pass it back" output="false">
		<cfargument name="data" type="any" required="true" default=""/>

		<cfif LSIsDate( arguments.data )>
			<cfreturn "DateTime"/>
		<cfelseif isNumeric( arguments.data )>
			<cfreturn "Number"/>
		<cfelseif isBoolean( arguments.data )>
			<cfreturn "Boolean"/>
		<cfelse>
			<cfreturn "String"/>
		</cfif>
	</cffunction>


	<cffunction name="getColumnList" access="private" returntype="any" hint="I return a query's column list in the correct order" output="false">
		<cfargument name="queryObj" type="any" required="true" default="#QueryNew("")#"/>

		<cfset var columnList = "" />
		<cfset var queryMeta = "" />
		<cfset var i = "" />

		<cftry>
			<cfset queryMeta = getMetaData(Arguments.queryObj) />
			<cfset columnList = "" />
			<cfloop index="i" from="1" to="#ArrayLen(queryMeta)#">
				<cfset columnList = ListAppend(columnList, queryMeta[i].name) />
			</cfloop>

			<cfcatch></cfcatch>
		</cftry>

		<cfreturn columnList />
	</cffunction>


	<cffunction name="getWorksheetName" access="private" returntype="any" hint="I get the worksheet name from the named list" output="false">
		<cfargument name="workSheetList" type="any" required="true" default=""/>
		<cfargument name="pos" type="any" required="true" default=""/>

		<cfif ListLen(arguments.workSheetList) gte pos>
			<cfreturn ListGetAt(arguments.workSheetList, pos)/>
		<cfelse>
			<cfreturn  'Worksheet ' & pos/>
		</cfif>
	</cffunction>


	<cffunction name="getXMLHeader" access="private" returntype="any" hint="I return the xml header" output="false">
		<cfreturn "<?xml version=""1.0""?>#Chr(13)##Chr(10)#"/>
	</cffunction>


	<cffunction name="scrubData" access="private" returntype="any" hint="I scrub the data and clean it up for the XML" output="false">
		<cfargument name="data" type="any" required="true" default=""/>

		<cfset var hVal = ""/>
		<cfswitch expression="#determineType(arguments.data)#">
			<cfcase value="DateTime">
				<cfset hVal = dateformat(arguments.data,'yyyy-mm-dd') & 'T' & timeformat(arguments.data,'HH:mm')/>
			</cfcase>
			<cfcase value="Number">
				<cfset hVal = arguments.data/>
			</cfcase>
			<cfcase value="Boolean">
				<cfset hVal = iif(arguments.data,DE('1'),DE('0'))/>
			</cfcase>
			<cfdefaultcase>
				<cfset hVal = arguments.data/>
			</cfdefaultcase>
		</cfswitch>
		<cfreturn xmlFormat(trim(hVal))/>
	</cffunction>


	<cffunction name="getThisStyle" access="private" returntype="any" hint="I check the type and determine the proper style for the cell" output="false">
		<cfargument name="data" type="any" required="true" default=""/>

		<cfif determineType(arguments.data) eq 'DateTime'>
			<cfreturn 'GenDate'/>
		<cfelse>
			<cfreturn 'Default'/>
		</cfif>
	</cffunction>


	<cffunction name="getStyles" access="private" returntype="any" hint="" output="false">
		<cfset var sStyles = ""/>
		<cfsavecontent variable="sStyles">
			<Styles>
				<Style ss:ID="Default" ss:Name="Normal">
					<Alignment ss:Vertical="Bottom"/>
					<Borders/>
					<Font/>
					<Interior/>
					<NumberFormat/>
					<Protection/>
				</Style>
				<Style ss:ID="GenDate">
					<NumberFormat ss:Format="General Date"/>
				</Style>
			</Styles>
		</cfsavecontent>

		<cfreturn sStyles/>
	</cffunction>

</cfcomponent>