/** * @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); }
private static void CollectValues(ValueEval arg, IList temp) { if (arg is TwoDEval) { TwoDEval ae = (TwoDEval)arg; int width = ae.Width; int height = ae.Height; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve1 = ae.GetValue(rrIx, rcIx); CollectValue(ve1, temp, false); } } return; } if (arg is RefEval) { RefEval re = (RefEval)arg; int firstSheetIndex = re.FirstSheetIndex; int lastSheetIndex = re.LastSheetIndex; for (int sIx = firstSheetIndex; sIx <= lastSheetIndex; sIx++) { CollectValue(re.GetInnerValueEval(sIx), temp, true); } return; } CollectValue(arg, temp, true); }
protected override ValueEval GetItemInternal(int index) { int rowIx = index / _width; int colIx = index % _width; return(_ae.GetValue(rowIx, colIx)); }
private static void CollectValues(ValueEval arg, IList temp) { if (arg is TwoDEval) { TwoDEval ae = (TwoDEval)arg; int width = ae.Width; int height = ae.Height; for (int rrIx = 0; rrIx < height; rrIx++) { for (int rcIx = 0; rcIx < width; rcIx++) { ValueEval ve1 = ae.GetValue(rrIx, rcIx); CollectValue(ve1, temp, false); } } return; } if (arg is RefEval) { RefEval re = (RefEval)arg; CollectValue(re.InnerValueEval, temp, true); return; } CollectValue(arg, temp, true); }
/** * Collects values from a single argument */ private void CollectValues(ValueEval operand, DoubleList temp) { if (operand is ThreeDEval) { ThreeDEval ae = (ThreeDEval)operand; for (int sIx = ae.FirstSheetIndex; sIx <= ae.LastSheetIndex; sIx++) { 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(sIx, rrIx, rcIx); if (!IsSubtotalCounted && ae.IsSubTotal(rrIx, rcIx)) { continue; } CollectValue(ve, true, temp); } } } return; } 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; for (int sIx = re.FirstSheetIndex; sIx <= re.LastSheetIndex; sIx++) { CollectValue(re.GetInnerValueEval(sIx), true, temp); } return; } CollectValue((ValueEval)operand, false, temp); }
/** * 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 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); }
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); }
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); }
/** * 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; }
/** * 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; }
/** * @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; }