/// <summary>
/// Sets the cell contents with a Formula. Throws CircularException if a circular dependency is established.
/// </summary>
/// <param name="name">Name of the cell to set contents inside of</param>
/// <param name="formula">Formula to place in cell</param>
/// <returns>ISet<string> of all the cells that need to be updated</string></returns>
protected override ISet <string> SetCellContents(string name, SpreadsheetUtilities.Formula formula)
{
IEnumerable <string> variables = formula.GetVariables();
foreach (String variable in variables)
{
if (!IsValid(variable))
{
throw new FormulaFormatException("Invalid cell name.");
}
cellDependency.AddDependency(Normalize(variable), name);
}
try
{
IEnumerable <string> cellsToRecalculate = GetCellsToRecalculate(name);
}
//Oops there's been a circular exception, restore the dependencies
catch (CircularException)
{
foreach (String variable in variables)
{
cellDependency.RemoveDependency(Normalize(variable), name);
}
//Now that the dependencies are restored, we can throw the exception out
throw new CircularException();
}
//No exception, change the cell to the formula and evaluate the formula also
//cells[name] = new Cell(formula, formula.Evaluate(s => (double)cells[s].value));
cells[name] = new Cell(formula, formula.Evaluate(VariableLookup));
return(new HashSet <string>(GetCellsToRecalculate(name)));
}
public void Test29()
{
Formula f = new Formula("x+7");
Assert.AreEqual(9.0, f.Evaluate(s => 2));
Formula f1 = new Formula("x+7", Normalize, Validate);
Assert.AreEqual(11.0, f.Evaluate(s => 4));
}
public void Test27()
{
Formula f = new Formula("((((x1+x2)+x3)+x4)+x5)+x6");
Assert.AreEqual(12.0, f.Evaluate(s => 2));
}
public void Test25()
{
Formula f = new Formula("y1*3-8/2+4*(8-9*2)/14*x7");
Assert.AreEqual(5.142857142, (double)f.Evaluate(s => (s == "x7") ? 1 : 4), 1e-9);
}
public void Test21()
{
Formula f = new Formula("xx");
Assert.AreEqual(0.0, f.Evaluate(s => 0));
}
public void Test16()
{
Formula f = new Formula("2+3*5+(3+4*8)*5+2");
Assert.AreEqual(194.0, f.Evaluate(s => 0));
}
public void Test14()
{
Formula f = new Formula("2+(3+5*9)");
Assert.AreEqual(50.0, f.Evaluate(s => 0));
}
public void Test10()
{
Formula f = new Formula("2+6*3");
Assert.AreEqual(20.0, f.Evaluate(s => 0));
}
public void Test30()
{
Formula f = new Formula("x1+(x2+(x3+(x4+(x5+x6))))");
Assert.AreEqual(6, (double)f.Evaluate(s => 1), 1e-9);
}
/// <summary>
/// Used to get values of direct or indirect dependents
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
private object myEvaluate(string name)
{
Formula myEval;
// GetCellContents(name);
if (GetCellContents(name) is double)
{
return (double)mySpreadsheet[name].myValue;
}
if (GetCellContents(name) is string)
{
return (string)mySpreadsheet[name].myValue;
}
if (GetCellContents(name) is Formula)
{
Formula myFormula = (Formula)GetCellContents(name);
string form = myFormula.ToString();
myEval = new Formula(form, IsValid, Normalize);
return myEval.Evaluate(myLookup);
}
return 0;
}
/// <summary>
/// Takes in a cell name and a formula and returns the dependents
/// Calculates the value of the formula then recalculates the value of any cell that depends directly or inderectly on it.
/// </summary>
/// <param name="name"></param>
/// <param name="formula"></param>
/// <returns></returns>
protected override ISet<string> SetCellContents(string name, Formula formula)
{
name = Normalize(name);
HashSet<string> mySet = new HashSet<string>();
HashSet<string> replaceSet = new HashSet<string>();
if (Object.ReferenceEquals(formula, null))
{
throw new ArgumentNullException();
}
if (Object.ReferenceEquals(name, null))
{
throw new InvalidNameException();
}
if (!Regex.IsMatch(name, "^([a-z]|[A-Z])+\\d+$") | !IsValid(name))
{
throw new InvalidNameException();
}
try
{
foreach (string s in formula.GetVariables())
{
replaceSet.Add(s);
}
// dependentCells.ReplaceDependees(name, mySet);
dependentCells.ReplaceDependees(name, replaceSet);
GetCellsToRecalculate(name);
}
catch (CircularException)
{
throw new CircularException();
}
Formula myEvl = new Formula(formula.ToString(), IsValid, Normalize);
myCell = new Cell(formula, myEvl.Evaluate(myLookup));
if (mySpreadsheet.ContainsKey(name))
{
mySpreadsheet[name] = myCell;
}
else
{
mySpreadsheet.Add(name, myCell);
}
mySet.Add(name);
foreach (string s in GetCellsToRecalculate(name))
{
mySet.Add(s);
}
foreach (string s in mySet)
{
myCell = new Cell(GetCellContents(s), myEvaluate(s));
mySpreadsheet[s] = myCell;
}
Changed = true;
return mySet;
}
public void StressTestMultipleVariables()
{
Formula testFormula = new Formula("(3+x)*y", s => s.ToUpper(), s => (s == "X" || s == "Y") ? true : false);
Func<string, double> lookup = s => (s == "X") ? 1 : 4;
Assert.AreEqual(16.0, testFormula.Evaluate(lookup));
}
public void TestEvaluateDivideByZero2()
{
Func<string, double> lookup = s => -2;
Formula testFormula = new Formula("6/(x+2)");
Object c = testFormula.Evaluate(lookup);
Assert.AreEqual("SpreadsheetUtilities.FormulaError", c.ToString());
}
/// <summary>
/// Constructor for cell with Formula content
/// Value of this cell is the evaluated content
/// </summary>
/// <param name="_name"></param>
/// <param name="_content"></param>
/// <param name="lookup"></param>
public Cell(string _name, Formula _content, Func<string, double> lookup)
{
name = _name;
content = _content;
value = _content.Evaluate(lookup);
}
public void testEval3stress()
{
Formula f1 = new Formula("2*(5+x)+(5-x*(x/x))");
Assert.AreEqual(17.0, f1.Evaluate(lookerupper));
}
public void Test31()
{
Formula f = new Formula("((((x1+x2)+x3)+x4)+x5)+x6");
Assert.AreEqual(12, (double)f.Evaluate(s => 2), 1e-9);
}
public void Test9()
{
Formula f = new Formula("2*6+3");
Assert.AreEqual(15.0, f.Evaluate(s => 0));
}
public void Test32()
{
Formula f = new Formula("a4-a4*a4/a4");
Assert.AreEqual(0, (double)f.Evaluate(s => 3), 1e-9);
}
public void Test11()
{
Formula f = new Formula("(2+6)*3");
Assert.AreEqual(24.0, f.Evaluate(s => 0));
}
public void Test16()
{
Formula f = new Formula("2+X1");
Assert.IsInstanceOfType(f.Evaluate(s => { throw new ArgumentException("Unknown variable"); }), typeof(FormulaError));
}
public void Test15()
{
Formula f = new Formula("2+3*(3+5)");
Assert.AreEqual(26.0, f.Evaluate(s => 0));
}
public void Test17()
{
Formula f = new Formula("5/0");
Assert.IsInstanceOfType(f.Evaluate(s => 0), typeof(FormulaError));
}
public void Test17()
{
Formula f = new Formula("5/0");
Assert.IsTrue(f.Evaluate(s => 0) is FormulaError);
}
public void Test18()
{
Formula f = new Formula("(5 + X1) / (X1 - 3)");
Assert.IsInstanceOfType(f.Evaluate(s => 3), typeof(FormulaError));
}
public void Test22()
{
Formula f = new Formula("5+xx");
Assert.AreEqual(5.0, f.Evaluate(s => 0));
}
public void Test2()
{
Formula f = new Formula("X5");
Assert.AreEqual(13.0, f.Evaluate(s => 13));
}
public void Test26()
{
Formula f = new Formula("x1+(x2+(x3+(x4+(x5+x6))))");
Assert.AreEqual(6.0, f.Evaluate(s => 1));
}
public void Test6()
{
Formula f = new Formula("16/2");
Assert.AreEqual(8.0, f.Evaluate(s => 0));
}
public void Test28()
{
Formula f = new Formula("a4-a4*a4/a4");
Assert.AreEqual(0.0, f.Evaluate(s => 3));
}
public void Test7()
{
Formula f = new Formula("2+X1");
Assert.AreEqual(6.0, f.Evaluate(s => 4));
}
public void Test33()
{
Formula f = new Formula("4-3*1/a4");
Assert.IsTrue(f.Evaluate(s => 0) is FormulaError);
}
public void TestNormalAddition()
{
SpreadsheetUtilities.Formula f = new SpreadsheetUtilities.Formula("2.0 + 2.5");
Assert.AreEqual(4.5, f.Evaluate(s => 0));
}