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