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