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