private string CalculateValue(string text, SpreadsheetCell senderCell)
{
text = text.Substring(1); //removes the first character of the string, which is =
if (text.Length > 1) //if there are variables to replace, they will be at least 2 chars
{
MatchCollection splitOperands = Regex.Matches(text, @"\w+\.?\d*"); //temporary way to get all the variables.
HashSet <SpreadsheetCell> referencedCells = new HashSet <SpreadsheetCell>();
foreach (Match mat in splitOperands)
{
if (!Regex.Match(mat.Value, @"^\d+").Success) // if the match starts with a number then its not a coordinate and we dont have to retrieve a value.
{
SpreadsheetCell cell = GetCell(mat.Value) as SpreadsheetCell;
cell.ValueChanged += senderCell.OnValueChanged;
senderCell.AddReferenceToCell(cell); //tell the sender cell what its referencing. Hashsets can only have unique values, so adding something that is already here will do nothing.
referencedCells.Add(cell); //keep track of which cells this specific expression looks for.
text = text.Replace(mat.Value, cell.Value); //replaces that substring in the text with that cell's value.
}
}
referencedCells.SymmetricExceptWith(senderCell.ReferencedCells); //removes all the cell references that are the same.
foreach (SpreadsheetCell cell in referencedCells) // all the cells that were referenced previously but are no longer being referenced.
{
cell.ValueChanged -= senderCell.OnValueChanged; // unsubsribes from the cell that is no longer being referenced.
senderCell.RemoveReferenceToCell(cell);
}
ExpTree tree = new ExpTree(text);
text = tree.Eval().ToString();
}
return(text);
}
public void Menu()
{
int flag = 0;
string expressionString = "(A1-12-C1)"; //now having error in parentheses
string varName;
string varValue;
double numValue;
newExpressionTree = new ExpTree(expressionString);
while (flag != 1) //1 causes problems
{
Console.WriteLine("_________________MENU_________________");
Console.WriteLine("The expression entered is: " + expressionString);
/*Console.WriteLine("Would you like to update this expression? Enter y for yes and n for no \n");
* char userInput = Console.ReadKey().KeyChar; //this can cause errors in program testing, just for my own testing purposes
* if (userInput == 'y')
* {
* Console.WriteLine("Please input your new expression with NO whitespaces: \n");
* expressionString = Console.ReadLine();
*
* }*/
Console.WriteLine("(1): Update Expression");
Console.WriteLine("(2): Update the value");
Console.WriteLine("(3): Evaluate the tree");
Console.WriteLine("(4): GoodBye");
string userInput = Console.ReadLine().ToString();
Console.WriteLine("You entered option {0}", userInput);
switch (userInput)
{
case "1":
Console.WriteLine("Please enter a new expression: ");
expressionString = Console.ReadLine();
newExpressionTree = new ExpTree(expressionString);
break;
case "2":
Console.WriteLine("Enter a variable name: ");
varName = Console.ReadLine();
Console.WriteLine("Now please enter a value of that variable: ");
varValue = Console.ReadLine();
numValue = Convert.ToDouble(varValue); //need to make the value a double
Console.WriteLine("Variable Name: {0}\nVariable Value: {1}", varName, numValue); //updated by SHW for readability
newExpressionTree.SetVar(varName, numValue); //takes string and double, makes brand new exp tree
break;
case "3":
Console.WriteLine("The result is: {0}", newExpressionTree.Eval());
break;
case "4":
flag = 1; //exit flag
break;
}
}
}
private string solver(string equation)
{
ExpTree mytree = new ExpTree(equation);
findCells(mytree);
return(mytree.Eval().ToString());
}
/*************************************************************
* 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);
}
}
//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());
m_Cell.SetValue(exptree.Eval().ToString());
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
private void getCellValue(Cell c)
{
//c = (cell)sender
if (c.setText.Length == 0)
{
c.Value = "";
}
else if (!c.setText.StartsWith("=")) //no formula
{
c.Value = c.setText;
}
else // formula
{
if (c.setText.Length > 1)
{
try
{
string equation = c.setText.Substring(1);
ExpTree xp = new ExpTree(equation);
string[] cref = xp.getVariables();
foreach (string name in cref)
{
double val = getCorrCellVal(name);
xp.SetVar(name, val);
Cell key = getCorrCell(name);
if (!dep.ContainsKey(key)) //add dependency
{
dep.Add(key, new HashSet <Cell>());
}
dep[key].Add(c);
}
c.Value = xp.Eval().ToString();
}
catch
{
c.Value = "#REF!";
throw;
}
}
}
}
// overloaded UpdateCell, takes a cell as the parameter
private void UpdateCell(Cell cellToUpdate)
{
// remove old dependencies
RemoveDependencies(cellToUpdate);
// first check if the cell is empty
// i.e. the user deleted the text from the cell
if (string.IsNullOrEmpty(cellToUpdate.Text))
{
cellToUpdate.Value = "";
}
// if the text doesn't begin with an =
else if (cellToUpdate.Text[0] != '=')
{
double cellValue;
// if the cell contains a double
if (double.TryParse(cellToUpdate.Text, out cellValue))
{
// build the ExpTree to set the cell's variable
ExpTree userExpTree = new ExpTree(cellToUpdate.Text);
cellValue = userExpTree.Eval();
userExpTree.SetVar(cellToUpdate.Name, cellValue);
// update the value to the double
cellToUpdate.Value = cellValue.ToString();
}
else
{
// otherwise the value is just set to the text
cellToUpdate.Value = cellToUpdate.Text;
}
}
// need to evaluate an expression here
else
{
// parse the text to get the expression
string exp = cellToUpdate.Text.Substring(1);
// build an ExpTree, eval to add variables
ExpTree userExpTree = new ExpTree(exp);
userExpTree.Eval();
// get the variable names
string[] varNames = userExpTree.GetVarNames();
// loop through each variable name in the array
foreach (string variable in varNames)
{
// get the cell and add its value to the dictionary
double value = 0.0;
Cell relevantCell = GetCell(variable);
// try to parse out the double
double.TryParse(relevantCell.Value, out value);
// set the variable
userExpTree.SetVar(variable, value);
}
// set the value to the computed value of the ExpTree
cellToUpdate.Value = userExpTree.Eval().ToString();
// add the dependencies
AddDependencies(cellToUpdate, varNames);
}
// if there are cells that depend on the one we just updated
if (m_dependencies.ContainsKey(cellToUpdate))
{
// update all dependent cells
UpdateDependencies(cellToUpdate);
}
// and notify subscribers that the cell property changed
// need to pass the sender which is the cell
// passing the this reference would pass the spreadsheet
CellPropertyChanged(cellToUpdate, new PropertyChangedEventArgs("CellChanged"));
}
public void Cells_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell spreadsheetCell = sender as SpreadsheetCell; //Sender as a spreadsheet cell
switch (e.PropertyName) //switch on the property changed
{
case ("Text"): //if text has been changed
if (spreadsheetCell.Text == string.Empty)
{
spreadsheetCell.Value = string.Empty;
NotifyCellPropertyChanged(spreadsheetCell);
//spreadsheetCell.Sendupdate();
}
else if (spreadsheetCell.Text[0] == '=')
{
spreadsheetCell.Value = spreadsheetCell.Text;
NotifyCellPropertyChanged(spreadsheetCell);
spreadsheetCell.Sendupdate();
}
else if (spreadsheetCell.Text[0] != '=' && spreadsheetCell.Value == string.Empty)
{
spreadsheetCell.Value = spreadsheetCell.Text;
NotifyCellPropertyChanged(spreadsheetCell);
spreadsheetCell.Sendupdate();
}
else if (spreadsheetCell.Text[0] != '=' && spreadsheetCell.Value[0] != '=')
{
spreadsheetCell.Value = spreadsheetCell.Text;
NotifyCellPropertyChanged(spreadsheetCell);
spreadsheetCell.Sendupdate();
}
break;
case ("Value"):
try
{
if (spreadsheetCell.Value != string.Empty)
{
if (spreadsheetCell.Value[0] == '=')
{
if (char.IsLetter(spreadsheetCell.Value[1]) && spreadsheetCell.Value.Length <= 4 && spreadsheetCell.Value.Length >= 2)
{
string varcol = spreadsheetCell.Value[1].ToString();
string varrow = spreadsheetCell.Value.Substring(2);
SpreadsheetCell referencedcell = getcellat(varcol, varrow);
if (spreadsheetCell.Text != referencedcell.Text)
{
spreadsheetCell.Text = referencedcell.Text;
}
referencedcell.Referencesme(spreadsheetCell);
}
else if (spreadsheetCell.Value.Length >= 2) //starts with = and is within the length of a valid function
{
ExpTree expTree = new ExpTree(spreadsheetCell.Value.Substring(1));
expTree.Eval();
string varcol;
string varrow;
double vardata;
Dictionary <string, double> vardic = new Dictionary <string, double>(expTree.variables);
foreach (string key in vardic.Keys)
{
varcol = key.Substring(0, 1);
varrow = key.Substring(1);
SpreadsheetCell referencedcell = getcellat(varcol, varrow);
if (referencedcell == spreadsheetCell)
{
//ToDo-- If self is in the equation
vardata = 0;
}
else
{
Double.TryParse(referencedcell.Text, out vardata);
}
referencedcell.Referencesme(spreadsheetCell);
expTree.SetVAr(key, vardata);
}
string eval = expTree.Eval().ToString();
if (spreadsheetCell.Text != eval)
{
spreadsheetCell.Text = eval;
}
}
}
}
}
catch
{
spreadsheetCell.Text = "#ERROR";
}
break;
}
}
private void Spreadsheet_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
if ("Text" == e.PropertyName) //checks if Cell.Text property was changed
{
string oldValue = ((Cell)sender).Value; //make a copy of the value of the old string for undo methods
ICmd temp = new RestoreText((Cell)sender, oldValue);
AddUndo(temp);
if (((Cell)sender).Text.IndexOf('=') == 0) //must evaluate if it's a formula
{
//remove cell from every hashset in dependency table to update the dependency table after formula evaluation
foreach (KeyValuePair <Cell, HashSet <Cell> > p in dependencies)
{
dependencies[p.Key].Remove((Cell)sender);
}
string formula = ((Cell)sender).Text.Replace("=", "");
var tree = new ExpTree(formula); //put the formula in tree for evaluation
string[] variables = tree.GetVars(); //get the variables from the tree
if (checkValidFormulaVariables(variables) && !checkSelfReference(((Cell)sender), variables)) //check if variables are in valid format
{
foreach (string s in variables)
{
double value = getCorrespondingCellValue(s); //get the individual variable value
tree.SetVar(s, value); //set the variable so the tree can evaluate the expression
updateDependencyTable((Cell)sender, s);
}
double finalValue = tree.Eval(); //returns the final result of formula evaluation
((Cell)sender).Value = finalValue.ToString();
}
else if (!checkValidFormulaVariables(variables)) //variables not in valid format
{
tree.ClearVariables(); //delete bad variables
((Cell)sender).Text = "!(invalid entry)";
}
else
{
tree.ClearVariables();
((Cell)sender).Text = "!(self reference)";
}
}
else //just a constant value or some string was entered
{
//check if any cells depend on the cell where value was changed and update them
if (dependencies.ContainsKey((Cell)sender) && dependencies[(Cell)sender].Count > 0)
{
foreach (Cell cell in dependencies[(Cell)sender])
{
//Re-evaluate the formulas of each cell depending on the changed cell
string formula = cell.Text.Replace("=", "");
var tree = new ExpTree(formula);
string[] vars = tree.GetVars();
if (checkValidFormulaVariables(vars)) //check if variables are in valid format
{
foreach (string v in vars)
{
Cell check = getCorrespondingCell(v);
double varValue = 0.0;
if (check == ((Cell)sender)) //check if the variable is the cell being changed
{
double.TryParse(((Cell)sender).Text, out varValue); //set value of that variable to the newly changed value
}
else
{
varValue = getCorrespondingCellValue(v);
}
tree.SetVar(v, varValue);
}
double finalValue = tree.Eval(); //returns the final result of formula evaluation
cell.Value = finalValue.ToString();
}
else
{
cell.Text = "!(invalid entry)";
}
}
}
((Cell)sender).Value = ((Cell)sender).Text; //set the value of the cell
}
}
if (CellPropertyChanged != null) //does the event have subscribers?
{
CellPropertyChanged(sender, e);
}
}
// overloaded UpdateCell, takes a cell as the parameter
private void UpdateCell(Cell cellToUpdate)
{
// remove old dependencies
RemoveDependencies(cellToUpdate);
// first check if the cell is empty
// i.e. the user deleted the text from the cell
if (string.IsNullOrEmpty(cellToUpdate.Text))
{
cellToUpdate.Value = "";
}
// if the text doesn't begin with an =
else if (cellToUpdate.Text[0] != '=')
{
double cellValue;
// if the cell contains a double
if (double.TryParse(cellToUpdate.Text, out cellValue))
{
// build the ExpTree to set the cell's variable
ExpTree userExpTree = new ExpTree(cellToUpdate.Text);
cellValue = userExpTree.Eval();
userExpTree.SetVar(cellToUpdate.Name, cellValue);
// update the value to the double
cellToUpdate.Value = cellValue.ToString();
}
else
{
// otherwise the value is just set to the text
cellToUpdate.Value = cellToUpdate.Text;
}
}
// need to evaluate an expression here
else
{
// flag to check for errors
bool errors = false;
// parse the text to get the expression
string exp = cellToUpdate.Text.Substring(1);
// build an ExpTree, eval to add variables
ExpTree userExpTree = new ExpTree(exp);
userExpTree.Eval();
// get the variable names
string[] varNames = userExpTree.GetVarNames();
// loop through each variable name in the array
foreach (string variable in varNames)
{
// get the cell and add its value to the dictionary
double value = 0.0;
Cell relevantCell = GetCell(variable);
// check for valid cells
// CASE ONE: BAD REFERENCE
if (relevantCell == null)
{
// display that the cell has a bad reference and notify subscribers
cellToUpdate.Value = "!(bad reference)";
CellPropertyChanged(cellToUpdate, new PropertyChangedEventArgs("CellChanged"));
// set the error flag to true
errors = true;
}
// CASE TWO: SELF-REFERENCE
else if (cellToUpdate.Name == variable)
{
// display that the cell has a self-reference and notify subscribers
cellToUpdate.Value = "!(self reference)";
CellPropertyChanged(cellToUpdate, new PropertyChangedEventArgs("CellChanged"));
// set the error flag to true
errors = true;
}
// if there's an error, get out of the function
if (errors == true)
{
// if there are cells that depend on the one we just updated
if (m_dependencies.ContainsKey(cellToUpdate))
{
// update all dependent cells
UpdateDependencies(cellToUpdate);
}
return;
}
// try to parse out the double
double.TryParse(relevantCell.Value, out value);
// set the variable
userExpTree.SetVar(variable, value);
// add the cell to the HashSet to check for circular references
this.visitedCells.Add(relevantCell);
}
// set the value to the computed value of the ExpTree
cellToUpdate.Value = userExpTree.Eval().ToString();
// add the dependencies
AddDependencies(cellToUpdate, varNames);
// check for circular references
if (CheckForCircularReferences(cellToUpdate))
{
// display that the cell has a circular-reference and notify subscribers
cellToUpdate.Value = "!(circular reference)";
CellPropertyChanged(cellToUpdate, new PropertyChangedEventArgs("CellChanged"));
// set the error flag to true
errors = true;
}
// if there's an error, get out of the function
if (errors == true)
{
return;
}
}
// if there are cells that depend on the one we just updated
if (m_dependencies.ContainsKey(cellToUpdate))
{
// update all dependent cells
UpdateDependencies(cellToUpdate);
}
// and notify subscribers that the cell property changed
// need to pass the sender which is the cell
// passing the this reference would pass the spreadsheet
CellPropertyChanged(cellToUpdate, new PropertyChangedEventArgs("CellChanged"));
// clear the visited cells HashSet at a successful cell change
this.visitedCells.Clear();
}
private void Spreadsheet_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
//from crandall
rmDep((Cell)sender);
if (e.PropertyName == "Text")
{
changedCells.Add((Cell)sender);
if (!((Cell)sender).Text.StartsWith("="))
{
((Cell)sender).Value = ((Cell)sender).Text;
}
else
{
var expression = ((Cell)sender).Text; //this will get the expression
try
{
var subExp = expression.Substring(1);
ExpTree ssExpression = new ExpTree(subExp);
DefineVars(ssExpression);
((Cell)sender).Value = ssExpression.Eval().ToString();
if (((Cell)sender).Value == "NaN")
{
((Cell)sender).Value = expression;
((Cell)sender).Value = GetCell(expression.Substring(1)).Value;
CellPropertyChanged.Invoke(sender,
new PropertyChangedEventArgs("Value"));
}
addDep((Cell)sender, ssExpression.varNameList);
}
catch
{
//we are concerned with #REF
try
{
var ssExpression = new ExpTree(((Cell)sender).Text.Substring(1).ToUpper());
((Cell)sender).Value = "#REF";
CellPropertyChanged.Invoke(sender,
new PropertyChangedEventArgs("Value"));
addDep((Cell)sender, ssExpression.varNameList);
}
catch
{
//need to check if there is an = operator without any following
((Cell)sender).Value = "#REF";
CellPropertyChanged.Invoke(sender,
new PropertyChangedEventArgs("Value"));
}
((Cell)sender).Value = "#REF";
CellPropertyChanged.Invoke(sender,
new PropertyChangedEventArgs("Value"));
}
}
}
if (cellDependency.ContainsKey((Cell)sender))
{
updateDep((Cell)sender);
}
CellPropertyChanged.Invoke(sender,
new PropertyChangedEventArgs("Value"));
}
