//function that evaluates the expression inside the cell and returns the value as double private void UpdateCellValue(SpreadsheetCell c) { //get the expression inside the cell string expression = c.newCellText; string cellName = Number2String(c.getColumn + 1, true); cellName += (c.getRow + 1).ToString(); string vari = ""; //parse the string in the exptree class taking out the = sign in the front of exp ExpTree tree = new ExpTree(expression.Substring(1)); //delete thr reference cells c.listOfCells.Clear(); //get all the variables inside the dictionary string[] variables = tree.getVar(); foreach (string variable in variables) { if (variable.Length > 2) { vari = variable; break; } //all the columns are marked from A to Z so we have to convert that to int int col = Convert.ToInt32(variable[0] - 65); // remaining part is the name of the cell we need to copy a value from //to convert that int row = Convert.ToInt32(variable.Substring(1)) - 1; //add it to the reference cell list c.addReferences(SS_Array[row, col]); if (string.IsNullOrEmpty(SS_Array[row, col].newCellText)) { c.newCellText = "0"; c.Value = "0"; break; } if (variable == cellName) { vari = variable; break; } tree.SetVar(variable, double.Parse(SS_Array[row, col].cellValue)); SS_Array[row, col].PropertyChanged += c.OnVariableChange; } if (vari == cellName) { c.newCellText = "!(Self/Circular reference)"; c.Value = "!(Self/Circular reference)"; } else if (vari.Length > 3) { c.newCellText = "!(Bad reference)"; c.Value = "!(Bad reference)"; } else { //evaluate the expression double newVal = tree.Eval(); c.Value = newVal.ToString(); } }
// PropertyChanged event for SpreadSheet. // This method is where all the meat is for when a cell gets an input by the user. // The purpose of it is to determine if a user entered an formula or a value. // If it's a formula it uses the ExpTree to calculate the value, and set the cells value to that. // If the user doesn't enter a formula, it sets that cell value and text to what the user entered. // ----------------------------------------------------------------------------- // It's good to point out also how the there is a check to see if the input is valid, // if it's invalid the cell will display "#REF", // Then after the cell gets updated, all the dictionaries get updated accordingly. private void SpreadSheetCell_PropertyChanged(object sender, PropertyChangedEventArgs e) { var currentCell = sender as SpreadsheetCell; ExpTree tree = new ExpTree(variables); List <string> multiDependencies = new List <string>(); // Check if input is valid if (!IsValidInput(currentCell)) { currentCell.SetValue("#REF"); return; } // If the user entered a formula if (currentCell.Text != null && currentCell.Text[0] == '=') { tree.SetExpression(currentCell.Text.Substring(1)); currentCell.SetExpression(currentCell.Text.Substring(1)); currentCell.SetValue(tree.Eval().ToString()); // Gets the new dependencies from the tree, // and in a loop updates the dependency dictionary multiDependencies = tree.GetDependencies(); foreach (string s in multiDependencies) { SpreadsheetCell myCell = GetCell(s); if (!dependencies.ContainsKey(myCell)) { dependencies.Add(myCell, new List <SpreadsheetCell>()); } dependencies[myCell].Add(currentCell); } } // If user enters a value else { // Set value of the cell currentCell.SetValue(currentCell.Text); // Set the expression of that cell to null currentCell.SetExpression(string.Empty); RemoveDependencies(currentCell); } UpdateVariables(currentCell); if (CellPropertyChanged != null) { CellPropertyChanged(sender, e); } UpdateDependencies(currentCell, tree); }
//INotifyPropertyChanged private void UpdateCellValue(CellHelper c) //Will be called when a cell that cell c references is updated, or when a cell itself is updated. //Will create a new expression tree based on the text, and get the cell values from the spreadsheet. //This is very similar to what happens when a new expression is added to a cell EXCEPT it doesn't update //the reference lists because the cell text itself isn't changing, just its value { if (c.cText == null || c.cText == "") { c.chValue = c.cText; } else { ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree List <string> referencedCells = tree.GetVariables(); //This list contains all referenced cells. So "=A1+B2*3" would have ["A1","B2"] foreach (string c_name in referencedCells) { string req_col = c_name.Substring(0, 1); //to get the required column we need the celltext for the first value "=A6" -> "A" string req_row = c_name.Substring(1); //This will take the rest of the information, there's no length so it could read it "=A15" -> "15 int colInt = Convert.ToChar(req_col) - 65; //gets the index based on the character int rowInt = Convert.ToInt32(req_row) - 1; //sets the index (and subtracts on so it's (0,49) instead of (1,50), matching the indexes double cellVal; if (cell_array[rowInt, colInt].chValue == null || cell_array[rowInt, colInt].chValue == "" || cell_array[rowInt, colInt].chValue == "!(bad reference)" || cell_array[rowInt, colInt].chValue == "!(self reference)" || cell_array[rowInt, colInt].chValue == "!(circular reference)") { cellVal = 0; } else { cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue); } tree.SetVar(c_name, cellVal); //now the tree knows what A2 is /*(sender as CellHelper).addReference(cell_array[rowInt, colInt]); //We're telling this cell what it references * cell_array[rowInt, colInt].addReferenceBy((sender as CellHelper)); //The cell we're referencing now knows we're referencing them*/ } c.chValue = Convert.ToString(tree.Eval()); foreach (CellHelper c2 in c.referencedBy) { UpdateCellValue(c2); } } NotifyPropertyChanged(c, new PropertyChangedEventArgs("CellValue")); }
/************************************************************* * Function: setExp(createACell m_Cell, Cell cell) * Date Created: Feb 8, 2017 * Date Last Modified: Feb 9, 2017 * Description: set value to exp * Return: void *************************************************************/ private void setExp(createACell m_Cell, Cell cell) { ExpTree exptree = new ExpTree(m_Cell.Text.Substring(1)); string[] variables = exptree.GetAllVariables(); foreach (string variableName in variables) { Cell variableCell = GetCell(variableName); double value; if (string.IsNullOrEmpty(variableCell.Value)) { exptree.SetVar(variableCell.Name, 0); } else if (!double.TryParse(variableCell.Value, out value)) { exptree.SetVar(variableName, 0); } else { exptree.SetVar(variableName, value); } } m_Cell.SetValue(exptree.Eval().ToString()); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); #region hw4code //old code from hw4 //string letter = m_Cell.Text.Substring(1); //string number = letter.Substring(1); //string lettersAZ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; //int col = 0, row = int.Parse(number) - 1; //for (int i = 0; i < 26; i++) //{ // if (lettersAZ[i] == letter[0]) // { // col = i; // break; // } //} //m_Cell.SetValue(GetCell(row, col).Value.ToString()); #endregion }
// Function to update the Dependencies private void UpdateDependencies(SpreadsheetCell cell) { // Create a new tree with current variables ExpTree tree = new ExpTree(VarTable); if (Dependencies.ContainsKey(cell)) { // For each of the cells dependent on cell, update dependencies foreach (SpreadsheetCell myCell in Dependencies[cell]) { tree.SetExp(myCell.Text.Substring(1)); myCell.SetValue(tree.Eval().ToString()); CellPropertyChanged(myCell, new PropertyChangedEventArgs("Value")); UpdateVars(myCell); UpdateDependencies(myCell); } } }
private void UpdateCellValue(CellHelper c) { if (string.IsNullOrEmpty(c.cText)) { c.chValue = c.cText; } else { var tree = new ExpTree(c.cText.Substring(1)); var referencedCells = tree.GetVariables(); foreach (var c_name in referencedCells) { var req_col = c_name.Substring(0, 1); var req_row = c_name.Substring(1); var colInt = Convert.ToChar(req_col) - 65; var rowInt = Convert.ToInt32(req_row) - 1; double cellVal; if (string.IsNullOrEmpty(cell_array[rowInt, colInt].chValue) || cell_array[rowInt, colInt].chValue == "!(bad reference)" || cell_array[rowInt, colInt].chValue == "!(self reference)" || cell_array[rowInt, colInt].chValue == "!(circular reference)") { cellVal = 0; } else { cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue); } tree.SetVar(c_name, cellVal); } c.chValue = Convert.ToString(tree.Eval()); foreach (var c2 in c.referencedBy) { UpdateCellValue(c2); } } NotifyPropertyChanged(c, new PropertyChangedEventArgs("CellValue")); }
// The purpose of this method is to update the dependencies in the spreadsheet // after a cell gets messed with. It iterates through a list of dependencies (from inside the dependencies dictionary) // and recursively updates all cells that are dependent on that specific cell public void UpdateDependencies(SpreadsheetCell currentCell, ExpTree tree) { // Check if that cell has dependencies if (dependencies.ContainsKey(currentCell)) { // iterate through the list inside dependencies dictionary foreach (SpreadsheetCell c in dependencies[currentCell]) { // Updates the tree's expression tree.SetExpression(c.GetExpression()); // Sets the new value to the new Eval from the tree c.SetValue(tree.Eval().ToString()); CellPropertyChanged(c, new PropertyChangedEventArgs("Value")); // Update variables dictionary UpdateVariables(c); UpdateDependencies(c, tree); } } }
/// <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 } }
public void evalTree() { try { if (_expTree != null) { base.Value = Convert.ToString(_expTree.Eval()); } else if (_mConnections.Peek() != null) { base.Value = _mConnections.Peek().Value; } else { base.Value = base.Text; } } catch { base.Value = "#REF!"; } }
/// <summary> /// Interpret the expression contained by a cell, and return its value /// </summary> /// <param name="expression">the expression to interpret</param> /// <param name="cellToInterpret">the cell containing the expression</param> /// <returns>the interpreted value</returns> private string InterpretExpression(string expression, ref SpreadsheetCell cellToInterpret) { var expTree = new ExpTree(expression); string value = ""; var singleCellExpression = (SpreadsheetCell)GetCellFromStr(expression); if (null != singleCellExpression && singleCellExpression != cellToInterpret) { if (VerifyNoCycles(cellToInterpret, singleCellExpression)) { value = String.IsNullOrEmpty(singleCellExpression.Value) ? "0" : singleCellExpression.Value; Subscribe(cellToInterpret, singleCellExpression); } else { value = "#REF!"; } } else if (expTree.IsValidExpression()) { if (AssignExpTreeVars(ref cellToInterpret, ref expTree)) { value = expTree.Eval().ToString(); } else { value = "#REF!"; } } else { value = "#VALUE!"; } return(value); }
/****************************************************************************************** * Mechanism: * If the Cell's Text starts with "="... * An exptree called formulaEvaluator is created. * Scan through the Cell's Text and look for any variables. * For any variable found in the cell's text, it gets updated to both * the formulaEvaluator(exptree)'s variable table and the spreadsheet's * dependency table. By update, it means that if it already exists in any * of the two dictionaries, it gets removed from that dictionary and re-added * in again. * * On the other hand, if the Cell's Text doesn't start with "="... * Set the Sender Cell's value to equal to its text. * * Regardless of whichever of the above happens, * if the sender Cell exists in the DepedencyDictionary, it means that * there are other cells that depend on the value of this cell. In this case, * for each cell "c" that depends on the sender cell, do a recursive function * call (this function, the UpdateValueOfCell() function) with "c" as the * sender cell (the e isn't used, so the current "e" is just passed in since * it doesn't matter). (I didn't actually use the foreach statement, I used * a forloop instead because when I used the foreach statement the compiler * gave me a runtime error for modification to the foreach collection). It * will constantly recursively call the function until it finally a cell whose * value is a constant. The line after the recursive function call is the line * that updates the UI, so for every recursive call, a cell in the UI * gets updated. ******************************************************************************************/ void UpdateValueOfCell(Object sender, PropertyChangedEventArgs e) { ICell cellToEval = sender as ICell; if (cellToEval.Text.StartsWith("=")) { string formula = cellToEval.Text.Substring(1); ExpTree formulaEvaluator = new ExpTree(formula); char[] charFormula = formula.ToCharArray(); for (int i = 0; i < charFormula.Length; i++) { if ((charFormula[i] >= 'A') && (charFormula[i] <= 'Z')) { string varToAdd = ""; varToAdd += Convert.ToString(charFormula[i]); i++; while (i < charFormula.Length && charFormula[i] >= '0' && charFormula[i] <= '9') { varToAdd += Convert.ToString(charFormula[i]); i++; } Point spreadsheetCoord = UIcoordToSpreadCoord(varToAdd); Cell cellToAdd = m_cell_arr[spreadsheetCoord.X, spreadsheetCoord.Y]; if (m_dependency_dictionary.ContainsKey(cellToAdd) == false) { List <Cell> newCellList = new List <Cell>(); newCellList.Add(cellToEval); m_dependency_dictionary.Add(cellToAdd, newCellList); } else if (!m_dependency_dictionary[cellToAdd].Contains(cellToEval)) { m_dependency_dictionary[cellToAdd].Add(cellToEval); } if (formulaEvaluator.VarTable.ContainsKey(varToAdd) == true) { formulaEvaluator.VarTable.Remove(varToAdd); } double cellToAddValue = double.NaN; if ("" != cellToAdd.Value && "#REF!" != cellToAdd.Value) { cellToAddValue = Convert.ToDouble(cellToAdd.Value); } if (null == cellToAdd.Value || "#REF!" == cellToAdd.Value) { cellToAddValue = double.NaN; } formulaEvaluator.VarTable.Add(varToAdd, cellToAddValue); } } double evaluatedValue = formulaEvaluator.Eval(); if (double.IsNaN(evaluatedValue)) { cellToEval.SetValue("#REF!"); } else { cellToEval.SetValue(Convert.ToString(evaluatedValue)); } } else { cellToEval.SetValue(cellToEval.Text); } if (m_dependency_dictionary.ContainsKey(cellToEval)) { for (int i = 0; i < m_dependency_dictionary[cellToEval].Count; i++) { UpdateValueOfCell(m_dependency_dictionary[cellToEval][i], e); } } CellPropertyChanged(sender, e); }
/************************************************************* * Function: setExp(createACell m_Cell, Cell cell) * Date Created: Feb 8, 2017 * Date Last Modified: Feb 9, 2017 * Description: set value to exp * Return: void *************************************************************/ private void setExp(createACell m_Cell, Cell cell, ref bool flag) { ExpTree exptree = new ExpTree(m_Cell.Text.Substring(1)); string[] variables = exptree.GetAllVariables(); foreach (string variableName in variables) { // bad reference if (GetCell(variableName) == null) { m_Cell.SetValue("!(Bad Reference)"); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); flag = true; break; } //SetExpressionVariable Cell variableCell = GetCell(variableName); double value; if (string.IsNullOrEmpty(variableCell.Value)) { exptree.SetVar(variableCell.Name, 0); } else if (!double.TryParse(variableCell.Value, out value)) { exptree.SetVar(variableName, 0); } else { exptree.SetVar(variableName, value); } //SetExpressionVariable end; //self reference if (variableName == m_Cell.Name) { m_Cell.SetValue("!(Self Reference)"); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); flag = true; break; } } if (flag) { return; } //Circular reference foreach (string varName in variables) { if (varName == m_Cell.Name) // directly same { m_Cell.SetValue("!(Circular Reference)"); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); flag = true; break; } if (!depDict.ContainsKey(m_Cell.Name)) //check in dict { continue; } string currCell = m_Cell.Name; for (int i = 0; i < depDict.Count; i++) //find all cell in dict { foreach (string dependentCell in depDict[currCell]) //do same thing as before { if (varName == dependentCell) { m_Cell.SetValue("!(Circular Reference)"); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); flag = true; break; } if (!depDict.ContainsKey(dependentCell)) { continue; } currCell = dependentCell; } } } if (flag) { return; } m_Cell.SetValue(exptree.Eval().ToString()); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); #region hw4code //old code from hw4 //string letter = m_Cell.Text.Substring(1); //string number = letter.Substring(1); //string lettersAZ = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; //int col = 0, row = int.Parse(number) - 1; //for (int i = 0; i < 26; i++) //{ // if (lettersAZ[i] == letter[0]) // { // col = i; // break; // } //} //m_Cell.SetValue(GetCell(row, col).Value.ToString()); #endregion }
// Create SheetPropertyChanged method to raise event // private void SheetPropertyChanged(object sender, PropertyChangedEventArgs e) { ExpTree tree = new ExpTree(VarTable); var currCell = (SpreadsheetCell)sender; // Check if cell is a valid input. Will get finish in the future if (!IsValid(currCell, Dependencies)) { currCell.SetValue("#REF"); return; } if (currCell.Text != null && currCell.Text[0] == '=') { // Create a new expression tree if the cell text starts with = tree.SetExp(currCell.Text.Substring(1)); // Set the cell value to the evaluation of the expression currCell.SetValue(tree.Eval().ToString()); // If the list of variables inside this cell is not null then // Add each variable to the dependencies dictionary and // set the current cell as being dependent to that cell if (tree.VarsForDeps != null) { foreach (string s in tree.VarsForDeps) { if (!Dependencies.ContainsKey(GetCell(s))) { Dependencies.Add((GetCell(s)), new List <Cell>()); } Dependencies[GetCell(s)].Add(currCell); } } } else { // If currCell doesn't start with '=', we set the value of the cell to the text that was entered currCell.SetValue(currCell.Text); // If variable is not currently in the variable table then // We add the cell name and its contents to the variable table string temp = CoordToName(currCell.ColumnIndex, currCell.RowIndex); if (!(VarTable.ContainsKey(temp))) { VarTable.Add(temp, Convert.ToDouble(currCell.Value)); } // If a cell got its value changed to a number, then it is no longer dependent on other cells // The foreach loops go through each cell in the dependencies dictionary // And make sure that the cell is no longer being written as being dependent // On another cell Dictionary <Cell, List <Cell> > NewDeps = new Dictionary <Cell, List <Cell> >(); foreach (KeyValuePair <Cell, List <Cell> > cells in Dependencies) { foreach (Cell cell in cells.Value) { if (currCell != cell) { if (!NewDeps.ContainsKey(cells.Key)) { NewDeps.Add(cells.Key, new List <Cell>()); } NewDeps[cells.Key].Add(cell); } } } // Update the dependencies dictionary. Dependencies = NewDeps; } // Update variables, dependencies, and call CellPropertyChanged. UpdateVars(currCell); UpdateDependencies(currCell); CellPropertyChanged(sender, e); }
public void EvalCell(Cell cell) // Evaluate a cell { CellInstance instance = cell as CellInstance; if (string.IsNullOrEmpty(cell.Text)) { instance.SetValue(""); //set to nothing for default CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); // if value changed } else if (cell.Text[0] == '=' && cell.Text.Length > 1) // for an equation { bool error = false; ExpTree expression = new ExpTree(); expression.ExpressionString = cell.Text.Substring(1); List <string> expressionVar = expression.GetVar(); foreach (string variable in expressionVar) // check for errors { if (variable == cell.Name) // self reference { instance.SetValue("!(self reference)"); error = true; break; } if (GetCell(variable) == null) // cell does not exist { instance.SetValue("!(bad reference)"); error = true; break; } if (IsCircularReference(variable, cell.Name)) // circular reference { instance.SetValue("!(circular reference)"); error = true; break; } Cell variableCell = GetCell(variable); double variableValue; if (string.IsNullOrEmpty(variableCell.Value)) // check for empty value { expression.SetVar(variable, 0); } else if (!double.TryParse(variableCell.Value, out variableValue)) { expression.SetVar(variable, 0); } else { expression.SetVar(variable, variableValue); } } if (error) // for errors { CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); return; } //at this point, the variables are set instance.SetValue(expression.Eval().ToString()); // Evaluate expression and set value of Cell CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); } else // if not an expression { instance.SetValue(cell.Text); // chage text of cell CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); } if (_references.ContainsKey(instance.Name)) // Evaluate referenced cells { foreach (string cellName in _references[instance.Name]) { EvalCell(GetCell(cellName)); } } }
//helper function to evaluate the text value of a cell to determine the value value private void EvaluateCell(Cell cell) { //make BasicCell to evaluate the cell BasicCell evalCell = cell as BasicCell; //variable for errors, if true, we have an error and should just return null bool error = false; //First check to see if it's empty if (string.IsNullOrWhiteSpace(evalCell.Text)) { //if text is empty, the value should be empty evalCell.setValue(""); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); } //next check to see if there is an '=' to make it a formula (and that it's more than just the =... else if (evalCell.Text.Length > 1 && evalCell.Text[0] == '=') { //first get rid of the = at (0) string text = evalCell.Text.Substring(1); //create an expression tree! ExpTree evalTree = new ExpTree(text); // get the variables from the tree string[] variables = evalTree.GetVariables(); //go through each variable. They are the locations of each cell needed for the formula. foreach (string variableName in variables) { //First check to make sure that there is even a value to reference (call our new GetCell) if (GetCell(variableName) == null) { //there was nothing to reference. Tell the user through the cell and cell prop changed evalCell.setValue("ERROR: BAD VAR REFERENCE."); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); //break out of the loop & set error to true error = true; break; } // We have determine that the cell reference is valid. Set the variable to the expTree variable //get the cell we need to edit Cell variableCell = GetCell(variableName); double variableValue; //We will need to chck to make sure it work //if the cell's value is empty, set the variable to 0. if (string.IsNullOrEmpty(variableCell.Value)) { evalTree.SetVar(variableName, 0); } //if the value of the cell is not a number, set to 0 else if (!double.TryParse(variableCell.Value, out variableValue)) { evalTree.SetVar(variableName, 0); } //ELSE: should be valid! Set to the value! else { evalTree.SetVar(variableName, variableValue); } //Don't have to worry about circular references, but self references could be bad here string cellToEval = evalCell.ColumnIndex.ToString() + evalCell.RowIndex.ToString(); if (variableName == cellToEval) { evalCell.setValue("ERROR: VAR REF SELF."); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); error = true; break; } } //if there is an error, stop here and return if (error) { return; } //Now, all variables should be set and we can evaluate the formula using the expression tree evalCell.setValue(evalTree.Eval().ToString()); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); } //last if it's neither of the above, it's not an formula, just test the value to the text of the original cell else { evalCell.setValue(evalCell.Text); CellPropertyChanged(cell, new PropertyChangedEventArgs("Value")); } //VERY LAST THING WE NEED IS TO UPDATE DEPENDENCIES! And evaluate all cells that were dependent on the one we just changed. string cellName = evalCell.GetCellName(); if (dependencyDict.ContainsKey(cellName)) { foreach (string dependentCell in dependencyDict[cellName]) { EvaluateCell(GetCell(dependentCell)); } } }
public void EvalCell(Cell CellArg) { m_Cell m_c = CellArg as m_Cell; if (string.IsNullOrEmpty(m_c.Text)) { m_c.setValue(""); SSPropertyChanged(CellArg, new PropertyChangedEventArgs("Value")); } else if (m_c.Text[0] == '=' && m_c.Text.Length > 1) { bool flag = false; string expStr = m_c.Text.Substring(1); // set the expression string and create the tree ExpTree exp = new ExpTree(expStr); // gets expression variables string[] expVars = exp.GetVarNames(); // sets each variable in the expression foreach (string varName in expVars) { // check if the cell is valid if (GetCell(varName) == null) { // set value to the error and fire property changed event for the value m_c.setValue("!(Bad Reference)"); SSPropertyChanged(m_c, new PropertyChangedEventArgs("Value")); flag = true; break; } // check to see if its a self reference if (varName == m_c.Name) { // set value to the error and fire property changed event for the value m_c.setValue("!(Self Reference)"); SSPropertyChanged(m_c, new PropertyChangedEventArgs("Value")); flag = true; break; } // valid variable SetExpVariable(exp, varName); // check for circular reference after we've determined its a valid reference if (CheckCircularRef(varName, m_c.Name)) { // set value to the error and fire property changed event for the value m_c.setValue("!(Circular Reference)"); SSPropertyChanged(m_c, new PropertyChangedEventArgs("Value")); updateCells(m_c.Name); flag = true; break; } } if (flag) { return; } // Sets the expression evaluation to the current cells value and fires the property changed event m_c.setValue(exp.Eval().ToString()); SSPropertyChanged(CellArg, new PropertyChangedEventArgs("Value")); } else // no equals sign present so change the text { m_c.setValue(m_c.Text); SSPropertyChanged(CellArg, new PropertyChangedEventArgs("Value")); } if (dependencies.ContainsKey(m_c.Name)) { foreach (string name in dependencies[m_c.Name]) { EvalCell(name); } } }
private void UpdateCellValue(ref SpreadsheetCell cell) { var mainTree = new ExpTree(); // Initializes a new expression tree to build the cell's expression ErrType Error = ErrType.None; if (cell.Text != "" && cell.Text[0] != '=') // not an expression, simply a text value { cellArr[cell.RowIndex, cell.ColumnIndex] = new Cell(cell, cell.Text); add_remove(cell, mainTree, true); } else { if (cell.Text != "") { mainTree.Expression = cell.Text.Substring(1).Replace(" ", ""); // Build the expression tree with the cell's text (minus the '=') :: Also ignores whitespaces } else { mainTree.Expression = cell.Text; } add_remove(cell, mainTree, true); // Removes all variables cooresponding to the old tree cell.VarList = GetVarNames(cell.Text); // Will Return all found variables in the new expression Error = add_remove(cell, mainTree, false); if (Error != ErrType.None) // Notifies the UI that there is an error in one of the cells that the expression references { return; // Exits the function before executing anything else, error display has already been taken care of at this point } try { cellArr[cell.RowIndex, cell.ColumnIndex] = new Cell(cell, mainTree.Eval().ToString()); // Attempts to evaluate the expression, placing it into a new cell } catch (DivideByZeroException) // User tried to divide by zero { CheckErr(cell, ErrType.DivZero, CellToString(cell)); UpdateErrorReferecedBy(cell, ErrType.DivZero, CellToString(cell)); return; } catch (NullReferenceException) // Input not regonized / invalid expression { if (cell.Text == "") { cellArr[cell.RowIndex, cell.ColumnIndex] = new Cell(cell, ""); // if the cell was deleted or reset, this will set the cell to an empty value (caught by expression tree as null) } else { CheckErr(cell, ErrType.InvExp, CellToString(cell)); UpdateErrorReferecedBy(cell, ErrType.InvExp, CellToString(cell)); // Notifies UI that an invalid expression has been entered return; } } } cellArr[cell.RowIndex, cell.ColumnIndex].PropertyChanged += detect_PropertyChanged; // Reassigns the the detect_property function to the cell's delegate CellPropertyChanged(cellArr[cell.RowIndex, cell.ColumnIndex], new PropertyChangedEventArgs("Value")); // fires the event that notifies the GUI of a change UpdateReferencedBy(cell); // Updates all cells that reference this cell }
private void NotifyCellPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "CellColor") { NotifyPropertyChanged((sender as CellHelper), new PropertyChangedEventArgs("CellColor")); return; } if (e.PropertyName == "CellText") { if ((sender as Cell).cText == "" || (sender as Cell).cText == null) { (sender as CellHelper).chValue = ""; var previously_references = (sender as CellHelper).clearReferences(); foreach (CellHelper c in previously_references) { UpdateCellValue(c); } if ((sender as CellHelper).referencedBy.Count != 0) { foreach (CellHelper c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } } } else if ((sender as Cell).cText[0] == '=') { if (badRef((sender as CellHelper))) { (sender as CellHelper).chValue = "!(bad reference)"; return; } if (selfRef((sender as CellHelper))) { (sender as CellHelper).chValue = "!(self reference)"; return; } if (circularRef((sender as CellHelper), new HashSet <CellHelper>())) { (sender as CellHelper).chValue = "!(circular reference)"; return; } var tree = new ExpTree((sender as CellHelper).cText.Substring(1)); var referencedCells = tree.GetVariables(); foreach (var c_name in referencedCells) { var req_col = c_name.Substring(0, 1); var req_row = c_name.Substring(1); var colInt = Convert.ToChar(req_col) - 65; var rowInt = Convert.ToInt32(req_row) - 1; double cellVal = 0; try { cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue); } catch (FormatException) { cellVal = 0; } tree.SetVar(c_name, cellVal); (sender as CellHelper).addReference(cell_array[rowInt, colInt]); cell_array[rowInt, colInt].addReferenceBy((sender as CellHelper)); } (sender as CellHelper).chValue = Convert.ToString(tree.Eval()); foreach (var c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } } else { (sender as CellHelper).chValue = (sender as Cell).cText; if ((sender as CellHelper).referencedBy.Count != 0) { foreach (CellHelper c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } } } NotifyPropertyChanged((sender as CellHelper), new PropertyChangedEventArgs("CellValue")); } }
private void NotifyCellPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "CellColor") { NotifyPropertyChanged((sender as CellHelper), new PropertyChangedEventArgs("CellColor")); return; } if (e.PropertyName == "CellText") { //if ((sender as Cell).cText != null && (sender as Cell).cText != "") //if ((sender as Cell).cText.ToString().Length > 0) //This means that there is something in the cell if ((sender as Cell).cText == "" || (sender as Cell).cText == null) { (sender as CellHelper).chValue = ""; var previously_references = (sender as CellHelper).clearReferences(); foreach (CellHelper c in previously_references) { UpdateCellValue(c); } if ((sender as CellHelper).referencedBy.Count != 0) //some stuff references this { foreach (CellHelper c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } } } else if ((sender as Cell).cText[0] == '=') //Text is an equation { if (badRef((sender as CellHelper))) //This was a pain figuring out which order we should check. Self ref is just a specific circular ref { (sender as CellHelper).chValue = "!(bad reference)"; return; } if (selfRef((sender as CellHelper))) { (sender as CellHelper).chValue = "!(self reference)"; return; } if (circularRef((sender as CellHelper), new HashSet <CellHelper>())) { (sender as CellHelper).chValue = "!(circular reference)"; return; } ExpTree tree = new ExpTree((sender as CellHelper).cText.Substring(1)); //create an expression tree List <string> referencedCells = tree.GetVariables(); //This list contains all referenced cells. So "=A1+B2*3" would have ["A1","B2"] var previously_references = (sender as CellHelper).clearReferences(); //UpdateCellValue((sender as CellHelper)); foreach (string c_name in referencedCells) { string req_col = c_name.Substring(0, 1); //to get the required column we need the celltext for the first value "=A6" -> "A" string req_row = c_name.Substring(1); //This will take the rest of the information, there's no length so it could read it "=A15" -> "15 int colInt = Convert.ToChar(req_col) - 65; //gets the index based on the character int rowInt = Convert.ToInt32(req_row) - 1; //sets the index (and subtracts on so it's (0,49) instead of (1,50), matching the indexes double cellVal = 0; try { cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue); } catch (FormatException err) { cellVal = 0; } tree.SetVar(c_name, cellVal); //now the tree knows what A2 is (sender as CellHelper).addReference(cell_array[rowInt, colInt]); //We're telling this cell what it references cell_array[rowInt, colInt].addReferenceBy((sender as CellHelper)); //The cell we're referencing now knows we're referencing them } (sender as CellHelper).chValue = Convert.ToString(tree.Eval()); foreach (CellHelper c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } foreach (CellHelper c in previously_references) { UpdateCellValue(c); } //will need to set the value of all referenced values in that equation //String[] vars = tree.Vars() that will return all "B1", "C3", etc that the expression tree needs //for each of those strings, tree.setvar(...); /*string req_col = (sender as Cell).cText.Substring(1, 1); //to get the required column we need the celltext for the first value "=A6" -> "A" * string req_row = (sender as Cell).cText.Substring(2); //This will take the rest of the information, there's no length so it could read it "=A15" -> "15 * int colInt = Convert.ToChar(req_col) - 65; //gets the index based on the character * int rowInt = Convert.ToInt32(req_row) - 1; //sets the index (and subtracts on so it's (0,49) instead of (1,50), matching the indexes * //(sender as CellHelper).chValue = tree.Eval(); * (sender as CellHelper).chValue = cell_array[rowInt, colInt].chValue; * //updated Dependencies*/ } else //if ((sender as Cell).cText[0] != '=') //no need to do equation processing because it doesn't start with '=' { var previously_references = (sender as CellHelper).clearReferences(); (sender as CellHelper).chValue = (sender as Cell).cText; if ((sender as CellHelper).referencedBy.Count != 0) //some stuff references this { foreach (CellHelper c in (sender as CellHelper).referencedBy) { UpdateCellValue(c); } } foreach (CellHelper c in previously_references) { UpdateCellValue(c); } } /*else //I'm not totally sure when this would be triggered, error on input maybe? * { * (sender as CellHelper).chValue = (sender as Cell).cText; * //(sender as Cell).cText = "ERROR"; * }*/ NotifyPropertyChanged((sender as CellHelper), new PropertyChangedEventArgs("CellValue")); } }