public void CellValueChanged(object sender)
{
SpreadsheetCell cell = sender as SpreadsheetCell;
double parsed;
// if the value of this cell was changed, and there are other cells that depend on it, and its value is able to be parsed into a double,
// then update the cells that depended on its value
if (cell.CellDependencies.Count > 0 && double.TryParse(cell.Value, out parsed) == true)
{
Cell updateCell;
for (int i = 0; i < cell.CellDependencies.Count; i++)
{
updateCell = cell.CellDependencies[i];
string expression = updateCell.Text.Substring(1); // grab expression from cell
ExpressionTree tree = new ExpressionTree(expression); // create new expression tree based on expression in cell
List <string> var = tree.Variables.Keys.ToList <string>(); // create list of variables needing values
if (tree.Variables.Count == 1 && expression.Length == var[0].Length)
{
bool error = IsReferenced(updateCell.Variables, updateCell); // checks for a circular reference
if (error == true)
{
updateCell.Value = "!(self reference)"; // sets self reference if circular reference is returned
}
else if (GetCell(expression).Value == "!(self reference)" || GetCell(expression).Value == "!(bad reference)")
{
updateCell.Value = "!(bad reference)"; // sets bad reference if variable in cell has self or bad reference
}
else
{
updateCell.Value = GetCell(expression).Value;
}
}
else
{
bool error = IsReferenced(updateCell.Variables, updateCell);
if (error == true)
{
updateCell.Value = "!(bad reference)"; // sets bad reference if circular reference is found
}
else
{
tree.SetVariables(this, var); // set variables based on cell values
updateCell.Value = tree.Evaluate().ToString(); // evaluate tree and set cell's value
}
}
alteredCell = updateCell;
if (ValueChange != null)
{
ValueChange(this);
}
}
}
else if (cell.CellDependencies.Count > 0 && (cell.Value == "!(bad reference)" || cell.Value == "!(self reference)"))
{
for (int i = 0; i < cell.CellDependencies.Count; i++)
{
if (cell.CellDependencies[i] != cell)
{
cell.CellDependencies[i].Value = "!(bad reference)"; // sets bad reference to all dependent cells if this cell has self or bad reference
}
else
{
cell.CellDependencies[i].Value = "!(self reference)"; // sets self reference if cell finds itself in its dependent cells
}
}
}
else if (cell.Variables != null && cell.CellDependencies.Count > 0)
{
for (int i = 0; i < cell.Variables.Count; i++)
{
if (IsReferenced(cell.Variables[i].Variables, cell.Variables[i]))
{
cell.Value = "!(circular reference)"; // sets circular reference if found
}
}
}
alteredCell = cell;
if (ValueChange != null)
{
ValueChange(this); // notify spreadsheet graphic of change in cell
}
}
/// <summary>
/// Updates the spreadsheet.
/// </summary>
/// <param name="sender">The sending object.</param>
/// <param name="e">A property changed event arguments.</param>
private void Sheet_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
try
{
if (!e.PropertyName.Contains("bgcolor"))
{
int row = Convert.ToInt32(e.PropertyName.Split(' ')[0]);
int column = Convert.ToInt32(e.PropertyName.Split(' ')[1]);
Cell cell = this.Cells[row, column];
// Check to see if cell evaluation is necessary.
if (cell.Text.StartsWith("="))
{
// Check for a self-reference.
if (cell.Text.Contains(((char)(column + 65)).ToString() + (row + 1)) || cell.Text.Contains(((char)(column + 97)).ToString() + (row + 1)))
{
this.CellPropertyChanged.Invoke(this, new PropertyChangedEventArgs(e.PropertyName + " SELF_REFERENCE")); // Call CellPropertyChanged methods in Form1.
return;
}
string expression = cell.Text.Substring(1);
ExpressionTree et = new ExpressionTree(expression);
et = new ExpressionTree(expression);
string[] expArr = expression.Split(et.Operators, StringSplitOptions.None);
// Check the length of the expression array.
if (expArr.Length == 1)
{
if (this.CircularReferenceCheck(this.GetCell(row, column), this.GetCell(expArr[0])))
{
this.CellPropertyChanged.Invoke(this, new PropertyChangedEventArgs(e.PropertyName + " CIRCULAR_REFERENCE"));
return;
}
this.GetCell(expArr[0]).DependencyChanged += this.Dependency_PropertyChanged;
this.GetCell(expArr[0]).AddDependency(cell);
cell.Value = this.GetCell(expArr[0]).Value;
}
else
{
foreach (string s in expArr)
{
foreach (char c in s)
{
if (c < 48 || c > 57)
{
if (this.CircularReferenceCheck(this.GetCell(row, column), this.GetCell(s)))
{
this.CellPropertyChanged.Invoke(this, new PropertyChangedEventArgs(e.PropertyName + " CIRCULAR_REFERENCE"));
return;
}
et.SetVariable(s, Convert.ToDouble(this.GetCell(s).Value));
if (!e.PropertyName.Contains("RESET"))
{
this.GetCell(s).DependencyChanged += this.Dependency_PropertyChanged;
this.GetCell(s).AddDependency(cell);
}
break;
}
}
}
cell.Value = et.Evaluate().ToString();
}
}
else
{
cell.Value = cell.Text;
}
}
this.CellPropertyChanged.Invoke(this, e); // Call CellPropertyChanged methods in Form1.
}
catch (Exception)
{
// Bad reference detected!
this.CellPropertyChanged.Invoke(this, new PropertyChangedEventArgs(e.PropertyName + " BAD_REFERENCE")); // Call CellPropertyChanged methods in Form1.
}
}
// tells spreadsheet what to do when a cell is changed
public void CellTextChanged(object sender)
{
SpreadsheetCell cell = sender as SpreadsheetCell;
alteredCell = cell; // set alteredCell to current cell value (allows spreadsheet graphic to view this cell to know what to change)
// determines what the cell's value should be set to based on its text attribute
if (cell.Text == string.Empty || cell.Text == null)
{
cell.Value = string.Empty;
cell.Variables = null;
}
else if (cell.Text[0] == '=')
{
cell.PreviousVariables = cell.Variables;
string expression = cell.Text.Substring(1); // grab expression from cell
ExpressionTree tree = new ExpressionTree(expression); // create new expression tree based on expression in cell
List <string> var = tree.Variables.Keys.ToList <string>(); // create list of variables needing values
List <Cell> var2 = new List <Cell>();
for (int k = 0; k < var.Count; k++)
{
if (GetCell(var[k]) != null)
{
var2.Add(GetCell(var[k])); // create list of cells that this cell depends on
}
}
cell.Variables = var2; // set cell's variable list
if (cell.PreviousVariables.Count != 0)
{
List <Cell> difference = cell.PreviousVariables.Except(var2).ToList(); // find difference between what variables are in current expression and what was in expression before
if (difference.Count > 0)
{
for (int t = 0; t < difference.Count; t++)
{
cell.CellDependencies.Remove(difference[t]); // removes cell from dependency list
}
}
}
// if there are variables in the expression tree, then set the cells with the names of the variables to have this cell as a dependent cell
if (var.Count != 0)
{
for (int i = 0; i < var.Count; i++)
{
// check to see if this cell is already a dependent of the variable cell
if (GetCell(var[i]) != null)
{
if (!GetCell(var[i]).CellDependencies.Contains(cell))
{
GetCell(var[i]).CellDependencies.Add(cell); // add to cell dependency if the current cell is not already there
}
}
}
}
// if there is only a single variable and the expression is comprised of only this variable, then copy the value of the variable over to the value of this cell
// otherwise, evaluate the expression tree and set this evaluated value as the value for this cell
if (tree.Variables.Count == 1 && expression.Length == var[0].Length)
{
if (GetCell(expression) == null)
{
cell.Value = "!(bad reference)"; // set to bad reference if cell cannot be found in spreadsheet
}
else if (GetCell(expression).Value == "!(self reference)")
{
cell.Value = "!(bad reference)"; // if cell being referred to has a self reference, then set to bad reference
}
else
{
bool error = IsReferenced(cell.Variables, cell); // check for circular reference
if (cell.CellDependencies.Contains(cell))
{
cell.Value = "!(self reference)"; // set to self reference if variables in cell includes itself
}
else if (error == true)
{
cell.Value = "!(circular reference)"; // sets circular reference if one is found
}
else if (GetCell(expression).Value == "!(self reference)" || GetCell(expression).Value == "!(bad reference)")
{
cell.Value = "!(bad reference)"; // sets bad reference if variable in cell is either self referenced or also has a bad reference
}
else
{
cell.Value = GetCell(expression).Value;
}
}
}
else
{
bool error = tree.SetVariables(this, var); // set variables based on cell values
string name = string.Empty;
char column = (char)(cell.ColumnIndex + 65);
string row = (cell.RowIndex + 1).ToString();
name = name + column;
name = name + row;
bool refError = IsReferenced(cell.Variables, cell);
if (GetCell(name).Variables.Contains(GetCell(name)))
{
cell.Value = "!(self reference)"; // sets self referenced if cell references itself
}
else if (refError == true)
{
cell.Value = "!(circular reference)"; // sets circular reference if one is found
}
else if (error == true)
{
cell.Value = "!(bad reference)"; // sets bad reference if one is found
}
else
{
cell.Value = tree.Evaluate().ToString(); // evaluate tree and set cell's value
}
}
}
else
{
// if we changed from an expression containing '=' to one that does not, then remove this cell from all
// dependency lists of other cells that were previously variables in this cell's expression
if (cell.PreviousVariables.Count > 0)
{
for (int i = 0; i < cell.PreviousVariables.Count; i++)
{
cell.PreviousVariables[i].CellDependencies.Remove(cell);
}
}
cell.Variables = new List <Cell>(); // clear variable list
cell.Value = cell.Text; // set this cell's value to its text
}
if (TextChange != null)
{
TextChange(this); // notify spreadsheet graphic of change in cell
}
}
public void TestForNewVaraibleException()
{
ExpressionTree tree = new ExpressionTree("x");
Assert.AreEqual("0", tree.Evaluate().ToString());
}
public void TestParentheses()
{
ExpressionTree tree = new ExpressionTree("((9+2)-(1+(6-1)))");
Assert.AreEqual("5", tree.Evaluate().ToString());
}
public void TestExpressionTreeWithNumbersAndVariables()
{
ExpressionTree tree = new ExpressionTree("w-5+y+3");
Assert.AreEqual("-2", tree.Evaluate().ToString());
}
public void TestExpressionTreeWithCombinationOfOperators()
{
ExpressionTree tree = new ExpressionTree("(2+3)*6-3/2");
Assert.AreEqual("28.5", tree.Evaluate().ToString());
}
public void TestDivisionPrecedence()
{
ExpressionTree tree = new ExpressionTree("5+10/2+5-2");
Assert.AreEqual("13", tree.Evaluate().ToString());
}
public void TestOperatorPrecedence()
{
ExpressionTree tree = new ExpressionTree("3+4*3+14/7");
Assert.AreEqual("17", tree.Evaluate().ToString());
}
public void TestParenthesisPrecedenceOverMultiplicationOperator()
{
ExpressionTree tree = new ExpressionTree("((3+3)*2)");
Assert.AreEqual("12", tree.Evaluate().ToString());
}
public void TestParenthesisPrecedence()
{
ExpressionTree tree = new ExpressionTree("((1+2)+(6+3)-6)");
Assert.AreEqual("6", tree.Evaluate().ToString());
}
public void TestMultiplicationPrecedenceProperty()
{
ExpressionTree tree = new ExpressionTree("3+4*3+4");
Assert.AreEqual("19", tree.Evaluate().ToString());
}
public void TestSetVariableMethodException()
{
ExpressionTree tree = new ExpressionTree("10+x+y");
Assert.AreEqual("10", tree.Evaluate().ToString());
}
public void TestMultiplyByZeroEvaluateMethod()
{
ExpressionTree tree = new ExpressionTree("3*4*3*99*7*0");
Assert.AreEqual("0", tree.Evaluate().ToString());
}