private bool selfRef(CellHelper c) //These functions are all pretty similar { //first you make sure that there's anything actually in the cell if (c.cText == "" || c.cText == null) { return(false); } else if (c.cText[0] != '=') //If there's no "=" then it can't be a circular/bad/self reference { return(false); } ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree List <string> nameList = tree.GetVariables(); //All the references in the cell var cellList = new List <CellHelper>(); foreach (string n in nameList) //This may seem a little unnecessary, but A4: "=B3 * A1 + A4" would need to look at each { //cell referenced, not just the first cellList.Add(stringToCell(n)); } if (cellList.Contains(c)) //Basically, if c references c { return(true); } return(false); }
private bool badRef(CellHelper c) { if (c.cText == "" || c.cText == null) { return(false); } else if (c.cText[0] != '=') { return(false); } ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree List <string> nameList = tree.GetVariables(); var cellList = new List <CellHelper>(); foreach (string n in nameList) { cellList.Add(stringToCell(n)); } if (cellList.Contains(null)) //This is a check that is implemented in stringToCell by using a try/catch, and if that creates something invalid { //It will add a null references to the cellList, which we now check for. return(true); } return(false); }
//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(); } }
public void OnPropertyChanged(object sender, PropertyChangedEventArgs e) { // if property name is equal to text if (e.PropertyName == "Text") { InstanciateCell tCell = sender as InstanciateCell; DeleteDeps(tCell.Name); // update cell value to text //tCell.SetVal(tCell.Text); // if it starts with an equal sign if (tCell.Text != "" && tCell.Text[0] == '=' && tCell.Text.Length > 1) { ExpTree exp = new ExpTree(tCell.Text.Substring(1)); MakeDeps(tCell.Name, exp.getVars()); } EvalCell(sender as Cell); } else if (e.PropertyName == "BackColor") { CellPropertyChanged(sender, new PropertyChangedEventArgs("BackColor")); } }
//description: cell constructor //parameters: column index, row index, text, and value //return: public Cell(int newColumnIndex = 0, int newRowIndex = 0, string newText = "", string newValue = "") { mColumnIndex = newColumnIndex; mRowIndex = newRowIndex; mText = newText; mValue = newValue; mBGColor = 4294967295; mTree = null; }
public Cell() //Default constructor { this.mColumnIndex = 0; this.mRowIndex = 0; this.mText = ""; this.mValue = ""; this.mTree = new ExpTree(""); this.mBGColor = 4294967295; //Start with a white background (0 is black) }
// 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); }
public void makeNewExpTree(string expression) { if (expression == "") { _expTree = null; } else { _expTree = new ExpTree(expression); } }
//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")); }
//makes new SpreadSheet with a specific amount cells public Spreadsheet(int newRows, int newColumns) { _Array = new Cell[newRows, newColumns]; ExpTree newTree = new ExpTree("0"); for (int x = 0; x < newRows; x++) { for (int y = 0; y < newColumns; y++) { string cellName = getCellName(x, y); _Array[x, y] = new SpreadSheetCell(x, y); _Array[x, y].PropertyChanged += SignalPropertyChanged; ExpTree.SetVar(cellName, _Array[x, y]); } } }
private bool circularRef(CellHelper c, ISet <CellHelper> hs) { if (string.IsNullOrEmpty(c.cText)) { return(false); } if (c.cText[0] != '=') { return(false); } var tree = new ExpTree(c.cText.Substring(1)); var nameList = tree.GetVariables(); if (hs.Contains(c)) { return(true); } hs.Add(c); var cellList = new List <CellHelper>(); foreach (var n in nameList) { cellList.Add(stringToCell(n)); } foreach (var ce in cellList) { if (string.IsNullOrEmpty(ce?.cText)) { continue; } var new_hs = new HashSet <CellHelper>(hs); if (circularRef(ce, new_hs)) { return(true); } } return(false); }
/************************************************************* * 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 }
// This function will set the expression variable retrieved from the spreadsheet // First parameter is the expression were in // Secon parameter is the cell of the expression variable we will set private void SetExpVariable(ExpTree exp, string ExpVarName) { Cell expVarCell = GetCell(ExpVarName); double valueToBeSet; if (string.IsNullOrEmpty(expVarCell.Value)) { exp.SetVar(expVarCell.Name, 0); } else if (!double.TryParse(expVarCell.Value, out valueToBeSet)) { exp.SetVar(ExpVarName, 0); } else { exp.SetVar(ExpVarName, valueToBeSet); } }
private bool circularRef(CellHelper c, HashSet <CellHelper> hs) { if (c.cText == "" || c.cText == null) { return(false); } else if (c.cText[0] != '=') { return(false); } ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree List <string> nameList = tree.GetVariables(); if (hs.Contains(c)) //forgot to do this check for a bit which was dumb { return(true); } hs.Add(c); //forgot this for a while, infinite loop var cellList = new List <CellHelper>(); foreach (string n in nameList) { cellList.Add(stringToCell(n)); } foreach (CellHelper ce in cellList) //This is the DFS for checking for circular references { if (ce != null) { if (ce.cText != "" && ce.cText != null) { var new_hs = new HashSet <CellHelper>(hs); //new_hs.Add(ce); if (circularRef(ce, new_hs)) //if any of them return true, return true { return(true); } } } } return(false); }
//Called when a cell's propertyChanged event is called. public void OnPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "Text") { BasicCell temporary = sender as BasicCell; string cellname = temporary.ColumnIndex.ToString() + temporary.RowIndex.ToString(); //Delete the dependencies because the text has changed, need to edit DeleteDependency(cellname); //if the cell text is a formula, create an new expression tree and set the new dependencies! if (temporary.Text != "" && temporary.Text[0] == '=' && temporary.Text.Length > 1) { ExpTree tree = new ExpTree(temporary.Text.Substring(1)); SetDependency(cellname, tree.GetVariables()); } EvaluateCell(sender as Cell); } }
// 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); } } }
/************************************************************* * Function: OnPropertyChanged(object sender, PropertyChangedEventArgs e) * Date Created: Feb 8, 2017 * Date Last Modified: Feb 9, 2017 * Description: OnPropertyChanged * Return: void *************************************************************/ public void OnPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "Text") { createACell tmpCell = sender as createACell; RemoveDep(tmpCell.Name); if (tmpCell.Text != "" && tmpCell.Text[0] == '=') { ExpTree exp = new ExpTree(tmpCell.Text.Substring(1)); MakeDep(tmpCell.Name, exp.GetAllVariables()); } Eval(sender as Cell); } else if (e.PropertyName == "BGColor") { CellPropertyChanged(sender, new PropertyChangedEventArgs("BGColor")); } }
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")); }
private void SetExpVar(ExpTree exp, string vName) { Cell vCell = GetCell(vName); double val; // case: empty string, set to 0 if (string.IsNullOrEmpty(vCell.Value)) { exp.SetVar(vCell.Name, 0); } // case: not value, setto 0 else if (!double.TryParse(vCell.Value, out val)) { exp.SetVar(vName, 0); } else { exp.SetVar(vName, val); } }
// 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 OnPropertyChanged(object sender, PropertyChangedEventArgs e) // For each fired event property changed { if (e.PropertyName == "Text") // Text changed { CellInstance currentCell = sender as CellInstance; RemoveReferences(currentCell.Name); if (!string.IsNullOrEmpty(currentCell.Text) && currentCell.Text.StartsWith("=") && currentCell.Text.Length > 1) { ExpTree expression = new ExpTree(); expression.ExpressionString = currentCell.Text.Substring(1); AddReferences(currentCell.Name, expression.GetVar()); } EvalCell(currentCell); } if (e.PropertyName == "BGColor") // Background Color changed { CellPropertyChanged(sender, new PropertyChangedEventArgs("BGColor")); } }
/// <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); }
private bool badRef(CellHelper c) { if (string.IsNullOrEmpty(c.cText)) { return(false); } if (c.cText[0] != '=') { return(false); } var tree = new ExpTree(c.cText.Substring(1)); var nameList = tree.GetVariables(); var cellList = new List <CellHelper>(); foreach (var n in nameList) { cellList.Add(stringToCell(n)); } return(cellList.Contains(null)); }
/// <summary> /// assign values to all variables found in the expression tree from the corresponding cells /// </summary> /// <param name="cellToInterpret">the cell who's value is being updated</param> /// <param name="expTree">the expression tree for the cell being updated</param> /// <returns>true if all variables assigned, otherwise false</returns> private bool AssignExpTreeVars(ref SpreadsheetCell cellToInterpret, ref ExpTree expTree) { // create copy of variables found in expression tree var tempVars = new Dictionary <string, double>(expTree.Variables); foreach (var variable in tempVars) // iterate through variables - find matching cells { var cellToGetValueFrom = (SpreadsheetCell)GetCellFromStr(variable.Key); if (cellToGetValueFrom != null && cellToGetValueFrom != cellToInterpret) { if (!VerifyNoCycles(cellToInterpret, cellToInterpret)) { return(false); } Subscribe(cellToInterpret, cellToGetValueFrom); if (double.TryParse(cellToGetValueFrom.Value, out var varVal)) { expTree.Variables[variable.Key] = varVal; // store cell value in expression tree } else if (String.IsNullOrEmpty(cellToGetValueFrom.Value)) { expTree.Variables[variable.Key] = 0.0f; // cell has no value -- default to zero } else { return(false); // could not interpret cell value } } else // cell not found, or variable cell points to self { return(false); } } return(true); }
public void CellPropertyChanged(object sender, PropertyChangedEventArgs e) { if (e.PropertyName == "Text") { // Create new cell instance and have it as a sender m_Cell m_c = sender as m_Cell; // free the cell of dependents FreeCell(m_c.Name); if ((m_c.Text.Length > 1) && (m_c.Text != "") && (m_c.Text[0] == '=')) { ExpTree exp = new ExpTree(m_c.Text.Substring(1)); trackDependents(m_c.Name, exp.GetVarNames()); } EvalCell(sender as Cell); } else if (e.PropertyName == "BGColor") { // pass the sender to get the currrent cell in the sheetchanged function in form1 // fire property changed event for the spreadsheet to color SSPropertyChanged(sender, new PropertyChangedEventArgs("BGColor")); } }
// 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); }
//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 ErrType add_remove(SpreadsheetCell toAlter, ExpTree mainTree, bool removing) { /*Adding to and removing from the reference table occurs in this function*/ ErrType Error = ErrType.None; if (toAlter.VarList != null && toAlter.VarList.Count > 0) { string referencedBy = CellToString(toAlter); if (removing) { foreach (string referencedCell in toAlter.VarList) // Removes all variables from the old tree { if (refTable.ContainsKey(referencedCell)) { if (refTable[referencedCell].Contains(referencedBy)) { refTable[referencedCell].Remove(referencedBy); // Removes the current cell from any other cells referencing hash } if (refTable[referencedCell].Count < 1) // If an entry in the table has no cells referencing it, then it is removed { refTable.Remove(referencedCell); } } } toAlter.VarList.Clear(); // Empty that variable list (will be rebuild below) } else // Adding value to the reference this { foreach (string s in toAlter.VarList) // Updates the reference table with all of the referenced cells (variables in the expTree's context) { double CellValue = 0.0; SpreadsheetCell next = StringToCell(s); if (next != null) { if (s == CellToString(toAlter)) // SELF-REFERENCING { Error = ErrType.SelfRef; CheckErr(toAlter, Error, CellToString(toAlter)); UpdateErrorReferecedBy(toAlter, ErrType.SelfRef, CellToString(toAlter)); // Updates all cells referencing this cell that there is a value return(ErrType.SelfRef); } else if (next.Value.Contains("REF")) // Won't check for already occuring errors (in referenced cell) { if (next.Value.Contains("=<BAD_REF")) // If this cell REFERENCES a cell that contains a bad_ref error { CheckErr(toAlter, ErrType.BadRef, s); UpdateErrorReferecedBy(toAlter, ErrType.BadRef, s); Error = ErrType.BadRef; } else if (next.Value.Contains("=<SELF_REF")) // If this cell REFERENCES a cell that contains a self_ref error { CheckErr(toAlter, ErrType.SelfRef, s); UpdateErrorReferecedBy(toAlter, ErrType.SelfRef, CellToString(toAlter)); Error = ErrType.SelfRef; } else if (next.Value.Contains("=<CIRC_REF")) { CheckErr(toAlter, ErrType.CircRef, s); UpdateErrorReferecedBy(toAlter, ErrType.CircRef, CellToString(toAlter)); Error = ErrType.CircRef; } } if (next.Text != "") { Double.TryParse(next.Value, out CellValue); // Gets the cell's value mainTree.SetVar(s, CellValue); // Sets the variable in the expression tree's dictionary (0 if not yet set) } if (refTable.ContainsKey(s)) // If The variable already has references, just add to its hash { refTable[s].Add(referencedBy); } else // Otherwise create the new variable key with a new list containing the cell that references it { refTable.Add(s, new HashSet <string>() { referencedBy }); } } else // If Cell parsing return null (cell not recovered), the there is a bad reference { Error = ErrType.BadRef; CheckErr(toAlter, Error, CellToString(toAlter)); UpdateErrorReferecedBy(toAlter, ErrType.BadRef, CellToString(toAlter)); return(ErrType.BadRef); } } if (Error == ErrType.CircRef) { return(Error); } if (Error != ErrType.SelfRef && CheckCircularRef(toAlter, CellToString(toAlter))) // Checks for circular references here *** { Error = ErrType.CircRef; CheckErr(toAlter, Error, CellToString(toAlter)); UpdateErrorReferecedBy(toAlter, ErrType.CircRef, CellToString(toAlter)); } } } return(Error); }