From 413c5594fbc277c4db9ed467c4b9ce95f3dfcd04 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 28 Dec 2012 03:34:18 +0000 Subject: [PATCH] Patch from Johan Karlsteen for bug #53966 - Implement IfError AP function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1426382 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 5 +- .../apache/poi/ss/formula/atp/IfError.java | 65 ++++++++++++++ .../poi/ss/formula/atp/TestIfError.java | 84 +++++++++++++++++++ 3 files changed, 151 insertions(+), 3 deletions(-) create mode 100644 src/java/org/apache/poi/ss/formula/atp/IfError.java create mode 100644 src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java diff --git a/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java index 51fda2648b..a9343e67a3 100644 --- a/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java +++ b/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java @@ -22,8 +22,7 @@ import org.apache.poi.ss.formula.udf.UDFFinder; 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 implements UDFFinder { 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); diff --git a/src/java/org/apache/poi/ss/formula/atp/IfError.java b/src/java/org/apache/poi/ss/formula/atp/IfError.java new file mode 100644 index 0000000000..5b7e21b8d0 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/atp/IfError.java @@ -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()
+ * + * Returns an error text if there is an error in the evaluation

+ * + * Syntax
+ * IFERROR(expression, string) + * + * @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 diff --git a/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java b/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java new file mode 100644 index 0000000000..bcaafda50f --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/atp/TestIfError.java @@ -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 -- 2.39.5