001: // Copyright © 2006-2007 ASERT. Released under the Canoo Webtest license.
002: package com.canoo.webtest.plugins.exceltest;
003:
004: import com.canoo.webtest.engine.StepFailedException;
005: import com.canoo.webtest.engine.StepExecutionException;
006: import org.apache.poi.hssf.util.CellReference;
007: import org.apache.poi.hssf.usermodel.HSSFCell;
008:
009: /**
010: * Verifies that a cell represents the sum of a range of cells in an Excel spreadsheet file, either
011: * as a "=SUM(<range>)" formula or numeric value.<p>
012: *
013: * @author Rob Nielsen
014: * @webtest.step category="Excel"
015: * name="excelVerifyCellSum"
016: * alias="verifyCellSum"
017: * description="This step verifies that a cell represents the sum of a range of cells, either as a formula (=SUM(<range>)) or numeric value."
018: */
019: public class ExcelVerifyCellSum extends AbstractExcelCellStep {
020: private String fRange;
021:
022: public String getRange() {
023: return fRange;
024: }
025:
026: /**
027: * @param range
028: * @webtest.parameter
029: * required="yes"
030: * description="The range of cells to verify sum against. (eg 'A1:A5')"
031: */
032: public void setRange(final String range) {
033: fRange = range;
034: }
035:
036: protected void verifyParameters() {
037: super .verifyParameters();
038: nullParamCheck(getRange(), "range");
039: if (!getRange().matches("[A-Za-z]+[0-9]+:[A-Za-z]+[0-9]+")) {
040: throw new StepExecutionException("Cannot parse \""
041: + getRange()
042: + "\" as a spreadsheet range. eg \"A10:A20\"", this );
043: }
044: }
045:
046: public void doExecute() throws Exception {
047: final HSSFCell excelCell = getExcelCell();
048: checkFormula(excelCell);
049: checkLiteralValue(excelCell);
050: }
051:
052: private void checkFormula(final HSSFCell excelCell) {
053: if (excelCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
054: final String expectedValue = "SUM(" + getRange()
055: + ")".toUpperCase();
056: final String actualValue = excelCell.getCellFormula()
057: .toUpperCase();
058: if (verifyStrings(expectedValue, actualValue)) {
059: return;
060: }
061: throw new StepFailedException("Unexpected formula in cell "
062: + getCellReference(), expectedValue, actualValue);
063: } else if (excelCell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
064: throw new StepFailedException("Cell " + getCellReference()
065: + " does not contain a formula or a numeric value.");
066: }
067: }
068:
069: private void checkLiteralValue(final HSSFCell excelCell) {
070: final double cellValue = excelCell.getNumericCellValue();
071: final int colon = getRange().indexOf(':');
072: final CellReference start = ExcelCellUtils.getCellReference(
073: this , getRange().substring(0, colon));
074: final CellReference end = ExcelCellUtils.getCellReference(this ,
075: getRange().substring(colon + 1));
076: double sum = 0;
077: for (int row = start.getRow(); row <= end.getRow(); row++) {
078: for (short col = start.getCol(); col <= end.getCol(); col++) {
079: final HSSFCell excelCellAt = ExcelCellUtils
080: .getExcelCellAt(this , row, col);
081: if (excelCellAt == null
082: || excelCellAt.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
083: continue;
084: }
085: if (excelCellAt.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
086: sum += excelCellAt.getNumericCellValue();
087: } else {
088: throw new StepFailedException("Cell "
089: + new CellReference(row, col)
090: + " does not contain a numeric value.");
091: }
092: }
093: }
094: if (Math.abs(cellValue - sum) > 0.01) {
095: throw new StepFailedException(
096: "Unexpected sum of cells from range " + fRange
097: + " in cell " + getCellReference(), String
098: .valueOf(sum), String.valueOf(cellValue));
099: }
100: }
101:
102: }
|