Author: yegor
Date: Sat Dec 11 12:41:17 2010
New Revision: 1044642
URL: http://svn.apache.org/viewvc?rev=1044642&view=rev
Log:
Added implementation for MROUND(), VAR() and VARP()
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java
- copied, changed from r1042717,
poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestMRound.java
- copied, changed from r1042717,
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestRandBetween.java
Modified:
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/NumericFunction.java
poi/trunk/src/java/org/apache/poi/ss/formula/functions/StatsLib.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestStatsLib.java
poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sat Dec 11 12:41:17
2010
@@ -34,6 +34,7 @@
<changes>
<release version="3.8-beta1" date="2010-??-??">
+ <action dev="POI-DEVELOPERS" type="add">48539 - Added
implementation for MROUND(), VAR() and VARP()</action>
<action dev="POI-DEVELOPERS" type="add">50446 - Code cleanup and
optimizations to keep some IDE quiet</action>
<action dev="POI-DEVELOPERS" type="add">50437 - Support passing
ranges to NPV()</action>
<action dev="POI-DEVELOPERS" type="add">50409 - Added
implementation for IRR()</action>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Sat
Dec 11 12:41:17 2010
@@ -138,7 +138,7 @@ public final class AnalysisToolPak imple
r(m, "JIS", null);
r(m, "LCM", null);
r(m, "MDURATION", null);
- r(m, "MROUND", null);
+ r(m, "MROUND", MRound.instance);
r(m, "MULTINOMIAL", null);
r(m, "NETWORKDAYS", null);
r(m, "NOMINAL", null);
Copied: poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java (from
r1042717, poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java)
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java?p2=poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java&p1=poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java&r1=1042717&r2=1044642&rev=1044642&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/atp/YearFrac.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/MRound.java Sat Dec 11
12:41:17 2010
@@ -17,143 +17,60 @@
package org.apache.poi.ss.formula.atp;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.*;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.functions.NumericFunction;
+import org.apache.poi.ss.usermodel.DateUtil;
+
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.regex.Pattern;
-import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.formula.eval.EvaluationException;
-import org.apache.poi.ss.formula.eval.NumberEval;
-import org.apache.poi.ss.formula.eval.OperandResolver;
-import org.apache.poi.ss.formula.eval.StringEval;
-import org.apache.poi.ss.formula.eval.ValueEval;
-import org.apache.poi.ss.formula.functions.FreeRefFunction;
-import org.apache.poi.ss.formula.OperationEvaluationContext;
-import org.apache.poi.ss.usermodel.DateUtil;
/**
- * Implementation of Excel 'Analysis ToolPak' function YEARFRAC()<br/>
+ * Implementation of Excel 'Analysis ToolPak' function MROUND()<br/>
*
- * Returns the fraction of the year spanned by two dates.<p/>
+ * Returns a number rounded to the desired multiple.<p/>
*
* <b>Syntax</b><br/>
- * <b>YEARFRAC</b>(<b>startDate</b>, <b>endDate</b>, basis)<p/>
+ * <b>MROUND</b>(<b>number</b>, <b>multiple</b>)
*
- * The <b>basis</b> optionally specifies the behaviour of YEARFRAC as follows:
- *
- * <table border="0" cellpadding="1" cellspacing="0" summary="basis parameter
description">
- * <tr><th>Value</th><th>Days per Month</th><th>Days per Year</th></tr>
- * <tr align='center'><td>0 (default)</td><td>30</td><td>360</td></tr>
- * <tr align='center'><td>1</td><td>actual</td><td>actual</td></tr>
- * <tr align='center'><td>2</td><td>actual</td><td>360</td></tr>
- * <tr align='center'><td>3</td><td>actual</td><td>365</td></tr>
- * <tr align='center'><td>4</td><td>30</td><td>360</td></tr>
- * </table>
+ * <p/>
*
+ * @author Yegor Kozlov
*/
-final class YearFrac implements FreeRefFunction {
+final class MRound implements FreeRefFunction {
- public static final FreeRefFunction instance = new YearFrac();
+ public static final FreeRefFunction instance = new MRound();
- private YearFrac() {
+ private MRound() {
// enforce singleton
}
public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext
ec) {
- int srcCellRow = ec.getRowIndex();
- int srcCellCol = ec.getColumnIndex();
- double result;
- try {
- int basis = 0; // default
- switch(args.length) {
- case 3:
- basis = evaluateIntArg(args[2],
srcCellRow, srcCellCol);
- case 2:
- break;
- default:
- return ErrorEval.VALUE_INVALID;
- }
- double startDateVal = evaluateDateArg(args[0],
srcCellRow, srcCellCol);
- double endDateVal = evaluateDateArg(args[1],
srcCellRow, srcCellCol);
- result = YearFracCalculator.calculate(startDateVal,
endDateVal, basis);
- } catch (EvaluationException e) {
- return e.getErrorEval();
- }
-
- return new NumberEval(result);
- }
-
- private static double evaluateDateArg(ValueEval arg, int srcCellRow,
int srcCellCol) throws EvaluationException {
- ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow,
(short) srcCellCol);
-
- if (ve instanceof StringEval) {
- String strVal = ((StringEval) ve).getStringValue();
- Double dVal = OperandResolver.parseDouble(strVal);
- if (dVal != null) {
- return dVal.doubleValue();
- }
- Calendar date = parseDate(strVal);
- return DateUtil.getExcelDate(date, false);
- }
- return OperandResolver.coerceValueToDouble(ve);
- }
-
- private static Calendar parseDate(String strVal) throws
EvaluationException {
- String[] parts = Pattern.compile("/").split(strVal);
- if (parts.length != 3) {
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
- }
- String part2 = parts[2];
- int spacePos = part2.indexOf(' ');
- if (spacePos > 0) {
- // drop time portion if present
- part2 = part2.substring(0, spacePos);
- }
- int f0;
- int f1;
- int f2;
- try {
- f0 = Integer.parseInt(parts[0]);
- f1 = Integer.parseInt(parts[1]);
- f2 = Integer.parseInt(part2);
- } catch (NumberFormatException e) {
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
- }
- if (f0<0 || f1<0 || f2<0 || (f0>12 && f1>12 && f2>12)) {
- // easy to see this cannot be a valid date
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
- }
-
- if (f0 >= 1900 && f0 < 9999) {
- // when 4 digit value appears first, the format is
YYYY/MM/DD, regardless of OS settings
- return makeDate(f0, f1, f2);
- }
- // otherwise the format seems to depend on OS settings (default
date format)
- if (false) {
- // MM/DD/YYYY is probably a good guess, if the in the US
- return makeDate(f2, f0, f1);
- }
- // TODO - find a way to choose the correct date format
- throw new RuntimeException("Unable to determine date format for
text '" + strVal + "'");
- }
-
- /**
- * @param month 1-based
- */
- private static Calendar makeDate(int year, int month, int day) throws
EvaluationException {
- if (month < 1 || month > 12) {
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
- }
- Calendar cal = new GregorianCalendar(year, month-1, 1, 0, 0, 0);
- cal.set(Calendar.MILLISECOND, 0);
- if (day <1 || day>cal.getActualMaximum(Calendar.DAY_OF_MONTH)) {
- throw new EvaluationException(ErrorEval.VALUE_INVALID);
- }
- cal.set(Calendar.DAY_OF_MONTH, day);
- return cal;
- }
+ double number, multiple, result;
- private static int evaluateIntArg(ValueEval arg, int srcCellRow, int
srcCellCol) throws EvaluationException {
- ValueEval ve = OperandResolver.getSingleValue(arg, srcCellRow,
(short) srcCellCol);
- return OperandResolver.coerceValueToInt(ve);
+ if (args.length != 2) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ try {
+ number =
OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[0],
ec.getRowIndex(), ec.getColumnIndex()));
+ multiple =
OperandResolver.coerceValueToDouble(OperandResolver.getSingleValue(args[1],
ec.getRowIndex(), ec.getColumnIndex()));
+
+ if( multiple == 0.0 ) {
+ result = 0.0;
+ } else {
+ if(number*multiple < 0) {
+ // Returns #NUM! because the number and the multiple have
different signs
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ result = multiple * Math.round( number / multiple );
+ }
+ NumericFunction.checkValue(result);
+ return new NumberEval(result);
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
}
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Sat Dec
11 12:41:17 2010
@@ -93,6 +93,8 @@ public final class FunctionEval {
retval[37] = BooleanFunction.OR;
retval[38] = BooleanFunction.NOT;
retval[39] = NumericFunction.MOD;
+
+ retval[46] = AggregateFunction.VAR;
retval[48] = TextFunction.TEXT;
retval[56] = FinanceFunction.PV;
@@ -153,6 +155,7 @@ public final class FunctionEval {
retval[184] = NumericFunction.FACT;
retval[190] = LogicalFunction.ISNONTEXT;
+ retval[194] = AggregateFunction.VARP;
retval[197] = NumericFunction.TRUNC;
retval[198] = LogicalFunction.ISLOGICAL;
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
(original)
+++
poi/trunk/src/java/org/apache/poi/ss/formula/functions/AggregateFunction.java
Sat Dec 11 12:41:17 2010
@@ -142,4 +142,20 @@ public abstract class AggregateFunction
return MathX.sumsq(values);
}
};
+ public static final Function VAR = new AggregateFunction() {
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.var(values);
+ }
+ };
+ public static final Function VARP = new AggregateFunction() {
+ protected double evaluate(double[] values) throws EvaluationException {
+ if (values.length < 1) {
+ throw new EvaluationException(ErrorEval.DIV_ZERO);
+ }
+ return StatsLib.varp(values);
+ }
+ };
}
Modified:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/NumericFunction.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/NumericFunction.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/NumericFunction.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/NumericFunction.java
Sat Dec 11 12:41:17 2010
@@ -43,7 +43,7 @@ public abstract class NumericFunction im
/**
* @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</>
or <tt>Infinity</tt>
*/
- static final void checkValue(double result) throws EvaluationException {
+ public static final void checkValue(double result) throws
EvaluationException {
if (Double.isNaN(result) || Double.isInfinite(result)) {
throw new EvaluationException(ErrorEval.NUM_ERROR);
}
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/StatsLib.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/StatsLib.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/StatsLib.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/StatsLib.java Sat
Dec 11 12:41:17 2010
@@ -59,6 +59,21 @@ final class StatsLib {
return r;
}
+ public static double var(double[] v) {
+ double r = Double.NaN;
+ if (v!=null && v.length > 1) {
+ r = devsq(v) / (v.length - 1);
+ }
+ return r;
+ }
+
+ public static double varp(double[] v) {
+ double r = Double.NaN;
+ if (v!=null && v.length > 1) {
+ r = devsq(v) /v.length;
+ }
+ return r;
+ }
public static double median(double[] v) {
double r = Double.NaN;
Modified:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
---
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
(original)
+++
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
Sat Dec 11 12:41:17 2010
@@ -26,16 +26,8 @@ import org.apache.poi.hssf.usermodel.HSS
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
-import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.DataFormatter;
-import org.apache.poi.ss.usermodel.Font;
-import org.apache.poi.ss.usermodel.FormulaError;
-import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.Name;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.formula.eval.NotImplementedException;
import org.apache.poi.xssf.XSSFITestDataProvider;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.model.CalculationChain;
@@ -212,8 +204,10 @@ public final class TestXSSFBugs extends
* NameXPtgs.
* Blows up on:
* IF(B6= (ROUNDUP(B6,0) + ROUNDDOWN(B6,0))/2, MROUND(B6,2),ROUND(B6,0))
+ *
+ * TODO: delete this test case when MROUND and VAR are implemented
*/
- public void DISABLEDtest48539() throws Exception {
+ public void test48539() throws Exception {
XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("48539.xlsx");
assertEquals(3, wb.getNumberOfSheets());
@@ -224,14 +218,21 @@ public final class TestXSSFBugs extends
for(Row r : s) {
for(Cell c : r) {
if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
- eval.evaluate(c);
+ CellValue cv = eval.evaluate(c);
+ if(cv.getCellType() == Cell.CELL_TYPE_NUMERIC) {
+ // assert that the calculated value agrees with
+ // the cached formula result calculated by Excel
+ double cachedFormulaResult = c.getNumericCellValue();
+ double evaluatedFormulaResult = cv.getNumberValue();
+ assertEquals(c.getCellFormula(), cachedFormulaResult,
evaluatedFormulaResult, 1E-7);
+ }
}
}
}
}
// Now all of them
- XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
+ XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
}
/**
Copied: poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestMRound.java
(from r1042717,
poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestRandBetween.java)
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestMRound.java?p2=poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestMRound.java&p1=poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestRandBetween.java&r1=1042717&r2=1044642&rev=1044642&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestRandBetween.java
(original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/atp/TestMRound.java Sat
Dec 11 12:41:17 2010
@@ -17,177 +17,55 @@
package org.apache.poi.ss.formula.atp;
import junit.framework.TestCase;
-
import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.eval.ErrorEval;
-import org.apache.poi.ss.usermodel.Cell;
-import org.apache.poi.ss.usermodel.FormulaEvaluator;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.*;
/**
- * Testcase for 'Analysis Toolpak' function RANDBETWEEN()
+ * Testcase for 'Analysis Toolpak' function MROUND()
*
- * @author Brendan Nolan
+ * @author Yegor Kozlov
*/
-public class TestRandBetween extends TestCase {
+public class TestMRound 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, Cell.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);
-
- }
-
+ /**
+=MROUND(10, 3) Rounds 10 to a nearest multiple of 3 (9)
+=MROUND(-10, -3) Rounds -10 to a nearest multiple of -3 (-9)
+=MROUND(1.3, 0.2) Rounds 1.3 to a nearest multiple of 0.2 (1.4)
+=MROUND(5, -2) Returns an error, because -2 and 5 have different signs
(#NUM!) *
+ */
+ public static void testEvaluate(){
+ Workbook wb = new HSSFWorkbook();
+ Sheet sh = wb.createSheet();
+ Cell cell1 = sh.createRow(0).createCell(0);
+ cell1.setCellFormula("MROUND(10, 3)");
+ Cell cell2 = sh.createRow(0).createCell(0);
+ cell2.setCellFormula("MROUND(-10, -3)");
+ Cell cell3 = sh.createRow(0).createCell(0);
+ cell3.setCellFormula("MROUND(1.3, 0.2)");
+ Cell cell4 = sh.createRow(0).createCell(0);
+ cell4.setCellFormula("MROUND(5, -2)");
+ Cell cell5 = sh.createRow(0).createCell(0);
+ cell5.setCellFormula("MROUND(5, 0)");
+
+ double accuracy = 1E-9;
+
+ FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
+
+ assertEquals("Rounds 10 to a nearest multiple of 3 (9)",
+ 9.0, evaluator.evaluate(cell1).getNumberValue(), accuracy);
+
+ assertEquals("Rounds -10 to a nearest multiple of -3 (-9)",
+ -9.0, evaluator.evaluate(cell2).getNumberValue(), accuracy);
+
+ assertEquals("Rounds 1.3 to a nearest multiple of 0.2 (1.4)",
+ 1.4, evaluator.evaluate(cell3).getNumberValue(), accuracy);
+
+ assertEquals("Returns an error, because -2 and 5 have different signs
(#NUM!)",
+ ErrorEval.NUM_ERROR.getErrorCode(),
evaluator.evaluate(cell4).getErrorValue());
+
+ assertEquals("Returns 0 because the multiple is 0",
+ 0.0, evaluator.evaluate(cell5).getNumberValue());
+ }
}
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestStatsLib.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestStatsLib.java?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestStatsLib.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestStatsLib.java
Sat Dec 11 12:41:17 2010
@@ -268,4 +268,54 @@ public class TestStatsLib extends Abstra
x = 3.02765035410;
assertEquals("stdev ", x, d);
}
+
+ public void testVar() {
+ double[] v = null;
+ double d, x = 0;
+
+ v = new double[] {3.50, 5.00, 7.23, 2.99};
+ d = StatsLib.var(v);
+ x = 3.6178;
+ assertEquals("var ", x, d);
+
+ v = new double[] {34.5, 2.0, 8.9, -4.0};
+ d = StatsLib.var(v);
+ x = 286.99;
+ assertEquals("var ", x, d);
+
+ v = new double[] {7.0, 25.0, 21.69};
+ d = StatsLib.var(v);
+ x = 91.79203333;
+ assertEquals("var ", x, d);
+
+ v = new double[] {1345,1301,1368,1322,1310,1370,1318,1350,1303,1299};
+ d = StatsLib.var(v);
+ x = 754.2666667;
+ assertEquals("var ", x, d);
+ }
+
+ public void testVarp() {
+ double[] v = null;
+ double d, x = 0;
+
+ v = new double[] {3.50, 5.00, 7.23, 2.99};
+ d = StatsLib.varp(v);
+ x = 2.71335;
+ assertEquals("varp ", x, d);
+
+ v = new double[] {34.5, 2.0, 8.9, -4.0};
+ d = StatsLib.varp(v);
+ x = 215.2425;
+ assertEquals("varp ", x, d);
+
+ v = new double[] {7.0, 25.0, 21.69};
+ d = StatsLib.varp(v);
+ x = 61.19468889;
+ assertEquals("varp ", x, d);
+
+ v = new double[] {1345,1301,1368,1322,1310,1370,1318,1350,1303,1299};
+ d = StatsLib.varp(v);
+ x = 678.84;
+ assertEquals("varp ", x, d);
+ }
}
Modified: poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls?rev=1044642&r1=1044641&r2=1044642&view=diff
==============================================================================
Binary files - no diff available.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
