/// <summary>
/// Returns if the refCell has any references to the ogCell.
/// </summary>
/// <param name="ogCell">Original cell.</param>
/// <param name="refCell">Reference cell.</param>
/// <returns>If there are circular references.</returns>
private bool ContainsCircularReference(SpreadsheetCell ogCell, SpreadsheetCell refCell)
{
if (refCell.Text.StartsWith("="))
{
ExpressionTree et = new ExpressionTree(refCell.Text.Substring(1).Replace(" ", string.Empty));
List <string> names = et.GetVariableNames();
foreach (string name in names)
{
SpreadsheetCell currCell = this.GetCellFromName(name);
if (currCell == ogCell)
{
return(true);
}
bool containsCircularReference = this.ContainsCircularReference(ogCell, currCell);
if (containsCircularReference)
{
return(true);
}
}
}
return(false);
}
/// <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);
}
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);
}
