Author: nick
Date: Fri Dec 28 03:34:18 2012
New Revision: 1426382
URL: http://svn.apache.org/viewvc?rev=1426382&view=rev
Log:
Patch from Johan Karlsteen for bug #53966 - Implement IfError AP function
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/IfError.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1426382&r1=1426381&r2=1426382&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Fri
Dec 28 03:34:18 2012
@@ -22,8 +22,7 @@ import org.apache.poi.ss.formula.udf.UDF
import java.util.*;
/**
- * @author Josh Micich
- * @author Petr Udalau - systematized work of add-in libraries and user
defined functions.
+ * Analysis Toolpack Function Definitions
*/
public final class AnalysisToolPak implements UDFFinder {
@@ -112,7 +111,7 @@ public final class AnalysisToolPak imple
r(m, "HEX2BIN", null);
r(m, "HEX2DEC", null);
r(m, "HEX2OCT", null);
- r(m, "IFERROR", null);
+ r(m, "IFERROR", IfError.instance);
r(m, "IMABS", null);
r(m, "IMAGINARY", null);
r(m, "IMARGUMENT", null);
Added: poi/trunk/src/java/org/apache/poi/ss/formula/atp/IfError.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/IfError.java?rev=1426382&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/IfError.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/IfError.java Fri Dec 28
03:34:18 2012
@@ -0,0 +1,65 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.atp;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+/**
+ * Implementation of 'Analysis Toolpak' Excel function IFERROR()<br/>
+ *
+ * Returns an error text if there is an error in the evaluation<p/>
+ *
+ * <b>Syntax</b><br/>
+ * <b>IFERROR</b>(<b>expression</b>, <b>string</b>)
+ *
+ * @author Johan Karlsteen
+ */
+final class IfError implements FreeRefFunction {
+
+ public static final FreeRefFunction instance = new IfError();
+
+ private IfError() {
+ // enforce singleton
+ }
+
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext
ec) {
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ ValueEval val;
+ try {
+ val = evaluateArgParity(args[0], args[1],
ec.getRowIndex(), ec.getColumnIndex());
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ return val;
+ }
+
+ private static ValueEval evaluateArgParity(ValueEval arg, ValueEval
iferror, int srcCellRow, int srcCellCol) throws EvaluationException {
+ if(arg instanceof ErrorEval) {
+ return iferror;
+ } else {
+ return arg;
+ }
+ }
+}
\ No newline at end of file
Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java?rev=1426382&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java
(added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java Fri
Dec 28 03:34:18 2012
@@ -0,0 +1,84 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.formula.atp;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.util.CellReference;
+
+/**
+ * Testcase for 'Analysis Toolpak' function IFERROR()
+ *
+ * @author Johan Karlsteen
+ */
+public class TestIfError extends TestCase {
+
+ /**
+ * =IFERROR(210/35,\"Error in calculation\")" Divides 210 by 35 and
returns 6.0
+ * =IFERROR(55/0,\"Error in calculation\")" Divides 55 by 0 and returns
the error text
+ */
+ public static void testEvaluate(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sh = wb.createSheet();
+ Row row1 = sh.createRow(0);
+ Row row2 = sh.createRow(1);
+
+ // Create cells
+ row1.createCell(0, Cell.CELL_TYPE_NUMERIC);
+ row1.createCell(1, Cell.CELL_TYPE_NUMERIC);
+ row2.createCell(0, Cell.CELL_TYPE_NUMERIC);
+ row2.createCell(1, Cell.CELL_TYPE_NUMERIC);
+
+ // Create references
+ CellReference a1 = new CellReference("A1");
+ CellReference a2 = new CellReference("A2");
+ CellReference b1 = new CellReference("B1");
+ CellReference b2 = new CellReference("B2");
+
+ // Set values
+ sh.getRow(a1.getRow()).getCell(a1.getCol()).setCellValue(210);
+ sh.getRow(a2.getRow()).getCell(a2.getCol()).setCellValue(55);
+ sh.getRow(b1.getRow()).getCell(b1.getCol()).setCellValue(35);
+ sh.getRow(b2.getRow()).getCell(b2.getCol()).setCellValue(0);
+
+ Cell cell1 = sh.createRow(3).createCell(0);
+ cell1.setCellFormula("IFERROR(A1/B1,\"Error in calculation\")");
+ Cell cell2 = sh.createRow(3).createCell(0);
+ cell2.setCellFormula("IFERROR(A2/B2,\"Error in calculation\")");
+
+ double accuracy = 1E-9;
+
+ FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals("Checks that the cell is numeric",
+ Cell.CELL_TYPE_NUMERIC,
evaluator.evaluate(cell1).getCellType());
+ assertEquals("Divides 210 by 35 and returns 6.0",
+ 6.0, evaluator.evaluate(cell1).getNumberValue(), accuracy);
+
+
+ assertEquals("Checks that the cell is numeric",
+ Cell.CELL_TYPE_STRING,
evaluator.evaluate(cell2).getCellType());
+ assertEquals("Rounds -10 to a nearest multiple of -3 (-9)",
+ "Error in calculation",
evaluator.evaluate(cell2).getStringValue());
+ }
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
