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