From 43a82fd2e9ef9f5488b83154947b386e753948cc Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Sat, 29 Mar 2008 18:23:33 +0000 Subject: Move the FormulaEvaluator code out of scratchpad git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@642574 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/hssf/model/TestFormulaParserEval.java | 110 +++ .../record/formula/eval/AllFormulaEvalTests.java | 40 + .../formula/eval/TestCircularReferences.java | 125 +++ .../record/formula/eval/TestExternalFunction.java | 61 ++ .../hssf/record/formula/eval/TestFormulaBugs.java | 217 +++++ .../formula/eval/TestFormulasFromSpreadsheet.java | 328 ++++++++ .../hssf/record/formula/eval/TestPercentEval.java | 82 ++ .../record/formula/eval/TestUnaryPlusEval.java | 61 ++ .../formula/functions/AbstractNumericTestCase.java | 73 ++ .../AllIndividualFunctionEvaluationTests.java | 53 ++ .../hssf/record/formula/functions/EvalFactory.java | 63 ++ .../formula/functions/NumericFunctionInvoker.java | 126 +++ .../hssf/record/formula/functions/TestAverage.java | 103 +++ .../record/formula/functions/TestCountFuncs.java | 150 ++++ .../hssf/record/formula/functions/TestDate.java | 100 +++ .../record/formula/functions/TestFinanceLib.java | 202 +++++ .../hssf/record/formula/functions/TestIndex.java | 89 ++ .../hssf/record/formula/functions/TestIsBlank.java | 62 ++ .../poi/hssf/record/formula/functions/TestLen.java | 73 ++ .../TestLookupFunctionsFromSpreadsheet.java | 385 +++++++++ .../hssf/record/formula/functions/TestMatch.java | 215 +++++ .../hssf/record/formula/functions/TestMathX.java | 909 +++++++++++++++++++++ .../poi/hssf/record/formula/functions/TestMid.java | 115 +++ .../hssf/record/formula/functions/TestOffset.java | 92 +++ .../poi/hssf/record/formula/functions/TestPmt.java | 87 ++ .../record/formula/functions/TestRoundFuncs.java | 49 ++ .../hssf/record/formula/functions/TestRowCol.java | 102 +++ .../record/formula/functions/TestStatsLib.java | 262 ++++++ .../record/formula/functions/TestSumproduct.java | 120 +++ .../hssf/record/formula/functions/TestTFunc.java | 118 +++ .../hssf/record/formula/functions/TestTrim.java | 78 ++ .../formula/functions/TestXYNumericFunction.java | 139 ++++ .../apache/poi/hssf/usermodel/TestBug42464.java | 94 +++ .../apache/poi/hssf/usermodel/TestBug43093.java | 59 ++ .../hssf/usermodel/TestFormulaEvaluatorBugs.java | 257 ++++++ .../hssf/usermodel/TestFormulaEvaluatorDocs.java | 117 +++ 36 files changed, 5316 insertions(+) create mode 100644 src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/AbstractNumericTestCase.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestFinanceLib.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndex.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestMathX.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestRoundFuncs.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestRowCol.java create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumproduct.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestTFunc.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java create mode 100755 src/testcases/org/apache/poi/hssf/record/formula/functions/TestXYNumericFunction.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestBug43093.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java (limited to 'src/testcases/org/apache') diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java new file mode 100644 index 0000000000..7419734535 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java @@ -0,0 +1,110 @@ +/* ==================================================================== + 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.hssf.model; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.model.FormulaParser.FormulaParseException; +import org.apache.poi.hssf.record.formula.FuncVarPtg; +import org.apache.poi.hssf.record.formula.NamePtg; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFName; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; + +/** + * Test the low level formula parser functionality, + * but using parts which need to use + * HSSFFormulaEvaluator. + */ +public final class TestFormulaParserEval extends TestCase { + + public void testWithNamedRange() throws Exception { + HSSFWorkbook workbook = new HSSFWorkbook(); + FormulaParser fp; + Ptg[] ptgs; + + HSSFSheet s = workbook.createSheet("Foo"); + s.createRow(0).createCell((short)0).setCellValue(1.1); + s.createRow(1).createCell((short)0).setCellValue(2.3); + s.createRow(2).createCell((short)2).setCellValue(3.1); + + HSSFName name = workbook.createName(); + name.setNameName("testName"); + name.setReference("A1:A2"); + + fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, "SUM(testName)"); + fp.parse(); + ptgs = fp.getRPNPtg(); + assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); + assertEquals(NamePtg.class, ptgs[0].getClass()); + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + + // Now make it a single cell + name.setReference("C3"); + + fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, "SUM(testName)"); + fp.parse(); + ptgs = fp.getRPNPtg(); + assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); + assertEquals(NamePtg.class, ptgs[0].getClass()); + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + + // And make it non-contiguous + name.setReference("A1:A2,C3"); + fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, "SUM(testName)"); + fp.parse(); + ptgs = fp.getRPNPtg(); + assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); + assertEquals(NamePtg.class, ptgs[0].getClass()); + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + } + + public void testEvaluateFormulaWithRowBeyond32768_Bug44539() { + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + wb.setSheetName(0, "Sheet1"); + + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell((short)0); + cell.setCellFormula("SUM(A32769:A32770)"); + + // put some values in the cells to make the evaluation more interesting + sheet.createRow(32768).createCell((short)0).setCellValue(31); + sheet.createRow(32769).createCell((short)0).setCellValue(11); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + CellValue result; + try { + result = fe.evaluate(cell); + } catch (FormulaParseException e) { + if(e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { + fail("Identifed bug 44539"); + } + throw new RuntimeException(e); + } + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, result.getCellType()); + assertEquals(42.0, result.getNumberValue(), 0.0); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java new file mode 100755 index 0000000000..5098c789a7 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/AllFormulaEvalTests.java @@ -0,0 +1,40 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import junit.framework.Test; +import junit.framework.TestSuite; + +/** + * Collects all tests the package org.apache.poi.hssf.record.formula.eval. + * + * @author Josh Micich + */ +public class AllFormulaEvalTests { + + public static Test suite() { + TestSuite result = new TestSuite("Tests for org.apache.poi.hssf.record.formula.eval"); + result.addTestSuite(TestCircularReferences.class); + result.addTestSuite(TestExternalFunction.class); + result.addTestSuite(TestFormulaBugs.class); + result.addTestSuite(TestFormulasFromSpreadsheet.class); + result.addTestSuite(TestPercentEval.class); + result.addTestSuite(TestUnaryPlusEval.class); + return result; + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java new file mode 100755 index 0000000000..72db658f77 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java @@ -0,0 +1,125 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +/** + * Tests HSSFFormulaEvaluator for its handling of cell formula circular references. + * + * @author Josh Micich + */ +public final class TestCircularReferences extends TestCase { + /** + * Translates StackOverflowError into AssertionFailedError + */ + private static CellValue evaluateWithCycles(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row, HSSFCell testCell) + throws AssertionFailedError { + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); + evaluator.setCurrentRow(row); + try { + return evaluator.evaluate(testCell); + } catch (StackOverflowError e) { + throw new AssertionFailedError( "circular reference caused stack overflow error"); + } + } + /** + * Makes sure that the specified evaluated cell value represents a circular reference error. + */ + private static void confirmCycleErrorCode(CellValue cellValue) { + assertTrue(cellValue.getCellType() == HSSFCell.CELL_TYPE_ERROR); + assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cellValue.getErrorValue()); + } + + + /** + * ASF Bugzilla Bug 44413 + * "INDEX() formula cannot contain its own location in the data array range" + */ + public void testIndexFormula() { + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + + short colB = 1; + sheet.createRow(0).createCell(colB).setCellValue(1); + sheet.createRow(1).createCell(colB).setCellValue(2); + sheet.createRow(2).createCell(colB).setCellValue(3); + HSSFRow row4 = sheet.createRow(3); + HSSFCell testCell = row4.createCell((short)0); + // This formula should evaluate to the contents of B2, + testCell.setCellFormula("INDEX(A1:B4,2,2)"); + // However the range A1:B4 also includes the current cell A4. If the other parameters + // were 4 and 1, this would represent a circular reference. Since POI 'fully' evaluates + // arguments before invoking operators, POI must handle such potential cycles gracefully. + + + CellValue cellValue = evaluateWithCycles(wb, sheet, row4, testCell); + + assertTrue(cellValue.getCellType() == HSSFCell.CELL_TYPE_NUMERIC); + assertEquals(2, cellValue.getNumberValue(), 0); + } + + /** + * Cell A1 has formula "=A1" + */ + public void testSimpleCircularReference() { + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + + HSSFRow row = sheet.createRow(0); + HSSFCell testCell = row.createCell((short)0); + testCell.setCellFormula("A1"); + + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); + evaluator.setCurrentRow(row); + CellValue cellValue = evaluateWithCycles(wb, sheet, row, testCell); + + confirmCycleErrorCode(cellValue); + } + + /** + * A1=B1, B1=C1, C1=D1, D1=A1 + */ + public void testMultiLevelCircularReference() { + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + + HSSFRow row = sheet.createRow(0); + row.createCell((short)0).setCellFormula("B1"); + row.createCell((short)1).setCellFormula("C1"); + row.createCell((short)2).setCellFormula("D1"); + HSSFCell testCell = row.createCell((short)3); + testCell.setCellFormula("A1"); + + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); + evaluator.setCurrentRow(row); + CellValue cellValue = evaluateWithCycles(wb, sheet, row, testCell); + + confirmCycleErrorCode(cellValue); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java new file mode 100755 index 0000000000..27e3338652 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java @@ -0,0 +1,61 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFName; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +/** + * + * @author Josh Micich + */ +public final class TestExternalFunction extends TestCase { + + /** + * Checks that an external function can get invoked from the formula evaluator. + */ + public void testInvoke() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + wb.setSheetName(0, "Sheet1"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell((short)0); + + HSSFName hssfName = wb.createName(); + hssfName.setNameName("myFunc"); + + cell.setCellFormula("myFunc()"); + String actualFormula=cell.getCellFormula(); + assertEquals("myFunc()", actualFormula); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + CellValue evalResult = fe.evaluate(cell); + + // Check the return value from ExternalFunction.evaluate() + // TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better + assertEquals(HSSFCell.CELL_TYPE_ERROR, evalResult.getCellType()); + assertEquals(ErrorEval.FUNCTION_NOT_IMPLEMENTED.getErrorCode(), evalResult.getErrorValue()); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java new file mode 100755 index 0000000000..617f5d0d4e --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java @@ -0,0 +1,217 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import java.io.File; +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; + +/** + * Miscellaneous tests for bugzilla entries.

The test name contains the + * bugzilla bug id. + * + * + * @author Josh Micich + */ +public final class TestFormulaBugs extends TestCase { + + private static final String TEST_DATA_DIR_SYS_PROPERTY_NAME = "HSSF.testdata.path"; + + /** + * Opens a sample file from the standard HSSF test data directory + * + * @return an open InputStream for the specified sample file + */ + private static InputStream openSampleFileStream(String sampleFileName) { + // TODO - move this method somewhere common + String dataDirName = System + .getProperty(TEST_DATA_DIR_SYS_PROPERTY_NAME); + if (dataDirName == null) { + throw new RuntimeException("Must set system property '" + + TEST_DATA_DIR_SYS_PROPERTY_NAME + + "' before running tests"); + } + File dataDir = new File(dataDirName); + if (!dataDir.exists()) { + throw new RuntimeException("Data dir '" + dataDirName + + "' specified by system property '" + + TEST_DATA_DIR_SYS_PROPERTY_NAME + "' does not exist"); + } + File f = new File(dataDir, sampleFileName); + if (!f.exists()) { + throw new RuntimeException("Sample file '" + sampleFileName + + "' not found in data dir '" + dataDirName + "'"); + } + InputStream is; + try { + is = new FileInputStream(f); + } catch (FileNotFoundException e) { + throw new RuntimeException(e); + } + return is; + } + + /** + * Bug 27349 - VLOOKUP with reference to another sheet.

This test was + * added long after the relevant functionality was fixed. + */ + public void test27349() { + // 27349-vlookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622 + InputStream is = openSampleFileStream("27349-vlookupAcrossSheets.xls"); + HSSFWorkbook wb; + try { + // original bug may have thrown exception here, or output warning to + // stderr + wb = new HSSFWorkbook(is); + } catch (IOException e) { + throw new RuntimeException(e); + } + + HSSFSheet sheet = wb.getSheetAt(0); + HSSFRow row = sheet.getRow(1); + HSSFCell cell = row.getCell(0); + + // this definitely would have failed due to 27349 + assertEquals("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell + .getCellFormula()); + + // We might as well evaluate the formula + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + CellValue cv = fe.evaluate(cell); + + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); + assertEquals(3.0, cv.getNumberValue(), 0.0); + } + + /** + * Bug 27405 - isnumber() formula always evaluates to false in if statement

+ * + * seems to be a duplicate of 24925 + */ + public void test27405() { + + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("input"); + // input row 0 + HSSFRow row = sheet.createRow((short) 0); + HSSFCell cell = row.createCell((short) 0); + cell = row.createCell((short) 1); + cell.setCellValue(1); // B1 + // input row 1 + row = sheet.createRow((short) 1); + cell = row.createCell((short) 1); + cell.setCellValue(999); // B2 + + int rno = 4; + row = sheet.createRow(rno); + cell = row.createCell((short) 1); // B5 + cell.setCellFormula("isnumber(b1)"); + cell = row.createCell((short) 3); // D5 + cell.setCellFormula("IF(ISNUMBER(b1),b1,b2)"); + + if (false) { // set true to check excel file manually + // bug report mentions 'Editing the formula in excel "fixes" the problem.' + try { + FileOutputStream fileOut = new FileOutputStream("27405output.xls"); + wb.write(fileOut); + fileOut.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + } + + // use POI's evaluator as an extra sanity check + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + CellValue cv; + cv = fe.evaluate(cell); + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); + assertEquals(1.0, cv.getNumberValue(), 0.0); + + cv = fe.evaluate(row.getCell(1)); + assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cv.getCellType()); + assertEquals(true, cv.getBooleanValue()); + } + + /** + * Bug 42448 - Can't parse SUMPRODUCT(A!C7:A!C67, B8:B68) / B69

+ */ + public void test42448() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet("Sheet1"); + + HSSFRow row = sheet1.createRow(0); + HSSFCell cell = row.createCell((short) 0); + + // it's important to create the referenced sheet first + HSSFSheet sheet2 = wb.createSheet("A"); // note name 'A' + // TODO - POI crashes if the formula is added before this sheet + // RuntimeException("Zero length string is an invalid sheet name") + // Excel doesn't crash but the formula doesn't work until it is + // re-entered + + String inputFormula = "SUMPRODUCT(A!C7:A!C67, B8:B68) / B69"; // as per bug report + try { + cell.setCellFormula(inputFormula); + } catch (StringIndexOutOfBoundsException e) { + throw new AssertionFailedError("Identified bug 42448"); + } + + assertEquals("SUMPRODUCT(A!C7:C67,B8:B68)/B69", cell.getCellFormula()); + + // might as well evaluate the sucker... + + addCell(sheet2, 5, 2, 3.0); // A!C6 + addCell(sheet2, 6, 2, 4.0); // A!C7 + addCell(sheet2, 66, 2, 5.0); // A!C67 + addCell(sheet2, 67, 2, 6.0); // A!C68 + + addCell(sheet1, 6, 1, 7.0); // B7 + addCell(sheet1, 7, 1, 8.0); // B8 + addCell(sheet1, 67, 1, 9.0); // B68 + addCell(sheet1, 68, 1, 10.0); // B69 + + double expectedResult = (4.0 * 8.0 + 5.0 * 9.0) / 10.0; + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb); + fe.setCurrentRow(row); + CellValue cv = fe.evaluate(cell); + + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); + assertEquals(expectedResult, cv.getNumberValue(), 0.0); + } + + private static void addCell(HSSFSheet sheet, int rowIx, int colIx, + double value) { + sheet.createRow(rowIx).createCell((short) colIx).setCellValue(value); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java new file mode 100644 index 0000000000..2d5408c76a --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java @@ -0,0 +1,328 @@ +/* +* 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.hssf.record.formula.eval; + +import java.io.FileInputStream; +import java.io.PrintStream; + +import junit.framework.Assert; +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.functions.TestMathX; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; + +/** + * Tests formulas and operators as loaded from a test data spreadsheet.

+ * This class does not test implementors of Function and OperationEval in + * isolation. Much of the evaluation engine (i.e. HSSFFormulaEvaluator, ...) gets + * exercised as well. Tests for bug fixes and specific/tricky behaviour can be found in the + * corresponding test class (TestXxxx) of the target (Xxxx) implementor, + * where execution can be observed more easily. + * + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + */ +public final class TestFormulasFromSpreadsheet extends TestCase { + + private static final class Result { + public static final int SOME_EVALUATIONS_FAILED = -1; + public static final int ALL_EVALUATIONS_SUCCEEDED = +1; + public static final int NO_EVALUATIONS_FOUND = 0; + } + + /** + * This class defines constants for navigating around the test data spreadsheet used for these tests. + */ + private static final class SS { + + /** + * Name of the test spreadsheet (found in the standard test data folder) + */ + public final static String FILENAME = "FormulaEvalTestData.xls"; + /** + * Row (zero-based) in the test spreadsheet where the operator examples start. + */ + public static final int START_OPERATORS_ROW_INDEX = 22; // Row '23' + /** + * Row (zero-based) in the test spreadsheet where the function examples start. + */ + public static final int START_FUNCTIONS_ROW_INDEX = 87; // Row '88' + /** + * Index of the column that contains the function names + */ + public static final short COLUMN_INDEX_FUNCTION_NAME = 1; // Column 'B' + + /** + * Used to indicate when there are no more functions left + */ + public static final String FUNCTION_NAMES_END_SENTINEL = ""; + + /** + * Index of the column where the test values start (for each function) + */ + public static final short COLUMN_INDEX_FIRST_TEST_VALUE = 3; // Column 'D' + + /** + * Each function takes 4 rows in the test spreadsheet + */ + public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4; + } + + private HSSFWorkbook workbook; + private HSSFSheet sheet; + // Note - multiple failures are aggregated before ending. + // If one or more functions fail, a single AssertionFailedError is thrown at the end + private int _functionFailureCount; + private int _functionSuccessCount; + private int _evaluationFailureCount; + private int _evaluationSuccessCount; + + private static final HSSFCell getExpectedValueCell(HSSFRow row, short columnIndex) { + if (row == null) { + return null; + } + return row.getCell(columnIndex); + } + + + private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { + if (expected == null) { + throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); + } + if(actual == null) { + throw new AssertionFailedError(msg + " - actual value was null"); + } + + if (expected.getCellType() == HSSFCell.CELL_TYPE_STRING) { + String value = expected.getRichStringCellValue().getString(); + if (value.startsWith("#")) { + // TODO - this code never called + expected.setCellType(HSSFCell.CELL_TYPE_ERROR); + // expected.setCellErrorValue(...?); + } + } + + switch (expected.getCellType()) { + case HSSFCell.CELL_TYPE_BLANK: + assertEquals(msg, HSSFCell.CELL_TYPE_BLANK, actual.getCellType()); + break; + case HSSFCell.CELL_TYPE_BOOLEAN: + assertEquals(msg, HSSFCell.CELL_TYPE_BOOLEAN, actual.getCellType()); + assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); + break; + case HSSFCell.CELL_TYPE_ERROR: + assertEquals(msg, HSSFCell.CELL_TYPE_ERROR, actual.getCellType()); + if(false) { // TODO: fix ~45 functions which are currently returning incorrect error values + assertEquals(msg, expected.getErrorCellValue(), actual.getErrorValue()); + } + break; + case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation + throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); + case HSSFCell.CELL_TYPE_NUMERIC: + assertEquals(msg, HSSFCell.CELL_TYPE_NUMERIC, actual.getCellType()); + TestMathX.assertEquals(msg, expected.getNumericCellValue(), actual.getNumberValue(), TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR); +// double delta = Math.abs(expected.getNumericCellValue()-actual.getNumberValue()); +// double pctExpected = Math.abs(0.00001*expected.getNumericCellValue()); +// assertTrue(msg, delta <= pctExpected); + break; + case HSSFCell.CELL_TYPE_STRING: + assertEquals(msg, HSSFCell.CELL_TYPE_STRING, actual.getCellType()); + assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); + break; + } + } + + + protected void setUp() throws Exception { + if (workbook == null) { + String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME; + FileInputStream fin = new FileInputStream( filePath ); + workbook = new HSSFWorkbook( fin ); + sheet = workbook.getSheetAt( 0 ); + } + _functionFailureCount = 0; + _functionSuccessCount = 0; + _evaluationFailureCount = 0; + _evaluationSuccessCount = 0; + } + + public void testFunctionsFromTestSpreadsheet() { + + processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, null); + processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, null); + // example for debugging individual functions/operators: +// processFunctionGroup(SS.START_OPERATORS_ROW_INDEX, "ConcatEval"); +// processFunctionGroup(SS.START_FUNCTIONS_ROW_INDEX, "AVERAGE"); + + // confirm results + String successMsg = "There were " + + _evaluationSuccessCount + " successful evaluation(s) and " + + _functionSuccessCount + " function(s) without error"; + if(_functionFailureCount > 0) { + String msg = _functionFailureCount + " function(s) failed in " + + _evaluationFailureCount + " evaluation(s). " + successMsg; + throw new AssertionFailedError(msg); + } + if(false) { // normally no output for successful tests + System.out.println(getClass().getName() + ": " + successMsg); + } + } + + /** + * @param startRowIndex row index in the spreadsheet where the first function/operator is found + * @param testFocusFunctionName name of a single function/operator to test alone. + * Typically pass null to test all functions + */ + private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) { + + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); + + int rowIndex = startRowIndex; + while (true) { + HSSFRow r = sheet.getRow(rowIndex); + String targetFunctionName = getTargetFunctionName(r); + if(targetFunctionName == null) { + throw new AssertionFailedError("Test spreadsheet cell empty on row (" + + (rowIndex+1) + "). Expected function name or '" + + SS.FUNCTION_NAMES_END_SENTINEL + "'"); + } + if(targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) { + // found end of functions list + break; + } + if(testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) { + + // expected results are on the row below + HSSFRow expectedValuesRow = sheet.getRow(rowIndex + 1); + if(expectedValuesRow == null) { + int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row + throw new AssertionFailedError("Missing expected values row for function '" + + targetFunctionName + " (row " + missingRowNum + ")"); + } + switch(processFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow)) { + case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; + case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; + default: + throw new RuntimeException("unexpected result"); + case Result.NO_EVALUATIONS_FOUND: // do nothing + } + } + rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION; + } + } + + /** + * + * @return a constant from the local Result class denoting whether there were any evaluation + * cases, and whether they all succeeded. + */ + private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, + HSSFRow formulasRow, HSSFRow expectedValuesRow) { + + int result = Result.NO_EVALUATIONS_FOUND; // so far + short endcolnum = formulasRow.getLastCellNum(); + evaluator.setCurrentRow(formulasRow); + + // iterate across the row for all the evaluation cases + for (short colnum=SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++) { + HSSFCell c = formulasRow.getCell(colnum); + if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) { + continue; + } + + HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(c); + + HSSFCell expectedValueCell = getExpectedValueCell(expectedValuesRow, colnum); + try { + confirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.getCellFormula() + " @ " + formulasRow.getRowNum() + ":" + colnum, + expectedValueCell, actualValue); + _evaluationSuccessCount ++; + if(result != Result.SOME_EVALUATIONS_FAILED) { + result = Result.ALL_EVALUATIONS_SUCCEEDED; + } + } catch (AssertionFailedError e) { + _evaluationFailureCount ++; + printShortStackTrace(System.err, e); + result = Result.SOME_EVALUATIONS_FAILED; + } + } + return result; + } + + /** + * Useful to keep output concise when expecting many failures to be reported by this test case + */ + private static void printShortStackTrace(PrintStream ps, AssertionFailedError e) { + StackTraceElement[] stes = e.getStackTrace(); + + int startIx = 0; + // skip any top frames inside junit.framework.Assert + while(startIx= endIx) { + // something went wrong. just print the whole stack trace + e.printStackTrace(ps); + } + endIx -= 4; // skip 4 frames of reflection invocation + ps.println(e.toString()); + for(int i=startIx; inull if cell is missing, empty or blank + */ + private static String getTargetFunctionName(HSSFRow r) { + if(r == null) { + System.err.println("Warning - given null row, can't figure out function name"); + return null; + } + HSSFCell cell = r.getCell(SS.COLUMN_INDEX_FUNCTION_NAME); + if(cell == null) { + System.err.println("Warning - Row " + r.getRowNum() + " has no cell " + SS.COLUMN_INDEX_FUNCTION_NAME + ", can't figure out function name"); + return null; + } + if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { + return null; + } + if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { + return cell.getRichStringCellValue().getString(); + } + + throw new AssertionFailedError("Bad cell type for 'function name' column: (" + + cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")"); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java new file mode 100755 index 0000000000..be8cef13fa --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java @@ -0,0 +1,82 @@ +/* ==================================================================== + 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.hssf.record.formula.eval; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.PercentPtg; +import org.apache.poi.hssf.record.formula.functions.NumericFunctionInvoker; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; + +/** + * Test for percent operator evaluator. + * + * @author Josh Micich + */ +public final class TestPercentEval extends TestCase { + + private static void confirm(ValueEval arg, double expectedResult) { + Eval[] args = { + arg, + }; + + PercentEval opEval = new PercentEval(new PercentPtg()); + double result = NumericFunctionInvoker.invoke(opEval, args, -1, (short)-1); + + assertEquals(expectedResult, result, 0); + } + + public void testBasic() { + confirm(new NumberEval(5), 0.05); + confirm(new NumberEval(3000), 30.0); + confirm(new NumberEval(-150), -1.5); + confirm(new StringEval("0.2"), 0.002); + confirm(BoolEval.TRUE, 0.01); + } + + public void testInSpreadSheet() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell((short)0); + cell.setCellFormula("B1%"); + row.createCell((short)1).setCellValue(50.0); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + CellValue cv; + try { + cv = fe.evaluate(cell); + } catch (RuntimeException e) { + if(e.getCause() instanceof NullPointerException) { + throw new AssertionFailedError("Identified bug 44608"); + } + // else some other unexpected error + throw e; + } + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType()); + assertEquals(0.5, cv.getNumberValue(), 0.0); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java new file mode 100755 index 0000000000..724c54cd90 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestUnaryPlusEval.java @@ -0,0 +1,61 @@ +/* +* 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.hssf.record.formula.eval; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.UnaryPlusPtg; +import org.apache.poi.hssf.record.formula.functions.NumericFunctionInvoker; + +import junit.framework.TestCase; + +/** + * Test for unary plus operator evaluator. + * + * @author Josh Micich + */ +public final class TestUnaryPlusEval extends TestCase { + + /** + * Test for bug observable at svn revision 618865 (5-Feb-2008)
+ * The code for handling column operands had been copy-pasted from the row handling code. + */ + public void testColumnOperand() { + + short firstRow = (short)8; + short lastRow = (short)12; + short colNum = (short)5; + AreaPtg areaPtg = new AreaPtg(firstRow, lastRow, colNum, colNum, false, false, false, false); + ValueEval[] values = { + new NumberEval(27), + new NumberEval(29), + new NumberEval(35), // value in row 10 + new NumberEval(37), + new NumberEval(38), + }; + Eval areaEval = new Area2DEval(areaPtg, values); + Eval[] args = { + areaEval, + }; + + double result = NumericFunctionInvoker.invoke(new UnaryPlusEval(new UnaryPlusPtg()), args, 10, (short)20); + + assertEquals(35, result, 0); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/AbstractNumericTestCase.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/AbstractNumericTestCase.java new file mode 100644 index 0000000000..ef5b3add07 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/AbstractNumericTestCase.java @@ -0,0 +1,73 @@ +/* +* 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. +*/ +/* + * Created on May 29, 2005 + * + */ +package org.apache.poi.hssf.record.formula.functions; + +import junit.framework.TestCase; + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public abstract class AbstractNumericTestCase extends TestCase { + + public static final double POS_ZERO = 1E-4; + public static final double DIFF_TOLERANCE_FACTOR = 1E-8; + + public void setUp() { + } + + public void tearDown() { + } + + /** + * Why doesnt JUnit have a method like this for doubles? + * The current impl (3.8.1) of Junit has a retar*** method + * for comparing doubles. DO NOT use that. + * TODO: This class should really be in an abstract super class + * to avoid code duplication across this project. + * @param message + * @param baseval + * @param checkval + */ + public static void assertEquals(String message, double baseval, double checkval, double almostZero, double diffToleranceFactor) { + double posZero = Math.abs(almostZero); + double negZero = -1 * posZero; + if (Double.isNaN(baseval)) { + assertTrue(message+": Expected " + baseval + " but was " + checkval + , Double.isNaN(baseval)); + } + else if (Double.isInfinite(baseval)) { + assertTrue(message+": Expected " + baseval + " but was " + checkval + , Double.isInfinite(baseval) && ((baseval<0) == (checkval<0))); + } + else { + assertTrue(message+": Expected " + baseval + " but was " + checkval + ,baseval != 0 + ? Math.abs(baseval - checkval) <= Math.abs(diffToleranceFactor * baseval) + : checkval < posZero && checkval > negZero); + } + } + + public static void assertEquals(String msg, double baseval, double checkval) { + assertEquals(msg, baseval, checkval, POS_ZERO, DIFF_TOLERANCE_FACTOR); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java new file mode 100755 index 0000000000..66d2a1d270 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java @@ -0,0 +1,53 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.Test; +import junit.framework.TestSuite; + +/** + * Direct tests for all implementors of Function. + * + * @author Josh Micich + */ +public final class AllIndividualFunctionEvaluationTests { + + // TODO - have this suite incorporated into a higher level one + public static Test suite() { + TestSuite result = new TestSuite("Tests for org.apache.poi.hssf.record.formula.functions"); + result.addTestSuite(TestAverage.class); + result.addTestSuite(TestCountFuncs.class); + result.addTestSuite(TestDate.class); + result.addTestSuite(TestFinanceLib.class); + result.addTestSuite(TestIndex.class); + result.addTestSuite(TestIsBlank.class); + result.addTestSuite(TestLen.class); + result.addTestSuite(TestMid.class); + result.addTestSuite(TestMathX.class); + result.addTestSuite(TestMatch.class); + result.addTestSuite(TestPmt.class); + result.addTestSuite(TestOffset.class); + result.addTestSuite(TestRowCol.class); + result.addTestSuite(TestSumproduct.class); + result.addTestSuite(TestStatsLib.class); + result.addTestSuite(TestTFunc.class); + result.addTestSuite(TestTrim.class); + result.addTestSuite(TestXYNumericFunction.class); + return result; + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java new file mode 100755 index 0000000000..a6e262b868 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/EvalFactory.java @@ -0,0 +1,63 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.Ref2DEval; +import org.apache.poi.hssf.record.formula.eval.RefEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +/** + * Test helper class for creating mock Eval objects + * + * @author Josh Micich + */ +final class EvalFactory { + private static final NumberEval ZERO = new NumberEval(0); + + private EvalFactory() { + // no instances of this class + } + + /** + * Creates a dummy AreaEval (filled with zeros) + *

+ * nCols and nRows could have been derived + */ + public static AreaEval createAreaEval(String areaRefStr, int nCols, int nRows) { + int nValues = nCols * nRows; + ValueEval[] values = new ValueEval[nValues]; + for (int i = 0; i < nValues; i++) { + values[i] = ZERO; + } + + return new Area2DEval(new AreaPtg(areaRefStr), values); + } + + /** + * Creates a single RefEval (with value zero) + */ + public static RefEval createRefEval(String refStr) { + return new Ref2DEval(new ReferencePtg(refStr), ZERO); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java new file mode 100755 index 0000000000..d477231349 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/NumericFunctionInvoker.java @@ -0,0 +1,126 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.AssertionFailedError; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumericValueEval; +import org.apache.poi.hssf.record.formula.eval.OperationEval; + +/** + * Test helper class for invoking functions with numeric results. + * + * @author Josh Micich + */ +public final class NumericFunctionInvoker { + + private NumericFunctionInvoker() { + // no instances of this class + } + + private static final class NumericEvalEx extends Exception { + public NumericEvalEx(String msg) { + super(msg); + } + } + + /** + * Invokes the specified function with the arguments. + *

+ * Assumes that the cell coordinate parameters of + * Function.evaluate(args, srcCellRow, srcCellCol) + * are not required. + *

+ * This method cannot be used for confirming error return codes. Any non-numeric evaluation + * result causes the current junit test to fail. + */ + public static double invoke(Function f, Eval[] args) { + try { + return invokeInternal(f, args, -1, -1); + } catch (NumericEvalEx e) { + throw new AssertionFailedError("Evaluation of function (" + f.getClass().getName() + + ") failed: " + e.getMessage()); + } + + } + /** + * Invokes the specified operator with the arguments. + *

+ * This method cannot be used for confirming error return codes. Any non-numeric evaluation + * result causes the current junit test to fail. + */ + public static double invoke(OperationEval f, Eval[] args, int srcCellRow, int srcCellCol) { + try { + return invokeInternal(f, args, srcCellRow, srcCellCol); + } catch (NumericEvalEx e) { + throw new AssertionFailedError("Evaluation of function (" + f.getClass().getName() + + ") failed: " + e.getMessage()); + } + + } + /** + * Formats nicer error messages for the junit output + */ + private static double invokeInternal(Object target, Eval[] args, int srcCellRow, int srcCellCol) + throws NumericEvalEx { + Eval evalResult; + // TODO - make OperationEval extend Function + if (target instanceof Function) { + Function ff = (Function) target; + evalResult = ff.evaluate(args, srcCellRow, (short)srcCellCol); + } else { + OperationEval ff = (OperationEval) target; + evalResult = ff.evaluate(args, srcCellRow, (short)srcCellCol); + } + + if(evalResult == null) { + throw new NumericEvalEx("Result object was null"); + } + if(evalResult instanceof ErrorEval) { + ErrorEval ee = (ErrorEval) evalResult; + throw new NumericEvalEx(formatErrorMessage(ee)); + } + if(!(evalResult instanceof NumericValueEval)) { + throw new NumericEvalEx("Result object type (" + evalResult.getClass().getName() + + ") is invalid. Expected implementor of (" + + NumericValueEval.class.getName() + ")"); + } + + NumericValueEval result = (NumericValueEval) evalResult; + return result.getNumberValue(); + } + private static String formatErrorMessage(ErrorEval ee) { + if(errorCodesAreEqual(ee, ErrorEval.FUNCTION_NOT_IMPLEMENTED)) { + return "Function not implemented"; + } + if(errorCodesAreEqual(ee, ErrorEval.VALUE_INVALID)) { + return "Error code: #VALUE! (invalid value)"; + } + return "Error code=" + ee.getErrorCode(); + } + private static boolean errorCodesAreEqual(ErrorEval a, ErrorEval b) { + if(a==b) { + return true; + } + return a.getErrorCode() == b.getErrorCode(); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java new file mode 100755 index 0000000000..4f0e5fff2c --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestAverage.java @@ -0,0 +1,103 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +/** + * Tests for Excel function AVERAGE() + * + * @author Josh Micich + */ +public final class TestAverage extends TestCase { + + + private static Eval invokeAverage(Eval[] args) { + return new Average().evaluate(args, -1, (short)-1); + } + + private void confirmAverage(Eval[] args, double expected) { + Eval result = invokeAverage(args); + assertEquals(NumberEval.class, result.getClass()); + assertEquals(expected, ((NumberEval)result).getNumberValue(), 0); + } + + private void confirmAverage(Eval[] args, ErrorEval expectedError) { + Eval result = invokeAverage(args); + assertEquals(ErrorEval.class, result.getClass()); + assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode()); + } + + public void testBasic() { + + ValueEval[] values = { + new NumberEval(1), + new NumberEval(2), + new NumberEval(3), + new NumberEval(4), + }; + + confirmAverage(values, 2.5); + + values = new ValueEval[] { + new NumberEval(1), + new NumberEval(2), + BlankEval.INSTANCE, + new NumberEval(3), + BlankEval.INSTANCE, + new NumberEval(4), + BlankEval.INSTANCE, + }; + + confirmAverage(values, 2.5); + } + + /** + * Valid cases where values are not pure numbers + */ + public void testUnusualArgs() { + ValueEval[] values = { + new NumberEval(1), + new NumberEval(2), + BoolEval.TRUE, + BoolEval.FALSE, + }; + + confirmAverage(values, 1.0); + + } + + // currently disabled because MultiOperandNumericFunction.getNumberArray(Eval[], int, short) + // does not handle error values properly yet + public void XtestErrors() { + ValueEval[] values = { + new NumberEval(1), + ErrorEval.NAME_INVALID, + new NumberEval(3), + ErrorEval.DIV_ZERO, + }; + confirmAverage(values, ErrorEval.NAME_INVALID); + + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java new file mode 100755 index 0000000000..ae93a2d41b --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java @@ -0,0 +1,150 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.Ref2DEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +/** + * Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK() + * + * @author Josh Micich + */ +public final class TestCountFuncs extends TestCase { + + public TestCountFuncs(String testName) { + super(testName); + } + + public void testCountA() { + + Eval[] args; + + args = new Eval[] { + new NumberEval(0), + }; + confirmCountA(1, args); + + args = new Eval[] { + new NumberEval(0), + new NumberEval(0), + new StringEval(""), + }; + confirmCountA(3, args); + + args = new Eval[] { + EvalFactory.createAreaEval("D2:F5", 3, 4), + }; + confirmCountA(12, args); + + args = new Eval[] { + EvalFactory.createAreaEval("D1:F5", 3, 5), // 15 + EvalFactory.createRefEval("A1"), + EvalFactory.createAreaEval("A1:F6", 7, 6), // 42 + new NumberEval(0), + }; + confirmCountA(59, args); + } + + public void testCountIf() { + + AreaEval range; + ValueEval[] values; + + // when criteria is a boolean value + values = new ValueEval[] { + new NumberEval(0), + new StringEval("TRUE"), // note - does not match boolean TRUE + BoolEval.TRUE, + BoolEval.FALSE, + BoolEval.TRUE, + BlankEval.INSTANCE, + }; + range = createAreaEval("A1:B2", values); + confirmCountIf(2, range, BoolEval.TRUE); + + // when criteria is numeric + values = new ValueEval[] { + new NumberEval(0), + new StringEval("2"), + new StringEval("2.001"), + new NumberEval(2), + new NumberEval(2), + BoolEval.TRUE, + BlankEval.INSTANCE, + }; + range = createAreaEval("A1:B2", values); + confirmCountIf(3, range, new NumberEval(2)); + // note - same results when criteria is a string that parses as the number with the same value + confirmCountIf(3, range, new StringEval("2.00")); + + if (false) { // not supported yet: + // when criteria is an expression (starting with a comparison operator) + confirmCountIf(4, range, new StringEval(">1")); + } + } + /** + * special case where the criteria argument is a cell reference + */ + public void testCountIfWithCriteriaReference() { + + ValueEval[] values = { + new NumberEval(22), + new NumberEval(25), + new NumberEval(21), + new NumberEval(25), + new NumberEval(25), + new NumberEval(25), + }; + Area2DEval arg0 = new Area2DEval(new AreaPtg("C1:C6"), values); + + Ref2DEval criteriaArg = new Ref2DEval(new ReferencePtg("A1"), new NumberEval(25)); + Eval[] args= { arg0, criteriaArg, }; + + double actual = NumericFunctionInvoker.invoke(new Countif(), args); + assertEquals(4, actual, 0D); + } + + + private static AreaEval createAreaEval(String areaRefStr, ValueEval[] values) { + return new Area2DEval(new AreaPtg(areaRefStr), values); + } + + private static void confirmCountA(int expected, Eval[] args) { + double result = NumericFunctionInvoker.invoke(new Counta(), args); + assertEquals(expected, result, 0); + } + private static void confirmCountIf(int expected, AreaEval range, Eval criteria) { + + Eval[] args = { range, criteria, }; + double result = NumericFunctionInvoker.invoke(new Countif(), args); + assertEquals(expected, result, 0); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java new file mode 100644 index 0000000000..2c5e152ab8 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java @@ -0,0 +1,100 @@ +/* + * Created on Sep 11, 2007 + * + * The Copyright statements and Licenses for the commons application may be + * found in the file LICENSE.txt + */ + +package org.apache.poi.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; + +/** + * @author Pavel Krupets (pkrupets at palmtreebusiness dot com) + */ +public class TestDate extends TestCase { + public void setUp() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("new sheet"); + HSSFRow row1 = sheet.createRow((short) 0); + + this.cell11 = row1.createCell((short) 0); + + this.evaluator = new HSSFFormulaEvaluator(sheet, wb); + this.evaluator.setCurrentRow(row1); + } + + /** + * Test disabled pending a fix in the formula parser + */ + public void DISABLEDtestSomeArgumentsMissing() throws Exception { + this.cell11.setCellFormula("DATE(, 1, 0)"); + assertEquals(0.0, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(, 1, 1)"); + assertEquals(1.0, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + } + + public void testValid() throws Exception { + this.cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA); + + this.cell11.setCellFormula("DATE(1900, 1, 1)"); + assertEquals(1, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 1, 32)"); + assertEquals(32, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 222, 1)"); + assertEquals(6727, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 2, 0)"); + assertEquals(31, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(2000, 1, 222)"); + assertEquals(36747.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(2007, 1, 1)"); + assertEquals(39083, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + } + + public void testBugDate() { + this.cell11.setCellFormula("DATE(1900, 2, 29)"); + assertEquals(60, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 2, 30)"); + assertEquals(61, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 1, 222)"); + assertEquals(222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 1, 2222)"); + assertEquals(2222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1900, 1, 22222)"); + assertEquals(22222, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + } + + public void testPartYears() { + this.cell11.setCellFormula("DATE(4, 1, 1)"); + assertEquals(1462.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(14, 1, 1)"); + assertEquals(5115.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(104, 1, 1)"); + assertEquals(37987.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + + this.cell11.setCellFormula("DATE(1004, 1, 1)"); + assertEquals(366705.00, this.evaluator.evaluate(this.cell11).getNumberValue(), 0); + } + + private HSSFCell cell11; + private HSSFFormulaEvaluator evaluator; +} + diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFinanceLib.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFinanceLib.java new file mode 100644 index 0000000000..4dfe1fbb52 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestFinanceLib.java @@ -0,0 +1,202 @@ +/* +* 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. +*/ +/* + * Created on May 23, 2005 + * + */ +package org.apache.poi.hssf.record.formula.functions; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public class TestFinanceLib extends AbstractNumericTestCase { + + public void testFv() { + double f, r, y, p, x; + int n; + boolean t = false; + + r = 0; n = 3; y = 2; p = 7; t = true; + f = FinanceLib.fv(r, n, y, p, t); + x = -13; + assertEquals("fv ", x, f); + + r = 1; n = 10; y = 100; p = 10000; t = false; + f = FinanceLib.fv(r, n, y, p, t); + x = -10342300; + assertEquals("fv ", x, f); + + r = 1; n = 10; y = 100; p = 10000; t = true; + f = FinanceLib.fv(r, n, y, p, t); + x = -10444600; + assertEquals("fv ", x, f); + + r = 2; n = 12; y = 120; p = 12000; t = false; + f = FinanceLib.fv(r, n, y, p, t); + x = -6409178400d; + assertEquals("fv ", x, f); + + r = 2; n = 12; y = 120; p = 12000; t = true; + f = FinanceLib.fv(r, n, y, p, t); + x = -6472951200d; + assertEquals("fv ", x, f); + + // cross tests with pv + r = 2.95; n = 13; y = 13000; p = -4406.78544294496; t = false; + f = FinanceLib.fv(r, n, y, p, t); + x = 333891.230010986; // as returned by excel + assertEquals("fv ", x, f); + + r = 2.95; n = 13; y = 13000; p = -17406.7852148156; t = true; + f = FinanceLib.fv(r, n, y, p, t); + x = 333891.230102539; // as returned by excel + assertEquals("fv ", x, f); + + } + public void testNpv() { + double r, v[], npv, x; + + r = 1; v = new double[]{100, 200, 300, 400}; + npv = FinanceLib.npv(r, v); + x = 162.5; + assertEquals("npv ", x, npv); + + r = 2.5; v = new double[]{1000, 666.66666, 333.33, 12.2768416}; + npv = FinanceLib.npv(r, v); + x = 347.99232604144827; + assertEquals("npv ", x, npv); + + r = 12.33333; v = new double[]{1000, 0, -900, -7777.5765}; + npv = FinanceLib.npv(r, v); + x = 74.3742433377061; + assertEquals("npv ", x, npv); + + r = 0.05; v = new double[]{200000, 300000.55, 400000, 1000000, 6000000, 7000000, -300000}; + npv = FinanceLib.npv(r, v); + x = 11342283.4233124; + assertEquals("npv ", x, npv); + } + public void testPmt() { + double f, r, y, p, x; + int n; + boolean t = false; + + r = 0; n = 3; p = 2; f = 7; t = true; + y = FinanceLib.pmt(r, n, p, f, t); + x = -3; + assertEquals("pmt ", x, y); + + // cross check with pv + r = 1; n = 10; p = -109.66796875; f = 10000; t = false; + y = FinanceLib.pmt(r, n, p, f, t); + x = 100; + assertEquals("pmt ", x, y); + + r = 1; n = 10; p = -209.5703125; f = 10000; t = true; + y = FinanceLib.pmt(r, n, p, f, t); + x = 100; + assertEquals("pmt ", x, y); + + // cross check with fv + r = 2; n = 12; f = -6409178400d; p = 12000; t = false; + y = FinanceLib.pmt(r, n, p, f, t); + x = 120; + assertEquals("pmt ", x, y); + + r = 2; n = 12; f = -6472951200d; p = 12000; t = true; + y = FinanceLib.pmt(r, n, p, f, t); + x = 120; + assertEquals("pmt ", x, y); + } + + public void testPv() { + double f, r, y, p, x; + int n; + boolean t = false; + + r = 0; n = 3; y = 2; f = 7; t = true; + f = FinanceLib.pv(r, n, y, f, t); + x = -13; + assertEquals("pv ", x, f); + + r = 1; n = 10; y = 100; f = 10000; t = false; + p = FinanceLib.pv(r, n, y, f, t); + x = -109.66796875; + assertEquals("pv ", x, p); + + r = 1; n = 10; y = 100; f = 10000; t = true; + p = FinanceLib.pv(r, n, y, f, t); + x = -209.5703125; + assertEquals("pv ", x, p); + + r = 2.95; n = 13; y = 13000; f = 333891.23; t = false; + p = FinanceLib.pv(r, n, y, f, t); + x = -4406.78544294496; + assertEquals("pv ", x, p); + + r = 2.95; n = 13; y = 13000; f = 333891.23; t = true; + p = FinanceLib.pv(r, n, y, f, t); + x = -17406.7852148156; + assertEquals("pv ", x, p); + + // cross tests with fv + r = 2; n = 12; y = 120; f = -6409178400d; t = false; + p = FinanceLib.pv(r, n, y, f, t); + x = 12000; + assertEquals("pv ", x, p); + + r = 2; n = 12; y = 120; f = -6472951200d; t = true; + p = FinanceLib.pv(r, n, y, f, t); + x = 12000; + assertEquals("pv ", x, p); + + } + + public void testNper() { + double f, r, y, p, x, n; + boolean t = false; + + r = 0; y = 7; p = 2; f = 3; t = false; + n = FinanceLib.nper(r, y, p, f, t); + x = -0.71428571429; // can you believe it? excel returns nper as a fraction!?? + assertEquals("nper ", x, n); + + // cross check with pv + r = 1; y = 100; p = -109.66796875; f = 10000; t = false; + n = FinanceLib.nper(r, y, p, f, t); + x = 10; + assertEquals("nper ", x, n); + + r = 1; y = 100; p = -209.5703125; f = 10000; t = true; + n = FinanceLib.nper(r, y, p, f, t); + x = 10; + assertEquals("nper ", x, n); + + // cross check with fv + r = 2; y = 120; f = -6409178400d; p = 12000; t = false; + n = FinanceLib.nper(r, y, p, f, t); + x = 12; + assertEquals("nper ", x, n); + + r = 2; y = 120; f = -6472951200d; p = 12000; t = true; + n = FinanceLib.nper(r, y, p, f, t); + x = 12; + assertEquals("nper ", x, n); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndex.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndex.java new file mode 100755 index 0000000000..902c4122ef --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndex.java @@ -0,0 +1,89 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +/** + * Tests for the INDEX() function + * + * @author Josh Micich + */ +public final class TestIndex extends TestCase { + + public TestIndex(String testName) { + super(testName); + } + + private static final double[] TEST_VALUES0 = { + 1, 2, + 3, 4, + 5, 6, + 7, 8, + 9, 10, + 11, 12, + 13, // excess array element. TODO - Area2DEval currently has no validation to ensure correct size of values array + }; + + /** + * For the case when the first argument to INDEX() is an area reference + */ + public void testEvaluateAreaReference() { + + double[] values = TEST_VALUES0; + confirmAreaEval("C1:D6", values, 4, 1, 7); + confirmAreaEval("C1:D6", values, 6, 2, 12); + confirmAreaEval("C1:D6", values, 3, -1, 5); + + // now treat same data as 3 columns, 4 rows + confirmAreaEval("C10:E13", values, 2, 2, 5); + confirmAreaEval("C10:E13", values, 4, -1, 10); + } + + /** + * @param areaRefString in Excel notation e.g. 'D2:E97' + * @param dValues array of evaluated values for the area reference + * @param rowNum 1-based + * @param colNum 1-based, pass -1 to signify argument not present + */ + private static void confirmAreaEval(String areaRefString, double[] dValues, + int rowNum, int colNum, double expectedResult) { + ValueEval[] values = new ValueEval[dValues.length]; + for (int i = 0; i < values.length; i++) { + values[i] = new NumberEval(dValues[i]); + } + Area2DEval arg0 = new Area2DEval(new AreaPtg(areaRefString), values); + + Eval[] args; + if (colNum > 0) { + args = new Eval[] { arg0, new NumberEval(rowNum), new NumberEval(colNum), }; + } else { + args = new Eval[] { arg0, new NumberEval(rowNum), }; + } + + double actual = NumericFunctionInvoker.invoke(new Index(), args); + assertEquals(expectedResult, actual, 0D); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java new file mode 100755 index 0000000000..7ce2bd245b --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java @@ -0,0 +1,62 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +/** + * Tests for Excel function ISBLANK() + * + * @author Josh Micich + */ +public final class TestIsBlank extends TestCase { + + + + public void test3DArea() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet1 = wb.createSheet(); + wb.setSheetName(0, "Sheet1"); + wb.createSheet(); + wb.setSheetName(1, "Sheet2"); + HSSFRow row = sheet1.createRow(0); + HSSFCell cell = row.createCell((short)0); + + + cell.setCellFormula("isblank(Sheet2!A1:A1)"); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb); + fe.setCurrentRow(row); + CellValue result = fe.evaluate(cell); + assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType()); + assertEquals(true, result.getBooleanValue()); + + cell.setCellFormula("isblank(D7:D7)"); + + result = fe.evaluate(cell); + assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType()); + assertEquals(true, result.getBooleanValue()); + + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java new file mode 100755 index 0000000000..a96fb4e2b0 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLen.java @@ -0,0 +1,73 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +/** + * Tests for Excel function LEN() + * + * @author Josh Micich + */ +public final class TestLen extends TestCase { + + + private static Eval invokeLen(Eval text) { + Eval[] args = new Eval[] { text, }; + return new Len().evaluate(args, -1, (short)-1); + } + + private void confirmLen(Eval text, int expected) { + Eval result = invokeLen(text); + assertEquals(NumberEval.class, result.getClass()); + assertEquals(expected, ((NumberEval)result).getNumberValue(), 0); + } + + private void confirmLen(Eval text, ErrorEval expectedError) { + Eval result = invokeLen(text); + assertEquals(ErrorEval.class, result.getClass()); + assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode()); + } + + public void testBasic() { + + confirmLen(new StringEval("galactic"), 8); + } + + /** + * Valid cases where text arg is not exactly a string + */ + public void testUnusualArgs() { + + // text (first) arg type is number, other args are strings with fractional digits + confirmLen(new NumberEval(123456), 6); + confirmLen(BoolEval.FALSE, 5); + confirmLen(BoolEval.TRUE, 4); + confirmLen(BlankEval.INSTANCE, 0); + } + + public void testErrors() { + confirmLen(ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java new file mode 100644 index 0000000000..071ca0f7d8 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java @@ -0,0 +1,385 @@ +/* +* 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.hssf.record.formula.functions; + +import java.io.FileInputStream; +import java.io.IOException; +import java.io.PrintStream; + +import junit.framework.Assert; +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +import org.apache.poi.hssf.util.CellReference; + +/** + * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.

+ * These tests have been separated from the common function and operator tests because the lookup + * functions have more complex test cases and test data setup. + * + * Tests for bug fixes and specific/tricky behaviour can be found in the corresponding test class + * (TestXxxx) of the target (Xxxx) implementor, where execution can be observed + * more easily. + * + * @author Josh Micich + */ +public final class TestLookupFunctionsFromSpreadsheet extends TestCase { + + private static final class Result { + public static final int SOME_EVALUATIONS_FAILED = -1; + public static final int ALL_EVALUATIONS_SUCCEEDED = +1; + public static final int NO_EVALUATIONS_FOUND = 0; + } + + /** + * This class defines constants for navigating around the test data spreadsheet used for these tests. + */ + private static final class SS { + + /** Name of the test spreadsheet (found in the standard test data folder) */ + public final static String FILENAME = "LookupFunctionsTestCaseData.xls"; + + /** Name of the first sheet in the spreadsheet (contains comments) */ + public final static String README_SHEET_NAME = "Read Me"; + + + /** Row (zero-based) in each sheet where the evaluation cases start. */ + public static final int START_TEST_CASES_ROW_INDEX = 4; // Row '5' + /** Index of the column that contains the function names */ + public static final short COLUMN_INDEX_MARKER = 0; // Column 'A' + public static final short COLUMN_INDEX_EVALUATION = 1; // Column 'B' + public static final short COLUMN_INDEX_EXPECTED_RESULT = 2; // Column 'C' + public static final short COLUMN_ROW_COMMENT = 3; // Column 'D' + + /** Used to indicate when there are no more test cases on the current sheet */ + public static final String TEST_CASES_END_MARKER = ""; + /** Used to indicate that the test on the current row should be ignored */ + public static final String SKIP_CURRENT_TEST_CASE_MARKER = ""; + + } + + // Note - multiple failures are aggregated before ending. + // If one or more functions fail, a single AssertionFailedError is thrown at the end + private int _sheetFailureCount; + private int _sheetSuccessCount; + private int _evaluationFailureCount; + private int _evaluationSuccessCount; + + + + private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { + if (expected == null) { + throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); + } + if(actual == null) { + throw new AssertionFailedError(msg + " - actual value was null"); + } + if(expected.getCellType() == HSSFCell.CELL_TYPE_ERROR) { + confirmErrorResult(msg, expected.getErrorCellValue(), actual); + return; + } + if(actual.getCellType() == HSSFCell.CELL_TYPE_ERROR) { + throw unexpectedError(msg, expected, actual.getErrorValue()); + } + if(actual.getCellType() != expected.getCellType()) { + throw wrongTypeError(msg, expected, actual); + } + + + switch (expected.getCellType()) { + case HSSFCell.CELL_TYPE_BOOLEAN: + assertEquals(msg, expected.getBooleanCellValue(), actual.getBooleanValue()); + break; + case HSSFCell.CELL_TYPE_FORMULA: // will never be used, since we will call method after formula evaluation + throw new AssertionFailedError("Cannot expect formula as result of formula evaluation: " + msg); + case HSSFCell.CELL_TYPE_NUMERIC: + assertEquals(expected.getNumericCellValue(), actual.getNumberValue(), 0.0); + break; + case HSSFCell.CELL_TYPE_STRING: + assertEquals(msg, expected.getRichStringCellValue().getString(), actual.getRichTextStringValue().getString()); + break; + } + } + + + private static AssertionFailedError wrongTypeError(String msgPrefix, HSSFCell expectedCell, CellValue actualValue) { + return new AssertionFailedError(msgPrefix + " Result type mismatch. Evaluated result was " + + formatValue(actualValue) + + " but the expected result was " + + formatValue(expectedCell) + ); + } + private static AssertionFailedError unexpectedError(String msgPrefix, HSSFCell expected, int actualErrorCode) { + return new AssertionFailedError(msgPrefix + " Error code (" + + ErrorEval.getText(actualErrorCode) + + ") was evaluated, but the expected result was " + + formatValue(expected) + ); + } + + + private static void confirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual) { + if(actual.getCellType() != HSSFCell.CELL_TYPE_ERROR) { + throw new AssertionFailedError(msgPrefix + " Expected cell error (" + + ErrorEval.getText(expectedErrorCode) + ") but actual value was " + + formatValue(actual)); + } + if(expectedErrorCode != actual.getErrorValue()) { + throw new AssertionFailedError(msgPrefix + " Expected cell error code (" + + ErrorEval.getText(expectedErrorCode) + + ") but actual error code was (" + + ErrorEval.getText(actual.getErrorValue()) + + ")"); + } + } + + + private static String formatValue(HSSFCell expecedCell) { + switch (expecedCell.getCellType()) { + case HSSFCell.CELL_TYPE_BLANK: return ""; + case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(expecedCell.getBooleanCellValue()); + case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(expecedCell.getNumericCellValue()); + case HSSFCell.CELL_TYPE_STRING: return expecedCell.getRichStringCellValue().getString(); + } + throw new RuntimeException("Unexpected cell type of expected value (" + expecedCell.getCellType() + ")"); + } + private static String formatValue(CellValue actual) { + switch (actual.getCellType()) { + case HSSFCell.CELL_TYPE_BLANK: return ""; + case HSSFCell.CELL_TYPE_BOOLEAN: return String.valueOf(actual.getBooleanValue()); + case HSSFCell.CELL_TYPE_NUMERIC: return String.valueOf(actual.getNumberValue()); + case HSSFCell.CELL_TYPE_STRING: return actual.getRichTextStringValue().getString(); + } + throw new RuntimeException("Unexpected cell type of evaluated value (" + actual.getCellType() + ")"); + } + + + protected void setUp() throws Exception { + _sheetFailureCount = 0; + _sheetSuccessCount = 0; + _evaluationFailureCount = 0; + _evaluationSuccessCount = 0; + } + + public void testFunctionsFromTestSpreadsheet() { + String filePath = System.getProperty("HSSF.testdata.path")+ "/" + SS.FILENAME; + HSSFWorkbook workbook; + try { + FileInputStream fin = new FileInputStream( filePath ); + workbook = new HSSFWorkbook( fin ); + } catch (IOException e) { + throw new RuntimeException(e); + } + + confirmReadMeSheet(workbook); + int nSheets = workbook.getNumberOfSheets(); + for(int i=1; i< nSheets; i++) { + int sheetResult = processTestSheet(workbook, i, workbook.getSheetName(i)); + switch(sheetResult) { + case Result.ALL_EVALUATIONS_SUCCEEDED: _sheetSuccessCount ++; break; + case Result.SOME_EVALUATIONS_FAILED: _sheetFailureCount ++; break; + } + } + + // confirm results + String successMsg = "There were " + + _sheetSuccessCount + " successful sheets(s) and " + + _evaluationSuccessCount + " function(s) without error"; + if(_sheetFailureCount > 0) { + String msg = _sheetFailureCount + " sheets(s) failed with " + + _evaluationFailureCount + " evaluation(s). " + successMsg; + throw new AssertionFailedError(msg); + } + if(false) { // normally no output for successful tests + System.out.println(getClass().getName() + ": " + successMsg); + } + } + + private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) { + HSSFSheet sheet = workbook.getSheetAt(sheetIndex); + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook); + int maxRows = sheet.getLastRowNum()+1; + int result = Result.NO_EVALUATIONS_FOUND; // so far + + String currentGroupComment = null; + for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex= endIx) { + // something went wrong. just print the whole stack trace + e.printStackTrace(ps); + } + endIx -= 4; // skip 4 frames of reflection invocation + ps.println(e.toString()); + for(int i=startIx; inull if cell is missing, empty or blank + */ + private static String getCellTextValue(HSSFRow r, int colIndex, String columnName) { + if(r == null) { + return null; + } + HSSFCell cell = r.getCell((short) colIndex); + if(cell == null) { + return null; + } + if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { + return null; + } + if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { + return cell.getRichStringCellValue().getString(); + } + + throw new RuntimeException("Bad cell type for '" + columnName + "' column: (" + + cell.getCellType() + ") row (" + (r.getRowNum() +1) + ")"); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java new file mode 100755 index 0000000000..d275e5f333 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMatch.java @@ -0,0 +1,215 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.NumericValueEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +/** + * Test cases for MATCH() + * + * @author Josh Micich + */ +public final class TestMatch extends TestCase { + /** less than or equal to */ + private static final NumberEval MATCH_LARGEST_LTE = new NumberEval(1); + private static final NumberEval MATCH_EXACT = new NumberEval(0); + /** greater than or equal to */ + private static final NumberEval MATCH_SMALLEST_GTE = new NumberEval(-1); + + + private static Eval invokeMatch(Eval lookup_value, Eval lookup_array, Eval match_type) { + Eval[] args = { lookup_value, lookup_array, match_type, }; + return new Match().evaluate(args, -1, (short)-1); + } + private static void confirmInt(int expected, Eval actualEval) { + if(!(actualEval instanceof NumericValueEval)) { + fail("Expected numeric result"); + } + NumericValueEval nve = (NumericValueEval)actualEval; + assertEquals(expected, nve.getNumberValue(), 0); + } + /** + * Convenience method + * @return new Area2DEval(new AreaPtg(ref), values) + */ + private static AreaEval createAreaEval(String ref, ValueEval[] values) { + return new Area2DEval(new AreaPtg(ref), values); + } + + public void testSimpleNumber() { + + ValueEval[] values = { + new NumberEval(4), + new NumberEval(5), + new NumberEval(10), + new NumberEval(10), + new NumberEval(25), + }; + + AreaEval ae = createAreaEval("A1:A5", values); + + confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE)); + confirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new NumberEval(10), ae, MATCH_LARGEST_LTE)); + confirmInt(3, invokeMatch(new NumberEval(10), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new NumberEval(20), ae, MATCH_LARGEST_LTE)); + assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(20), ae, MATCH_EXACT)); + } + + public void testReversedNumber() { + + ValueEval[] values = { + new NumberEval(25), + new NumberEval(10), + new NumberEval(10), + new NumberEval(10), + new NumberEval(4), + }; + + AreaEval ae = createAreaEval("A1:A5", values); + + confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_SMALLEST_GTE)); + confirmInt(2, invokeMatch(new NumberEval(10), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new NumberEval(9), ae, MATCH_SMALLEST_GTE)); + confirmInt(1, invokeMatch(new NumberEval(20), ae, MATCH_SMALLEST_GTE)); + assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(20), ae, MATCH_EXACT)); + assertEquals(ErrorEval.NA, invokeMatch(new NumberEval(26), ae, MATCH_SMALLEST_GTE)); + } + + public void testSimpleString() { + + ValueEval[] values = { + new StringEval("Albert"), + new StringEval("Charles"), + new StringEval("Ed"), + new StringEval("Greg"), + new StringEval("Ian"), + }; + + AreaEval ae = createAreaEval("A1:A5", values); + + // Note String comparisons are case insensitive + confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_LARGEST_LTE)); + confirmInt(3, invokeMatch(new StringEval("eD"), ae, MATCH_LARGEST_LTE)); + confirmInt(3, invokeMatch(new StringEval("Ed"), ae, MATCH_EXACT)); + confirmInt(3, invokeMatch(new StringEval("ed"), ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE)); + assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT)); + } + + public void testSimpleBoolean() { + + ValueEval[] values = { + BoolEval.FALSE, + BoolEval.FALSE, + BoolEval.TRUE, + BoolEval.TRUE, + }; + + AreaEval ae = createAreaEval("A1:A4", values); + + // Note String comparisons are case insensitive + confirmInt(2, invokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE)); + confirmInt(1, invokeMatch(BoolEval.FALSE, ae, MATCH_EXACT)); + confirmInt(4, invokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE)); + confirmInt(3, invokeMatch(BoolEval.TRUE, ae, MATCH_EXACT)); + } + + public void testHeterogeneous() { + + ValueEval[] values = { + new NumberEval(4), + BoolEval.FALSE, + new NumberEval(5), + new StringEval("Albert"), + BoolEval.FALSE, + BoolEval.TRUE, + new NumberEval(10), + new StringEval("Charles"), + new StringEval("Ed"), + new NumberEval(10), + new NumberEval(25), + BoolEval.TRUE, + new StringEval("Ed"), + }; + + AreaEval ae = createAreaEval("A1:A13", values); + + assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Aaron"), ae, MATCH_LARGEST_LTE)); + + confirmInt(5, invokeMatch(BoolEval.FALSE, ae, MATCH_LARGEST_LTE)); + confirmInt(2, invokeMatch(BoolEval.FALSE, ae, MATCH_EXACT)); + confirmInt(3, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE)); + confirmInt(3, invokeMatch(new NumberEval(5), ae, MATCH_EXACT)); + + confirmInt(8, invokeMatch(new StringEval("CHARLES"), ae, MATCH_EXACT)); + + confirmInt(4, invokeMatch(new StringEval("Ben"), ae, MATCH_LARGEST_LTE)); + + confirmInt(13, invokeMatch(new StringEval("ED"), ae, MATCH_LARGEST_LTE)); + confirmInt(9, invokeMatch(new StringEval("ED"), ae, MATCH_EXACT)); + + confirmInt(13, invokeMatch(new StringEval("Hugh"), ae, MATCH_LARGEST_LTE)); + assertEquals(ErrorEval.NA, invokeMatch(new StringEval("Hugh"), ae, MATCH_EXACT)); + + confirmInt(11, invokeMatch(new NumberEval(30), ae, MATCH_LARGEST_LTE)); + confirmInt(12, invokeMatch(BoolEval.TRUE, ae, MATCH_LARGEST_LTE)); + } + + + /** + * Ensures that the match_type argument can be an AreaEval.
+ * Bugzilla 44421 + */ + public void testMatchArgTypeArea() { + + ValueEval[] values = { + new NumberEval(4), + new NumberEval(5), + new NumberEval(10), + new NumberEval(10), + new NumberEval(25), + }; + + AreaEval ae = createAreaEval("A1:A5", values); + + AreaEval matchAE = createAreaEval("C1:C1", new ValueEval[] { MATCH_LARGEST_LTE, }); + + try { + confirmInt(4, invokeMatch(new NumberEval(10), ae, matchAE)); + } catch (RuntimeException e) { + if(e.getMessage().startsWith("Unexpected match_type type")) { + // identified bug 44421 + fail(e.getMessage()); + } + // some other error ?? + throw e; + } + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMathX.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMathX.java new file mode 100644 index 0000000000..eb576506a0 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMathX.java @@ -0,0 +1,909 @@ +/* +* 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. +*/ +/* + * Created on May 23, 2005 + * + */ +package org.apache.poi.hssf.record.formula.functions; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public class TestMathX extends AbstractNumericTestCase { + + public void testAcosh() { + double d = 0; + + d = MathX.acosh(0); + assertTrue("Acosh 0 is NaN", Double.isNaN(d)); + + d = MathX.acosh(1); + assertEquals("Acosh 1 ", 0, d); + + d = MathX.acosh(-1); + assertTrue("Acosh -1 is NaN", Double.isNaN(d)); + + d = MathX.acosh(100); + assertEquals("Acosh 100 ", 5.298292366d, d); + + d = MathX.acosh(101.001); + assertEquals("Acosh 101.001 ", 5.308253091d, d); + + d = MathX.acosh(200000); + assertEquals("Acosh 200000 ", 12.89921983d, d); + + } + + public void testAsinh() { + double d = 0; + + d = MathX.asinh(0); + assertEquals("asinh 0", d, 0); + + d = MathX.asinh(1); + assertEquals("asinh 1 ", 0.881373587, d); + + d = MathX.asinh(-1); + assertEquals("asinh -1 ", -0.881373587, d); + + d = MathX.asinh(-100); + assertEquals("asinh -100 ", -5.298342366, d); + + d = MathX.asinh(100); + assertEquals("asinh 100 ", 5.298342366, d); + + d = MathX.asinh(200000); + assertEquals("asinh 200000", 12.899219826096400, d); + + d = MathX.asinh(-200000); + assertEquals("asinh -200000 ", -12.899223853137, d); + + } + + public void testAtanh() { + double d = 0; + d = MathX.atanh(0); + assertEquals("atanh 0", d, 0); + + d = MathX.atanh(1); + assertEquals("atanh 1 ", Double.POSITIVE_INFINITY, d); + + d = MathX.atanh(-1); + assertEquals("atanh -1 ", Double.NEGATIVE_INFINITY, d); + + d = MathX.atanh(-100); + assertEquals("atanh -100 ", Double.NaN, d); + + d = MathX.atanh(100); + assertEquals("atanh 100 ", Double.NaN, d); + + d = MathX.atanh(200000); + assertEquals("atanh 200000", Double.NaN, d); + + d = MathX.atanh(-200000); + assertEquals("atanh -200000 ", Double.NaN, d); + + d = MathX.atanh(0.1); + assertEquals("atanh 0.1", 0.100335348, d); + + d = MathX.atanh(-0.1); + assertEquals("atanh -0.1 ", -0.100335348, d); + + } + + public void testCosh() { + double d = 0; + d = MathX.cosh(0); + assertEquals("cosh 0", 1, d); + + d = MathX.cosh(1); + assertEquals("cosh 1 ", 1.543080635, d); + + d = MathX.cosh(-1); + assertEquals("cosh -1 ", 1.543080635, d); + + d = MathX.cosh(-100); + assertEquals("cosh -100 ", 1.344058570908070E+43, d); + + d = MathX.cosh(100); + assertEquals("cosh 100 ", 1.344058570908070E+43, d); + + d = MathX.cosh(15); + assertEquals("cosh 15", 1634508.686, d); + + d = MathX.cosh(-15); + assertEquals("cosh -15 ", 1634508.686, d); + + d = MathX.cosh(0.1); + assertEquals("cosh 0.1", 1.005004168, d); + + d = MathX.cosh(-0.1); + assertEquals("cosh -0.1 ", 1.005004168, d); + + } + + public void testTanh() { + double d = 0; + d = MathX.tanh(0); + assertEquals("tanh 0", 0, d); + + d = MathX.tanh(1); + assertEquals("tanh 1 ", 0.761594156, d); + + d = MathX.tanh(-1); + assertEquals("tanh -1 ", -0.761594156, d); + + d = MathX.tanh(-100); + assertEquals("tanh -100 ", -1, d); + + d = MathX.tanh(100); + assertEquals("tanh 100 ", 1, d); + + d = MathX.tanh(15); + assertEquals("tanh 15", 1, d); + + d = MathX.tanh(-15); + assertEquals("tanh -15 ", -1, d); + + d = MathX.tanh(0.1); + assertEquals("tanh 0.1", 0.099667995, d); + + d = MathX.tanh(-0.1); + assertEquals("tanh -0.1 ", -0.099667995, d); + + } + + public void testMax() { + double[] d = new double[100]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + double m = MathX.max(d); + assertEquals("Max ", 20.1, m); + + d = new double[1000]; + m = MathX.max(d); + assertEquals("Max ", 0, m); + + d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; + d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; + d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; + d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; + d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; + m = MathX.max(d); + assertEquals("Max ", 20.1, m); + + d = new double[20]; + d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; + d[4] = -5.1; d[5] = -6.1; d[6] = -7.1; d[7] = -8.1; + d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; + d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; + d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; + m = MathX.max(d); + assertEquals("Max ", -1.1, m); + + } + + public void testMin() { + double[] d = new double[100]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + double m = MathX.min(d); + assertEquals("Min ", 0, m); + + d = new double[20]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + m = MathX.min(d); + assertEquals("Min ", 1.1, m); + + d = new double[1000]; + m = MathX.min(d); + assertEquals("Min ", 0, m); + + d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; + d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; + d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; + d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; + d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; + m = MathX.min(d); + assertEquals("Min ", -19.1, m); + + d = new double[20]; + d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; + d[4] = -5.1; d[5] = -6.1; d[6] = -7.1; d[7] = -8.1; + d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; + d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; + d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; + m = MathX.min(d); + assertEquals("Min ", -20.1, m); + } + + public void testProduct() { + double[] d = new double[100]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + double m = MathX.min(d); + assertEquals("Min ", 0, m); + + d = new double[20]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + m = MathX.min(d); + assertEquals("Min ", 1.1, m); + + d = new double[1000]; + m = MathX.min(d); + assertEquals("Min ", 0, m); + + d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; + d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; + d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; + d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; + d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; + m = MathX.min(d); + assertEquals("Min ", -19.1, m); + + d = new double[20]; + d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; + d[4] = -5.1; d[5] = -6.1; d[6] = -7.1; d[7] = -8.1; + d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; + d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; + d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; + m = MathX.min(d); + assertEquals("Min ", -20.1, m); + } + + public void testMod() { + } + + public void testNChooseK() { + int n=100; + int k=50; + double d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 1.00891344545564E29, d); + + n = -1; k = 1; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", Double.NaN, d); + + n = 1; k = -1; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", Double.NaN, d); + + n = 0; k = 1; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", Double.NaN, d); + + n = 1; k = 0; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 1, d); + + n = 10; k = 9; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 10, d); + + n = 10; k = 10; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 1, d); + + n = 10; k = 1; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 10, d); + + n = 1000; k = 1; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 1000, d); // awesome ;) + + n = 1000; k = 2; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 499500, d); // awesome ;) + + n = 13; k = 7; + d = MathX.nChooseK(n, k); + assertEquals("NChooseK ", 1716, d); + + } + + public void testSign() { + final short minus = -1; + final short zero = 0; + final short plus = 1; + double d = 0; + + + assertEquals("Sign ", minus, MathX.sign(minus)); + assertEquals("Sign ", plus, MathX.sign(plus)); + assertEquals("Sign ", zero, MathX.sign(zero)); + + d = 0; + assertEquals("Sign ", zero, MathX.sign(d)); + + d = -1.000001; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = -.000001; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = -1E-200; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = Double.NEGATIVE_INFINITY; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = -200.11; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = -2000000000000.11; + assertEquals("Sign ", minus, MathX.sign(d)); + + d = 1.000001; + assertEquals("Sign ", plus, MathX.sign(d)); + + d = .000001; + assertEquals("Sign ", plus, MathX.sign(d)); + + d = 1E-200; + assertEquals("Sign ", plus, MathX.sign(d)); + + d = Double.POSITIVE_INFINITY; + assertEquals("Sign ", plus, MathX.sign(d)); + + d = 200.11; + assertEquals("Sign ", plus, MathX.sign(d)); + + d = 2000000000000.11; + assertEquals("Sign ", plus, MathX.sign(d)); + + } + + public void testSinh() { + double d = 0; + d = MathX.sinh(0); + assertEquals("sinh 0", 0, d); + + d = MathX.sinh(1); + assertEquals("sinh 1 ", 1.175201194, d); + + d = MathX.sinh(-1); + assertEquals("sinh -1 ", -1.175201194, d); + + d = MathX.sinh(-100); + assertEquals("sinh -100 ", -1.344058570908070E+43, d); + + d = MathX.sinh(100); + assertEquals("sinh 100 ", 1.344058570908070E+43, d); + + d = MathX.sinh(15); + assertEquals("sinh 15", 1634508.686, d); + + d = MathX.sinh(-15); + assertEquals("sinh -15 ", -1634508.686, d); + + d = MathX.sinh(0.1); + assertEquals("sinh 0.1", 0.10016675, d); + + d = MathX.sinh(-0.1); + assertEquals("sinh -0.1 ", -0.10016675, d); + + } + + public void testSum() { + double[] d = new double[100]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + double s = MathX.sum(d); + assertEquals("Sum ", 212, s); + + d = new double[1000]; + s = MathX.sum(d); + assertEquals("Sum ", 0, s); + + d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; + d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; + d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; + d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; + d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; + s = MathX.sum(d); + assertEquals("Sum ", 10, s); + + d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; + d[4] = -5.1; d[5] = -6.1; d[6] = -7.1; d[7] = -8.1; + d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; + d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; + d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; + s = MathX.sum(d); + assertEquals("Sum ", -212, s); + + } + + public void testSumproduct() { + double d = 0; + double[][] darr = new double[][] + {{0 ,0.11 ,23.23}, + {1 ,0.22 ,46.46}, + {2 ,0.33 ,69.69}, + {3 ,0.44 ,92.92}, + {4 ,0.55 ,116.15}, + {5 ,0.66 ,139.38}, + {6 ,0.77 ,162.61}, + {7 ,0.88 ,185.84}, + {8 ,0.99 ,209.07}, + {9 ,1.1 ,232.3}, + {10 ,1.21 ,255.53}}; + d = MathX.sumproduct(darr); + assertEquals("Sumproduct ", 4.243234425E+22, d); + darr = new double[][] + {{0 ,0.11 ,23.23}, + {0 ,0.22 ,46.46}, + {0 ,0.33 ,69.69}, + {0 ,0.44 ,92.92}, + {0 ,0.55 ,116.15}, + {0 ,0.66 ,139.38}, + {0 ,0.77 ,162.61}, + {0 ,0.88 ,185.84}, + {0 ,0.99 ,209.07}, + {0 ,1.1 ,232.3}, + {0 ,1.21 ,255.53}}; + d = MathX.sumproduct(darr); + assertEquals("Sumproduct ", 4.243234425E+22, d); + + darr = new double[][] + {{0, 0, 0, 0, 0, 0, 0, 0}, + {0.11, 0.22, 0.33, 0.44, 0.55, 0.66, 0.77, 0.88}, + {23.23, 46.46, 69.69, 92.92, 116.15, 139.38, 162.61, 185.84}}; + d = MathX.sumproduct(darr); + assertEquals("Sumproduct ", 0, d); + + darr = new double[][] + {{0, 1, 2, 3, 4, 5, 6, 7}, + {0.11, 0.22, 0.33, 0.44, 0.55, 0.66, 0.77, 0.88}, + {23.23, 46.46, 69.69, 92.92, 116.15, 139.38, 162.61, 185.84}}; + d = MathX.sumproduct(darr); + assertEquals("Sumproduct ", 2790.3876, d); + + + } + + public void testSumsq() { + double[] d = new double[100]; + d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; + d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; + d[8] = 9.1; d[9] = 10.1; d[10] = 11.1; d[11] = 12.1; + d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; + d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; + + double s = MathX.sumsq(d); + assertEquals("Sumsq ", 2912.2, s); + + d = new double[1000]; + s = MathX.sumsq(d); + assertEquals("Sumsq ", 0, s); + + d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; + d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; + d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; + d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; + d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; + s = MathX.sumsq(d); + assertEquals("Sumsq ", 2912.2, s); + + d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; + d[4] = -5.1; d[5] = -6.1; d[6] = -7.1; d[7] = -8.1; + d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; + d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; + d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; + s = MathX.sumsq(d); + assertEquals("Sumsq ", 2912.2, s); + } + + public void testFactorial() { + int n = 0; + double s = 0; + + n = 0; + s = MathX.factorial(n); + assertEquals("Factorial ", 1, s); + + n = 1; + s = MathX.factorial(n); + assertEquals("Factorial ", 1, s); + + n = 10; + s = MathX.factorial(n); + assertEquals("Factorial ", 3628800, s); + + n = 99; + s = MathX.factorial(n); + assertEquals("Factorial ", 9.33262154439E+155, s); + + n = -1; + s = MathX.factorial(n); + assertEquals("Factorial ", Double.NaN, s); + + n = Integer.MAX_VALUE; + s = MathX.factorial(n); + assertEquals("Factorial ", Double.POSITIVE_INFINITY, s); + } + + public void testSumx2my2() { + double d = 0; + double[] xarr = null; + double[] yarr = null; + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumx2my2(xarr, yarr); + assertEquals("sumx2my2 ", 100, d); + + xarr = new double[]{-1, -2, -3, -4, -5, -6, -7, -8, -9, -10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumx2my2(xarr, yarr); + assertEquals("sumx2my2 ", 100, d); + + xarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumx2my2(xarr, yarr); + assertEquals("sumx2my2 ", -100, d); + + xarr = new double[]{10}; + yarr = new double[]{9}; + d = MathX.sumx2my2(xarr, yarr); + assertEquals("sumx2my2 ", 19, d); + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumx2my2(xarr, yarr); + assertEquals("sumx2my2 ", 0, d); + + } + + public void testSumx2py2() { + double d = 0; + double[] xarr = null; + double[] yarr = null; + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumx2py2(xarr, yarr); + assertEquals("sumx2py2 ", 670, d); + + xarr = new double[]{-1, -2, -3, -4, -5, -6, -7, -8, -9, -10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumx2py2(xarr, yarr); + assertEquals("sumx2py2 ", 670, d); + + xarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumx2py2(xarr, yarr); + assertEquals("sumx2py2 ", 670, d); + + xarr = new double[]{10}; + yarr = new double[]{9}; + d = MathX.sumx2py2(xarr, yarr); + assertEquals("sumx2py2 ", 181, d); + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumx2py2(xarr, yarr); + assertEquals("sumx2py2 ", 770, d); + } + + public void testSumxmy2() { + double d = 0; + double[] xarr = null; + double[] yarr = null; + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumxmy2(xarr, yarr); + assertEquals("sumxmy2 ", 10, d); + + xarr = new double[]{-1, -2, -3, -4, -5, -6, -7, -8, -9, -10}; + yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + d = MathX.sumxmy2(xarr, yarr); + assertEquals("sumxmy2 ", 1330, d); + + xarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumxmy2(xarr, yarr); + assertEquals("sumxmy2 ", 10, d); + + xarr = new double[]{10}; + yarr = new double[]{9}; + d = MathX.sumxmy2(xarr, yarr); + assertEquals("sumxmy2 ", 1, d); + + xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + yarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}; + d = MathX.sumxmy2(xarr, yarr); + assertEquals("sumxmy2 ", 0, d); + } + + public void testRound() { + double d = 0; + int p = 0; + + d = 0; p = 0; + assertEquals("round ", 0, MathX.round(d, p)); + + d = 10; p = 0; + assertEquals("round ", 10, MathX.round(d, p)); + + d = 123.23; p = 0; + assertEquals("round ", 123, MathX.round(d, p)); + + d = -123.23; p = 0; + assertEquals("round ", -123, MathX.round(d, p)); + + d = 123.12; p = 2; + assertEquals("round ", 123.12, MathX.round(d, p)); + + d = 88.123459; p = 5; + assertEquals("round ", 88.12346, MathX.round(d, p)); + + d = 0; p = 2; + assertEquals("round ", 0, MathX.round(d, p)); + + d = 0; p = -1; + assertEquals("round ", 0, MathX.round(d, p)); + + d = 0.01; p = -1; + assertEquals("round ", 0, MathX.round(d, p)); + + d = 123.12; p = -2; + assertEquals("round ", 100, MathX.round(d, p)); + + d = 88.123459; p = -3; + assertEquals("round ", 0, MathX.round(d, p)); + + d = 49.00000001; p = -1; + assertEquals("round ", 50, MathX.round(d, p)); + + d = 149.999999; p = -2; + assertEquals("round ", 100, MathX.round(d, p)); + + d = 150.0; p = -2; + assertEquals("round ", 200, MathX.round(d, p)); + } + + public void testRoundDown() { + double d = 0; + int p = 0; + + d = 0; p = 0; + assertEquals("roundDown ", 0, MathX.roundDown(d, p)); + + d = 10; p = 0; + assertEquals("roundDown ", 10, MathX.roundDown(d, p)); + + d = 123.99; p = 0; + assertEquals("roundDown ", 123, MathX.roundDown(d, p)); + + d = -123.99; p = 0; + assertEquals("roundDown ", -123, MathX.roundDown(d, p)); + + d = 123.99; p = 2; + assertEquals("roundDown ", 123.99, MathX.roundDown(d, p)); + + d = 88.123459; p = 5; + assertEquals("roundDown ", 88.12345, MathX.roundDown(d, p)); + + d = 0; p = 2; + assertEquals("roundDown ", 0, MathX.roundDown(d, p)); + + d = 0; p = -1; + assertEquals("roundDown ", 0, MathX.roundDown(d, p)); + + d = 0.01; p = -1; + assertEquals("roundDown ", 0, MathX.roundDown(d, p)); + + d = 199.12; p = -2; + assertEquals("roundDown ", 100, MathX.roundDown(d, p)); + + d = 88.123459; p = -3; + assertEquals("roundDown ", 0, MathX.roundDown(d, p)); + + d = 99.00000001; p = -1; + assertEquals("roundDown ", 90, MathX.roundDown(d, p)); + + d = 100.00001; p = -2; + assertEquals("roundDown ", 100, MathX.roundDown(d, p)); + + d = 150.0; p = -2; + assertEquals("roundDown ", 100, MathX.roundDown(d, p)); + } + + public void testRoundUp() { + double d = 0; + int p = 0; + + d = 0; p = 0; + assertEquals("roundUp ", 0, MathX.roundUp(d, p)); + + d = 10; p = 0; + assertEquals("roundUp ", 10, MathX.roundUp(d, p)); + + d = 123.23; p = 0; + assertEquals("roundUp ", 124, MathX.roundUp(d, p)); + + d = -123.23; p = 0; + assertEquals("roundUp ", -124, MathX.roundUp(d, p)); + + d = 123.12; p = 2; + assertEquals("roundUp ", 123.12, MathX.roundUp(d, p)); + + d = 88.123459; p = 5; + assertEquals("roundUp ", 88.12346, MathX.roundUp(d, p)); + + d = 0; p = 2; + assertEquals("roundUp ", 0, MathX.roundUp(d, p)); + + d = 0; p = -1; + assertEquals("roundUp ", 0, MathX.roundUp(d, p)); + + d = 0.01; p = -1; + assertEquals("roundUp ", 10, MathX.roundUp(d, p)); + + d = 123.12; p = -2; + assertEquals("roundUp ", 200, MathX.roundUp(d, p)); + + d = 88.123459; p = -3; + assertEquals("roundUp ", 1000, MathX.roundUp(d, p)); + + d = 49.00000001; p = -1; + assertEquals("roundUp ", 50, MathX.roundUp(d, p)); + + d = 149.999999; p = -2; + assertEquals("roundUp ", 200, MathX.roundUp(d, p)); + + d = 150.0; p = -2; + assertEquals("roundUp ", 200, MathX.roundUp(d, p)); + } + + public void testCeiling() { + double d = 0; + double s = 0; + + d = 0; s = 0; + assertEquals("ceiling ", 0, MathX.ceiling(d, s)); + + d = 1; s = 0; + assertEquals("ceiling ", 0, MathX.ceiling(d, s)); + + d = 0; s = 1; + assertEquals("ceiling ", 0, MathX.ceiling(d, s)); + + d = -1; s = 0; + assertEquals("ceiling ", 0, MathX.ceiling(d, s)); + + d = 0; s = -1; + assertEquals("ceiling ", 0, MathX.ceiling(d, s)); + + d = 10; s = 1.11; + assertEquals("ceiling ", 11.1, MathX.ceiling(d, s)); + + d = 11.12333; s = 0.03499; + assertEquals("ceiling ", 11.12682, MathX.ceiling(d, s)); + + d = -11.12333; s = 0.03499; + assertEquals("ceiling ", Double.NaN, MathX.ceiling(d, s)); + + d = 11.12333; s = -0.03499; + assertEquals("ceiling ", Double.NaN, MathX.ceiling(d, s)); + + d = -11.12333; s = -0.03499; + assertEquals("ceiling ", -11.12682, MathX.ceiling(d, s)); + + d = 100; s = 0.001; + assertEquals("ceiling ", 100, MathX.ceiling(d, s)); + + d = -0.001; s = -9.99; + assertEquals("ceiling ", -9.99, MathX.ceiling(d, s)); + + d = 4.42; s = 0.05; + assertEquals("ceiling ", 4.45, MathX.ceiling(d, s)); + + d = 0.05; s = 4.42; + assertEquals("ceiling ", 4.42, MathX.ceiling(d, s)); + + d = 0.6666; s = 3.33; + assertEquals("ceiling ", 3.33, MathX.ceiling(d, s)); + + d = 2d/3; s = 3.33; + assertEquals("ceiling ", 3.33, MathX.ceiling(d, s)); + } + + public void testFloor() { + double d = 0; + double s = 0; + + d = 0; s = 0; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = 1; s = 0; + assertEquals("floor ", Double.NaN, MathX.floor(d, s)); + + d = 0; s = 1; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = -1; s = 0; + assertEquals("floor ", Double.NaN, MathX.floor(d, s)); + + d = 0; s = -1; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = 10; s = 1.11; + assertEquals("floor ", 9.99, MathX.floor(d, s)); + + d = 11.12333; s = 0.03499; + assertEquals("floor ", 11.09183, MathX.floor(d, s)); + + d = -11.12333; s = 0.03499; + assertEquals("floor ", Double.NaN, MathX.floor(d, s)); + + d = 11.12333; s = -0.03499; + assertEquals("floor ", Double.NaN, MathX.floor(d, s)); + + d = -11.12333; s = -0.03499; + assertEquals("floor ", -11.09183, MathX.floor(d, s)); + + d = 100; s = 0.001; + assertEquals("floor ", 100, MathX.floor(d, s)); + + d = -0.001; s = -9.99; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = 4.42; s = 0.05; + assertEquals("floor ", 4.4, MathX.floor(d, s)); + + d = 0.05; s = 4.42; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = 0.6666; s = 3.33; + assertEquals("floor ", 0, MathX.floor(d, s)); + + d = 2d/3; s = 3.33; + assertEquals("floor ", 0, MathX.floor(d, s)); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java new file mode 100755 index 0000000000..dc3d595aed --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestMid.java @@ -0,0 +1,115 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.Ref2DEval; +import org.apache.poi.hssf.record.formula.eval.RefEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +import junit.framework.TestCase; +/** + * Tests for Excel function MID() + * + * @author Josh Micich + */ +public final class TestMid extends TestCase { + + + private static Eval invokeMid(Eval text, Eval startPos, Eval numChars) { + Eval[] args = new Eval[] { text, startPos, numChars, }; + return new Mid().evaluate(args, -1, (short)-1); + } + + private void confirmMid(Eval text, Eval startPos, Eval numChars, String expected) { + Eval result = invokeMid(text, startPos, numChars); + assertEquals(StringEval.class, result.getClass()); + assertEquals(expected, ((StringEval)result).getStringValue()); + } + + private void confirmMid(Eval text, Eval startPos, Eval numChars, ErrorEval expectedError) { + Eval result = invokeMid(text, startPos, numChars); + assertEquals(ErrorEval.class, result.getClass()); + assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode()); + } + + public void testBasic() { + + confirmMid(new StringEval("galactic"), new NumberEval(3), new NumberEval(4), "lact"); + } + + /** + * Valid cases where args are not precisely (string, int, int) but can be resolved OK. + */ + public void testUnusualArgs() { + // startPos with fractional digits + confirmMid(new StringEval("galactic"), new NumberEval(3.1), new NumberEval(4), "lact"); + + // string startPos + confirmMid(new StringEval("galactic"), new StringEval("3"), new NumberEval(4), "lact"); + + // text (first) arg type is number, other args are strings with fractional digits + confirmMid(new NumberEval(123456), new StringEval("3.1"), new StringEval("2.9"), "34"); + + // startPos is 1x1 area ref, numChars is cell ref + AreaEval aeStart = new Area2DEval(new AreaPtg("A1:A1"), new ValueEval[] { new NumberEval(2), } ); + RefEval reNumChars = new Ref2DEval(new ReferencePtg("B1"), new NumberEval(3)); + confirmMid(new StringEval("galactic"), aeStart, reNumChars, "ala"); + + confirmMid(new StringEval("galactic"), new NumberEval(3.1), BlankEval.INSTANCE, ""); + + confirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.FALSE, ""); + confirmMid(new StringEval("galactic"), new NumberEval(3), BoolEval.TRUE, "l"); + confirmMid(BlankEval.INSTANCE, new NumberEval(3), BoolEval.TRUE, ""); + + } + + /** + * Extreme values for startPos and numChars + */ + public void testExtremes() { + confirmMid(new StringEval("galactic"), new NumberEval(4), new NumberEval(400), "actic"); + + confirmMid(new StringEval("galactic"), new NumberEval(30), new NumberEval(4), ""); + confirmMid(new StringEval("galactic"), new NumberEval(3), new NumberEval(0), ""); + } + + /** + * All sorts of ways to make MID return defined errors. + */ + public void testErrors() { + confirmMid(ErrorEval.NAME_INVALID, new NumberEval(3), new NumberEval(4), ErrorEval.NAME_INVALID); + confirmMid(new StringEval("galactic"), ErrorEval.NAME_INVALID, new NumberEval(4), ErrorEval.NAME_INVALID); + confirmMid(new StringEval("galactic"), new NumberEval(3), ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID); + confirmMid(new StringEval("galactic"), ErrorEval.DIV_ZERO, ErrorEval.NAME_INVALID, ErrorEval.DIV_ZERO); + + confirmMid(new StringEval("galactic"), BlankEval.INSTANCE, new NumberEval(3.1), ErrorEval.VALUE_INVALID); + + confirmMid(new StringEval("galactic"), new NumberEval(0), new NumberEval(4), ErrorEval.VALUE_INVALID); + confirmMid(new StringEval("galactic"), new NumberEval(1), new NumberEval(-1), ErrorEval.VALUE_INVALID); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java new file mode 100755 index 0000000000..f114662985 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestOffset.java @@ -0,0 +1,92 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.functions.Offset.LinearOffsetRange; + +/** + * Tests for OFFSET function implementation + * + * @author Josh Micich + */ +public final class TestOffset extends TestCase { + + + private static void confirmDoubleConvert(double doubleVal, int expected) { + assertEquals(expected, Offset.convertDoubleToInt(doubleVal)); + } + /** + * Excel's double to int conversion (for function 'OFFSET()') behaves more like Math.floor(). + * Note - negative values are not symmetrical + */ + public void testDoubleConversion() { + + confirmDoubleConvert(100.09, 100); + confirmDoubleConvert(100.01, 100); + confirmDoubleConvert(100.00, 100); + confirmDoubleConvert(99.99, 99); + + confirmDoubleConvert(+2.01, +2); + confirmDoubleConvert(+2.00, +2); + confirmDoubleConvert(+1.99, +1); + confirmDoubleConvert(+1.01, +1); + confirmDoubleConvert(+1.00, +1); + confirmDoubleConvert(+0.99, 0); + confirmDoubleConvert(+0.01, 0); + confirmDoubleConvert( 0.00, 0); + confirmDoubleConvert(-0.01, -1); + confirmDoubleConvert(-0.99, -1); + confirmDoubleConvert(-1.00, -1); + confirmDoubleConvert(-1.01, -2); + confirmDoubleConvert(-1.99, -2); + confirmDoubleConvert(-2.00, -2); + confirmDoubleConvert(-2.01, -3); + } + + public void testLinearOffsetRange() { + LinearOffsetRange lor; + + lor = new LinearOffsetRange(3, 2); + assertEquals(3, lor.getFirstIndex()); + assertEquals(4, lor.getLastIndex()); + lor = lor.normaliseAndTranslate(0); // expected no change + assertEquals(3, lor.getFirstIndex()); + assertEquals(4, lor.getLastIndex()); + + lor = lor.normaliseAndTranslate(5); + assertEquals(8, lor.getFirstIndex()); + assertEquals(9, lor.getLastIndex()); + + // negative length + + lor = new LinearOffsetRange(6, -4).normaliseAndTranslate(0); + assertEquals(3, lor.getFirstIndex()); + assertEquals(6, lor.getLastIndex()); + + + // bounds checking + lor = new LinearOffsetRange(0, 100); + assertFalse(lor.isOutOfBounds(0, 16383)); + lor = lor.normaliseAndTranslate(16300); + assertTrue(lor.isOutOfBounds(0, 16383)); + assertFalse(lor.isOutOfBounds(0, 65535)); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java new file mode 100644 index 0000000000..935615acae --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java @@ -0,0 +1,87 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.usermodel.HSSFErrorConstants; + +/** + * + * @author Josh Micich + */ +public final class TestPmt extends TestCase { + + private static void confirm(double expected, NumberEval ne) { + // only asserting accuracy to 4 fractional digits + assertEquals(expected, ne.getNumberValue(), 0.00005); + } + private static Eval invoke(Eval[] args) { + return new Pmt().evaluate(args, -1, (short)-1); + } + /** + * Invocation when not expecting an error result + */ + private static NumberEval invokeNormal(Eval[] args) { + Eval ev = invoke(args); + if(ev instanceof ErrorEval) { + throw new AssertionFailedError("Normal evaluation failed with error code: " + + ev.toString()); + } + return (NumberEval) ev; + } + + private static void confirm(double expected, double rate, double nper, double pv, double fv, boolean isBeginning) { + Eval[] args = { + new NumberEval(rate), + new NumberEval(nper), + new NumberEval(pv), + new NumberEval(fv), + new NumberEval(isBeginning ? 1 : 0), + }; + confirm(expected, invokeNormal(args)); + } + + + public void testBasic() { + confirm(-1037.0321, (0.08/12), 10, 10000, 0, false); + confirm(-1030.1643, (0.08/12), 10, 10000, 0, true); + } + + public void test3args() { + + Eval[] args = { + new NumberEval(0.005), + new NumberEval(24), + new NumberEval(1000), + }; + Eval ev = invoke(args); + if(ev instanceof ErrorEval) { + ErrorEval err = (ErrorEval) ev; + if(err.getErrorCode() == HSSFErrorConstants.ERROR_VALUE) { + throw new AssertionFailedError("Identified bug 44691"); + } + } + + confirm(-44.3206, invokeNormal(args)); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRoundFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRoundFuncs.java new file mode 100755 index 0000000000..a6ce345aeb --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRoundFuncs.java @@ -0,0 +1,49 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; + +import junit.framework.TestCase; + +/** + * Test cases for ROUND(), ROUNDUP(), ROUNDDOWN() + * + * @author Josh Micich + */ +public final class TestRoundFuncs extends TestCase { + public void testRounddownWithStringArg() { + + Eval strArg = new StringEval("abc"); + Eval[] args = { strArg, new NumberEval(2), }; + Eval result = new Rounddown().evaluate(args, -1, (short)-1); + assertEquals(ErrorEval.VALUE_INVALID, result); + } + + public void testRoundupWithStringArg() { + + Eval strArg = new StringEval("abc"); + Eval[] args = { strArg, new NumberEval(2), }; + Eval result = new Roundup().evaluate(args, -1, (short)-1); + assertEquals(ErrorEval.VALUE_INVALID, result); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRowCol.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRowCol.java new file mode 100755 index 0000000000..4002c30d0f --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestRowCol.java @@ -0,0 +1,102 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.Eval; + +/** + * Tests for ROW(), ROWS(), COLUMN(), COLUMNS() + * + * @author Josh Micich + */ +public final class TestRowCol extends TestCase { + + public TestRowCol(String testName) { + super(testName); + } + + public void testCol() { + Function target = new Column(); + { + Eval[] args = { EvalFactory.createRefEval("C5"), }; + double actual = NumericFunctionInvoker.invoke(target, args); + assertEquals(3, actual, 0D); + } + { + Eval[] args = { EvalFactory.createAreaEval("E2:H12", 4, 11), }; + double actual = NumericFunctionInvoker.invoke(target, args); + assertEquals(5, actual, 0D); + } + } + + public void testRow() { + Function target = new Row(); + { + Eval[] args = { EvalFactory.createRefEval("C5"), }; + double actual = NumericFunctionInvoker.invoke(target, args); + assertEquals(5, actual, 0D); + } + { + Eval[] args = { EvalFactory.createAreaEval("E2:H12", 4, 11), }; + double actual = NumericFunctionInvoker.invoke(target, args); + assertEquals(2, actual, 0D); + } + } + + public void testColumns() { + + confirmColumnsFunc("A1:F1", 6, 1); + confirmColumnsFunc("A1:C2", 3, 2); + confirmColumnsFunc("A1:B3", 2, 3); + confirmColumnsFunc("A1:A6", 1, 6); + + Eval[] args = { EvalFactory.createRefEval("C5"), }; + double actual = NumericFunctionInvoker.invoke(new Columns(), args); + assertEquals(1, actual, 0D); + } + + public void testRows() { + + confirmRowsFunc("A1:F1", 6, 1); + confirmRowsFunc("A1:C2", 3, 2); + confirmRowsFunc("A1:B3", 2, 3); + confirmRowsFunc("A1:A6", 1, 6); + + Eval[] args = { EvalFactory.createRefEval("C5"), }; + double actual = NumericFunctionInvoker.invoke(new Rows(), args); + assertEquals(1, actual, 0D); + } + + private static void confirmRowsFunc(String areaRefStr, int nCols, int nRows) { + Eval[] args = { EvalFactory.createAreaEval(areaRefStr, nCols, nRows), }; + + double actual = NumericFunctionInvoker.invoke(new Rows(), args); + assertEquals(nRows, actual, 0D); + } + + + private static void confirmColumnsFunc(String areaRefStr, int nCols, int nRows) { + Eval[] args = { EvalFactory.createAreaEval(areaRefStr, nCols, nRows), }; + + double actual = NumericFunctionInvoker.invoke(new Columns(), args); + assertEquals(nCols, actual, 0D); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java new file mode 100644 index 0000000000..237366baf0 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestStatsLib.java @@ -0,0 +1,262 @@ +/* +* 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. +*/ +/* + * Created on May 30, 2005 + * + */ +package org.apache.poi.hssf.record.formula.functions; + + +/** + * @author Amol S. Deshmukh < amolweb at ya hoo dot com > + * + */ +public class TestStatsLib extends AbstractNumericTestCase { + + public void testDevsq() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.devsq(v); + x = 82.5; + assertEquals("devsq ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.devsq(v); + x = 0; + assertEquals("devsq ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.devsq(v); + x = 0; + assertEquals("devsq ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.devsq(v); + x = 2.5; + assertEquals("devsq ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.devsq(v); + x = 10953.7416965767; + assertEquals("devsq ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.devsq(v); + x = 82.5; + assertEquals("devsq ", x, d); + } + + public void testKthLargest() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.kthLargest(v, 3); + x = 8; + assertEquals("kthLargest ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.kthLargest(v, 3); + x = 1; + assertEquals("kthLargest ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.kthLargest(v, 3); + x = 0; + assertEquals("kthLargest ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.kthLargest(v, 3); + x = 2; + assertEquals("kthLargest ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.kthLargest(v, 3); + x = 5.37828; + assertEquals("kthLargest ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.kthLargest(v, 3); + x = -3; + assertEquals("kthLargest ", x, d); + } + + public void testKthSmallest() { + } + + public void testAvedev() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.avedev(v); + x = 2.5; + assertEquals("avedev ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.avedev(v); + x = 0; + assertEquals("avedev ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.avedev(v); + x = 0; + assertEquals("avedev ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.avedev(v); + x = 0.5; + assertEquals("avedev ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.avedev(v); + x = 36.42176053333; + assertEquals("avedev ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.avedev(v); + x = 2.5; + assertEquals("avedev ", x, d); + } + + public void testMedian() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.median(v); + x = 5.5; + assertEquals("median ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.median(v); + x = 1; + assertEquals("median ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.median(v); + x = 0; + assertEquals("median ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.median(v); + x = 1.5; + assertEquals("median ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.median(v); + x = 5.37828; + assertEquals("median ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.median(v); + x = -5.5; + assertEquals("median ", x, d); + + v = new double[] {-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.median(v); + x = -6; + assertEquals("median ", x, d); + + v = new double[] {1,2,3,4,5,6,7,8,9}; + d = StatsLib.median(v); + x = 5; + assertEquals("median ", x, d); + } + + public void testMode() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.mode(v); + x = Double.NaN; + assertEquals("mode ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.mode(v); + x = 1; + assertEquals("mode ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.mode(v); + x = 0; + assertEquals("mode ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.mode(v); + x = 1; + assertEquals("mode ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.mode(v); + x = Double.NaN; + assertEquals("mode ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.mode(v); + x = Double.NaN; + assertEquals("mode ", x, d); + + v = new double[] {1,2,3,4,1,1,1,1,0,0,0,0,0}; + d = StatsLib.mode(v); + x = 1; + assertEquals("mode ", x, d); + + v = new double[] {0,1,2,3,4,1,1,1,0,0,0,0,1}; + d = StatsLib.mode(v); + x = 0; + assertEquals("mode ", x, d); + } + + public void testStddev() { + double[] v = null; + double d, x = 0; + + v = new double[] {1,2,3,4,5,6,7,8,9,10}; + d = StatsLib.stdev(v); + x = 3.02765035410; + assertEquals("stdev ", x, d); + + v = new double[] {1,1,1,1,1,1,1,1,1,1}; + d = StatsLib.stdev(v); + x = 0; + assertEquals("stdev ", x, d); + + v = new double[] {0,0,0,0,0,0,0,0,0,0}; + d = StatsLib.stdev(v); + x = 0; + assertEquals("stdev ", x, d); + + v = new double[] {1,2,1,2,1,2,1,2,1,2}; + d = StatsLib.stdev(v); + x = 0.52704627669; + assertEquals("stdev ", x, d); + + v = new double[] {123.12,33.3333,2d/3d,5.37828,0.999}; + d = StatsLib.stdev(v); + x = 52.33006233652; + assertEquals("stdev ", x, d); + + v = new double[] {-1,-2,-3,-4,-5,-6,-7,-8,-9,-10}; + d = StatsLib.stdev(v); + x = 3.02765035410; + assertEquals("stdev ", x, d); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumproduct.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumproduct.java new file mode 100755 index 0000000000..73043911f4 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestSumproduct.java @@ -0,0 +1,120 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.NumericValueEval; +import org.apache.poi.hssf.record.formula.eval.Ref2DEval; +import org.apache.poi.hssf.record.formula.eval.RefEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +import junit.framework.TestCase; + +/** + * Test cases for SUMPRODUCT() + * + * @author Josh Micich + */ +public final class TestSumproduct extends TestCase { + + private static Eval invokeSumproduct(Eval[] args) { + // srcCellRow and srcCellColumn are ignored by SUMPRODUCT + return new Sumproduct().evaluate(args, -1, (short)-1); + } + private static void confirmDouble(double expected, Eval actualEval) { + if(!(actualEval instanceof NumericValueEval)) { + fail("Expected numeric result"); + } + NumericValueEval nve = (NumericValueEval)actualEval; + assertEquals(expected, nve.getNumberValue(), 0); + } + + public void testScalarSimple() { + + RefEval refEval = new Ref2DEval(new ReferencePtg("A1"), new NumberEval(3)); + Eval[] args = { + refEval, + new NumberEval(2), + }; + Eval result = invokeSumproduct(args); + confirmDouble(6D, result); + } + + + public void testAreaSimple() { + + AreaEval aeA = EvalFactory.createAreaEval("A1:A3", 1, 3); + AreaEval aeB = EvalFactory.createAreaEval("B1:B3", 1, 3); + ValueEval[] aValues = aeA.getValues(); + ValueEval[] bValues = aeB.getValues(); + aValues[0] = new NumberEval(2); + aValues[1] = new NumberEval(4); + aValues[2] = new NumberEval(5); + bValues[0] = new NumberEval(3); + bValues[1] = new NumberEval(6); + bValues[2] = new NumberEval(7); + + Eval[] args = { aeA, aeB, }; + Eval result = invokeSumproduct(args); + confirmDouble(65D, result); + } + + /** + * For scalar products, the terms may be 1x1 area refs + */ + public void testOneByOneArea() { + + AreaEval ae = EvalFactory.createAreaEval("A1:A1", 1, 1); + ae.getValues()[0] = new NumberEval(7); + + Eval[] args = { + ae, + new NumberEval(2), + }; + Eval result = invokeSumproduct(args); + confirmDouble(14D, result); + } + + + public void testMismatchAreaDimensions() { + + AreaEval aeA = EvalFactory.createAreaEval("A1:A3", 1, 3); + AreaEval aeB = EvalFactory.createAreaEval("B1:D1", 3, 1); + + Eval[] args; + args = new Eval[] { aeA, aeB, }; + assertEquals(ErrorEval.VALUE_INVALID, invokeSumproduct(args)); + + args = new Eval[] { aeA, new NumberEval(5), }; + assertEquals(ErrorEval.VALUE_INVALID, invokeSumproduct(args)); + } + + public void testAreaWithErrorCell() { + AreaEval aeA = EvalFactory.createAreaEval("A1:A2", 1, 2); + AreaEval aeB = EvalFactory.createAreaEval("B1:B2", 1, 2); + aeB.getValues()[1] = ErrorEval.REF_INVALID; + + Eval[] args = { aeA, aeB, }; + assertEquals(ErrorEval.REF_INVALID, invokeSumproduct(args)); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTFunc.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTFunc.java new file mode 100755 index 0000000000..4d63cad1c5 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTFunc.java @@ -0,0 +1,118 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import org.apache.poi.hssf.record.formula.ReferencePtg; +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.Ref2DEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; + +import junit.framework.TestCase; + +/** + * Test cases for Excel function T() + * + * @author Josh Micich + */ +public final class TestTFunc extends TestCase { + + /** + * @return the result of calling function T() with the specified argument + */ + private static Eval invokeT(Eval arg) { + Eval[] args = { arg, }; + Eval result = new T().evaluate(args, -1, (short)-1); + assertNotNull("result may never be null", result); + return result; + } + /** + * Simulates call: T(A1) + * where cell A1 has the specified innerValue + */ + private Eval invokeTWithReference(ValueEval innerValue) { + Eval arg = new Ref2DEval(new ReferencePtg((short)1, (short)1, false, false), innerValue); + return invokeT(arg); + } + + private static void confirmText(String text) { + Eval arg = new StringEval(text); + Eval eval = invokeT(arg); + StringEval se = (StringEval) eval; + assertEquals(text, se.getStringValue()); + } + + public void testTextValues() { + + confirmText("abc"); + confirmText(""); + confirmText(" "); + confirmText("~"); + confirmText("123"); + confirmText("TRUE"); + } + + private static void confirmError(Eval arg) { + Eval eval = invokeT(arg); + assertTrue(arg == eval); + } + + public void testErrorValues() { + + confirmError(ErrorEval.VALUE_INVALID); + confirmError(ErrorEval.NA); + confirmError(ErrorEval.REF_INVALID); + } + + private static void confirmString(Eval eval, String expected) { + assertTrue(eval instanceof StringEval); + assertEquals(expected, ((StringEval)eval).getStringValue()); + } + + private static void confirmOther(Eval arg) { + Eval eval = invokeT(arg); + confirmString(eval, ""); + } + + public void testOtherValues() { + confirmOther(new NumberEval(2)); + confirmOther(BoolEval.FALSE); + confirmOther(BlankEval.INSTANCE); // can this particular case be verified? + } + + public void testRefValues() { + Eval eval; + + eval = invokeTWithReference(new StringEval("def")); + confirmString(eval, "def"); + eval = invokeTWithReference(new StringEval(" ")); + confirmString(eval, " "); + + eval = invokeTWithReference(new NumberEval(2)); + confirmString(eval, ""); + eval = invokeTWithReference(BoolEval.TRUE); + confirmString(eval, ""); + + eval = invokeTWithReference(ErrorEval.NAME_INVALID); + assertTrue(eval == ErrorEval.NAME_INVALID); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java new file mode 100755 index 0000000000..076ac1fc7e --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestTrim.java @@ -0,0 +1,78 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +/** + * Tests for Excel function TRIM() + * + * @author Josh Micich + */ +public final class TestTrim extends TestCase { + + + private static Eval invokeTrim(Eval text) { + Eval[] args = new Eval[] { text, }; + return new Trim().evaluate(args, -1, (short)-1); + } + + private void confirmTrim(Eval text, String expected) { + Eval result = invokeTrim(text); + assertEquals(StringEval.class, result.getClass()); + assertEquals(expected, ((StringEval)result).getStringValue()); + } + + private void confirmTrim(Eval text, ErrorEval expectedError) { + Eval result = invokeTrim(text); + assertEquals(ErrorEval.class, result.getClass()); + assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode()); + } + + public void testBasic() { + + confirmTrim(new StringEval(" hi "), "hi"); + confirmTrim(new StringEval("hi "), "hi"); + confirmTrim(new StringEval(" hi"), "hi"); + confirmTrim(new StringEval(" hi there "), "hi there"); + confirmTrim(new StringEval(""), ""); + confirmTrim(new StringEval(" "), ""); + } + + /** + * Valid cases where text arg is not exactly a string + */ + public void testUnusualArgs() { + + // text (first) arg type is number, other args are strings with fractional digits + confirmTrim(new NumberEval(123456), "123456"); + confirmTrim(BoolEval.FALSE, "FALSE"); + confirmTrim(BoolEval.TRUE, "TRUE"); + confirmTrim(BlankEval.INSTANCE, ""); + } + + public void testErrors() { + confirmTrim(ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID); + } +} diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestXYNumericFunction.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestXYNumericFunction.java new file mode 100755 index 0000000000..c9f043bd3b --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestXYNumericFunction.java @@ -0,0 +1,139 @@ +/* ==================================================================== + 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.hssf.record.formula.functions; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.eval.Area2DEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +/** + * Tests for Excel functions SUMX2MY2(), SUMX2PY2(), SUMXMY2() + * + * @author Josh Micich + */ +public final class TestXYNumericFunction extends TestCase { + private static final Function SUM_SQUARES = new Sumx2py2(); + private static final Function DIFF_SQUARES = new Sumx2my2(); + private static final Function SUM_SQUARES_OF_DIFFS = new Sumxmy2(); + + private static Eval invoke(Function function, Eval xArray, Eval yArray) { + Eval[] args = new Eval[] { xArray, yArray, }; + return function.evaluate(args, -1, (short)-1); + } + + private void confirm(Function function, Eval xArray, Eval yArray, double expected) { + Eval result = invoke(function, xArray, yArray); + assertEquals(NumberEval.class, result.getClass()); + assertEquals(expected, ((NumberEval)result).getNumberValue(), 0); + } + private void confirmError(Function function, Eval xArray, Eval yArray, ErrorEval expectedError) { + Eval result = invoke(function, xArray, yArray); + assertEquals(ErrorEval.class, result.getClass()); + assertEquals(expectedError.getErrorCode(), ((ErrorEval)result).getErrorCode()); + } + + private void confirmError(Eval xArray, Eval yArray, ErrorEval expectedError) { + confirmError(SUM_SQUARES, xArray, yArray, expectedError); + confirmError(DIFF_SQUARES, xArray, yArray, expectedError); + confirmError(SUM_SQUARES_OF_DIFFS, xArray, yArray, expectedError); + } + + public void testBasic() { + ValueEval[] xValues = { + new NumberEval(1), + new NumberEval(2), + }; + ValueEval areaEvalX = createAreaEval(xValues); + confirm(SUM_SQUARES, areaEvalX, areaEvalX, 10.0); + confirm(DIFF_SQUARES, areaEvalX, areaEvalX, 0.0); + confirm(SUM_SQUARES_OF_DIFFS, areaEvalX, areaEvalX, 0.0); + + ValueEval[] yValues = { + new NumberEval(3), + new NumberEval(4), + }; + ValueEval areaEvalY = createAreaEval(yValues); + confirm(SUM_SQUARES, areaEvalX, areaEvalY, 30.0); + confirm(DIFF_SQUARES, areaEvalX, areaEvalY, -20.0); + confirm(SUM_SQUARES_OF_DIFFS, areaEvalX, areaEvalY, 8.0); + } + + /** + * number of items in array is not limited to 30 + */ + public void testLargeArrays() { + ValueEval[] xValues = createMockNumberArray(100, 3); + ValueEval[] yValues = createMockNumberArray(100, 2); + + confirm(SUM_SQUARES, createAreaEval(xValues), createAreaEval(yValues), 1300.0); + confirm(DIFF_SQUARES, createAreaEval(xValues), createAreaEval(yValues), 500.0); + confirm(SUM_SQUARES_OF_DIFFS, createAreaEval(xValues), createAreaEval(yValues), 100.0); + } + + + private ValueEval[] createMockNumberArray(int size, double value) { + ValueEval[] result = new ValueEval[size]; + for (int i = 0; i < result.length; i++) { + result[i] = new NumberEval(value); + } + return result; + } + + private static ValueEval createAreaEval(ValueEval[] values) { + String refStr = "A1:A" + values.length; + return new Area2DEval(new AreaPtg(refStr), values); + } + + public void testErrors() { + ValueEval[] xValues = { + ErrorEval.REF_INVALID, + new NumberEval(2), + }; + ValueEval areaEvalX = createAreaEval(xValues); + ValueEval[] yValues = { + new NumberEval(2), + ErrorEval.NULL_INTERSECTION, + }; + ValueEval areaEvalY = createAreaEval(yValues); + ValueEval[] zValues = { // wrong size + new NumberEval(2), + }; + ValueEval areaEvalZ = createAreaEval(zValues); + + // if either arg is an error, that error propagates + confirmError(ErrorEval.REF_INVALID, ErrorEval.NAME_INVALID, ErrorEval.REF_INVALID); + confirmError(areaEvalX, ErrorEval.NAME_INVALID, ErrorEval.NAME_INVALID); + confirmError(ErrorEval.NAME_INVALID, areaEvalX, ErrorEval.NAME_INVALID); + + // array sizes must match + confirmError(areaEvalX, areaEvalZ, ErrorEval.NA); + confirmError(areaEvalZ, areaEvalY, ErrorEval.NA); + + // any error in an array item propagates up + confirmError(areaEvalX, areaEvalX, ErrorEval.REF_INVALID); + + // search for errors array by array, not pair by pair + confirmError(areaEvalX, areaEvalY, ErrorEval.REF_INVALID); + confirmError(areaEvalY, areaEvalX, ErrorEval.NULL_INTERSECTION); + + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java new file mode 100644 index 0000000000..c849fd4369 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java @@ -0,0 +1,94 @@ +/* ==================================================================== + 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.hssf.usermodel; + +import java.io.File; +import java.io.FileInputStream; +import java.util.Iterator; +import java.util.List; + +import junit.framework.TestCase; + +import org.apache.poi.hssf.record.FormulaRecord; +import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue; +import org.apache.poi.hssf.util.CellReference; + +public final class TestBug42464 extends TestCase { + String dirname; + + protected void setUp() throws Exception { + super.setUp(); + dirname = System.getProperty("HSSF.testdata.path"); + } + + public void testOKFile() throws Exception { + HSSFWorkbook wb = new HSSFWorkbook( + new FileInputStream(new File(dirname,"42464-ExpPtg-ok.xls")) + ); + process(wb); + } + public void testExpSharedBadFile() throws Exception { + HSSFWorkbook wb = new HSSFWorkbook( + new FileInputStream(new File(dirname,"42464-ExpPtg-bad.xls")) + ); + process(wb); + } + + protected void process(HSSFWorkbook wb) { + for(int i=0; i " + cell.getCellFormula()); + } + + CellValue evalResult = eval.evaluate(cell); + assertNotNull(evalResult); + } + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBug43093.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBug43093.java new file mode 100644 index 0000000000..ce072e9e01 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBug43093.java @@ -0,0 +1,59 @@ +/* ==================================================================== + 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.hssf.usermodel; + +import junit.framework.TestCase; + +public class TestBug43093 extends TestCase { + + private static void addNewSheetWithCellsA1toD4(HSSFWorkbook book, int sheet) { + + HSSFSheet sht = book .createSheet("s" + sheet); + for (short r=0; r < 4; r++) { + + HSSFRow row = sht.createRow (r); + for (short c=0; c < 4; c++) { + + HSSFCell cel = row.createCell(c); + /**/ cel.setCellValue(sheet*100 + r*10 + c); + } + } + } + + public void testBug43093() throws Exception { + HSSFWorkbook xlw = new HSSFWorkbook(); + + addNewSheetWithCellsA1toD4(xlw, 1); + addNewSheetWithCellsA1toD4(xlw, 2); + addNewSheetWithCellsA1toD4(xlw, 3); + addNewSheetWithCellsA1toD4(xlw, 4); + + HSSFSheet s2 = xlw.getSheet("s2"); + HSSFRow s2r3 = s2.getRow(3); + HSSFCell s2E4 = s2r3.createCell((short)4); + /**/ s2E4.setCellFormula("SUM(s3!B2:C3)"); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(s2, xlw); + eva.setCurrentRow(s2r3); + double d = eva.evaluate(s2E4).getNumberValue(); + + // internalEvaluate(...) Area3DEval.: 311+312+321+322 expected + assertEquals(d, (double)(311+312+321+322), 0.0000001); + // System.out.println("Area3DEval ok.: 311+312+321+322=" + d); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java new file mode 100644 index 0000000000..87a760f714 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorBugs.java @@ -0,0 +1,257 @@ +/* ==================================================================== + 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.hssf.usermodel; + +import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.FuncVarPtg; + +import java.io.File; +import java.io.FileInputStream; +import java.io.FileOutputStream; +import java.io.IOException; +import java.util.Iterator; +import java.util.List; + +import junit.framework.TestCase; + +public final class TestFormulaEvaluatorBugs extends TestCase { + private String dirName; + private String tmpDirName; + + protected void setUp() throws Exception { + super.setUp(); + dirName = System.getProperty("HSSF.testdata.path"); + tmpDirName = System.getProperty("java.io.tmpdir"); + } + + /** + * An odd problem with evaluateFormulaCell giving the + * right values when file is opened, but changes + * to the source data in some versions of excel + * doesn't cause them to be updated. However, other + * versions of excel, and gnumeric, work just fine + * WARNING - tedious bug where you actually have to + * open up excel + */ + public void test44636() throws Exception { + // Open the existing file, tweak one value and + // re-calculate + FileInputStream in = new FileInputStream(new File(dirName,"44636.xls")); + HSSFWorkbook wb = new HSSFWorkbook(in); + HSSFSheet sheet = wb.getSheetAt (0); + HSSFRow row = sheet.getRow (0); + + row.getCell((short)0).setCellValue(4.2); + row.getCell((short)2).setCellValue(25); + + HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); + assertEquals(4.2*25, row.getCell((short)3).getNumericCellValue(), 0.0001); + + // Save + File existing = new File(tmpDirName,"44636-existing.xls"); + FileOutputStream out = new FileOutputStream(existing); + wb.write(out); + out.close(); + System.err.println("Existing file for bug #44636 written to " + existing.toString()); + + + // Now, do a new file from scratch + wb = new HSSFWorkbook(); + sheet = wb.createSheet(); + + row = sheet.createRow(0); + row.createCell((short)0).setCellValue(1.2); + row.createCell((short)1).setCellValue(4.2); + + row = sheet.createRow(1); + row.createCell((short)0).setCellFormula("SUM(A1:B1)"); + + HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); + assertEquals(5.4, row.getCell((short)0).getNumericCellValue(), 0.0001); + + // Save + File scratch = new File(tmpDirName,"44636-scratch.xls"); + out = new FileOutputStream(scratch); + wb.write(out); + out.close(); + System.err.println("New file for bug #44636 written to " + scratch.toString()); + } + + /** + * Bug 44297: 32767+32768 is evaluated to -1 + * Fix: IntPtg must operate with unsigned short. Reading signed short results in incorrect formula calculation + * if a formula has values in the interval [Short.MAX_VALUE, (Short.MAX_VALUE+1)*2] + * + * @author Yegor Kozlov + */ + public void test44297() throws IOException { + FileInputStream in = new FileInputStream(new File(dirName, "44297.xls")); + HSSFWorkbook wb = new HSSFWorkbook(in); + in.close(); + + HSSFRow row; + HSSFCell cell; + + HSSFSheet sheet = wb.getSheetAt(0); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); + + row = (HSSFRow)sheet.getRow(0); + cell = row.getCell((short)0); + assertEquals("31+46", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(77, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(1); + cell = row.getCell((short)0); + assertEquals("30+53", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(83, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(2); + cell = row.getCell((short)0); + assertEquals("SUM(A1:A2)", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(160, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(4); + cell = row.getCell((short)0); + assertEquals("32767+32768", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(65535, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(7); + cell = row.getCell((short)0); + assertEquals("32744+42333", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(75077, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(8); + cell = row.getCell((short)0); + assertEquals("327680.0/32768", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(10, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(9); + cell = row.getCell((short)0); + assertEquals("32767+32769", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(65536, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(10); + cell = row.getCell((short)0); + assertEquals("35000+36000", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(71000, eva.evaluate(cell).getNumberValue(), 0); + + row = (HSSFRow)sheet.getRow(11); + cell = row.getCell((short)0); + assertEquals("-1000000.0-3000000.0", cell.getCellFormula()); + eva.setCurrentRow(row); + assertEquals(-4000000, eva.evaluate(cell).getNumberValue(), 0); + } + + /** + * Bug 44410: SUM(C:C) is valid in excel, and means a sum + * of all the rows in Column C + * + * @author Nick Burch + */ + public void test44410() throws IOException { + FileInputStream in = new FileInputStream(new File(dirName, "SingleLetterRanges.xls")); + HSSFWorkbook wb = new HSSFWorkbook(in); + in.close(); + + HSSFSheet sheet = wb.getSheetAt(0); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); + + // =index(C:C,2,1) -> 2 + HSSFRow rowIDX = (HSSFRow)sheet.getRow(3); + // =sum(C:C) -> 6 + HSSFRow rowSUM = (HSSFRow)sheet.getRow(4); + // =sum(C:D) -> 66 + HSSFRow rowSUM2D = (HSSFRow)sheet.getRow(5); + + // Test the sum + HSSFCell cellSUM = rowSUM.getCell((short)0); + + FormulaRecordAggregate frec = + (FormulaRecordAggregate)cellSUM.getCellValueRecord(); + List ops = frec.getFormulaRecord().getParsedExpression(); + assertEquals(2, ops.size()); + assertEquals(AreaPtg.class, ops.get(0).getClass()); + assertEquals(FuncVarPtg.class, ops.get(1).getClass()); + + // Actually stored as C1 to C65536 + // (last row is -1 === 65535) + AreaPtg ptg = (AreaPtg)ops.get(0); + assertEquals(2, ptg.getFirstColumn()); + assertEquals(2, ptg.getLastColumn()); + assertEquals(0, ptg.getFirstRow()); + assertEquals(65535, ptg.getLastRow()); + assertEquals("C:C", ptg.toFormulaString(wb.getWorkbook())); + + // Will show as C:C, but won't know how many + // rows it covers as we don't have the sheet + // to hand when turning the Ptgs into a string + assertEquals("SUM(C:C)", cellSUM.getCellFormula()); + eva.setCurrentRow(rowSUM); + + // But the evaluator knows the sheet, so it + // can do it properly + assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0); + + + // Test the index + // Again, the formula string will be right but + // lacking row count, evaluated will be right + HSSFCell cellIDX = rowIDX.getCell((short)0); + assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula()); + eva.setCurrentRow(rowIDX); + assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0); + + // Across two colums + HSSFCell cellSUM2D = rowSUM2D.getCell((short)0); + assertEquals("SUM(C:D)", cellSUM2D.getCellFormula()); + eva.setCurrentRow(rowSUM2D); + assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0); + } + + /** + * Tests that we can evaluate boolean cells properly + */ + public void testEvaluateBooleanInCell_bug44508() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet(); + wb.setSheetName(0, "Sheet1"); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell((short)0); + + cell.setCellFormula("1=1"); + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb); + fe.setCurrentRow(row); + try { + fe.evaluateInCell(cell); + } catch (NumberFormatException e) { + fail("Identified bug 44508"); + } + assertEquals(true, cell.getBooleanCellValue()); + } +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java new file mode 100644 index 0000000000..6c2e3b6412 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java @@ -0,0 +1,117 @@ +package org.apache.poi.hssf.usermodel; + +import java.util.Iterator; + +import junit.framework.TestCase; + +/** + * Tests to show that our documentation at + * http://poi.apache.org/hssf/eval.html + * all actually works as we'd expect them to + */ +public class TestFormulaEvaluatorDocs extends TestCase { + protected void setUp() throws Exception { + super.setUp(); + } + + /** + * http://poi.apache.org/hssf/eval.html#EvaluateAll + */ + public void testEvaluateAll() throws Exception { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet s1 = wb.createSheet(); + HSSFSheet s2 = wb.createSheet(); + wb.setSheetName(0, "S1"); + wb.setSheetName(1, "S2"); + + HSSFRow s1r1 = s1.createRow(0); + HSSFRow s1r2 = s1.createRow(1); + HSSFRow s2r1 = s2.createRow(0); + + HSSFCell s1r1c1 = s1r1.createCell((short)0); + HSSFCell s1r1c2 = s1r1.createCell((short)1); + HSSFCell s1r1c3 = s1r1.createCell((short)2); + s1r1c1.setCellValue(22.3); + s1r1c2.setCellValue(33.4); + s1r1c3.setCellFormula("SUM(A1:B1)"); + + HSSFCell s1r2c1 = s1r2.createCell((short)0); + HSSFCell s1r2c2 = s1r2.createCell((short)1); + HSSFCell s1r2c3 = s1r2.createCell((short)2); + s1r2c1.setCellValue(-1.2); + s1r2c2.setCellValue(-3.4); + s1r2c3.setCellFormula("SUM(A2:B2)"); + + HSSFCell s2r1c1 = s2r1.createCell((short)0); + s2r1c1.setCellFormula("S1!A1"); + + // Not evaluated yet + assertEquals(0.0, s1r1c3.getNumericCellValue(), 0); + assertEquals(0.0, s1r2c3.getNumericCellValue(), 0); + assertEquals(0.0, s2r1c1.getNumericCellValue(), 0); + + // Do a full evaluate, as per our docs + // uses evaluateFormulaCell() + for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { + HSSFSheet sheet = wb.getSheetAt(sheetNum); + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); + + for(Iterator rit = sheet.rowIterator(); rit.hasNext();) { + HSSFRow r = (HSSFRow)rit.next(); + evaluator.setCurrentRow(r); + + for(Iterator cit = r.cellIterator(); cit.hasNext();) { + HSSFCell c = (HSSFCell)cit.next(); + if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { + evaluator.evaluateFormulaCell(c); + + // For testing - all should be numeric + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, evaluator.evaluateFormulaCell(c)); + } + } + } + } + + // Check now as expected + assertEquals(55.7, wb.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0); + assertEquals("SUM(A1:B1)", wb.getSheetAt(0).getRow(0).getCell((short)2).getCellFormula()); + assertEquals(HSSFCell.CELL_TYPE_FORMULA, wb.getSheetAt(0).getRow(0).getCell((short)2).getCellType()); + + assertEquals(-4.6, wb.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0); + assertEquals("SUM(A2:B2)", wb.getSheetAt(0).getRow(1).getCell((short)2).getCellFormula()); + assertEquals(HSSFCell.CELL_TYPE_FORMULA, wb.getSheetAt(0).getRow(1).getCell((short)2).getCellType()); + + assertEquals(22.3, wb.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0); + assertEquals("'S1'!A1", wb.getSheetAt(1).getRow(0).getCell((short)0).getCellFormula()); + assertEquals(HSSFCell.CELL_TYPE_FORMULA, wb.getSheetAt(1).getRow(0).getCell((short)0).getCellType()); + + + // Now do the alternate call, which zaps the formulas + // uses evaluateInCell() + for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { + HSSFSheet sheet = wb.getSheetAt(sheetNum); + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); + + for(Iterator rit = sheet.rowIterator(); rit.hasNext();) { + HSSFRow r = (HSSFRow)rit.next(); + evaluator.setCurrentRow(r); + + for(Iterator cit = r.cellIterator(); cit.hasNext();) { + HSSFCell c = (HSSFCell)cit.next(); + if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { + evaluator.evaluateInCell(c); + } + } + } + } + + assertEquals(55.7, wb.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0); + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, wb.getSheetAt(0).getRow(0).getCell((short)2).getCellType()); + + assertEquals(-4.6, wb.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0); + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, wb.getSheetAt(0).getRow(1).getCell((short)2).getCellType()); + + assertEquals(22.3, wb.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0); + assertEquals(HSSFCell.CELL_TYPE_NUMERIC, wb.getSheetAt(1).getRow(0).getCell((short)0).getCellType()); + } +} -- cgit v1.2.3