/// <summary>
/// REQUIREMENT: cell expression text starts with "=".
/// Evaluates cell's text as an expression and returns its value.
/// </summary>
/// <param name="cell">cell who's text is to be evaluated.</param>
/// <returns>Evaluated expression.</returns>
private string EvaluateCellExpression(SpreadsheetCell cell)
{
// removes the first character (which should be '=') and whitespace.
string expression = cell.Text.Substring(1).Replace(" ", string.Empty);
ExpressionTree et = new ExpressionTree(expression);
bool variableError = this.HandleCellVariables(et, cell);
if (!variableError)
{
return(et.Evaluate().ToString());
}
else
{
return(cell.Value);
}
}
/// <summary>
/// REQUIREMENT: Variable names must be formatted as: "[column letter][row number]"
/// Defines cell variables in the expression tree and subscribes the
/// current cell to each variable cell's property changed event.
/// </summary>
/// <param name="et">Expression tree.</param>
/// <param name="currCell">Current spreadsheet cell.</param>\
/// <returns>If there was an error.</returns>
private bool HandleCellVariables(ExpressionTree et, SpreadsheetCell currCell)
{
bool variableError = false;
List <string> variableNames = et.GetVariableNames();
foreach (string name in variableNames)
{
SpreadsheetCell varCell = this.GetCellFromName(name);
if (varCell == null)
{
currCell.SetValue("!(bad reference)");
variableError = true;
}
else if (varCell == currCell)
{
currCell.SetValue("!(self reference)");
variableError = true;
}
else if (this.ContainsCircularReference(currCell, varCell))
{
currCell.SetValue("!(circular reference)");
variableError = true;
}
else
{
// If the cell's value string cannot be parsed as a double, defaults to 0.
double value = 0.0;
try
{
value = double.Parse(varCell.Value);
}
catch (FormatException) { }
et.SetVariable(name, value);
currCell.SubToCellChange(varCell);
}
}
return(variableError);
}
/// <summary>
/// Here is where each cell that's changed essentially has a delegate that notifies its respective event has changed.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
{
if (e.PropertyName == "Text")
{
BasicCell temporary = sender as BasicCell;
string cellname = temporary.GetCellName();
DeleteDependency(cellname);
if (temporary.Text != "" && temporary.Text[0] == '=' && temporary.Text.Length > 1)
{
ExpressionTree tree = new ExpressionTree(temporary.Text.Substring(1));
SetDependency(cellname, tree.GetVariables());
}
EvaluateCell(sender as Cell);
}
else if (e.PropertyName == "BGColor")
{
CellPropertyChanged(sender, new PropertyChangedEventArgs("BGColor"));
}
}
/// <summary>
/// Function used to evaluate all the cells.
/// LOGIC LIVES HERE COULDN'T ENCAPSULATE FURTHER
/// </summary>
/// <param name="cell"></param>
private void EvaluateCell(Cell cell)
{
BasicCell evalCell = cell as BasicCell;
bool error = false;
if (string.IsNullOrWhiteSpace(evalCell.Text))
{
evalCell.setValue("");
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
else if (evalCell.Text.Length > 1 && evalCell.Text[0] == '=')
{
string text = evalCell.Text.Substring(1);
ExpressionTree evalTree = new ExpressionTree(text);
string[] variables = evalTree.GetVariables();
foreach (string variableName in variables)
{
if (GetCell(variableName) == null)
{
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
error = true;
break;
}
Cell variableCell = GetCell(variableName);
double variableValue;
if (string.IsNullOrEmpty(variableCell.Value))
{
evalTree.SetVariable(variableName, 0);
}
else if (!double.TryParse(variableCell.Value, out variableValue))
{
evalTree.SetVariable(variableName, 0);
}
else
{
evalTree.SetVariable(variableName, variableValue);
}
string cellToEval = evalCell.ColumnIndex.ToString() + evalCell.RowIndex.ToString();
if (variableName == cellToEval)
{
evalCell.setValue("!(Self Reference)");
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
error = true;
break;
}
}
if (error)
{
return;
}
foreach (string variableName in variables)
{
if (IsCircularReference(variableName, evalCell.GetCellName()))
{
evalCell.setValue("!(Circular Reference!)");
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
error = true;
break;
}
}
if (error)
{
return;
}
evalCell.setValue(evalTree.Evaluate().ToString());
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
else
{
evalCell.setValue(evalCell.Text);
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
string cellName = evalCell.GetCellName();
if (dependencyDict.ContainsKey(cellName))
{
foreach (string dependentCell in dependencyDict[cellName])
{
EvaluateCell(GetCell(dependentCell));
}
}
}
public void setValue(spreadSheetCell cell)
{
//we dont want to actually change the cell's text property, so we use toDelimit to store a copy
string toDelimit = cell.Text;
bool InvalidName = false;
bool SelfRef = false;
bool CircleRef = false;
//if the cell's text starts with an "=" then we will need to perform operations on the cell
if (toDelimit.StartsWith("="))
{
InvalidName = false;
//this is an empty string that will store the new expression to be passed to the tree
string newExpression = "";
//This is going to help us grab all the important elements of the cell such as the constants and the variables
var importantElements = Regex.Matches(toDelimit, @"\d+|\w+|\S").OfType<Match>().Select(m => m.Value);
//string s will either be an operator, parenthesis, or cell name
foreach (string s in importantElements)
{
//if a letter is the first character in the string, then it is a cell name
if (s[0] > 64 && s[0] < 91)
{
//The following checks for a letter in the substring following the first letter
try
{
Int32.Parse(s.Substring(1));
}
catch (FormatException)
{
InvalidName = true;
}
//Check to make sure that the cell is within the range specific (1-50)
if (InvalidName == false)
{
if (Int32.Parse(s.Substring(1)) < 51 && Int32.Parse(s.Substring(1)) > 0)
{
InvalidName = false;
}
else
InvalidName = true;
}
if (cell.ReturnName() == s)
{
SelfRef = true;
break;
}
if (InvalidName == false && GetCell(s).Value != "")
{
//Check circular reference of cell about to be added
if (circleRef(cell.ReturnName(), s, GetCell(s).Text))
{
CircleRef = true;
break;
}
//If we get to this point we know we aren't adding a circular ref
newExpression += GetCell(s).Value;
}
//Default value, simply add to new expression
else if (InvalidName == false && GetCell(s).Value == "")
{
newExpression += "0";
}
}
else
{
newExpression += s;
}
}
if (CircleRef == false && SelfRef == false && InvalidName == false)
{
ExpressionTree Exp = new ExpressionTree();
Exp.BuildTree(newExpression.Substring(1));
cell.Value = Exp.Evaluate().ToString();
}
}
else
if (InvalidName == false && SelfRef == false)
cell.Value = toDelimit;
if (CircleRef)
{
cell.Value = "!(circular reference)";
}
if (InvalidName)
{
cell.Value = "!(bad cell name)";
}
if (SelfRef)
{
cell.Value = "!(self reference)";
}
}
protected void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell sheetCell = (SpreadsheetCell)sender;
string temp_string = sheetCell.Text;
// If the text is a formula
if (temp_string == string.Empty || temp_string[0] != '=') // If the text is NOT a formula
{
sheetCell.Text = temp_string;
sheetCell.Value = sheetCell.Text;
}
else //(temp_string != string.Empty || temp_string[0] == '=')
{
if (temp_string.Length > 2)
{
string newExpression = temp_string.Substring(1); // Take out the '=' in the text, the rest will be an expression
// Create a tree from that expression
ExpressionTree tree = new ExpressionTree(newExpression);
// Go through every variables in the tree dictionary
foreach (string key in tree.GetVariableNames())
{
// We are using a dictionary because dictionary cannot add the same key twice
// So it is easier to catch acception and check whether the function went into a circular loop
try
{
circularReferenceKeys.Add(sheetCell, 1);
}
catch (ArgumentException)
{
if (!this.CheckCircularReference())
{
Ok = 1;
return;
}
};
// MAKE THE KEY TRUE
// These booleans are here to evaluate the formula
// Covers when formula is less than 4
// When its 3 or 2, its in the correct format (Char Int (Int))
// Covers when the formula is larger than 1
// Anything that evaluate false from this will not be considered a proper formula
bool four = key.Length < 4;
bool three = true;
if (key.Length == 3)
{
// three = Int32.TryParse(key[1].ToString(), out int temp1) && Int32.TryParse(key[2].ToString(), out int temp2);
if (Int32.TryParse(key[1].ToString(), out int temp1))
{
if (Int32.TryParse(key[2].ToString(), out int temp2))
{
Int32.TryParse(key.Substring(1), out int temp3);
if (temp3 > 50)
{
three = false;
}
else
{
three = true;
}
}
else
{
three = false;
}
}
}
bool two = true;
if (key.Length == 2)
{
two = Int32.TryParse(key[1].ToString(), out int tempp1);
if (tempp1 == 0)
{
two = false;
}
}
bool one = key.Length > 1;
if (four && three && two && one)
{
// Here we are doing the Try-Catch key
// Because if the program fails from creating a cell from that key
// that means the formula is bad
try
{
// Make a Cell out of that tree dictionary key
Cell cell = this.GetCellFromName(key);
double val = 0.0;
if (double.TryParse(cell.Value, out val))
{
tree.SetVariable(key, val);
// Evaluate the tree expression
temp_string = tree.Evaluate().ToString();
// Set the value of the cell
sheetCell.Value = temp_string;
// Only allow the most recent cell that caused the issue to change its value
if (Ok == 1)
{
sheetCell.Value = "!Circular Reference";
Ok = 0;
circularReferenceKeys.Clear();
}
}
else
{
if (cell.Value == "")
{
sheetCell.Value = 0.ToString();
}
else
{
// Only allow the most recent cell that caused the issue to change its value
if (cell.Value != "!Self Reference")
{
sheetCell.Value = cell.Value;
}
}
}
if (GetNameFromCell(sheetCell) == GetNameFromCell(cell))
{
sheetCell.Value = "!Self Reference";
}
// Subsribe the cell to the PropertyChanged event
cell.ReferencedCellPropertyChanged += sheetCell.OnPropertyChanged;
}
catch (KeyNotFoundException)
{
sheetCell.Value = "!Bad Input";
};
}
else
{
sheetCell.Value = "!Bad Input";
}
}
if (tree.GetVariableNames().Count == 0)
{
// Evaluate the tree expression
temp_string = tree.Evaluate().ToString();
// Set the value of the cell
sheetCell.Value = temp_string;
}
}
else
{
if (temp_string.Length == 2 && Int32.TryParse(temp_string[1].ToString(), out int temp))
{
sheetCell.Value = temp_string[1].ToString();
}
else
{
sheetCell.Value = "!Bad Input";
}
}
}
// Invoke the CellPropertyChanged event
this.OnCellPropertyChanged(sheetCell, e);
}
