// Copyright � 2006-2007 ASERT. Released under the Canoo Webtest license.
package com.canoo.webtest.plugins.exceltest;
import com.canoo.webtest.engine.StepFailedException;
import com.canoo.webtest.engine.StepExecutionException;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.hssf.usermodel.HSSFCell;
/**
* Verifies that a cell represents the sum of a range of cells in an Excel spreadsheet file, either
* as a "=SUM(<range>)" formula or numeric value.<p>
*
* @author Rob Nielsen
* @webtest.step category="Excel"
* name="excelVerifyCellSum"
* alias="verifyCellSum"
* description="This step verifies that a cell represents the sum of a range of cells, either as a formula (=SUM(<range>)) or numeric value."
*/
public class ExcelVerifyCellSum extends AbstractExcelCellStep {
private String fRange;
public String getRange() {
return fRange;
}
/**
* @param range
* @webtest.parameter
* required="yes"
* description="The range of cells to verify sum against. (eg 'A1:A5')"
*/
public void setRange(final String range) {
fRange = range;
}
protected void verifyParameters() {
super.verifyParameters();
nullParamCheck(getRange(), "range");
if (!getRange().matches("[A-Za-z]+[0-9]+:[A-Za-z]+[0-9]+")) {
throw new StepExecutionException("Cannot parse \""+getRange()+"\" as a spreadsheet range. eg \"A10:A20\"", this);
}
}
public void doExecute() throws Exception {
final HSSFCell excelCell = getExcelCell();
checkFormula(excelCell);
checkLiteralValue(excelCell);
}
private void checkFormula(final HSSFCell excelCell) {
if (excelCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
final String expectedValue = "SUM(" + getRange() + ")".toUpperCase();
final String actualValue = excelCell.getCellFormula().toUpperCase();
if (verifyStrings(expectedValue, actualValue)) {
return;
}
throw new StepFailedException("Unexpected formula in cell " + getCellReferenceStr(), expectedValue, actualValue);
}
else if (excelCell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
throw new StepFailedException("Cell " + getCellReferenceStr() + " does not contain a formula or a numeric value.");
}
}
private void checkLiteralValue(final HSSFCell excelCell) {
final double cellValue = excelCell.getNumericCellValue();
final int colon = getRange().indexOf(':');
final CellReference start = ExcelCellUtils.getCellReference(this, getRange().substring(0, colon));
final CellReference end = ExcelCellUtils.getCellReference(this, getRange().substring(colon + 1));
double sum = 0;
for(int row = start.getRow() ; row <= end.getRow() ; row++ ) {
for(short col = start.getCol(); col <= end.getCol(); col++) {
final HSSFCell excelCellAt = ExcelCellUtils.getExcelCellAt(this, row, col);
if (excelCellAt == null || excelCellAt.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
continue;
}
if (excelCellAt.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
sum += excelCellAt.getNumericCellValue();
} else {
throw new StepFailedException("Cell " + ((char) ('A' + col)) + (row + 1) + " does not contain a numeric value.");
}
}
}
if (Math.abs(cellValue - sum) > 0.01) {
throw new StepFailedException("Unexpected sum of cells from range " + fRange + " in cell " + getCellReferenceStr(),
String.valueOf(sum), String.valueOf(cellValue));
}
}
}