/// <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 } } } }