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