You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2010/08/12 16:14:26 UTC
svn commit: r984796 - in /poi/trunk: src/documentation/content/xdocs/
src/java/org/apache/poi/hssf/record/formula/atp/
src/testcases/org/apache/poi/hssf/record/formula/atp/ test-data/spreadsheet/
Author: yegor
Date: Thu Aug 12 14:14:26 2010
New Revision: 984796
URL: http://svn.apache.org/viewvc?rev=984796&view=rev
Log:
added support for RANDBETWEEN(), see Bugzilla 48526
Added:
poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/RandBetween.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/atp/TestRandBetween.java
poi/trunk/test-data/spreadsheet/TestRandBetween.xls (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=984796&r1=984795&r2=984796&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Thu Aug 12 14:14:26 2010
@@ -34,6 +34,7 @@
<changes>
<release version="3.7-beta3" date="2010-??-??">
+ <action dev="POI-DEVELOPERS" type="add">48526 - added implementation for RANDBETWEEN()</action>
<action dev="POI-DEVELOPERS" type="fix">49725 - avoid exception in OperandResolver.parseDouble when input is minus ("-")</action>
<action dev="POI-DEVELOPERS" type="fix">49723 - fixed OperandResolver to correctly handle inputs with leading decimal place</action>
<action dev="POI-DEVELOPERS" type="add">initial support for Excel autofilter</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java?rev=984796&r1=984795&r2=984796&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java Thu Aug 12 14:14:26 2010
@@ -138,7 +138,7 @@ public final class AnalysisToolPak imple
r(m, "PRICEDISC", null);
r(m, "PRICEMAT", null);
r(m, "QUOTIENT", null);
- r(m, "RAND BETWEEN", null);
+ r(m, "RANDBETWEEN", RandBetween.instance);
r(m, "RECEIVED", null);
r(m, "SERIESSUM", null);
r(m, "SQRTPI", null);
Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/RandBetween.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/RandBetween.java?rev=984796&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/RandBetween.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/atp/RandBetween.java Thu Aug 12 14:14:26 2010
@@ -0,0 +1,85 @@
+/* ====================================================================
+ 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.atp;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.OperandResolver;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+
+/**
+ * Implementation of Excel 'Analysis ToolPak' function RANDBETWEEN()<br/>
+ *
+ * Returns a random integer number between the numbers you specify.<p/>
+ *
+ * <b>Syntax</b><br/>
+ * <b>RANDBETWEEN</b>(<b>bottom</b>, <b>top</b>)<p/>
+ *
+ * <b>bottom</b> is the smallest integer RANDBETWEEN will return.<br/>
+ * <b>top</b> is the largest integer RANDBETWEEN will return.<br/>
+
+ * @author Brendan Nolan
+ */
+final class RandBetween implements FreeRefFunction{
+
+ public static final FreeRefFunction instance = new RandBetween();
+
+ private RandBetween() {
+ //enforces singleton
+ }
+
+ /**
+ * Evaluate for RANDBETWEEN(). Must be given two arguments. Bottom must be greater than top.
+ * Bottom is rounded up and top value is rounded down. After rounding top has to be set greater
+ * than top.
+ *
+ * @see org.apache.poi.hssf.record.formula.functions.FreeRefFunction#evaluate(org.apache.poi.hssf.record.formula.eval.ValueEval[], org.apache.poi.ss.formula.OperationEvaluationContext)
+ */
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
+
+ double bottom, top;
+
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ try {
+ bottom = OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()));
+ top = OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex()));
+ if(bottom > top) {
+ return ErrorEval.NUM_ERROR;
+ }
+ } catch (EvaluationException e) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ bottom = Math.ceil(bottom);
+ top = Math.floor(top);
+
+ if(bottom > top) {
+ top = bottom;
+ }
+
+ return new NumberEval((bottom + (int)(Math.random() * ((top - bottom) + 1))));
+
+ }
+
+}
Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/atp/TestRandBetween.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/atp/TestRandBetween.java?rev=984796&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/atp/TestRandBetween.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/atp/TestRandBetween.java Thu Aug 12 14:14:26 2010
@@ -0,0 +1,194 @@
+/* ====================================================================
+ 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.atp;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+
+/**
+ * Testcase for 'Analysis Toolpak' function RANDBETWEEN()
+ *
+ * @author Brendan Nolan
+ */
+public class TestRandBetween extends TestCase {
+
+ private Workbook wb;
+ private FormulaEvaluator evaluator;
+ private Cell bottomValueCell;
+ private Cell topValueCell;
+ private Cell formulaCell;
+
+ @Override
+ protected void setUp() throws Exception {
+ super.setUp();
+ wb = HSSFTestDataSamples.openSampleWorkbook("TestRandBetween.xls");
+ evaluator = wb.getCreationHelper().createFormulaEvaluator();
+
+ Sheet sheet = wb.createSheet("RandBetweenSheet");
+ Row row = sheet.createRow(0);
+ bottomValueCell = row.createCell(0);
+ topValueCell = row.createCell(1);
+ formulaCell = row.createCell(2, HSSFCell.CELL_TYPE_FORMULA);
+ }
+
+ @Override
+ protected void tearDown() throws Exception {
+ // TODO Auto-generated method stub
+ super.tearDown();
+ }
+
+ /**
+ * Check where values are the same
+ */
+ public void testRandBetweenSameValues() {
+
+ evaluator.clearAllCachedResultValues();
+ formulaCell.setCellFormula("RANDBETWEEN(1,1)");
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(1, formulaCell.getNumericCellValue(), 0);
+ evaluator.clearAllCachedResultValues();
+ formulaCell.setCellFormula("RANDBETWEEN(-1,-1)");
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(-1, formulaCell.getNumericCellValue(), 0);
+
+ }
+
+ /**
+ * Check special case where rounded up bottom value is greater than
+ * top value.
+ */
+ public void testRandBetweenSpecialCase() {
+
+
+ bottomValueCell.setCellValue(0.05);
+ topValueCell.setCellValue(0.1);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(1, formulaCell.getNumericCellValue(), 0);
+ bottomValueCell.setCellValue(-0.1);
+ topValueCell.setCellValue(-0.05);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(0, formulaCell.getNumericCellValue(), 0);
+ bottomValueCell.setCellValue(-1.1);
+ topValueCell.setCellValue(-1.05);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(-1, formulaCell.getNumericCellValue(), 0);
+ bottomValueCell.setCellValue(-1.1);
+ topValueCell.setCellValue(-1.1);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(-1, formulaCell.getNumericCellValue(), 0);
+ }
+
+ /**
+ * Check top value of BLANK which Excel will evaluate as 0
+ */
+ public void testRandBetweenTopBlank() {
+
+ bottomValueCell.setCellValue(-1);
+ topValueCell.setCellType(Cell.CELL_TYPE_BLANK);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertTrue(formulaCell.getNumericCellValue() == 0 || formulaCell.getNumericCellValue() == -1);
+
+ }
+ /**
+ * Check where input values are of wrong type
+ */
+ public void testRandBetweenWrongInputTypes() {
+ // Check case where bottom input is of the wrong type
+ bottomValueCell.setCellValue("STRING");
+ topValueCell.setCellValue(1);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(Cell.CELL_TYPE_ERROR, formulaCell.getCachedFormulaResultType());
+ assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
+
+
+ // Check case where top input is of the wrong type
+ bottomValueCell.setCellValue(1);
+ topValueCell.setCellValue("STRING");
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(Cell.CELL_TYPE_ERROR, formulaCell.getCachedFormulaResultType());
+ assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
+
+ // Check case where both inputs are of wrong type
+ bottomValueCell.setCellValue("STRING");
+ topValueCell.setCellValue("STRING");
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(Cell.CELL_TYPE_ERROR, formulaCell.getCachedFormulaResultType());
+ assertEquals(ErrorEval.VALUE_INVALID.getErrorCode(), formulaCell.getErrorCellValue());
+
+ }
+
+ /**
+ * Check case where bottom is greater than top
+ */
+ public void testRandBetweenBottomGreaterThanTop() {
+
+ // Check case where bottom is greater than top
+ bottomValueCell.setCellValue(1);
+ topValueCell.setCellValue(0);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(Cell.CELL_TYPE_ERROR, formulaCell.getCachedFormulaResultType());
+ assertEquals(ErrorEval.NUM_ERROR.getErrorCode(), formulaCell.getErrorCellValue());
+ bottomValueCell.setCellValue(1);
+ topValueCell.setCellType(Cell.CELL_TYPE_BLANK);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertEquals(Cell.CELL_TYPE_ERROR, formulaCell.getCachedFormulaResultType());
+ assertEquals(ErrorEval.NUM_ERROR.getErrorCode(), formulaCell.getErrorCellValue());
+ }
+
+ /**
+ * Boundary check of Double MIN and MAX values
+ */
+ public void testRandBetweenBoundaryCheck() {
+
+ bottomValueCell.setCellValue(Double.MIN_VALUE);
+ topValueCell.setCellValue(Double.MAX_VALUE);
+ formulaCell.setCellFormula("RANDBETWEEN($A$1,$B$1)");
+ evaluator.clearAllCachedResultValues();
+ evaluator.evaluateFormulaCell(formulaCell);
+ assertTrue(formulaCell.getNumericCellValue() >= Double.MIN_VALUE && formulaCell.getNumericCellValue() <= Double.MAX_VALUE);
+
+ }
+
+}
Added: poi/trunk/test-data/spreadsheet/TestRandBetween.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/TestRandBetween.xls?rev=984796&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/TestRandBetween.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org