/// <summary>
/// Loads a new spreadsheet configuration from stream.
/// </summary>
/// <param name="stream">Spreadsheet configuration stream.</param>
public void Load(Stream stream)
{
XDocument doc = XDocument.Load(stream);
if (doc.Elements().ToArray()[0].Name == "spreadsheet")
{
// The array of elements one level down. Should be cell elements.
XElement[] elements = doc.Elements().Elements().ToArray();
for (int i = 0; i < elements.Length; i++)
{
XElement element = elements[i];
if (element.Name == "cell")
{
XAttribute name = element.Attribute(XName.Get("name"));
SpreadsheetCell currCell = this.GetCellFromName(name.Value);
// Loops over cell elements for text and bg color elements
foreach (XElement childElement in element.Elements())
{
if (childElement.Name == "text")
{
currCell.Text = childElement.Value;
}
else if (childElement.Name == "bgcolor")
{
// Converts hex string to uints
currCell.BGColor = Convert.ToUInt32(childElement.Value, 16);
}
}
}
}
}
}
//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();
}
}
private void UpdateErrorReferecedBy(SpreadsheetCell cell, ErrType Check, string root)
{
/* This function will update all of the cells that reference the passed cell IFF an error has been detected
* Cells that contain the error and cells that reference cells containing an error will use this function */
if (this.refTable.ContainsKey(CellToString(cell))) // Ensures that the current cell has other cells that reference it
{
for (int i = 0; i < this.refTable[CellToString(cell)].Count; i++)
{
SpreadsheetCell nextCell = StringToCell(this.refTable[CellToString(cell)].ElementAt <string>(i)); // Looks up the next cell that references the current cell
if (Check == ErrType.SelfRef && CellToString(nextCell) == root) // Stop self-referencing loops
{
break; // Updates all cells that reference it
}
else if (Check == ErrType.CircRef && CellToString(nextCell) == root) // stops circular-referencing loops
{
break;
}
else
{
CheckErr(nextCell, Check, root);
UpdateErrorReferecedBy(nextCell, Check, root); // Continues updated all cells that reference THIS cell
}
}
}
}
public Cell(SpreadsheetCell from) : base(from.RowIndex, from.ColumnIndex)
{
this.valueStr = from.Value;
this.Text = from.Text;
this.BackColor = from.BGColor;
this.VarList = from.VarList;
}
// The purpose of this method is too remove dependencies that are no longer
// need. It only gets called when a cell that was dependent on other cells
// gets changed to a value
private void RemoveDependencies(SpreadsheetCell currentCell)
{
// Creating a dictionary that will keep track of which dependencies to delete
Dictionary <SpreadsheetCell, List <SpreadsheetCell> > deleteTheseDependencies = new Dictionary <SpreadsheetCell, List <SpreadsheetCell> >();
// Iterate through the dependencies dictionary and check if currentCell == a
// cell in the dictionary. If it does then add it to the deleteTheseDependencies dictionary
foreach (KeyValuePair <SpreadsheetCell, List <SpreadsheetCell> > kvp in dependencies)
{
foreach (SpreadsheetCell ssc in kvp.Value)
{
if (!deleteTheseDependencies.ContainsKey(kvp.Key))
{
deleteTheseDependencies.Add(kvp.Key, new List <SpreadsheetCell>());
}
if (ssc == currentCell)
{
deleteTheseDependencies[kvp.Key].Add(ssc);
}
}
}
// This loop just removes those dependencies from the dependencies dictionary
foreach (KeyValuePair <SpreadsheetCell, List <SpreadsheetCell> > kvp in deleteTheseDependencies)
{
foreach (SpreadsheetCell ssc in kvp.Value)
{
dependencies[kvp.Key].Remove(ssc);
}
}
}
private bool CheckCircularRef(SpreadsheetCell cell, string root)
{
bool Error = false;
string CellName = CellToString(cell);
if (cell.VarList != null)
{
foreach (string ReferencedCell in cell.VarList) // Will check all variables in the cell's variable list
{
if (null == StringToCell(ReferencedCell)) // If the parsing of the referenced cell failed, the error should be bad_ref not circ_ref
{
return(false);
}
//else if (cell.Value.Contains("CIRC_REF"))
// root = CellToString(cell);
else if (CellName != root && ReferencedCell == root) // If the loop came back around to the root value while checking, then a circular reference has been found
{
return(true);
}
else
{
Error = CheckCircularRef(StringToCell(ReferencedCell), root); // Continues checks for circular referencing
}
}
}
return(Error);
}
internal SpreadsheetCell StringToCell(string name)
{
char temp = name[0];
int j = temp - 65; // converts the letter into the index (Ex: Column A -> 0)
if (j < 0 || j > this.width)
{
return(null);
}
name = name.Remove(0, 1);
int i = 0;
if (Int32.TryParse(name, out i)) // Finds Row
{
if (i < 0 || i > this.height)
{
return(null);
}
}
else
{
return(null);
}
SpreadsheetCell from = cellArr[i - 1, j]; // Finds the cell given the parsed row and column number
return(from);
}
/// <summary>
/// Returns if the refCell has any references to the ogCell.
/// </summary>
/// <param name="ogCell">Original cell.</param>
/// <param name="refCell">Reference cell.</param>
/// <returns>If there are circular references.</returns>
private bool ContainsCircularReference(SpreadsheetCell ogCell, SpreadsheetCell refCell)
{
if (refCell.Text.StartsWith("="))
{
ExpressionTree et = new ExpressionTree(refCell.Text.Substring(1).Replace(" ", string.Empty));
List <string> names = et.GetVariableNames();
foreach (string name in names)
{
SpreadsheetCell currCell = this.GetCellFromName(name);
if (currCell == ogCell)
{
return(true);
}
bool containsCircularReference = this.ContainsCircularReference(ogCell, currCell);
if (containsCircularReference)
{
return(true);
}
}
}
return(false);
}
internal string CellToString(SpreadsheetCell from)
{
char column = (char)(from.ColumnIndex + 65); // Converts column index to an uppercase char
int row = from.RowIndex + 1; // corrects the row offset
return(column.ToString() + row.ToString()); // Returns the string version of the cell
}
/// <summary>
/// Initializes a new instance of the <see cref="CellTextCommand"/> class.
/// </summary>
/// <param name="cell">Spreadsheet cell.</param>
/// <param name="newText">Cell text.</param>
public CellTextCommand(SpreadsheetCell cell, string newText)
{
this.cell = cell;
this.newText = newText;
this.oldText = cell.Text;
this.Description = "text change";
}
/// <summary>
/// unsubscribe a cell to all its subscriptions
/// </summary>
/// <param name="subscriber">a cell which no longer depends on any other cells</param>
private void Unsubscribe(SpreadsheetCell subscriber)
{
for (int i = 0; i < subscriber.Subscriptions.Count; i++)
{
var subscription = subscriber.Subscriptions[i];
subscription.Subscribers.Remove(subscriber);
}
subscriber.Subscriptions.Clear();
}
// The function of IsValidInput is to check if the user
// entered a valid value or formula into the Spreadsheet.
// It uses a regular expression I grabbed from the ExpTree
// to parse the inputted text. It returns false when:
// - A cell is dependent on itself
// - If the user enters something besides a formula or a value
// E.g. If user enters 24gfg123
// - If a cell is dependent on a cell that doesn't have anytext
private bool IsValidInput(SpreadsheetCell currentCell)
{
// If the user's input begins with "="
if (currentCell.Text != null && currentCell.Text[0] == '=')
{
// Used the regular expression from the ExpTree to split
// the currentCell's text to see if it's a valid entry
List <string> tokens = System.Text.RegularExpressions.Regex.Split(currentCell.Text.Substring(1),
@"([-/\+\*\(\)])|([A-Za-z]+\d*)|(\d+\.?\d+)").ToList();
// Removes all "" from the list
for (int i = 0; i < tokens.Count; i++)
{
if (tokens.ElementAt(i) == "")
{
tokens.RemoveAt(i);
}
}
// Iterate through the List of strings and
// check if the user entered a valid Cell (A1-Z50 counts as valid)
foreach (string s in tokens)
{
if (Char.IsUpper(s[0]))
{
int output;
bool isNumeric = int.TryParse(s.Substring(1), out output);
if (isNumeric && output >= 1 && output <= 50)
{
if (currentCell == GetCell(s))
{
// Means current cell references itself
return(false);
}
else if (GetCell(s).Text == null)
{
// Referencing a cell that doesn't have currentCell as a dependency
return(false);
}
}
}
}
}
// If input doesn't begin with an "="
else if (currentCell.Text != null && currentCell.Text[0] != '=')
{
foreach (char c in currentCell.Text)
{
if (!Char.IsDigit(c))
{
// If the value entered is a mix of digits and letters
return(false);
}
}
}
return(true);
}
private bool Save_Pvt(TextWriter ToStream, Spreadsheet Sender)
{
XmlWriter xmlWrite = null;
try
{
xmlWrite = XmlWriter.Create(ToStream); // initializes the XML Writer with the passed stream
}
catch (ArgumentException e)
{
throw e;
}
if (xmlWrite != null) // Ensures the stream is usable
{
using (xmlWrite)
{
xmlWrite.WriteStartDocument(); // Places start tag
xmlWrite.WriteStartElement("Spreadsheet"); // Places the root node -> the spreadsheet itself
for (int i = 0; i < Sender.RowCount; i++) // iterates through every cell in the spreadsheet
{
for (int j = 0; j < Sender.ColumnCount; j++)
{
SpreadsheetCell from = Sender[i, j]; // grabs a cell
if (!Sender[i, j].IsDefault()) // Only saves those cells that have been altered
{
xmlWrite.WriteStartElement("Cell"); // Creates a cell start tag
xmlWrite.WriteAttributeString("Name", Sender.CellToString(from)); // Gives the cell a NAME attribute
if (from.Text != "") // No point in saving an empty string
{
xmlWrite.WriteElementString("Text", from.Text); // sets the TEXT from the cell
/* Cell's VALUE does NOT have to be saved
* Every cell's text will be re-computed when it is loaded in
* order does NOT matter, cell's that have references to it will simply be updated when it's their turn
*/
}
xmlWrite.WriteElementString("BGColor", from.BGColor.ToString()); // writes the BGColor element inside of the cell
xmlWrite.WriteEndElement(); // Ends the cell block
}
}
}
xmlWrite.WriteEndElement(); // Ends the Spreadsheet block
xmlWrite.WriteEndDocument(); // Ends file
}
return(true);
}
return(false);
}
/// <summary>
/// set a cell to subscribe to updates to another cell
/// </summary>
/// <param name="subscriber">a cell which depends on the value of another cell</param>
/// <param name="subscription">the cell which the subscriber depends on</param>
private void Subscribe(SpreadsheetCell subscriber, SpreadsheetCell subscription)
{
if (!subscriber.Subscriptions.Contains(subscription))
{
subscriber.Subscriptions.Add(subscription);
}
if (!subscription.Subscribers.Contains(subscriber))
{
subscription.Subscribers.Add(subscriber);
}
}
// This function updates the variables dictionary.
// It does this by first checking if the variables dictionary
// already has a key for that variable. Then it updates the value
private void UpdateVariables(SpreadsheetCell currentCell)
{
string cellName = GetCellName(currentCell);
if (!variables.ContainsKey(cellName))
{
variables.Add(cellName, Convert.ToDouble(currentCell.Value));
}
variables[cellName] = Convert.ToDouble(currentCell.Value);
}
// This method justs updates the currentCells text
public void UpdateAfterTextboxChanged(SpreadsheetCell currentCell, string val)
{
// Checks if the value is null or empty
if (val == null || val == "")
{
return;
}
// Update the cells Text
currentCell.Text = val;
}
// 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);
}
private void UpdateReferencedBy(SpreadsheetCell cell)
{
/*This function will update all of the cells that reference the passed cell*/
if (this.refTable.ContainsKey(CellToString(cell))) // Ensures that the current cell has other cells that reference it
{
for (int i = 0; i < this.refTable[CellToString(cell)].Count; i++)
{
SpreadsheetCell nextCell = StringToCell(this.refTable[CellToString(cell)].ElementAt <string>(i)); // Looks up the next cell that references the current cell
UpdateCellValue(ref nextCell);
}
}
}
// Pull function
private void Pull(string text, SpreadsheetCell currentCell)
{
string pull;
string pulledValue;
int pullColumn;
int pullRow;
int[] rowDigits = new int[2];
if (text[0] == '=')
{
pull = text.Substring(1, text.Length - 1);
pull = pull.Trim();
pullColumn = char.ToUpper(pull[0]) - 65;
pull = pull.Substring(1, pull.Length - 1);
if (pull.Length > 1)
{
for (int i = 0; i < pull.Length; i++)
{
rowDigits[i] = pull[i] - 48;
}
pullRow = 10 * rowDigits[0];
pullRow += rowDigits[1] - 1;
}
else
{
pullRow = pull[0] - 49;
}
pulledValue = this.GetCell(pullRow, pullColumn).CellText;
// If the pulled cell is also pulling from another cell, keep following
if (pulledValue[0] == '=')
{
Pull(pulledValue, currentCell);
}
// End condition to bring out
else
{
currentCell.ValueText = pulledValue;
}
}
else
{
currentCell.ValueText = text;
}
return;
}
// Clears out entire spreadsheet
public void ClearSheet()
{
for (int i = 0; i < ColumnCount; i++)
{
for (int j = 0; j < RowCount; j++)
{
SpreadsheetCell temp = (SpreadsheetCell)cells[i, j];
temp.Text = null;
temp.SetValue(null);
}
}
}
private void detect_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell toAlter = (sender as SpreadsheetCell);
if (e.PropertyName == "Value")
{
UpdateCellValue(ref toAlter); // Updates the Cell and all of the cells that reference it
}
if (e.PropertyName == "BGColor")
{
CellPropertyChanged(toAlter, new PropertyChangedEventArgs("BGColor")); // notifies the UI that the spreadsheet is changing a cells background color
}
}
// Event handler when SpreadsheetCell is changed
public void OnCellPropertyChanged(object sender, PropertyChangedEventArgs e)
{
// Value is what is shown in form
// Text is the formula held in cell
string text = e.PropertyName;
SpreadsheetCell currentCell = (SpreadsheetCell)sender;
Pull(text, currentCell);
// Raising the event that a cell value has been changed
CellPropertyChanged(currentCell, new PropertyChangedEventArgs(currentCell.ValueText));
return;
}
/// <summary>
/// Initializes each cell in cells as a SpreadsheetCell.
/// </summary>
private void InitializeCells()
{
for (int rowIndex = 0; rowIndex < this.cells.GetLength(0); rowIndex++)
{
for (int colIndex = 0; colIndex < this.cells.GetLength(1); colIndex++)
{
SpreadsheetCell newCell = new SpreadsheetCell(rowIndex, colIndex);
this.cells[rowIndex, colIndex] = newCell;
newCell.PropertyChanged += this.UpdateOnCellPropertyChanged;
newCell.DependentCellValueChanged += this.UpdateOnDependentCellValueChange;
}
}
}
/// <summary>
/// Updates (and possibly evaluates) a cell's value.
/// </summary>
/// <param name="cell">Spreadsheet cell.</param>
private void UpdateCellValue(SpreadsheetCell cell)
{
string newValue = cell.Text;
if (newValue.StartsWith("="))
{
newValue = this.EvaluateCellExpression(cell).ToString();
}
cell.SetValue(newValue);
this.CellPropertyChanged?.Invoke(cell, new PropertyChangedEventArgs("Value"));
}
// Spreadsheet Constructor
// Creates a 2-d array of cells and subscribes the SheetPropertyChanged Event
// To the Property changed event of each cell
public Spreadsheet(int columns, int rows)
{
RowCount = rows;
ColumnCount = columns;
cells = new SpreadsheetCell[columns, rows];
for (int i = 0; i < columns; i++)
{
for (int j = 0; j < rows; j++)
{
cells[i, j] = new SpreadsheetCell(i, j);
cells[i, j].PropertyChanged += SheetPropertyChanged;
}
}
}
/// <summary>
/// initializes a spreadsheet with specified rows/columns
/// </summary>
/// <param name="rows">number of rows contained by the Spreadsheet</param>
/// <param name="columns">number of columns contained by the Spreadsheet</param>
public Spreadsheet(int rows, int columns)
{
_cells = new SpreadsheetCell[rows, columns];
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
// create cells and subscribe to each cell
_cells[i, j] = new SpreadsheetCell(i, j);
_cells[i, j].PropertyChanged += HandlePropertyChangedEvent;
}
}
}
// Constructor that builds all the cell objects
public SpreadSheet(int rows, int columns)
{
// Builds the 2D array of Cells of size rows and columns
cells = new SpreadsheetCell[rows, columns];
// Loops that creates the cell object and gives it a PropertyChanged event
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
cells[i, j] = new SpreadsheetCell(i, j);
cells[i, j].PropertyChanged += SpreadSheetCell_PropertyChanged;
}
}
}
private bool Load_Pvt(TextReader FromStream, Spreadsheet Sender)
{
XmlReader xmlRead = XmlReader.Create(FromStream); // initializes the XMLReader with the passed stream
if (xmlRead != null)
{
using (xmlRead) // opens the stream
{
SpreadsheetCell to = null; // changes to the cell that is pulled from the .xml file
while (!xmlRead.EOF) // while there is still information to be read
{
if (xmlRead.IsStartElement()) // ensures that the reader is at a start element
{
switch (xmlRead.Name) // checks various names -> skips over unknown names
{
case "Cell":
xmlRead.MoveToFirstAttribute(); // moves to the name attribute
string cell = xmlRead.Value;
to = Sender.StringToCell(cell); // will grab the cell from the spreadsheet using this name/attribute
xmlRead.Read();
break;
case "Text":
Sender[to.RowIndex, to.ColumnIndex].Text = xmlRead.ReadElementContentAsString(); // sets Text
break;
case "BGColor":
Sender[to.RowIndex, to.ColumnIndex].BGColor = xmlRead.ReadElementContentAsInt(); // sets BGColor
break;
default: // simply reads over the unknown element -> also skips spreadsheet name (nothing is done here at the moment)
xmlRead.Read();
break;
}
}
else
{
xmlRead.Read(); // simply passed anything that is not a start element
}
}
}
return(true);
}
return(false);
}
//a constructor for the spreadsheet class that takes a number of rows and columns.
public Spreadsheet(int newRow, int newColumn)
{
NumberOfRows = newRow;
NumberOfColumns = newColumn;
SS_Array = new SpreadsheetCell[NumberOfRows, NumberOfColumns];
Undos = new Stack <MultiCmd>();
Redos = new Stack <MultiCmd>();
//go through the 2d array and subscribe to property change event handler
for (int i = 0; i < NumberOfRows; i++)
{
for (int j = 0; j < NumberOfColumns; j++)
{
SS_Array[i, j] = new SpreadsheetCell(i, j);
SS_Array[i, j].PropertyChanged += CellPropertyChanged;
}
}
}
public Spreadsheet(int rows, int cols)
{
Rows = rows;
Cols = cols;
SpreadsheetCell[,] newGrid = new SpreadsheetCell[Rows, Cols];
CellGrid = newGrid;
for (int i = 0; i < Rows; i++)
{
for (int j = 0; j < Cols; j++)
{
CellGrid[i, j] = new SpreadsheetCell(i, j);
CellGrid[i, j].PropertyChanged += CellPropertyChanged;
}
}
}
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 UpdateReferencedBy(SpreadsheetCell cell)
{
/*This function will update all of the cells that reference the passed cell*/
if (this.refTable.ContainsKey(CellToString(cell))) // Ensures that the current cell has other cells that reference it
{
for (int i = 0; i < this.refTable[CellToString(cell)].Count; i++)
{
SpreadsheetCell nextCell = StringToCell(this.refTable[CellToString(cell)].ElementAt<string>(i)); // Looks up the next cell that references the current cell
UpdateCellValue(ref nextCell);
}
}
}
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 CheckErr(SpreadsheetCell cell, ErrType err, string root)
{
/* This function will check the passed error and update the cell accordingly
* This function will also update cells referencing an error with the location
where the error is occuring (exluding Circular references)
* Reinitializes cell delegate */
if (cell.Text == "") // Skips cells that have been reset
return;
if (err == ErrType.BadRef) // BAD REFERENCES
{
if (CellToString(cell) == root)
cell = new Cell(cell, "=<BAD_REF::SRC>"); // Plants the source of the error
else
{
cell = new Cell(cell, "=<BAD_REF::AT[" + root + "]"); // Updates the cell's value with the location of the error
}
}
else if (err == ErrType.SelfRef) // SELF REFERENCES
{
if (CellToString(cell) == root)
cell = new Cell(cell, "=<SELF_REF::SRC>");
else
{
cell = new Cell(cell, "=<SELF_REF::AT[" + root + "]");
}
}
else if (err == ErrType.CircRef) // CIRCULAR REFERENCES
{
cell = new Cell(cell, "=<CIRC_REF>");
}
else if (err == ErrType.DivZero) // DIVISION BY ZERO
{
if (CellToString(cell) == root)
cell = new Cell(cell, "=<DIV_ZERO::SRC>");
else
{
cell = new Cell(cell, "=<DIV_ZERO::AT[" + root + "]");
}
}
else if (err == ErrType.InvExp) // INVALID EXPRESSIONS
{
if (CellToString(cell) == root)
cell = new Cell(cell, "=<INV_EXP::SRC>");
else
{
cell = new Cell(cell, "=<INV::EXP::AT[" + root + "]");
}
}
if (err != ErrType.None)
{
cellArr[cell.RowIndex, cell.ColumnIndex] = new Cell(cell, cell.Value);
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
}
}
private bool CheckCircularRef(SpreadsheetCell cell, string root)
{
bool Error = false;
string CellName = CellToString(cell);
if (cell.VarList != null)
{
foreach (string ReferencedCell in cell.VarList) // Will check all variables in the cell's variable list
{
if (null == StringToCell(ReferencedCell)) // If the parsing of the referenced cell failed, the error should be bad_ref not circ_ref
return false;
//else if (cell.Value.Contains("CIRC_REF"))
// root = CellToString(cell);
else if (CellName != root && ReferencedCell == root) // If the loop came back around to the root value while checking, then a circular reference has been found
return true;
else
Error = CheckCircularRef(StringToCell(ReferencedCell), root); // Continues checks for circular referencing
}
}
return Error;
}
internal string CellToString(SpreadsheetCell from)
{
char column = (char)(from.ColumnIndex + 65); // Converts column index to an uppercase char
int row = from.RowIndex + 1; // corrects the row offset
return column.ToString() + row.ToString(); // Returns the string version of the cell
}
public RestoreText(SpreadsheetCell from, string text)
{
this.Current = from;
this.CellText = text;
}
public RestoreBGColor(SpreadsheetCell from, int color)
{
this.Current = from;
this.ARBGColor = color;
}
public Cell(SpreadsheetCell from, string newVal) : base(from.RowIndex, from.ColumnIndex)
{
this.valueStr = newVal;
this.Text = from.Text;
this.BackColor = from.BGColor;
this.VarList = from.VarList;
}
private void UpdateErrorReferecedBy(SpreadsheetCell cell, ErrType Check, string root)
{
/* This function will update all of the cells that reference the passed cell IFF an error has been detected
* Cells that contain the error and cells that reference cells containing an error will use this function */
if (this.refTable.ContainsKey(CellToString(cell))) // Ensures that the current cell has other cells that reference it
{
for (int i = 0; i < this.refTable[CellToString(cell)].Count; i++)
{
SpreadsheetCell nextCell = StringToCell(this.refTable[CellToString(cell)].ElementAt<string>(i)); // Looks up the next cell that references the current cell
if (Check == ErrType.SelfRef && CellToString(nextCell) == root) // Stop self-referencing loops
break; // Updates all cells that reference it
else if (Check == ErrType.CircRef && CellToString(nextCell) == root) // stops circular-referencing loops
break;
else
{
CheckErr(nextCell, Check, root);
UpdateErrorReferecedBy(nextCell, Check, root); // Continues updated all cells that reference THIS cell
}
}
}
}