You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2015/06/20 15:10:28 UTC
svn commit: r1686610 - in /poi/trunk/src:
java/org/apache/poi/ss/formula/functions/
ooxml/testcases/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/ss/formula/functions/
Author: centic
Date: Sat Jun 20 13:10:28 2015
New Revision: 1686610
URL: http://svn.apache.org/r1686610
Log:
Bug 56655: Fix Sumifs for cases where the criteria is in error.
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java?rev=1686610&r1=1686609&r2=1686610&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java Sat Jun 20 13:10:28 2015
@@ -30,7 +30,7 @@ import org.apache.poi.ss.formula.eval.Re
import org.apache.poi.ss.formula.eval.StringEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
-import org.apache.poi.ss.usermodel.ErrorConstants;
+import org.apache.poi.ss.usermodel.FormulaError;
/**
* Implementation for the function COUNTIF
@@ -254,6 +254,7 @@ public final class Countif extends Fixed
// boolean values when the target(x) is a string
return false;
}
+ @SuppressWarnings("unused")
StringEval se = (StringEval)x;
Boolean val = parseBoolean(se.getStringValue());
if(val == null) {
@@ -286,7 +287,7 @@ public final class Countif extends Fixed
return evaluate(testValue - _value);
}
}
- private static final class ErrorMatcher extends MatcherBase {
+ public static final class ErrorMatcher extends MatcherBase {
private final int _value;
@@ -296,7 +297,7 @@ public final class Countif extends Fixed
}
@Override
protected String getValueText() {
- return ErrorConstants.getText(_value);
+ return FormulaError.forInt(_value).getString();
}
public boolean matches(ValueEval x) {
@@ -306,6 +307,10 @@ public final class Countif extends Fixed
}
return false;
}
+
+ public int getValue() {
+ return _value;
+ }
}
public static final class StringMatcher extends MatcherBase {
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java?rev=1686610&r1=1686609&r2=1686610&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Sumifs.java Sat Jun 20 13:10:28 2015
@@ -20,8 +20,14 @@
package org.apache.poi.ss.formula.functions;
import org.apache.poi.ss.formula.OperationEvaluationContext;
-import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.formula.eval.AreaEval;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.RefEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.CountUtils.I_MatchPredicate;
+import org.apache.poi.ss.formula.functions.Countif.ErrorMatcher;
/**
* Implementation for the Excel function SUMIFS<p>
@@ -60,10 +66,12 @@ public final class Sumifs implements Fre
I_MatchPredicate[] mp = new I_MatchPredicate[ae.length];
for(int i = 1, k=0; i < args.length; i += 2, k++){
ae[k] = convertRangeArg(args[i]);
+
mp[k] = Countif.createCriteriaPredicate(args[i+1], ec.getRowIndex(), ec.getColumnIndex());
}
validateCriteriaRanges(ae, sumRange);
+ validateCriteria(mp);
double result = sumMatchingCells(ae, mp, sumRange);
return new NumberEval(result);
@@ -76,7 +84,7 @@ public final class Sumifs implements Fre
* Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns
* as the <code>sumRange</code> argument
*
- * @throws EvaluationException if
+ * @throws EvaluationException if the ranges do not match.
*/
private void validateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange) throws EvaluationException {
for(AreaEval r : criteriaRanges){
@@ -88,6 +96,22 @@ public final class Sumifs implements Fre
}
/**
+ * Verify that each <code>criteria</code> predicate is valid, i.e. not an error
+ *
+ * @throws EvaluationException if there are criteria which resulted in Errors.
+ */
+ private void validateCriteria(I_MatchPredicate[] criteria) throws EvaluationException {
+ for(I_MatchPredicate predicate : criteria) {
+
+ // check for errors in predicate and return immediately using this error code
+ if(predicate instanceof ErrorMatcher) {
+ throw new EvaluationException(ErrorEval.valueOf(((ErrorMatcher)predicate).getValue()));
+ }
+ }
+ }
+
+
+ /**
*
* @param ranges criteria ranges, each range must be of the same dimensions as <code>aeSum</code>
* @param predicates array of predicates, a predicate for each value in <code>ranges</code>
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java?rev=1686610&r1=1686609&r2=1686610&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java Sat Jun 20 13:10:28 2015
@@ -34,7 +34,6 @@ import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
-import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@@ -219,68 +218,6 @@ public final class TestUnfixedBugs exten
assertEquals(4, strBack.getIndexOfFormattingRun(2));
}
- @Test
- public void testBug56655() {
- XSSFWorkbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet();
-
- setCellFormula(sheet, 0, 0, "#VALUE!");
- setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
-
- XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
-
- assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0,0).getCachedFormulaResultType());
- assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,0).getErrorCellValue());
- assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0,1).getCachedFormulaResultType());
- assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,1).getErrorCellValue());
- }
-
- @Test
- public void testBug56655a() {
- XSSFWorkbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet();
-
- setCellFormula(sheet, 0, 0, "B1*C1");
- sheet.getRow(0).createCell(1).setCellValue("A");
- setCellFormula(sheet, 1, 0, "B1*C1");
- sheet.getRow(1).createCell(1).setCellValue("A");
- setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
-
- XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
-
- assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0, 0).getCachedFormulaResultType());
- assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
- assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 1, 0).getCachedFormulaResultType());
- assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 1, 0).getErrorCellValue());
- assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0, 3).getCachedFormulaResultType());
- assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 3).getErrorCellValue());
- }
-
- /**
- * @param row 0-based
- * @param column 0-based
- */
- private void setCellFormula(Sheet sheet, int row, int column, String formula) {
- Row r = sheet.getRow(row);
- if (r == null) {
- r = sheet.createRow(row);
- }
- Cell cell = r.getCell(column);
- if (cell == null) {
- cell = r.createCell(column);
- }
- cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
- cell.setCellFormula(formula);
- }
-
- /**
- * @param rowNo 0-based
- * @param column 0-based
- */
- private Cell getCell(Sheet sheet, int rowNo, int column) {
- return sheet.getRow(rowNo).getCell(column);
- }
-
@Test
public void testBug55752() throws IOException {
Workbook wb = new XSSFWorkbook();
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=1686610&r1=1686609&r2=1686610&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Sat Jun 20 13:10:28 2015
@@ -25,6 +25,7 @@ import org.apache.poi.hssf.HSSFTestDataS
import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@@ -186,28 +187,32 @@ public final class TestXSSFFormulaEvalua
// Link and re-try
Workbook alt = new XSSFWorkbook();
- alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook");
- // TODO Implement the rest of this, see bug #57184
-/*
- wb.linkExternalWorkbook("alt.xlsx", alt);
-
- cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
- // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
- assertEquals("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula());
-
- // Evaluate it, without a link to that workbook
try {
+ alt.createSheet().createRow(0).createCell(0).setCellValue("In another workbook");
+ // TODO Implement the rest of this, see bug #57184
+/*
+ wb.linkExternalWorkbook("alt.xlsx", alt);
+
+ cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
+ // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
+ assertEquals("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula());
+
+ // Evaluate it, without a link to that workbook
+ try {
+ evaluator.evaluate(cXSLX_nw_cell);
+ fail("No cached value and no link to workbook, shouldn't evaluate");
+ } catch(Exception e) {}
+
+ // Add a link, check it does
+ evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator());
+ evaluator.setupReferencedWorkbooks(evaluators);
+
evaluator.evaluate(cXSLX_nw_cell);
- fail("No cached value and no link to workbook, shouldn't evaluate");
- } catch(Exception e) {}
-
- // Add a link, check it does
- evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator());
- evaluator.setupReferencedWorkbooks(evaluators);
-
- evaluator.evaluate(cXSLX_nw_cell);
- assertEquals("In another workbook", cXSLX_nw_cell.getStringCellValue());
+ assertEquals("In another workbook", cXSLX_nw_cell.getStringCellValue());
*/
+ } finally {
+ alt.close();
+ }
}
/**
@@ -519,7 +524,6 @@ public final class TestXSSFFormulaEvalua
}
}
-
public void testBug55843f() throws IOException {
XSSFWorkbook wb = new XSSFWorkbook();
try {
@@ -539,4 +543,68 @@ public final class TestXSSFFormulaEvalua
wb.close();
}
}
+
+ public void testBug56655() throws IOException {
+ Workbook wb = new XSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+
+ setCellFormula(sheet, 0, 0, "#VALUE!");
+ setCellFormula(sheet, 0, 1, "SUMIFS(A:A,A:A,#VALUE!)");
+
+ wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+ assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0,0).getCachedFormulaResultType());
+ assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,0).getErrorCellValue());
+ assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0,1).getCachedFormulaResultType());
+ assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0,1).getErrorCellValue());
+
+ wb.close();
+ }
+
+ public void testBug56655a() throws IOException {
+ Workbook wb = new XSSFWorkbook();
+ Sheet sheet = wb.createSheet();
+
+ setCellFormula(sheet, 0, 0, "B1*C1");
+ sheet.getRow(0).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 1, 0, "B1*C1");
+ sheet.getRow(1).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
+
+ wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+ assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0, 0).getCachedFormulaResultType());
+ assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 0).getErrorCellValue());
+ assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 1, 0).getCachedFormulaResultType());
+ assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 1, 0).getErrorCellValue());
+ assertEquals(XSSFCell.CELL_TYPE_ERROR, getCell(sheet, 0, 3).getCachedFormulaResultType());
+ assertEquals(FormulaError.VALUE.getCode(), getCell(sheet, 0, 3).getErrorCellValue());
+
+ wb.close();
+ }
+
+ /**
+ * @param row 0-based
+ * @param column 0-based
+ */
+ private void setCellFormula(Sheet sheet, int row, int column, String formula) {
+ Row r = sheet.getRow(row);
+ if (r == null) {
+ r = sheet.createRow(row);
+ }
+ Cell cell = r.getCell(column);
+ if (cell == null) {
+ cell = r.createCell(column);
+ }
+ cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
+ cell.setCellFormula(formula);
+ }
+
+ /**
+ * @param rowNo 0-based
+ * @param column 0-based
+ */
+ private Cell getCell(Sheet sheet, int rowNo, int column) {
+ return sheet.getRow(rowNo).getCell(column);
+ }
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java?rev=1686610&r1=1686609&r2=1686610&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java Sat Jun 20 13:10:28 2015
@@ -21,6 +21,7 @@ package org.apache.poi.ss.formula.functi
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
+
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.OperationEvaluationContext;
@@ -264,4 +265,84 @@ public final class TestSumifs extends Te
assertEquals(625000., ex5cell.getNumericCellValue());
}
+
+ public void testBug56655() {
+ ValueEval[] a2a9 = new ValueEval[] {
+ new NumberEval(5),
+ new NumberEval(4),
+ new NumberEval(15),
+ new NumberEval(3),
+ new NumberEval(22),
+ new NumberEval(12),
+ new NumberEval(10),
+ new NumberEval(33)
+ };
+
+ ValueEval[] args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A9", a2a9),
+ ErrorEval.VALUE_INVALID,
+ new StringEval("A*"),
+ };
+
+ ValueEval result = invokeSumifs(args, EC);
+ assertTrue("Expect to have an error when an input is an invalid value, but had: " + result.getClass(), result instanceof ErrorEval);
+
+ args = new ValueEval[]{
+ EvalFactory.createAreaEval("A2:A9", a2a9),
+ EvalFactory.createAreaEval("A2:A9", a2a9),
+ ErrorEval.VALUE_INVALID,
+ };
+
+ result = invokeSumifs(args, EC);
+ assertTrue("Expect to have an error when an input is an invalid value, but had: " + result.getClass(), result instanceof ErrorEval);
+ }
+
+ public void testBug56655b() {
+/*
+ setCellFormula(sheet, 0, 0, "B1*C1");
+ sheet.getRow(0).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 1, 0, "B1*C1");
+ sheet.getRow(1).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
+ */
+ ValueEval[] a0a1 = new ValueEval[] {
+ NumberEval.ZERO,
+ NumberEval.ZERO
+ };
+
+ ValueEval[] args = new ValueEval[]{
+ EvalFactory.createAreaEval("A0:A1", a0a1),
+ EvalFactory.createAreaEval("A0:A1", a0a1),
+ ErrorEval.VALUE_INVALID
+ };
+
+ ValueEval result = invokeSumifs(args, EC);
+ assertTrue("Expect to have an error when an input is an invalid value, but had: " + result.getClass(), result instanceof ErrorEval);
+ assertEquals(ErrorEval.VALUE_INVALID, result);
+ }
+
+
+ public void testBug56655c() {
+/*
+ setCellFormula(sheet, 0, 0, "B1*C1");
+ sheet.getRow(0).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 1, 0, "B1*C1");
+ sheet.getRow(1).createCell(1).setCellValue("A");
+ setCellFormula(sheet, 0, 3, "SUMIFS(A:A,A:A,A2)");
+ */
+ ValueEval[] a0a1 = new ValueEval[] {
+ NumberEval.ZERO,
+ NumberEval.ZERO
+ };
+
+ ValueEval[] args = new ValueEval[]{
+ EvalFactory.createAreaEval("A0:A1", a0a1),
+ EvalFactory.createAreaEval("A0:A1", a0a1),
+ ErrorEval.NAME_INVALID
+ };
+
+ ValueEval result = invokeSumifs(args, EC);
+ assertTrue("Expect to have an error when an input is an invalid value, but had: " + result.getClass(), result instanceof ErrorEval);
+ assertEquals(ErrorEval.NAME_INVALID, result);
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org