Example #1
0
        public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
        {
            if (args.Length < 3 || args.Length % 2 == 0)
            {
                return ErrorEval.VALUE_INVALID;
            }

            try
            {
                AreaEval sumRange = ConvertRangeArg(args[0]);

                // collect pairs of ranges and criteria
                AreaEval[] ae = new AreaEval[(args.Length - 1) / 2];
                I_MatchPredicate[] mp = new I_MatchPredicate[ae.Length];
                for (int i = 1, k = 0; i < args.Length; i += 2, k++)
                {
                    ae[k] = ConvertRangeArg(args[i]);
                    mp[k] = Countif.CreateCriteriaPredicate(args[i + 1], ec.RowIndex, ec.ColumnIndex);
                }

                ValidateCriteriaRanges(ae, sumRange);

                double result = SumMatchingCells(ae, mp, sumRange);
                return new NumberEval(result);
            }
            catch (EvaluationException e)
            {
                return e.GetErrorEval();
            }
        }
Example #2
0
        /**
         * @return simple rectangular {@link AreaEval} which represents the intersection of areas
         * <c>aeA</c> and <c>aeB</c>. If the two areas do not intersect, the result is <code>null</code>.
         */
        private static AreaEval ResolveRange(AreaEval aeA, AreaEval aeB)
        {

            int aeAfr = aeA.FirstRow;
            int aeAfc = aeA.FirstColumn;
            int aeBlc = aeB.LastColumn;
            if (aeAfc > aeBlc)
            {
                return null;
            }
            int aeBfc = aeB.FirstColumn;
            if (aeBfc > aeA.LastColumn)
            {
                return null;
            }
            int aeBlr = aeB.LastRow;
            if (aeAfr > aeBlr)
            {
                return null;
            }
            int aeBfr = aeB.FirstRow;
            int aeAlr = aeA.LastRow;
            if (aeBfr > aeAlr)
            {
                return null;
            }


            int top = Math.Max(aeAfr, aeBfr);
            int bottom = Math.Min(aeAlr, aeBlr);
            int left = Math.Max(aeAfc, aeBfc);
            int right = Math.Min(aeA.LastColumn, aeBlc);

            return aeA.Offset(top - aeAfr, bottom - aeAfr, left - aeAfc, right - aeAfc);
        }
Example #3
0
 /**
  * Returns one column from an <tt>AreaEval</tt>
  *
  * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high
  */
 private ValueVector CreateResultColumnVector(AreaEval tableArray, int rowIndex)
 {
     if (rowIndex >= tableArray.Height)
     {
         throw EvaluationException.InvalidRef();
     }
     return LookupUtils.CreateRowVector(tableArray, rowIndex);
 }
Example #4
0
 /**
  * Returns one column from an <c>AreaEval</c>
  *
  * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high
  */
 private ValueVector CreateResultColumnVector(AreaEval tableArray, int rowIndex)
 {
     if (rowIndex >= tableArray.Height)
     {
         throw EvaluationException.InvalidRef();
     }
     return(LookupUtils.CreateRowVector(tableArray, rowIndex));
 }
Example #5
0
 /**
  * Returns one column from an <tt>AreaEval</tt>
  *
  * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high
  */
 private ValueVector CreateResultColumnVector(AreaEval tableArray, int colIndex)
 {
     if (colIndex >= tableArray.Width)
     {
         throw EvaluationException.InvalidRef();
     }
     return(LookupUtils.CreateColumnVector(tableArray, colIndex));
 }
Example #6
0
 public BaseRef(RefEval re)
 {
     _refEval          = re;
     _areaEval         = null;
     _firstRowIndex    = re.Row;
     _firstColumnIndex = re.Column;
     _height           = 1;
     _width            = 1;
 }
Example #7
0
        private static ValueEval Eval(int srcRowIndex, int srcColumnIndex, ValueEval arg1, AreaEval aeRange,
                                      AreaEval aeSum)
        {
            // TODO - junit to prove last arg must be srcColumnIndex and not srcRowIndex
            I_MatchPredicate mp     = Countif.CreateCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
            double           result = SumMatchingCells(aeRange, mp, aeSum);

            return(new NumberEval(result));
        }
Example #8
0
 public BaseRef(AreaEval ae)
 {
     _refEval          = null;
     _areaEval         = ae;
     _firstRowIndex    = ae.FirstRow;
     _firstColumnIndex = ae.FirstColumn;
     _height           = ae.LastRow - ae.FirstRow + 1;
     _width            = ae.LastColumn - ae.FirstColumn + 1;
 }
Example #9
0
        private static ValueEval Eval(int srcRowIndex, int srcColumnIndex, ValueEval arg1, AreaEval aeRange,
                AreaEval aeSum)
        {

            // TODO - junit to prove last arg must be srcColumnIndex and not srcRowIndex
            I_MatchPredicate mp = Countif.CreateCriteriaPredicate(arg1, srcRowIndex, srcColumnIndex);
            double result = SumMatchingCells(aeRange, mp, aeSum);
            return new NumberEval(result);

        }
Example #10
0
 private static ValueVector CreateVector(AreaEval ae)
 {
     ValueVector result = LookupUtils.CreateVector(ae);
     if (result != null)
     {
         return result;
     }
     // extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
     throw new InvalidOperationException("non-vector lookup or result areas not supported yet");
 }
        /**
         * @return possibly  <c>ErrorEval</c>, and <c>null</c>
         */
        private static ValueEval ChooseSingleElementFromAreaInternal(AreaEval ae,
                                                                     int srcCellRow, int srcCellCol)
        {
            //if (false)
            //{
            //    // this is too simplistic
            //    if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol))
            //    {
            //        throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR);
            //    }
            //    /*
            //    Circular references are not dealt with directly here, but it is worth noting some Issues.

            //    ANY one of the return statements in this method could return a cell that is identical
            //    to the one immediately being Evaluated.  The evaluating cell is identified by srcCellRow,
            //    srcCellRow AND sheet.  The sheet is not available in any nearby calling method, so that's
            //    one reason why circular references are not easy to detect here. (The sheet of the returned
            //    cell can be obtained from ae if it is an Area3DEval.)

            //    Another reason there's little value in attempting to detect circular references here Is
            //    that only direct circular references could be detected.  If the cycle involved two or more
            //    cells this method could not detect it.

            //    Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector
            //    (and HSSFFormulaEvaluator).
            //     */
            //}

            if (ae.IsColumn)
            {
                if (ae.IsRow)
                {
                    return(ae.GetRelativeValue(0, 0));
                }
                if (!ae.ContainsRow(srcCellRow))
                {
                    throw EvaluationException.InvalidValue();
                }
                return(ae.GetAbsoluteValue(srcCellRow, ae.FirstColumn));
            }
            if (!ae.IsRow)
            {
                // multi-column, multi-row area
                if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol))
                {
                    return(ae.GetAbsoluteValue(ae.FirstRow, ae.FirstColumn));
                }
                throw EvaluationException.InvalidValue();
            }
            if (!ae.ContainsColumn(srcCellCol))
            {
                throw EvaluationException.InvalidValue();
            }
            return(ae.GetAbsoluteValue(ae.FirstRow, srcCellCol));
        }
Example #12
0
 /**
  * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns
  * as the <code>sumRange</code> argument
  *
  * @throws EvaluationException if
  */
 private void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange)
 {
     foreach (AreaEval r in criteriaRanges)
     {
         if (r.Height != sumRange.Height ||
            r.Width != sumRange.Width)
         {
             throw EvaluationException.InvalidValue();
         }
     }
 }
Example #13
0
        private static Double GetValue(AreaEval aeRange, int relRowIndex, int relColIndex)
        {
            ValueEval addend = aeRange.GetRelativeValue(relRowIndex, relColIndex);

            if (addend is NumberEval)
            {
                return(((NumberEval)addend).NumberValue);
            }
            // everything else (including string and boolean values) counts as zero
            return(Double.NaN);
        }
Example #14
0
        private static ValueVector CreateVector(AreaEval ae)
        {
            ValueVector result = LookupUtils.CreateVector(ae);

            if (result != null)
            {
                return(result);
            }
            // extra complexity required to emulate the way LOOKUP can handles these abnormal cases.
            throw new InvalidOperationException("non-vector lookup or result areas not supported yet");
        }
Example #15
0
 /**
  * Resolve a ValueEval that's in an AreaEval.
  *
  * @param db AreaEval from which the cell to resolve is retrieved.
  * @param dbRow Relative row in the AreaEval.
  * @param dbCol Relative column in the AreaEval.
  * @return A ValueEval that is a NumberEval, StringEval, BoolEval, BlankEval or ErrorEval.
  */
 private static ValueEval ResolveReference(AreaEval db, int dbRow, int dbCol)
 {
     try
     {
         return(OperandResolver.GetSingleValue(db.GetValue(dbRow, dbCol), db.FirstRow + dbRow, db.FirstColumn + dbCol));
     }
     catch (EvaluationException e)
     {
         return(e.GetErrorEval());
     }
 }
        /**
         * Implements (some perhaps not well known) Excel functionality to select a single cell from an
         * area depending on the coordinates of the calling cell.  Here is an example demonstrating
         * both selection from a single row area and a single column area in the same formula.
         *
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
         *      <tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr>
         *      <tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr>
         *      <tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr>
         *    </table>
         *
         * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet
         * will look like this:
         *
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
         *      <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr>
         *      <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr>
         *      <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr>
         *    </table>
         *
         * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does
         * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula
         * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/>
         *
         * The same concept is extended to references across sheets, such that even multi-row,
         * multi-column areas can be useful.<p/>
         *
         * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and
         * hence this method <b>can</b> throw a 'circular reference' EvaluationException.  Note that
         * this method does not attempt to detect cycles.  Every cell in the specified Area <c>ae</c>
         * has already been Evaluated prior to this method call.  Any cell (or cell<b>s</b>) part of
         * <c>ae</c> that would incur a cyclic reference error if selected by this method, will
         * already have the value <c>ErrorEval.CIRCULAR_REF_ERROR</c> upon entry to this method.  It
         * is assumed logic exists elsewhere to produce this behaviour.
         *
         * @return whatever the selected cell's Evaluated value Is.  Never <c>null</c>. Never
         *  <c>ErrorEval</c>.
         * @if there is a problem with indexing into the area, or if the
         *  Evaluated cell has an error.
         */
        public static ValueEval ChooseSingleElementFromArea(AreaEval ae,
                                                            int srcCellRow, int srcCellCol)
        {
            ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol);

            if (result is ErrorEval)
            {
                throw new EvaluationException((ErrorEval)result);
            }
            return(result);
        }
Example #17
0
        public void TestReferenceResult()
        {
            ValueEval[] values = new ValueEval[4];
            Arrays.Fill(values, NumberEval.ZERO);
            AreaEval arg0 = EvalFactory.CreateAreaEval("A1:B2", values);

            ValueEval[] args = new ValueEval[] { arg0, new NumberEval(2), new NumberEval(1), };
            ValueEval   ve   = FUNC_INST.Evaluate(args, -1, -1);

            ConfirmAreaEval("A2:A2", ve);
        }
Example #18
0
 /**
  * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns
  * as the <code>sumRange</code> argument
  *
  * @throws EvaluationException if
  */
 internal static void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval sumRange)
 {
     foreach (AreaEval r in criteriaRanges)
     {
         if (r.Height != sumRange.Height ||
             r.Width != sumRange.Width)
         {
             throw EvaluationException.InvalidValue();
         }
     }
 }
Example #19
0
        /**
         * Reads the numeric values from the row/col of the specified area - other values return the indicated missing value.
         */
        private static double?ReadValue(AreaEval aeSum, int relRowIndex, int relColIndex)
        {
            ValueEval addend = aeSum.GetRelativeValue(relRowIndex, relColIndex);

            if (addend is NumberEval)
            {
                return(((NumberEval)addend).NumberValue);
            }
            // everything else (including string and boolean values) counts as zero
            return(null);
        }
Example #20
0
 /**
  * Verify that each <code>criteriaRanges</code> argument contains the same number of rows and columns
  * as the <code>avgRange</code> argument
  *
  * @throws EvaluationException if
  */
 private void ValidateCriteriaRanges(AreaEval[] criteriaRanges, AreaEval avgRange)
 {
     foreach (AreaEval r in criteriaRanges)
     {
         if (r.Height != avgRange.Height ||
             r.Width != avgRange.Width)
         {
             throw EvaluationException.InvalidValue();
         }
     }
 }
Example #21
0
 /**
  * @return <c>null</c> if the supplied area is neither a single row nor a single colum
  */
 public static ValueVector CreateVector(AreaEval ae)
 {
     if (ae.IsColumn)
     {
         return(CreateColumnVector(ae, 0));
     }
     if (ae.IsRow)
     {
         return(CreateRowVector(ae, 0));
     }
     return(null);
 }
Example #22
0
        private static AreaEval ResolveRange(AreaEval aeA, AreaEval aeB)
        {
            int aeAfr = aeA.FirstRow;
            int aeAfc = aeA.FirstColumn;

            int top    = Math.Min(aeAfr, aeB.FirstRow);
            int bottom = Math.Max(aeA.LastRow, aeB.LastRow);
            int left   = Math.Min(aeAfc, aeB.FirstColumn);
            int right  = Math.Max(aeA.LastColumn, aeB.LastColumn);

            return(aeA.Offset(top - aeAfr, bottom - aeAfr, left - aeAfc, right - aeAfc));
        }
Example #23
0
        public void TestMismatchAreaDimensions()
        {
            AreaEval aeA = EvalFactory.CreateAreaEval("A1:A3", new ValueEval[3]);
            AreaEval aeB = EvalFactory.CreateAreaEval("B1:D1", new ValueEval[3]);

            ValueEval[] args;
            args = new ValueEval[] { aeA, aeB, };
            Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args));

            args = new ValueEval[] { aeA, new NumberEval(5), };
            Assert.AreEqual(ErrorEval.VALUE_INVALID, InvokeSumproduct(args));
        }
Example #24
0
        /**
         * @return a range of the same dimensions as aeRange using eval to define the top left corner.
         * @throws EvaluationException if eval is not a reference
         */

        private static AreaEval CreateSumRange(ValueEval eval, AreaEval aeRange)
        {
            if (eval is AreaEval)
            {
                return(((AreaEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1));
            }
            if (eval is RefEval)
            {
                return(((RefEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1));
            }
            throw new EvaluationException(ErrorEval.VALUE_INVALID);
        }
Example #25
0
 public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1)
 {
     try
     {
         AreaEval reA = EvaluateRef(arg0);
         AreaEval reB = EvaluateRef(arg1);
         return(ResolveRange(reA, reB));
     }
     catch (EvaluationException e)
     {
         return(e.GetErrorEval());
     }
 }
Example #26
0
        public void TestOneByOneArea()
        {
            AreaEval ae = EvalFactory.CreateAreaEval("A1:A1", new ValueEval[] { new NumberEval(7), });

            ValueEval[] args =
            {
                ae,
                new NumberEval(2),
            };
            ValueEval result = InvokeSumproduct(args);

            ConfirmDouble(14D, result);
        }
Example #27
0
        /**
         * Confirms that the result is an area ref with the specified coordinates
         * @return <c>ve</c> cast to {@link AreaEval} if it is valid
         */
        private static AreaEval ConfirmAreaEval(String refText, ValueEval ve)
        {
            CellRangeAddress cra = CellRangeAddress.ValueOf(refText);

            Assert.IsTrue(ve is AreaEval);
            AreaEval ae = (AreaEval)ve;

            Assert.AreEqual(cra.FirstRow, ae.FirstRow);
            Assert.AreEqual(cra.FirstColumn, ae.FirstColumn);
            Assert.AreEqual(cra.LastRow, ae.LastRow);
            Assert.AreEqual(cra.LastColumn, ae.LastColumn);
            return(ae);
        }
Example #28
0
        public void TestTildeString()
        {
            ValueEval[] values =
            {
                new StringEval("what?"),
                new StringEval("all*")
            };

            AreaEval ae = EvalFactory.CreateAreaEval("A1:A2", values);

            ConfirmInt(1, invokeMatch(new StringEval("what~?"), ae, MATCH_EXACT));
            ConfirmInt(2, invokeMatch(new StringEval("all~*"), ae, MATCH_EXACT));
        }
Example #29
0
 public RowVector(AreaEval tableArray, int rowIndex)
 {
     _rowIndex = rowIndex;
     int _rowAbsoluteIndex = tableArray.FirstRow + rowIndex;
     if (!tableArray.ContainsRow(_rowAbsoluteIndex))
     {
         int lastRowIx = tableArray.LastRow - tableArray.FirstRow;
         throw new ArgumentException("Specified row index (" + rowIndex
                 + ") is outside the allowed range (0.." + lastRowIx + ")");
     }
     _tableArray = tableArray;
     _size = tableArray.Width;
 }
Example #30
0
 public ColumnVector(AreaEval tableArray, int columnIndex)
 {
     _columnIndex = columnIndex;
     int _columnAbsoluteIndex = tableArray.FirstColumn + columnIndex;
     if (!tableArray.ContainsColumn((short)_columnAbsoluteIndex))
     {
         int lastColIx = tableArray.LastColumn - tableArray.FirstColumn;
         throw new ArgumentException("Specified column index (" + columnIndex
                 + ") is outside the allowed range (0.." + lastColIx + ")");
     }
     _tableArray = tableArray;
     _size = _tableArray.Height;
 }
Example #31
0
        public void TestAreaWithErrorCell()
        {
            ValueEval[] aValues =
            {
                ErrorEval.REF_INVALID,
                null,
            };
            AreaEval aeA = EvalFactory.CreateAreaEval("A1:A2", aValues);
            AreaEval aeB = EvalFactory.CreateAreaEval("B1:B2", new ValueEval[2]);

            ValueEval[] args = { aeA, aeB, };
            Assert.AreEqual(ErrorEval.REF_INVALID, InvokeSumproduct(args));
        }
Example #32
0
            public RowVector(AreaEval tableArray, int rowIndex)
            {
                _rowIndex = rowIndex;
                int _rowAbsoluteIndex = tableArray.FirstRow + rowIndex;

                if (!tableArray.ContainsRow(_rowAbsoluteIndex))
                {
                    int lastRowIx = tableArray.LastRow - tableArray.FirstRow;
                    throw new ArgumentException("Specified row index (" + rowIndex
                                                + ") is outside the allowed range (0.." + lastRowIx + ")");
                }
                _tableArray = tableArray;
                _size       = tableArray.Width;
            }
Example #33
0
            public ColumnVector(AreaEval tableArray, int columnIndex)
            {
                _columnIndex = columnIndex;
                int _columnAbsoluteIndex = tableArray.FirstColumn + columnIndex;

                if (!tableArray.ContainsColumn((short)_columnAbsoluteIndex))
                {
                    int lastColIx = tableArray.LastColumn - tableArray.FirstColumn;
                    throw new ArgumentException("Specified column index (" + columnIndex
                                                + ") is outside the allowed range (0.." + lastColIx + ")");
                }
                _tableArray = tableArray;
                _size       = _tableArray.Height;
            }
Example #34
0
        private ValueEval EvaluateAreaSumProduct(ValueEval[] evalArgs)
        {
            int maxN = evalArgs.Length;

            AreaEval[] args = new AreaEval[maxN];
            try
            {
                Array.Copy(evalArgs, 0, args, 0, maxN);
            }
            catch (Exception)
            {
                // one of the other args was not an AreaRef
                return(ErrorEval.VALUE_INVALID);
            }


            AreaEval firstArg = args[0];

            int height = firstArg.LastRow - firstArg.FirstRow + 1;
            int width  = firstArg.LastColumn - firstArg.FirstColumn + 1; // TODO - junit

            // first check dimensions
            if (!AreasAllSameSize(args, height, width))
            {
                // normally this results in #VALUE!,
                // but errors in individual cells take precedence
                for (int i = 1; i < args.Length; i++)
                {
                    ThrowFirstError(args[i]);
                }
                return(ErrorEval.VALUE_INVALID);
            }
            double acc = 0;

            for (int rrIx = 0; rrIx < height; rrIx++)
            {
                for (int rcIx = 0; rcIx < width; rcIx++)
                {
                    double term = 1D;
                    for (int n = 0; n < maxN; n++)
                    {
                        double val = GetProductTerm(args[n].GetRelativeValue(rrIx, rcIx), false);
                        term *= val;
                    }
                    acc += term;
                }
            }

            return(new NumberEval(acc));
        }
Example #35
0
        private static double SumMatchingCells(AreaEval aeRange, IMatchPredicate mp, AreaEval aeSum)
        {
            int height = aeRange.Height;
            int width  = aeRange.Width;

            double result = 0.0D;

            for (int r = 0; r < height; r++)
            {
                for (int c = 0; c < width; c++)
                {
                    result += Accumulate(aeRange, mp, aeSum, r, c);
                }
            }
            return(result);
        }
Example #36
0
        private static double SumMatchingCells(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum)
        {
            int height = aeRange.Height;
            int width = aeRange.Width;

            double result = 0.0;

            for (int r = 0; r < height; r++)
            {
                for (int c = 0; c < width; c++)
                {
                    result += Accumulate(aeRange, mp, aeSum, r, c);
                }
            }
            return result;
        }
Example #37
0
        protected ValueEval SingleOperandEvaluate(Eval eval, int srcRow, short srcCol)
        {
            ValueEval retval;

            if (eval is AreaEval)
            {
                AreaEval ae = (AreaEval)eval;
                if (ae.Contains(srcRow, srcCol))
                { // circular ref!
                    retval = ErrorEval.CIRCULAR_REF_ERROR;
                }
                else if (ae.IsRow)
                {
                    if (ae.ContainsColumn(srcCol))
                    {
                        ValueEval ve = ae.GetValueAt(ae.FirstRow, srcCol);
                        ve     = Xlator.AttemptXlateToNumeric(ve);
                        retval = Xlator.AttemptXlateToNumeric(ve);
                    }
                    else
                    {
                        retval = ErrorEval.VALUE_INVALID;
                    }
                }
                else if (ae.IsColumn)
                {
                    if (ae.ContainsRow(srcRow))
                    {
                        ValueEval ve = ae.GetValueAt(srcRow, ae.FirstColumn);
                        retval = Xlator.AttemptXlateToNumeric(ve);
                    }
                    else
                    {
                        retval = ErrorEval.VALUE_INVALID;
                    }
                }
                else
                {
                    retval = ErrorEval.VALUE_INVALID;
                }
            }
            else
            {
                retval = Xlator.AttemptXlateToNumeric((ValueEval)eval);
            }
            return(retval);
        }
Example #38
0
        /**
         * Returns an is StringValueEval or ErrorEval or BlankEval
         *
         * @param eval
         * @param srcRow
         * @param srcCol
         */
        protected ValueEval SingleOperandEvaluate(Eval eval, int srcRow, short srcCol)
        {
            ValueEval retval;

            if (eval is AreaEval)
            {
                AreaEval ae = (AreaEval)eval;
                if (ae.Contains(srcRow, srcCol))
                { // circular ref!
                    retval = ErrorEval.CIRCULAR_REF_ERROR;
                }
                else if (ae.IsRow)
                {
                    if (ae.ContainsColumn(srcCol))
                    {
                        ValueEval ve = ae.GetValue(ae.FirstRow, srcCol);
                        retval = InternalResolveEval(eval);
                    }
                    else
                    {
                        retval = ErrorEval.NAME_INVALID;
                    }
                }
                else if (ae.IsColumn)
                {
                    if (ae.ContainsRow(srcRow))
                    {
                        ValueEval ve = ae.GetValue(srcRow, ae.FirstColumn);
                        retval = InternalResolveEval(eval);
                    }
                    else
                    {
                        retval = ErrorEval.NAME_INVALID;
                    }
                }
                else
                {
                    retval = ErrorEval.NAME_INVALID;
                }
            }
            else
            {
                retval = InternalResolveEval(eval);
            }
            return(retval);
        }
Example #39
0
 private static bool AreasAllSameSize(AreaEval[] args, int height, int width)
 {
     for (int i = 0; i < args.Length; i++)
     {
         AreaEval areaEval = args[i];
         // check that height and width match
         if (areaEval.Height != height)
         {
             return false;
         }
         if (areaEval.Width != width)
         {
             return false;
         }
     }
     return true;
 }
Example #40
0
        /**
         * Implements (some perhaps not well known) Excel functionality to select a single cell from an
         * area depending on the coordinates of the calling cell.  Here is an example demonstrating
         * both selection from a single row area and a single column area in the same formula.
         *
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th>&nbsp;</th><th>&nbsp;A&nbsp;</th><th>&nbsp;B&nbsp;</th><th>&nbsp;C&nbsp;</th><th>&nbsp;D&nbsp;</th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td>&nbsp;</td></tr>
         *      <tr><th>2</th><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>200</td></tr>
         *      <tr><th>3</th><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>300</td></tr>
         *      <tr><th>3</th><td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td><td>400</td></tr>
         *    </table>
         *
         * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet
         * will look like this:
         *
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th>&nbsp;</th><th>&nbsp;A&nbsp;</th><th>&nbsp;B&nbsp;</th><th>&nbsp;C&nbsp;</th><th>&nbsp;D&nbsp;</th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td>&nbsp;</td></tr>
         *      <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr>
         *      <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr>
         *      <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr>
         *    </table>
         *
         * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does
         * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula
         * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/>
         *
         * The same concept is extended to references across sheets, such that even multi-row,
         * multi-column areas can be useful.<p/>
         *
         * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and
         * hence this method <b>can</b> throw a 'circular reference' EvaluationException.  Note that
         * this method does not attempt to detect cycles.  Every cell in the specified Area <tt>ae</tt>
         * has already been Evaluated prior to this method call.  Any cell (or cell<b>s</b>) part of
         * <tt>ae</tt> that would incur a cyclic reference error if selected by this method, will
         * already have the value <t>ErrorEval.CIRCULAR_REF_ERROR</tt> upon entry to this method.  It
         * is assumed logic exists elsewhere to produce this behaviour.
         *
         * @return whatever the selected cell's Evaluated value Is.  Never <c>null</c>. Never
         *  <tt>ErrorEval</tt>.
         * @if there is a problem with indexing into the area, or if the
         *  Evaluated cell has an error.
         */
        public static ValueEval ChooseSingleElementFromArea(AreaEval ae,
                int srcCellRow, int srcCellCol)
        {
            ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol);
            if (result == null)
            {
                // This seems to be required because AreaEval.Values array may contain nulls.
                // perhaps that should not be allowed.
                result = BlankEval.instance;
            }
            if (result is ErrorEval)
            {
                throw new EvaluationException((ErrorEval)result);

            }
            return result;
        }
Example #41
0
        public void TestInvalidMatchType()
        {
            ValueEval[] values =
            {
                new NumberEval(4),
                new NumberEval(5),
                new NumberEval(10),
                new NumberEval(10),
                new NumberEval(25),
            };

            AreaEval ae = EvalFactory.CreateAreaEval("A1:A5", values);

            ConfirmInt(2, invokeMatch(new NumberEval(5), ae, MATCH_LARGEST_LTE));

            Assert.AreEqual(ErrorEval.REF_INVALID, invokeMatch(new StringEval("Ben"), ae, MATCH_INVALID), "Should return #REF! for invalid match type");
        }
Example #42
0
        /**
         * @return the number of evaluated cells in the range that match the specified criteria
         */
        public static int CountMatchingCellsInArea(AreaEval areaEval, I_MatchPredicate criteriaPredicate)
        {
            int result = 0;

            int height = areaEval.Height;
            int width = areaEval.Width;
            for (int rrIx = 0; rrIx < height; rrIx++)
            {
                for (int rcIx = 0; rcIx < width; rcIx++)
                {
                    ValueEval ve = areaEval.GetRelativeValue(rrIx, rcIx);
                    if (criteriaPredicate.Matches(ve))
                    {
                        result++;
                    }
                }
            }
            return result;
        }
Example #43
0
        /**
         * @return possibly  <tt>ErrorEval</tt>, and <c>null</c> 
         */
        private static ValueEval ChooseSingleElementFromAreaInternal(AreaEval ae,
                int srcCellRow, short srcCellCol)
        {

            //if (false)
            //{
            //    // this Is too simplistic
            //    if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol))
            //    {
            //        throw new EvaluationException(ErrorEval.CIRCULAR_REF_ERROR);
            //    }
            //    /*
            //    Circular references are not dealt with directly here, but it Is worth noting some Issues.

            //    ANY one of the return statements in this method could return a cell that Is identical
            //    to the one immediately being Evaluated.  The evaluating cell Is identified by srcCellRow,
            //    srcCellRow AND sheet.  The sheet Is not available in any nearby calling method, so that's
            //    one reason why circular references are not easy to detect here. (The sheet of the returned
            //    cell can be obtained from ae if it Is an Area3DEval.)

            //    Another reason there's little value in attempting to detect circular references here Is
            //    that only direct circular references could be detected.  If the cycle involved two or more
            //    cells this method could not detect it.  

            //    Logic to detect evaluation cycles of all kinds has been coded in EvaluationCycleDetector
            //    (and HSSFFormulaEvaluator). 
            //     */
            //}

            if (ae.IsColumn)
            {
                if (ae.IsRow)
                {
                    return ae.GetRelativeValue(0, 0);
                }
                if (!ae.ContainsRow(srcCellRow))
                {
                    throw EvaluationException.InvalidValue();
                }
                return ae.GetValueAt(srcCellRow, ae.FirstColumn);
            }
            if (!ae.IsRow)
            {
                // multi-column, multi-row area
                if (ae.ContainsRow(srcCellRow) && ae.ContainsColumn(srcCellCol))
                {
                    return ae.GetValueAt(ae.FirstRow, ae.FirstColumn);
                }
                throw EvaluationException.InvalidValue();
            }
            if (!ae.ContainsColumn(srcCellCol))
            {
                throw EvaluationException.InvalidValue();
            }
            return ae.GetValueAt(ae.FirstRow, srcCellCol);
        }
Example #44
0
        private static Double GetValue(AreaEval aeRange, int relRowIndex, int relColIndex)
        {

            ValueEval addend = aeRange.GetRelativeValue(relRowIndex, relColIndex);
            if (addend is NumberEval)
            {
                return ((NumberEval)addend).NumberValue;
            }
            // everything else (including string and boolean values) counts as zero
            return Double.NaN;
        }
Example #45
0
 public static ValueVector CreateRowVector(AreaEval tableArray, int relativeRowIndex)
 {
     return new RowVector(tableArray, relativeRowIndex);
 }
Example #46
0
 /**
  * @return <c>null</c> if the supplied area is neither a single row nor a single colum
  */
 public static ValueVector CreateVector(AreaEval ae)
 {
     if (ae.IsColumn)
     {
         return CreateColumnVector(ae, 0);
     }
     if (ae.IsRow)
     {
         return CreateRowVector(ae, 0);
     }
     return null;
 }
Example #47
0
        private static ValueEval eval(int srcRowIndex, int srcColumnIndex, double arg0, AreaEval aeRange, bool descending_order)
        {

            int rank = 1;
            int height = aeRange.Height;
            int width = aeRange.Width;
            for (int r = 0; r < height; r++)
            {
                for (int c = 0; c < width; c++)
                {

                    Double value = GetValue(aeRange, r, c);
                    if (Double.IsNaN(value)) continue;
                    if (descending_order && value > arg0 || !descending_order && value < arg0)
                    {
                        rank++;
                    }
                }
            }
            return new NumberEval(rank);
        }
Example #48
0
        /**
         * @return a range of the same dimensions as aeRange using eval to define the top left corner.
         * @throws EvaluationException if eval is not a reference
         */

        private static AreaEval CreateSumRange(ValueEval eval, AreaEval aeRange)
        {
            if (eval is AreaEval)
            {
                return ((AreaEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1);
            }
            if (eval is RefEval)
            {
                return ((RefEval)eval).Offset(0, aeRange.Height - 1, 0, aeRange.Width - 1);
            }
            throw new EvaluationException(ErrorEval.VALUE_INVALID);

        }
Example #49
0
 private static void ThrowFirstError(AreaEval areaEval)
 {
     int height = areaEval.Height;
     int width = areaEval.Width;
     for (int rrIx = 0; rrIx < height; rrIx++)
     {
         for (int rcIx = 0; rcIx < width; rcIx++)
         {
             ValueEval ve = areaEval.GetRelativeValue(rrIx, rcIx);
             if (ve is ErrorEval)
             {
                 throw new EvaluationException((ErrorEval)ve);
             }
         }
     }
 }
Example #50
0
        /**
         * Implements (some perhaps not well known) Excel functionality to select a single cell from an
         * area depending on the coordinates of the calling cell.  Here is an example demonstrating
         * both selection from a single row area and a single column area in the same formula.
         * 
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
         *      <tr><th>2</th><td> </td><td> </td><td> </td><td>200</td></tr>
         *      <tr><th>3</th><td> </td><td> </td><td> </td><td>300</td></tr>
         *      <tr><th>3</th><td> </td><td> </td><td> </td><td>400</td></tr>
         *    </table>
         * 
         * If the formula "=1000+A1:B1+D2:D3" is put into the 9 cells from A2 to C4, the spReadsheet
         * will look like this:
         * 
         *    <table border="1" cellpAdding="1" cellspacing="1" summary="sample spReadsheet">
         *      <tr><th> </th><th> A </th><th> B </th><th> C </th><th> D </th></tr>
         *      <tr><th>1</th><td>15</td><td>20</td><td>25</td><td> </td></tr>
         *      <tr><th>2</th><td>1215</td><td>1220</td><td>#VALUE!</td><td>200</td></tr>
         *      <tr><th>3</th><td>1315</td><td>1320</td><td>#VALUE!</td><td>300</td></tr>
         *      <tr><th>4</th><td>#VALUE!</td><td>#VALUE!</td><td>#VALUE!</td><td>400</td></tr>
         *    </table>
         * 
         * Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does 
         * not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula
         * as written, but in the 4 cells A2:B3, the row and column selection works ok.<p/>
         * 
         * The same concept is extended to references across sheets, such that even multi-row, 
         * multi-column areas can be useful.<p/>
         * 
         * Of course with carefully (or carelessly) chosen parameters, cyclic references can occur and
         * hence this method <b>can</b> throw a 'circular reference' EvaluationException.  Note that 
         * this method does not attempt to detect cycles.  Every cell in the specified Area <c>ae</c>
         * has already been Evaluated prior to this method call.  Any cell (or cell<b>s</b>) part of 
         * <c>ae</c> that would incur a cyclic reference error if selected by this method, will 
         * already have the value <c>ErrorEval.CIRCULAR_REF_ERROR</c> upon entry to this method.  It
         * is assumed logic exists elsewhere to produce this behaviour.
         * 
         * @return whatever the selected cell's Evaluated value Is.  Never <c>null</c>. Never
         *  <c>ErrorEval</c>.
         * @if there is a problem with indexing into the area, or if the
         *  Evaluated cell has an error.
         */
        public static ValueEval ChooseSingleElementFromArea(AreaEval ae,
                int srcCellRow, int srcCellCol)
        {
            ValueEval result = ChooseSingleElementFromAreaInternal(ae, srcCellRow, srcCellCol);
            
            if (result is ErrorEval)
            {
                throw new EvaluationException((ErrorEval)result);

            }
            return result;
        }
Example #51
0
        /**
         * @param colArgWasPassed <code>false</code> if the INDEX argument lIst had just 2 items
         *            (exactly 1 comma).  If anything Is passed for the <tt>column_num</tt> argument
         *            (including {@link BlankEval} or {@link MIssingArgEval}) this parameter will be
         *            <code>true</code>.  ThIs parameter is needed because error codes are slightly
         *            different when only 2 args are passed.
         */
        private static ValueEval GetValueFromArea(AreaEval ae, int pRowIx, int pColumnIx,
                bool colArgWasPassed, int srcRowIx, int srcColIx)
        {
            bool rowArgWasEmpty = pRowIx == 0;
            bool colArgWasEmpty = pColumnIx == 0;
            int rowIx;
            int columnIx;

            // when the area ref Is a single row or a single column,
            // there are special rules for conversion of rowIx and columnIx
            if (ae.IsRow)
            {
                if (ae.IsColumn)
                {
                    // single cell ref
                    rowIx = rowArgWasEmpty ? 0 : pRowIx - 1;
                    columnIx = colArgWasEmpty ? 0 : pColumnIx - 1;
                }
                else
                {
                    if (colArgWasPassed)
                    {
                        rowIx = rowArgWasEmpty ? 0 : pRowIx - 1;
                        columnIx = pColumnIx - 1;
                    }
                    else
                    {
                        // special case - row arg seems to Get used as the column index
                        rowIx = 0;
                        // transfer both the index value and the empty flag from 'row' to 'column':
                        columnIx = pRowIx - 1;
                        colArgWasEmpty = rowArgWasEmpty;
                    }
                }
            }
            else if (ae.IsColumn)
            {
                if (rowArgWasEmpty)
                {
                    rowIx = srcRowIx - ae.FirstRow;
                }
                else
                {
                    rowIx = pRowIx - 1;
                }
                if (colArgWasEmpty)
                {
                    columnIx = 0;
                }
                else
                {
                    columnIx = colArgWasEmpty ? 0 : pColumnIx - 1;
                }
            }
            else
            {
                // ae Is an area (not single row or column)
                if (!colArgWasPassed)
                {
                    // always an error with 2-D area refs
                    // Note - the type of error Changes if the pRowArg is negative
                    throw new EvaluationException(pRowIx < 0 ? ErrorEval.VALUE_INVALID : ErrorEval.REF_INVALID);
                }
                // Normal case - area ref Is 2-D, and both index args were provided
                // if either arg Is missing (or blank) the logic is similar to OperandResolver.getSingleValue()
                if (rowArgWasEmpty)
                {
                    rowIx = srcRowIx - ae.FirstRow;
                }
                else
                {
                    rowIx = pRowIx - 1;
                }
                if (colArgWasEmpty)
                {
                    columnIx = srcColIx - ae.FirstColumn;
                }
                else
                {
                    columnIx = pColumnIx - 1;
                }
            }

            int width = ae.Width;
            int height = ae.Height;
            // Slightly irregular logic for bounds checking errors
            if (!rowArgWasEmpty && rowIx >= height || !colArgWasEmpty && columnIx >= width)
            {
                // high bounds check fail gives #REF! if arg was explicitly passed
                throw new EvaluationException(ErrorEval.REF_INVALID);
            }
            if (rowIx < 0 || columnIx < 0 || rowIx >= height || columnIx >= width)
            {
                throw new EvaluationException(ErrorEval.VALUE_INVALID);
            }
            return ae.GetRelativeValue(rowIx, columnIx);
        }
Example #52
0
 private static void Confirm(int expectedValue, AreaEval ae, int row, int col)
 {
     NumberEval v = (NumberEval)ae.GetAbsoluteValue(row, col);
     Assert.AreEqual(expectedValue, v.NumberValue, 0.0);
 }
Example #53
0
        private static void ConfirmCountBlank(int expected, AreaEval range)
        {

            ValueEval[] args = { range };
            double result = NumericFunctionInvoker.Invoke(new Countblank(), args);
            Assert.AreEqual(expected, result, 0);
        }
Example #54
0
        private ValueEval EvaluateAreaSumProduct(ValueEval[] evalArgs)
        {
            int maxN = evalArgs.Length;
            AreaEval[] args = new AreaEval[maxN];
            try
            {
                Array.Copy(evalArgs, 0, args, 0, maxN);
            }
            catch (Exception)
            {
                // one of the other args was not an AreaRef
                return ErrorEval.VALUE_INVALID;
            }


            AreaEval firstArg = args[0];

            int height = firstArg.LastRow - firstArg.FirstRow + 1;
            int width = firstArg.LastColumn - firstArg.FirstColumn + 1; // TODO - junit

            // first check dimensions
            if (!AreasAllSameSize(args, height, width))
            {
                // normally this results in #VALUE!, 
                // but errors in individual cells take precedence
                for (int i = 1; i < args.Length; i++)
                {
                    ThrowFirstError(args[i]);
                }
                return ErrorEval.VALUE_INVALID;
            }
            double acc = 0;

            for (int rrIx = 0; rrIx < height; rrIx++)
            {
                for (int rcIx = 0; rcIx < width; rcIx++)
                {
                    double term = 1D;
                    for (int n = 0; n < maxN; n++)
                    {
                        double val = GetProductTerm(args[n].GetRelativeValue(rrIx, rcIx), false);
                        term *= val;
                    }
                    acc += term;
                }
            }

            return new NumberEval(acc);
        }
Example #55
0
 public BaseRef(AreaEval ae)
 {
     _refEval = null;
     _areaEval = ae;
     _firstRowIndex = ae.FirstRow;
     _firstColumnIndex = ae.FirstColumn;
     _height = ae.LastRow - ae.FirstRow + 1;
     _width = ae.LastColumn - ae.FirstColumn + 1;
 }
Example #56
0
 public static ValueVector CreateColumnVector(AreaEval tableArray, int relativeColumnIndex)
 {
     return new ColumnVector(tableArray, relativeColumnIndex);
 }
Example #57
0
 public AreaValueArray(AreaEval ae)
     : base(ae.Width * ae.Height)
 {
     _ae = ae;
     _width = ae.Width;
 }
Example #58
0
        private static void ConfirmCountIf(int expected, AreaEval range, ValueEval criteria)
        {

            ValueEval[] args = { range, criteria, };
            double result = NumericFunctionInvoker.Invoke(new Countif(), args);
            Assert.AreEqual(expected, result, 0);
        }
Example #59
0
 public BaseRef(RefEval re)
 {
     _refEval = re;
     _areaEval = null;
     _firstRowIndex = re.Row;
     _firstColumnIndex = re.Column;
     _height = 1;
     _width = 1;
 }
Example #60
0
        private static double Accumulate(AreaEval aeRange, I_MatchPredicate mp, AreaEval aeSum, int relRowIndex,
                int relColIndex)
        {
            if (!mp.Matches(aeRange.GetRelativeValue(relRowIndex, relColIndex)))
            {
                return 0.0;
            }

            ValueEval addend = aeSum.GetRelativeValue(relRowIndex, relColIndex);
            if (addend is NumberEval)
            {
                return ((NumberEval)addend).NumberValue;
            }
            // everything else (including string and boolean values) counts as zero
            return 0.0;
        }