poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1044642 - in /poi/trunk: src/documentation/content/xdocs/ src/java/org/apache/poi/ss/formula/atp/ src/java/org/apache/poi/ss/formula/eval/ src/java/org/apache/poi/ss/formula/functions/ src/ooxml/testcases/org/apache/poi/xssf/usermodel/ src...
Date Sat, 11 Dec 2010 12:41:17 GMT
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: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message