<!--- Create a new instance of the POI utility. --->
<cfset objPOIUtility = CreateObject(
	"component",
	"POIUtility"
	).Init()
	/>
 
<!---
	Get the path to our Excel document. Our Excel document
	workbook contains three sheets with information regarding
	three meals (Breakfast, lunch, and dinner).
--->
<cfset strFilePath = ExpandPath( "./meals.xls" ) />
 
<!---
	Read the Excel document into an array of Sheet objects.
	Each sheet object will contain the data in the Excel
	sheet as well as some other property-type information.
	We are telling the POI Utility to expect the first row
	of the Excel document to function as a header row.
--->
<cfset arrSheets = objPOIUtility.ReadExcel(
	FilePath = strFilePath,
	HasHeaderRow = true
	) />
 
 
<!---
	We want to get the data from the Excel document into our
	local database. The first thing we are going to want to
	do is loop over each sheet and attach each set of
	data individually.
--->
<cfloop
	index="intSheet"
	from="1"
	to="#ArrayLen( arrSheets )#"
	step="1">
 
	<!---
		Let's get a pointer to the current sheet object. We
		could continue to refer to the sheet as an index of
		the sheets array, but this is more convenient and
		breaks it up into easier to read code.
	--->
	<cfset objSheet = arrSheets[ intSheet ] />
 
	<!---
		The data from the excel object is stored in a query
		within this "sheet" object and can be accessed at the
		key "query." For ease of use and short hand, let's
		get a pointer to that query.
	--->
	<cfset qSheetData = objSheet.Query />
 
	<!---
		We can treat this query just like any old ColdFusion
		query because it is just a plain old ColdFusion query.
		Let's loop over it to get at each row.
 
		NOTE: Since we told the POI Utility to use the first
		row as a header row, the first row has already been
		stripped off and returned as part of the sheet object.
		We will not encounter it in THIS query.
	--->
	<cfloop query="qSheetData">
 
		<!---
			For ease of demonstration, let's get the values
			out of the query that we want to use for our
			database insert. This is step that is not required,
			but helps clarify how things are working.
 
			NOTE: Remember that the POI Utility auto names the
			columns as it encounters them as COLUMN1, COLUMN2,
			COLUMN3, .... etc.
 
			We are throwing the second column (quantity) into a
			Val() method call as we need to get it as a number.
			The POI Utility reads in everything as a string.
		--->
		<cfset strFood = qSheetData.column1 />
		<cfset flQuantity = Val( qSheetData.column2 ) />
		<cfset strTastiness = qSheetData.column3 />
 
 
		<!---
			Now that we have the values we are going after, we
			can insert them into our database.
		--->
		<cfquery name="qInsert" datasource="#App.DSN.Source#">
			INSERT INTO food_diary
			(
				meal,
				food,
				quantity,
				tastiness,
				date_created
			) VALUES (
				<cfqueryparam value="#objSheet.Name#" cfsqltype="CF_SQL_VARCHAR" />,
				<cfqueryparam value="#strFood#" cfsqltype="CF_SQL_VARCHAR" />,
				<cfqueryparam value="#flQuantity#" cfsqltype="CF_SQL_FLOAT" />,
				<cfqueryparam value="#strTastiness#" cfsqltype="CF_SQL_VARCHAR" />,
				<cfqueryparam value="#Now()#" cfsqltype="CF_SQL_TIMESTAMP" />
			);
		</cfquery>
 
	</cfloop>
 
</cfloop>