com.canoo.webtest.plugins.exceltest.ExcelVerifyCellSum.java Source code

Introduction

Here is the source code for com.canoo.webtest.plugins.exceltest.ExcelVerifyCellSum.java

Source

// Copyright  2006-2007 ASERT. Released under the Canoo Webtest license.
package com.canoo.webtest.plugins.exceltest;

import com.canoo.webtest.engine.StepExecutionException;
import com.canoo.webtest.engine.StepFailedException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;

/**
 * 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 Cell excelCell = getExcelCell();
        checkFormula(excelCell);
        checkLiteralValue(excelCell);
    }

    private void checkFormula(final Cell excelCell) {
        if (excelCell.getCellType() == Cell.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() != Cell.CELL_TYPE_NUMERIC) {
            throw new StepFailedException(
                    "Cell " + getCellReferenceStr() + " does not contain a formula or a numeric value.");
        }
    }

    private void checkLiteralValue(final Cell 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 Cell excelCellAt = ExcelCellUtils.getExcelCellAt(this, row, col);
                if (excelCellAt == null || excelCellAt.getCellType() == Cell.CELL_TYPE_BLANK) {
                    continue;
                }
                if (excelCellAt.getCellType() == Cell.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));
        }
    }

}