001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017: /*
018: * Created on May 11, 2005
019: *
020: */
021: package org.apache.poi.hssf.record.formula.eval;
022:
023: import java.io.FileInputStream;
024:
025: import junit.framework.AssertionFailedError;
026: import junit.framework.TestCase;
027:
028: import org.apache.poi.hssf.record.formula.functions.TestMathX;
029: import org.apache.poi.hssf.usermodel.HSSFCell;
030: import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
031: import org.apache.poi.hssf.usermodel.HSSFRow;
032: import org.apache.poi.hssf.usermodel.HSSFSheet;
033: import org.apache.poi.hssf.usermodel.HSSFWorkbook;
034: import org.apache.poi.hssf.util.CellReference;
035:
036: /**
037: * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
038: *
039: */
040: public class GenericFormulaTestCase extends TestCase {
041:
042: protected final static String FILENAME = System
043: .getProperty("HSSF.testdata.path")
044: + "/FormulaEvalTestData.xls";
045:
046: protected static HSSFWorkbook workbook = null;
047:
048: protected CellReference beginCell;
049:
050: protected int getBeginRow() {
051: return beginCell.getRow();
052: }
053:
054: protected short getBeginCol() {
055: return beginCell.getCol();
056: }
057:
058: protected final HSSFCell getExpectedValueCell(HSSFSheet sheet,
059: HSSFRow row, HSSFCell cell) {
060: HSSFCell retval = null;
061: if (sheet != null) {
062: row = sheet.getRow(row.getRowNum() + 1);
063: if (row != null) {
064: retval = row.getCell(cell.getCellNum());
065: }
066: }
067:
068: return retval;
069: }
070:
071: protected void assertEquals(String msg, HSSFCell expected,
072: HSSFFormulaEvaluator.CellValue actual) {
073: if (expected != null && actual != null) {
074: if (expected != null
075: && expected.getCellType() == HSSFCell.CELL_TYPE_STRING) {
076: String value = expected.getRichStringCellValue()
077: .getString();
078: if (value.startsWith("#")) {
079: expected.setCellType(HSSFCell.CELL_TYPE_ERROR);
080: }
081: }
082: if (!(expected == null || actual == null)) {
083: switch (expected.getCellType()) {
084: case HSSFCell.CELL_TYPE_BLANK:
085: assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual
086: .getCellType());
087: break;
088: case HSSFCell.CELL_TYPE_BOOLEAN:
089: assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN,
090: actual.getCellType());
091: assertEquals(msg, expected.getBooleanCellValue(),
092: actual.getBooleanValue());
093: break;
094: case HSSFCell.CELL_TYPE_ERROR:
095: assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual
096: .getCellType()); // TODO: check if exact error matches
097: break;
098: case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation
099: throw new AssertionFailedError(
100: "Cannot expect formula as result of formula evaluation: "
101: + msg);
102: case HSSFCell.CELL_TYPE_NUMERIC:
103: assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC,
104: actual.getCellType());
105: TestMathX.assertEquals(msg, expected
106: .getNumericCellValue(), actual
107: .getNumberValue(), TestMathX.POS_ZERO,
108: TestMathX.DIFF_TOLERANCE_FACTOR);
109: // double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue());
110: // double pctExpected = Math.abs(0.00001*expected.getNumericCellValue());
111: // assertTrue(msg, delta <= pctExpected);
112: break;
113: case HSSFCell.CELL_TYPE_STRING:
114: assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual
115: .getCellType());
116: assertEquals(msg, expected.getRichStringCellValue()
117: .getString(), actual
118: .getRichTextStringValue().getString());
119: break;
120: }
121: } else {
122: throw new AssertionFailedError("expected: " + expected
123: + " got:" + actual);
124: }
125: }
126: }
127:
128: public GenericFormulaTestCase(String beginCell) throws Exception {
129: super ("genericTest");
130: if (workbook == null) {
131: FileInputStream fin = new FileInputStream(FILENAME);
132: workbook = new HSSFWorkbook(fin);
133: fin.close();
134: }
135: this .beginCell = new CellReference(beginCell);
136: }
137:
138: public void setUp() {
139: }
140:
141: public void genericTest() throws Exception {
142: HSSFSheet s = workbook.getSheetAt(0);
143: HSSFRow r = s.getRow(getBeginRow());
144: short endcolnum = r.getLastCellNum();
145: HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(s,
146: workbook);
147: evaluator.setCurrentRow(r);
148:
149: HSSFCell c = null;
150: for (short colnum = getBeginCol(); colnum < endcolnum; colnum++) {
151: try {
152: c = r.getCell(colnum);
153: if (c == null
154: || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
155: continue;
156:
157: HSSFFormulaEvaluator.CellValue actualValue = evaluator
158: .evaluate(c);
159:
160: HSSFCell expectedValueCell = getExpectedValueCell(s, r,
161: c);
162: assertEquals("Formula: " + c.getCellFormula() + " @ "
163: + getBeginRow() + ":" + colnum,
164: expectedValueCell, actualValue);
165: } catch (RuntimeException re) {
166: throw new RuntimeException("CELL[" + getBeginRow()
167: + "," + colnum + "]: " + re.getMessage(), re);
168: }
169: }
170: }
171:
172: }
|