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