/// <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>
/// 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));
}
}
}
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);
}