testing-rounding.cfm

test-rounding.cfm

<cfset root = Expandpath("/") />
<cfset xlsFile = root & "\temp\CF-test-data.xlsx" />

<cfspreadsheet action="read" src="#xlsFile#" query="qry2" headerrow="1" excludeHeaderRow="true" />

<cfscript>
rowNumber = 1569;
columnName = "Total";
i = 0;
qry = ExcelToQuery(xlsFile); //read the file using the function below

</cfscript>


<table style="width: 400px; table-layout: fixed;" border="1" cellpadding="5" >
	<cfoutput query="qry">
		<cfset i = i + 1 />
	<tr>
		<td>
			#i#
		</td>
		<td>#total#</td>
		<td>
			#qry2["total"][i]#
		</td>
		<td <cfif (total - qry2["total"][i]) GT 0 >bgcolor="##ffcccc"</cfif> >
			#total - qry2["total"][i]#
		</td>
	</tr>
	</cfoutput>
</table>

<cfscript>

//http://www.silverink.nl/cfspreadsheet-cfscript-hard/
public Query function ExcelToQuery( required String fileNameStr ){

 var xlsObj = SpreadsheetRead( arguments.fileNameStr );

 /* Extract the workbook object from the spreadsheet */
 var workbookObj = xlsObj.getWorkBook();
 var sheetIndex = workbookObj.getActiveSheetIndex();

 /* Extract the sheet */
 var sheetObj = workbookObj.getSheetAt( sheetIndex );

 /* Extract column names (values in the first row in Excel sheet) */
 var rowObj = sheetObj.getRow(0);
 var columnList = "";
 for (headerCellIdx=0; headerCellIdx < rowObj.getLastCellNum(); headerCellIdx++) {
		 var headerCellObj = rowObj.getCell(headerCellIdx);
		 var headerCellValue = headerCellObj.getRichStringCellValue().getString();
				 columnList = ListAppend(columnList,ReplaceNoCase(headerCellValue, ' ', '_', "ALL"));
		 }


	/* Create new query object */
	var outputQry = QueryNew(columnList);

	/* Fetch the DateUtil object (POI), we'll need it later */
	var DateUtilObj = createObject("java","org.apache.poi.hssf.usermodel.HSSFDateUtil");

	/* Loop through the sheet. Mind that the iterator starts with 0 as we are using a Java method
		 but we ignore the data from the first row as it contains column labels!  */
	for (rowIdx=1; rowIdx < sheetObj.getLastRowNum(); rowIdx++) {

			/* Extract row */
			var rowObj = sheetObj.getRow(rowIdx);
			/* Add a new row to the query */
			QueryAddRow(outputQry);

			/* Extract cell and pass it to the query */
			for (cellIdx=0; cellIdx < rowObj.getLastCellNum(); cellIdx++) {
					var cellObj = rowObj.getCell(cellIdx);
		var cellValue = "";
if (not isNull(cellObj)){
		/* Please note that I ignore cellTypes CELL_TYPE_ERROR, CELL_TYPE_FORMULA and CELL_TYPE_BLANK as they are not relevant to me. Add your own handlers if you need them */
		if (cellObj.getCellType() eq cellObj.CELL_TYPE_STRING) {
				cellValue = cellObj.getStringCellValue().toString();
		} else if (cellObj.getCellType() eq cellObj.CELL_TYPE_BOOLEAN) {
	cellValue = cellObj.getBooleanCellValue();
		} else if (cellObj.getCellType() eq cellObj.CELL_TYPE_NUMERIC) {
	if (DateUtilObj.isCellDateFormatted(cellObj)) {
			cellValue = cellObj.getDateCellValue();
	} else {
			cellValue = cellObj.getNumericCellValue();
	}
		 }
}
			/* Set query cell to the spreadsheet cell value. Mind the iterators! Coldfusion starts with 1 */
QuerySetCell(outputQry, ListGetAt(columnList,cellIdx+1), cellValue,rowIdx );

			}
	}
	return outputQry;
}


</cfscript>