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