/// <summary> /// REQUIREMENT: cell expression text starts with "=". /// Evaluates cell's text as an expression and returns its value. /// </summary> /// <param name="cell">cell who's text is to be evaluated.</param> /// <returns>Evaluated expression.</returns> private string EvaluateCellExpression(SpreadsheetCell cell) { // removes the first character (which should be '=') and whitespace. string expression = cell.Text.Substring(1).Replace(" ", string.Empty); ExpressionTree et = new ExpressionTree(expression); bool variableError = this.HandleCellVariables(et, cell); if (!variableError) { return(et.Evaluate().ToString()); } else { return(cell.Value); } }
/// <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> /// Here is where each cell that's changed essentially has a delegate that notifies its respective event has changed. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> public void OnPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "Text") { BasicCell temporary = sender as BasicCell; string cellname = temporary.GetCellName(); DeleteDependency(cellname); if (temporary.Text != "" && temporary.Text[0] == '=' && temporary.Text.Length > 1) { ExpressionTree tree = new ExpressionTree(temporary.Text.Substring(1)); SetDependency(cellname, tree.GetVariables()); } EvaluateCell(sender as Cell); } else if (e.PropertyName == "BGColor") { CellPropertyChanged(sender, new PropertyChangedEventArgs("BGColor")); } }
/// <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)); } } }
public void setValue(spreadSheetCell cell) { //we dont want to actually change the cell's text property, so we use toDelimit to store a copy string toDelimit = cell.Text; bool InvalidName = false; bool SelfRef = false; bool CircleRef = false; //if the cell's text starts with an "=" then we will need to perform operations on the cell if (toDelimit.StartsWith("=")) { InvalidName = false; //this is an empty string that will store the new expression to be passed to the tree string newExpression = ""; //This is going to help us grab all the important elements of the cell such as the constants and the variables var importantElements = Regex.Matches(toDelimit, @"\d+|\w+|\S").OfType<Match>().Select(m => m.Value); //string s will either be an operator, parenthesis, or cell name foreach (string s in importantElements) { //if a letter is the first character in the string, then it is a cell name if (s[0] > 64 && s[0] < 91) { //The following checks for a letter in the substring following the first letter try { Int32.Parse(s.Substring(1)); } catch (FormatException) { InvalidName = true; } //Check to make sure that the cell is within the range specific (1-50) if (InvalidName == false) { if (Int32.Parse(s.Substring(1)) < 51 && Int32.Parse(s.Substring(1)) > 0) { InvalidName = false; } else InvalidName = true; } if (cell.ReturnName() == s) { SelfRef = true; break; } if (InvalidName == false && GetCell(s).Value != "") { //Check circular reference of cell about to be added if (circleRef(cell.ReturnName(), s, GetCell(s).Text)) { CircleRef = true; break; } //If we get to this point we know we aren't adding a circular ref newExpression += GetCell(s).Value; } //Default value, simply add to new expression else if (InvalidName == false && GetCell(s).Value == "") { newExpression += "0"; } } else { newExpression += s; } } if (CircleRef == false && SelfRef == false && InvalidName == false) { ExpressionTree Exp = new ExpressionTree(); Exp.BuildTree(newExpression.Substring(1)); cell.Value = Exp.Evaluate().ToString(); } } else if (InvalidName == false && SelfRef == false) cell.Value = toDelimit; if (CircleRef) { cell.Value = "!(circular reference)"; } if (InvalidName) { cell.Value = "!(bad cell name)"; } if (SelfRef) { cell.Value = "!(self reference)"; } }
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); }