<cfscript>
	/**
	 * function func_ExcelToQueryStruct_POI
	 * 
	 *   input excelFile = full path of excel file to be read in
	 *   input numColumns = number of columns to pull from excel data [could not figure out how to do this using the POI]
	 * 
	 *   return Struct containing query objects.
	 *   
	 *     Notes: this is an example only, and pulls from many online examples...  
	 *             I cannot give credit where credit is due, unfortunately, because I do not remember which specific blogs I found 
	 *             some of this info on...
	 *   
	 *     USAGE: <cfset myVar = Request.func_ExcelToQueryStruct_POI("M:\test\myTest.xls", "25") />
	 *            <cfdump var="#myVar#" />
	 *   
	 *   
	 *   
	 */
	function func_ExcelToQueryStruct_POI(excelFile, numColumns) {

		var numCols 		= numColumns;
		var fileIn   		= CreateObject("java","java.io.FileInputStream").init(excelFile);
		var workBook 		= CreateObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(fileIn);
		var numSheets 		= workBook.getNumberOfSheets();
		var myStruct 		= StructNew();
		var sheetIndex 		= "";
		var workSheet 		= "";
		var Last_Row 		= "";
		var numRows 		= "";
		var myQueryCols		= "";
		var myQuery 		= "";
		var newRow			= "";
		var tmpCellValue 	= "";
		var cell_Add 		= "";
		var temp 			= "";

		for (sheetIndex=0; sheetIndex lte numSheets-1; sheetIndex=sheetIndex+1) {
			workSheet = workBook.getSheetAt(javacast("int", sheetIndex));
			Last_Row  = workSheet.getLastRowNum();

			numRows = Last_Row;

			//Create Column Names for CF Query
			myQueryCols = "col_1, ";
			for (indexCols=2; indexCols lte numCols+1; indexCols=indexCols+1) {
				myQueryCols = myQueryCols & "col_#indexCols#, ";
			}
			myQueryCols = Left(myQueryCols, Len(myQueryCols)-2);

			myQuery = QueryNew(myQueryCols);

			if( numRows ) {
				newRow = QueryAddRow(MyQuery, numRows+1);
				for (indexRows=1; indexRows lte numRows+1; indexRows=indexRows+1) {
					if( Len(workSheet.getRow(javacast("int", indexRows-1))) ) {
						row = workSheet.getRow(javacast("int", indexRows-1));

						for (indexCols=1; indexCols lte numCols; indexCols=indexCols+1) {
							tmpCellValue = "";

							if( Len(row.getCell(javacast("int", indexCols))) GT 0 ) {
								cell_Add = row.getCell(javacast("int", indexCols));
								if( cell_Add.getCellType() ) {
									tmpCellValue = cell_Add.getStringCellValue();
								}
								else {
									tmpCellValue = cell_Add.getNumericCellValue();
								}
							}

							temp = QuerySetCell(myQuery, "col_#Evaluate(indexCols+1)#", tmpCellValue, indexRows);
						}
					}
				}
			}
			myStruct[workBook.getSheetName(javacast("int", sheetIndex))] = myQuery;
		}
		return myStruct;
	}
	Request.func_ExcelToQueryStruct_POI = func_ExcelToQueryStruct_POI;
</cfscript>