private static ValueEval GetValueFromArea(TwoDEval ae, int pRowIx, int pColumnIx) { Debug.Assert(pRowIx >= 0); Debug.Assert(pColumnIx >= 0); TwoDEval result = ae; if (pRowIx != 0) { // Slightly irregular logic for bounds checking errors if (pRowIx > ae.Height) { // high bounds check fail gives #REF! if arg was explicitly passed throw new EvaluationException(ErrorEval.REF_INVALID); } result = result.GetRow(pRowIx - 1); } if (pColumnIx != 0) { // Slightly irregular logic for bounds checking errors if (pColumnIx > ae.Width) { // high bounds check fail gives #REF! if arg was explicitly passed throw new EvaluationException(ErrorEval.REF_INVALID); } result = result.GetColumn(pColumnIx - 1); } return(result); }
/** * Collects values from a single argument */ private void CollectValues(ValueEval operand, DoubleList temp) { if (operand is TwoDEval) { TwoDEval ae = (TwoDEval)operand; int width = ae.Width; int height = ae.Height; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.GetValue(rrIx, rcIx); if (!IsSubtotalCounted && ae.IsSubTotal(rrIx, rcIx)) { continue; } CollectValue(ve, true, temp); } } return; } if (operand is RefEval) { RefEval re = (RefEval)operand; CollectValue(re.InnerValueEval, true, temp); return; } CollectValue((ValueEval)operand, false, temp); }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(TwoDEval 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.GetValue(rrIx, rcIx); if (criteriaPredicate is I_MatchAreaPredicate) { I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate; if (!areaPredicate.Matches(areaEval, rrIx, rcIx)) continue; } if (criteriaPredicate.Matches(ve)) { result++; } } } return result; }
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2) { //AreaEval reference = ConvertFirstArg(arg0); //bool colArgWasPassed = true; //try //{ // int columnIx = ResolveIndexArg(arg2, srcRowIndex, srcColumnIndex); // int rowIx = ResolveIndexArg(arg1, srcRowIndex, srcColumnIndex); // return GetValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex); //} //catch (EvaluationException e) //{ // return e.GetErrorEval(); //} TwoDEval reference = ConvertFirstArg(arg0); try { int columnIx = ResolveIndexArg(arg2, srcRowIndex, srcColumnIndex); int rowIx = ResolveIndexArg(arg1, srcRowIndex, srcColumnIndex); return(GetValueFromArea(reference, rowIx, columnIx)); } catch (EvaluationException e) { return(e.GetErrorEval()); } }
/** * @return the number of evaluated cells in the range that match the specified criteria */ public static int CountMatchingCellsInArea(TwoDEval areaEval, IMatchPredicate 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.GetValue(rrIx, rcIx); if (criteriaPredicate is I_MatchAreaPredicate) { I_MatchAreaPredicate areaPredicate = (I_MatchAreaPredicate)criteriaPredicate; if (!areaPredicate.Matches(areaEval, rrIx, rcIx)) { continue; } } if (criteriaPredicate.Matches(ve)) { result++; } } } return(result); }
private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, ValueEval returnEval, String notFound, LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode, bool isSingleValue) { try { ValueEval lookupValue = OperandResolver.GetSingleValue(lookupEval, srcRowIndex, srcColumnIndex); TwoDEval tableArray = LookupUtils.ResolveTableArrayArg(indexEval); int matchedRow; try { matchedRow = LookupUtils.XlookupIndexOfValue(lookupValue, LookupUtils.CreateColumnVector(tableArray, 0), matchMode, searchMode); } catch (EvaluationException e) { if (ErrorEval.NA.Equals(e.GetErrorEval())) { if (string.IsNullOrEmpty(notFound)) { if (returnEval is AreaEval) { AreaEval area = (AreaEval)returnEval; int width = area.Width; if (isSingleValue || width <= 1) { return(new StringEval(notFound)); } return(new NotFoundAreaEval(notFound, width)); } else { return(new StringEval(notFound)); } } return(ErrorEval.NA); } else { return(e.GetErrorEval()); } } if (returnEval is AreaEval) { AreaEval area = (AreaEval)returnEval; if (isSingleValue) { return(area.GetRelativeValue(matchedRow, 0)); } return(area.Offset(matchedRow, matchedRow, 0, area.Width - 1)); } else { return(returnEval); } } catch (EvaluationException e) { return(e.GetErrorEval()); } }
/** * @return <c>null</c> if the supplied area is neither a single row nor a single colum */ public static ValueVector CreateVector(TwoDEval ae) { if (ae.IsColumn) { return(CreateColumnVector(ae, 0)); } if (ae.IsRow) { return(CreateRowVector(ae, 0)); } return(null); }
/** * For a given database returns the column number for a column heading. * * @param db Database. * @param name Column heading. * @return Corresponding column number. * @If it's not possible to turn all headings into strings. */ private static int GetColumnForString(TwoDEval db, String name) { int resultColumn = -1; for (int column = 0; column < db.Width; ++column) { ValueEval columnNameValueEval = db.GetValue(0, column); String columnName = GetStringFromValueEval(columnNameValueEval); if (name.Equals(columnName)) { resultColumn = column; break; } } return(resultColumn); }
private static void ThrowFirstError(TwoDEval 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.GetValue(rrIx, rcIx); if (ve is ErrorEval) { throw new EvaluationException((ErrorEval)ve); } } } }
private static bool AreasAllSameSize(TwoDEval[] args, int height, int width) { for (int i = 0; i < args.Length; i++) { TwoDEval areaEval = args[i]; // check that height and width match if (areaEval.Height != height) { return(false); } if (areaEval.Width != width) { return(false); } } return(true); }
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { //AreaEval reference = ConvertFirstArg(arg0); //bool colArgWasPassed = false; //int columnIx = 0; //try //{ // int rowIx = ResolveIndexArg(arg1, srcRowIndex, srcColumnIndex); // return GetValueFromArea(reference, rowIx, columnIx, colArgWasPassed, srcRowIndex, srcColumnIndex); //} //catch (EvaluationException e) //{ // return e.GetErrorEval(); //} TwoDEval reference = ConvertFirstArg(arg0); int columnIx = 0; try { int rowIx = ResolveIndexArg(arg1, srcRowIndex, srcColumnIndex); if (!reference.IsColumn) { if (!reference.IsRow) { // always an error with 2-D area refs // Note - the type of error changes if the pRowArg is negative return(ErrorEval.REF_INVALID); } // When the two-arg version of INDEX() has been invoked and the reference // is a single column ref, the row arg seems to get used as the column index columnIx = rowIx; rowIx = 0; } return(GetValueFromArea(reference, rowIx, columnIx)); } catch (EvaluationException e) { return(e.GetErrorEval()); } }
/** * Returns the first column index that matches the given name. The name can either be * a string or an integer, when it's an integer, then the respective column * (1 based index) is returned. * @param nameValueEval * @param db * @return the first column index that matches the given name (or int) * @ */ private static int GetColumnForTag(ValueEval nameValueEval, TwoDEval db) { int resultColumn = -1; // Numbers as column indicator are allowed, check that. if (nameValueEval is NumericValueEval) { double doubleResultColumn = ((NumericValueEval)nameValueEval).NumberValue; resultColumn = (int)doubleResultColumn; // Floating comparisions are usually not possible, but should work for 0.0. if (doubleResultColumn - resultColumn != 0.0) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } resultColumn -= 1; // Numbers are 1-based not 0-based. } else { resultColumn = GetColumnForName(nameValueEval, db); } return(resultColumn); }
private ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval lookupEval, ValueEval indexEval, LookupUtils.MatchMode matchMode, LookupUtils.SearchMode searchMode) { try { ValueEval lookupValue = OperandResolver.GetSingleValue(lookupEval, srcRowIndex, srcColumnIndex); TwoDEval tableArray = LookupUtils.ResolveTableArrayArg(indexEval); ValueVector vector; if (tableArray.IsColumn) { vector = LookupUtils.CreateColumnVector(tableArray, 0); } else { vector = LookupUtils.CreateRowVector(tableArray, 0); } int matchedIdx = LookupUtils.XlookupIndexOfValue(lookupValue, vector, matchMode, searchMode); return(new NumberEval((double)matchedIdx + 1)); } catch (EvaluationException e) { return(e.GetErrorEval()); } }
private static int GetColumnForName(ValueEval nameValueEval, TwoDEval db) { String name = GetStringFromValueEval(nameValueEval); return GetColumnForString(db, name); }
public static ValueVector CreateRowVector(TwoDEval tableArray, int relativeRowIndex) { return new RowVector((AreaEval)tableArray, relativeRowIndex); }
private bool Calculate(ValueEval[] args) { bool result = InitialResultValue; bool atleastOneNonBlank = false; bool?tempVe; /* * Note: no short-circuit bool loop exit because any ErrorEvals will override the result */ for (int i = 0, iSize = args.Length; i < iSize; i++) { ValueEval arg = args[i]; if (arg is TwoDEval) { TwoDEval ae = (TwoDEval)arg; int height = ae.Height; int width = ae.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.GetValue(rrIx, rcIx); tempVe = OperandResolver.CoerceValueToBoolean(ve, true); if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture)); atleastOneNonBlank = true; } } } continue; } if (arg is RefEval) { ValueEval ve = ((RefEval)arg).InnerValueEval; tempVe = OperandResolver.CoerceValueToBoolean(ve, true); } else if (arg is ValueEval) { ValueEval ve = (ValueEval)arg; tempVe = OperandResolver.CoerceValueToBoolean(ve, false); } else { throw new InvalidOperationException("Unexpected eval (" + arg.GetType().Name + ")"); } if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture)); atleastOneNonBlank = true; } } if (!atleastOneNonBlank) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } return(result); }
/** * For a given database returns the column number for a column heading. * * @param db Database. * @param name Column heading. * @return Corresponding column number. * @If it's not possible to turn all headings into strings. */ private static int GetColumnForString(TwoDEval db, String name) { int resultColumn = -1; for (int column = 0; column < db.Width; ++column) { ValueEval columnNameValueEval = db.GetValue(0, column); String columnName = GetStringFromValueEval(columnNameValueEval); if (name.Equals(columnName)) { resultColumn = column; break; } } return resultColumn; }
public AreaValueArray(TwoDEval ae) : base(ae.Width * ae.Height) { _ae = ae; _width = ae.Width; }
public static ValueVector CreateColumnVector(TwoDEval tableArray, int relativeColumnIndex) { return(new ColumnVector((AreaEval)tableArray, relativeColumnIndex)); }
private bool Calculate(ValueEval[] args) { bool result = InitialResultValue; bool atleastOneNonBlank = false; /* * Note: no short-circuit bool loop exit because any ErrorEvals will override the result */ foreach (ValueEval arg in args) { bool?tempVe; if (arg is TwoDEval) { TwoDEval ae = (TwoDEval)arg; int height = ae.Height; int width = ae.Width; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve = ae.GetValue(rrIx, rcIx); tempVe = OperandResolver.CoerceValueToBoolean(ve, true); if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture)); atleastOneNonBlank = true; } } } continue; } if (arg is RefEval) { RefEval re = (RefEval)arg; int firstSheetIndex = re.FirstSheetIndex; int lastSheetIndex = re.LastSheetIndex; for (int sIx = firstSheetIndex; sIx <= lastSheetIndex; sIx++) { ValueEval ve = re.GetInnerValueEval(sIx); tempVe = OperandResolver.CoerceValueToBoolean(ve, true); if (tempVe != null) { result = PartialEvaluate(result, tempVe.Value); atleastOneNonBlank = true; } } continue; } //else if (arg is ValueEval) //{ // ValueEval ve = (ValueEval)arg; // tempVe = OperandResolver.CoerceValueToBoolean(ve, false); //} if (arg == MissingArgEval.instance) { tempVe = null; // you can leave out parameters, they are simply ignored } else { tempVe = OperandResolver.CoerceValueToBoolean(arg, false); } if (tempVe != null) { result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture)); atleastOneNonBlank = true; } } if (!atleastOneNonBlank) { throw new EvaluationException(ErrorEval.VALUE_INVALID); } return(result); }
/** * Returns the first column index that matches the given name. The name can either be * a string or an integer, when it's an integer, then the respective column * (1 based index) is returned. * @param nameValueEval * @param db * @return the first column index that matches the given name (or int) * @ */ private static int GetColumnForTag(ValueEval nameValueEval, TwoDEval db) { int resultColumn = -1; // Numbers as column indicator are allowed, check that. if (nameValueEval is NumericValueEval) { double doubleResultColumn = ((NumericValueEval)nameValueEval).NumberValue; resultColumn = (int)doubleResultColumn; // Floating comparisions are usually not possible, but should work for 0.0. if (doubleResultColumn - resultColumn != 0.0) throw new EvaluationException(ErrorEval.VALUE_INVALID); resultColumn -= 1; // Numbers are 1-based not 0-based. } else { resultColumn = GetColumnForName(nameValueEval, db); } return resultColumn; }
private static int GetColumnForName(ValueEval nameValueEval, TwoDEval db) { String name = GetStringFromValueEval(nameValueEval); return(GetColumnForString(db, name)); }
/** * Checks a row in a database against a condition database. * * @param db Database. * @param row The row in the database to Check. * @param cdb The condition database to use for Checking. * @return Whether the row matches the conditions. * @If references could not be Resolved or comparison * operators and operands didn't match. */ private static bool FullFillsConditions(TwoDEval db, int row, TwoDEval cdb) { // Only one row must match to accept the input, so rows are ORed. // Each row is made up of cells where each cell is a condition, // all have to match, so they are ANDed. for (int conditionRow = 1; conditionRow < cdb.Height; ++conditionRow) { bool matches = true; for (int column = 0; column < cdb.Width; ++column) { // columns are ANDed // Whether the condition column matches a database column, if not it's a // special column that accepts formulas. bool columnCondition = true; ValueEval condition = null; try { // The condition to Apply. condition = solveReference(cdb.GetValue(conditionRow, column)); } catch (Exception e) { // It might be a special formula, then it is ok if it fails. columnCondition = false; } // If the condition is empty it matches. if (condition is BlankEval) continue; // The column in the DB to apply the condition to. ValueEval targetHeader = solveReference(cdb.GetValue(0, column)); targetHeader = solveReference(targetHeader); if (!(targetHeader is StringValueEval)) columnCondition = false; else if (GetColumnForName(targetHeader, db) == -1) // No column found, it's again a special column that accepts formulas. columnCondition = false; if (columnCondition == true) { // normal column condition // Should not throw, Checked above. ValueEval target = db.GetValue( row, GetColumnForName(targetHeader, db)); // Must be a string. String conditionString = GetStringFromValueEval(condition); if (!testNormalCondition(target, conditionString)) { matches = false; break; } } else { // It's a special formula condition. throw new NotImplementedException( "D* function with formula conditions"); } } if (matches == true) { return true; } } return false; }
/** * Checks a row in a database against a condition database. * * @param db Database. * @param row The row in the database to Check. * @param cdb The condition database to use for Checking. * @return Whether the row matches the conditions. * @If references could not be Resolved or comparison * operators and operands didn't match. */ private static bool FullFillsConditions(TwoDEval db, int row, TwoDEval cdb) { // Only one row must match to accept the input, so rows are ORed. // Each row is made up of cells where each cell is a condition, // all have to match, so they are ANDed. for (int conditionRow = 1; conditionRow < cdb.Height; ++conditionRow) { bool matches = true; for (int column = 0; column < cdb.Width; ++column) { // columns are ANDed // Whether the condition column matches a database column, if not it's a // special column that accepts formulas. bool columnCondition = true; ValueEval condition = null; try { // The condition to Apply. condition = solveReference(cdb.GetValue(conditionRow, column)); } catch (Exception e) { // It might be a special formula, then it is ok if it fails. columnCondition = false; } // If the condition is empty it matches. if (condition is BlankEval) { continue; } // The column in the DB to apply the condition to. ValueEval targetHeader = solveReference(cdb.GetValue(0, column)); targetHeader = solveReference(targetHeader); if (!(targetHeader is StringValueEval)) { columnCondition = false; } else if (GetColumnForName(targetHeader, db) == -1) { // No column found, it's again a special column that accepts formulas. columnCondition = false; } if (columnCondition == true) { // normal column condition // Should not throw, Checked above. ValueEval target = db.GetValue( row, GetColumnForName(targetHeader, db)); // Must be a string. String conditionString = GetStringFromValueEval(condition); if (!testNormalCondition(target, conditionString)) { matches = false; break; } } else { // It's a special formula condition. throw new NotImplementedException( "D* function with formula conditions"); } } if (matches == true) { return(true); } } return(false); }
public ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval database, ValueEval filterColumn, ValueEval conditionDatabase) { // Input Processing and error Checks. if (!(database is TwoDEval) || !(conditionDatabase is TwoDEval)) { return(ErrorEval.VALUE_INVALID); } TwoDEval db = (TwoDEval)database; TwoDEval cdb = (TwoDEval)conditionDatabase; int fc; try { fc = GetColumnForName(filterColumn, db); } catch (EvaluationException e) { return(ErrorEval.VALUE_INVALID); } if (fc == -1) { // column not found return(ErrorEval.VALUE_INVALID); } // Reset algorithm. algorithm.Reset(); // Iterate over all db entries. for (int row = 1; row < db.Height; ++row) { bool matches = true; try { matches = FullFillsConditions(db, row, cdb); } catch (EvaluationException e) { return(ErrorEval.VALUE_INVALID); } // Filter each entry. if (matches) { try { ValueEval currentValueEval = solveReference(db.GetValue(row, fc)); // Pass the match to the algorithm and conditionally abort the search. bool shouldContinue = algorithm.ProcessMatch(currentValueEval); if (!shouldContinue) { break; } } catch (EvaluationException e) { return(e.GetErrorEval()); } } } // Return the result of the algorithm. return(algorithm.Result); }
/** * don't count cells that are subtotals */ public bool Matches(TwoDEval areEval, int rowIndex, int columnIndex) { return !areEval.IsSubTotal(rowIndex, columnIndex); }
public static ValueVector CreateColumnVector(TwoDEval tableArray, int relativeColumnIndex) { return new ColumnVector((AreaEval)tableArray, relativeColumnIndex); }
/** * don't count cells that are subtotals */ public bool Matches(TwoDEval areEval, int rowIndex, int columnIndex) { return(!areEval.IsSubTotal(rowIndex, columnIndex)); }
/** * @return <c>null</c> if the supplied area is neither a single row nor a single colum */ public static ValueVector CreateVector(TwoDEval ae) { if (ae.IsColumn) { return CreateColumnVector(ae, 0); } if (ae.IsRow) { return CreateRowVector(ae, 0); } return null; }
private static ValueEval GetValueFromArea(TwoDEval ae, int pRowIx, int pColumnIx) { Debug.Assert(pRowIx >= 0); Debug.Assert(pColumnIx >= 0); TwoDEval result = ae; if (pRowIx != 0) { // Slightly irregular logic for bounds checking errors if (pRowIx > ae.Height) { // high bounds check fail gives #REF! if arg was explicitly passed throw new EvaluationException(ErrorEval.REF_INVALID); } result = result.GetRow(pRowIx - 1); } if (pColumnIx != 0) { // Slightly irregular logic for bounds checking errors if (pColumnIx > ae.Width) { // high bounds check fail gives #REF! if arg was explicitly passed throw new EvaluationException(ErrorEval.REF_INVALID); } result = result.GetColumn(pColumnIx - 1); } return result; }
private static bool AreasAllSameSize(TwoDEval[] args, int height, int width) { for (int i = 0; i < args.Length; i++) { TwoDEval areaEval = args[i]; // check that height and width match if (areaEval.Height != height) { return false; } if (areaEval.Width != width) { return false; } } return true; }
/** * Returns one column from an <c>AreaEval</c> * * @(#VALUE!) if colIndex Is negative, (#REF!) if colIndex Is too high */ private ValueVector CreateResultColumnVector(TwoDEval tableArray, int colIndex) { if (colIndex >= tableArray.Width) { throw EvaluationException.InvalidRef(); } return LookupUtils.CreateColumnVector(tableArray, colIndex); }
public static ValueVector CreateRowVector(TwoDEval tableArray, int relativeRowIndex) { return(new RowVector((AreaEval)tableArray, relativeRowIndex)); }