/// <summary>
/// Sets the cell's "Value" Property based on its "Text" Property
/// If "Text" is empty or doesn't begin with "=", then set "Value" to "Text."
/// Otherwise, evaluate the formula.
/// </summary>
/// <param name="cell"></param>
private void DetermineCellValue(AbstractCell cell)
{
if (cell.Text.Length == 0)
{
cell.Value = "";
}
else if (cell.Text.StartsWith("=")) // formula type
{
if (cell.Text.Length > 1) // valid formula
{
try
{
EvaluateFormula(cell);
}
catch
{
cell.Value = "#REF!"; // set display value to some weird string, so that the user knows to fix it
throw; // propagate error upwards
}
}
else
{
throw new ArgumentException(_error_message = @"'=' is not a valid formula.");
}
}
else
{
Console.WriteLine("modified cell's text: " + cell.Text);
cell.Value = cell.Text;
}
}
/// <summary>
/// Remove the key "cell" from the dependency dictionary.
/// </summary>
/// <param name="cell"></param>
private void RemoveDependencies(AbstractCell cell)
{
if (_dependencies.ContainsKey(cell)) // If the cell had any dependencies,
{
_dependencies[cell].Clear(); // then remove them
}
_dependencies.Remove(cell); // Remove key
}
/// <summary>
/// Updates all other cell values when one cell changes
/// </summary>
/// <param name="cell"></param>
private void CascadingEffect(AbstractCell cell)
{
foreach (AbstractCell key in dependencies.Keys) // For every cell that has dependencies
{
if (dependencies[key].Contains(cell)) // If "cell" is in a hashset (mapped to by key), then "key" needs to be reevaluated since "cell"'s value changed
{
DetermineCellValue(key);
}
}
}
/// <summary>
/// Load the spreadsheet.
/// </summary>
/// <param name="infile"></param>
public void Load(Stream infile)
{
// XDocument is abstract, so obtain reference to it and laod infile.
XDocument xmlReader = XDocument.Load(infile);
foreach (XElement tag in xmlReader.Root.Elements("cell")) // for each tag labeled "cell"
{
int[] indices = ReferenceToIndices(tag.Element("cellname").Value); // grab value within cellname tag
AbstractCell cell = GetCell(indices[0], indices[1]); // get the cell's indices
cell.Text = tag.Element("celltext").Value; // update the cell's text
}
}
/// <summary>
/// figures out what the value of the cell should be
/// </summary>
/// <param name="cell"></param>
private void DetermineCellValue(AbstractCell cell)
{
if (cell.Text.Length == 0)
{
cell.Value = "";
}
else if (cell.Text.StartsWith("="))
{
cell.Value = GetCell(headerLookUp[cell.Text[2]], Convert.ToInt32(cell.Text[3])).Value;
}
else
{
cell.Value = cell.Text;
}
}
/// <summary>
/// The algorithm behind any spreadsheet's classic "cascading changes" effect.
/// This is called when a cell's value changes. We must always check to see if
/// other cells depended on that value. If so, they need to be updated as well.
/// </summary>
/// <param name="cell"></param>
private void CascadingEffect(AbstractCell cell)
{
if (NoReferenceCycles(cell, cell))
{
foreach (AbstractCell key in _dependencies.Keys) // For every cell that has dependencies
{
if (_dependencies[key].Contains(cell)) // If "cell" is in a hashset (mapped to by key), then "key" needs to be reevaluated since "cell"'s value changed
{
DetermineCellValue(key);
}
}
}
else
{
throw new Exception("Circular reference detected.");
}
}
/// <summary>
/// The passed in cell object's Text field begins with an '=', indicating it is
/// a formula. This function evaluats that formula.
/// </summary>
/// <param name="cell"></param>
private void EvaluateFormula(AbstractCell cell)
{
try
{
ExpTree expTree = new ExpTree(cell.Text.Substring(1)); // pass in full string BUT '=' at start to expression tree constructor
foreach (string cellName in expTree.VariablesInExpression)
{
// note: the "RefToIndices" method should probs throw errors instead of catching them internally
int[] indices = ReferenceToIndices(cellName);
AbstractCell cellReliesOnThisGuy = GetCell(indices[0], indices[1]); // throws error if out of bounds
// 'cell' DEPENDS on each cell that cellName refers to, so add it to dict
if (!_dependencies.ContainsKey(cell))
{
_dependencies.Add(cell, new HashSet <AbstractCell>()); // we first check if this is a new entry in the dict-- if so, add
}
_dependencies[cell].Add(cellReliesOnThisGuy);
// Now, only allow a reference if the referenced cell's value can be converted to a double!
bool success = Double.TryParse(cellReliesOnThisGuy.Value, out double result);
if (success)
{
expTree.SetVar(cellName, result); // now that we have the cell, set its value in the expression tree
}
else
{
expTree.SetVar(cellName, 0.0);
}
// My Design Choice:
// For now, allow this because otherwise it makes loading/saving impossible. This can be fixed carefully, but I don't have
// the time to make a smart design choice... As such, I commented out my original exception throw.
//throw new ArgumentException(String.Format("Cell \"{0}\" contains a value that cannot be referenced in a formula.", cellReliesOnThisGuy.Name));
}
cell.Value = expTree.Eval().ToString();
}
catch
{
throw; // propagate error up
}
}
/// <summary>
/// Returns true if there are no reference cycles. False otherwise.
/// EX of Reference Cycle:
/// A5 = 5 + B7
/// B7 = C5
/// C5 = A5
/// Note that since this is done each time the text is updated, there won't be any cycles
/// that don't include 'cell.' If there were, we would have already thrown an error.
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private bool NoReferenceCycles(AbstractCell root, AbstractCell cell)
{
// BASE CASE: cell has no dependencies, so no cycle exists in this subtree
if (!_dependencies.ContainsKey(cell))
{
return(true);
}
// RECURSION: cell depends on other cells, so we need to continue to check them for a cycle
bool result = true; // assume true since we only need a single "false" to return false
foreach (AbstractCell ac in _dependencies[cell])
{
if (ReferenceEquals(ac, root))
{
return(false);
}
result = result && NoReferenceCycles(root, ac); // AND result of recursive call for each of cell's dependencies-- if one or more cycles, returns false
}
return(result);
}
/// <summary>
/// when the cell changes value, handles it
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void OnCellPropertyChanged(object sender, PropertyChangedEventArgs e)
{
AbstractCell c = sender as AbstractCell;
switch (e.PropertyName)
{
case "Text":
RemoveDependencies(c);
DetermineCellValue(c);
PropertyChanged?.Invoke(sender, new PropertyChangedEventArgs("Value")); // Pass along event to whoever uses this class
break;
case "Value":
CascadingEffect(c);
PropertyChanged?.Invoke(sender, new PropertyChangedEventArgs("Value")); // Pass along event to whoever uses this class
break;
default:
break;
}
}
/// <summary>
/// Handles the event of text change fired by the AbstractCell Class.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void OnCellPropertyChanged(object sender, PropertyChangedEventArgs e)
{
AbstractCell c = sender as AbstractCell;
switch (e.PropertyName)
{
case "Text":
try
{
RemoveDependencies(c); // At the start, remove all dependencies since the changed text could have removed them.
DetermineCellValue(c);
}
catch (Exception ex)
{
_error_occurred = true;
_error_message = ex.Message;
}
PropertyChanged?.Invoke(sender, new PropertyChangedEventArgs("Value")); // Pass along event to whoever uses this class
break;
case "Value":
try
{
CascadingEffect(c);
}
catch (Exception ex)
{
_error_occurred = true;
_error_message = ex.Message;
}
PropertyChanged?.Invoke(sender, new PropertyChangedEventArgs("Value")); // Pass along event to whoever uses this class
break;
default:
break;
}
}
/// <summary>
/// Returns true if cell is the default cell (i.e. after construction, no changes).
/// In the future, it would be better design to override Equals in Cell, and then compare
/// cell to a newly constructed AbstractCell to determine if default or not.
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private bool IsDefaultCell(AbstractCell cell)
{
return(cell.Text == "" || cell.Value == "");
}
