// undo a previously made singular cell change, pop from changeList
public void UndoCell(Tuple <Cell, string> tuple)
{
Cell cell = tuple.Item1; // get cell from tuple in changeList
// get the column and row of the cell
int column = cell.ColumnIndex;
int row = cell.RowIndex;
Cell temp = this.GetCell(column, row); // get the corresponding node in the spreadsheet
undoRedoCell = temp; // set undoRedoCell to this spreadsheet cell, will help avoid duplications of undos
Cell saveCell = new SpreadsheetCell(column, row); // create a new cell that will be added to the redo changeList
// copy the spreadsheet cell's current property values over to this new cell
saveCell.Color = temp.Color;
saveCell.Text = temp.Text;
string change = tuple.Item2; // copy the string from the tuples second value
AddRedo(saveCell, change); // pass the new cell and string to AddRedo function
// change the spreadsheet cell's property values based on the tuple cell's values
temp.Color = cell.Color;
temp.Text = cell.Text;
undoChangeList.RemoveAt(undoChangeList.Count - 1); // remove the cell from the undo change list
}
/// <summary>
/// Checks for Circular References in Spreadsheet Cells
/// </summary>
/// <param name="variableCell">Cell that current cell depends on</param>
/// <param name="currentCell">The current cell being changed</param>
/// <returns></returns>
public bool CheckCircularReference(SpreadsheetCell variableCell, SpreadsheetCell currentCell)
{
// Self-Reference is a form of Circular Reference so return
if (variableCell == currentCell)
{
return(true);
}
// If current cell is not in its dependent dictionary return
if (!dependentCells.ContainsKey(currentCell))
{
return(false);
}
foreach (SpreadsheetCell dependentCell in dependentCells[currentCell])
{
// Recursively Check Other Cell Dependencies
if (CheckCircularReference(variableCell, dependentCell))
{
// Circular Reference Found
return(true);
}
}
// Dependencies Have No Circular References
return(false);
}
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);
}
/// <summary>
/// Will show the color dialog and allow change the background color of a cell.
/// </summary>
/// <param name="sender">Spreadsheet.</param>
/// <param name="e">Background Color Notification.</param>
private void ChangeBackgToolStripMenuItem_Click(object sender, EventArgs e)
{
ColorDialog myDialog = new ColorDialog();
// Keeps the user from selecting a custom color.
myDialog.AllowFullOpen = true;
// Allows the user to get help. (The default is false.)
myDialog.ShowHelp = true;
// If OK was not pressed, then set the cd color to white
if (myDialog.ShowDialog() != DialogResult.OK)
{
myDialog.Color = Color.FromArgb(-1);
}
// For all selected cells, change the background color in the spreadsheet.
foreach (DataGridViewCell gridCell in this.dataGridView1.SelectedCells)
{
SpreadsheetCell dataCell = this.mainSpreadSheet.GetCell(gridCell.RowIndex, gridCell.ColumnIndex);
this.mainSpreadSheet.SetCellColor(gridCell.RowIndex, gridCell.ColumnIndex, (uint)myDialog.Color.ToArgb());
}
this.dataGridView1.ClearSelection(); // Clears off user highlighted cells.
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="cell"></param>
/// <returns></returns>
private bool CheckSelfReference(SpreadsheetCell sender, SpreadsheetCell cell)
{
if (cell == sender)
{
sender.CellValue = "!(self reference)";
this.OnPropertyChanged(sender, "CellChanged");
return(true);
}
return(false);
}
/// <summary>
/// Clears the dependency of a cell from any lists
/// </summary>
/// <param name="cell">Cell to clear dependency</param>
private void RemoveCellDependency(SpreadsheetCell cell)
{
foreach (HashSet <SpreadsheetCell> dependencySet in this.dependentCells.Values)
{
if (dependencySet.Contains(cell))
{
dependencySet.Remove(cell);
}
}
}
private void parseSingleVariable(SpreadsheetCell c)
{
string txt = c.Text; //use text field to set value
int row = 0, col = 0, singularValue = 0; //=ColRow (ex. =B2)
double cellVal = 0.0, junk = 0.0;
bool res = false;
res = int.TryParse(txt.Substring(1), out singularValue);
if (txt.Substring(1) == c.ToVarName)
{
c.Value = "#REF!";
c.bgColor = "#FF0000";
return;
}
if (!res)
{
int.TryParse(txt.Substring(2), out row); //parse row# //substring to grab all numbers for row
row--; //change indexing mode
if ('a' <= txt[1] && txt[1] <= 'z') //columns are letters => ascii math
{
col = (int)(txt[1] - 'a'); //support lower-case letters (extra)
}
else
{
col = (int)(txt[1] - 'A'); //support upper-case (default)
}
//Debug.WriteLine("row={0}, col={1}", row, col);
Double.TryParse(this.sheet[row][col].Value, out cellVal);
string temp = this.sheet[row][col].Value; //set value (not text, else infinite loop)
c.Value = temp; // singularValue;
//int.TryParse(t.Substring(1), out curRow); //get row
//int col = (int)(t[0] - 'A'); //get column
//double.TryParse(sheet[row - 1][col].Value, out leafVal); //find value mapped to given cell
if (!c.references.TryGetValue(txt.Substring(1), out junk))
{
if (this.sheet[row][col].Value != "") //this bypasses short-circuit eval.
{
c.references.Add(txt.Substring(1), cellVal); //add dictionary reference to cell
}
else
{
c.references.Add(txt.Substring(1), 0); //add null dictionary reference to cell
}
}
if (!sheet[row][col].referencedBy.TryGetValue(c.ToVarName, out junk))
{
sheet[row][col].referencedBy.Add(c.ToVarName, cellVal); //make cell know it's referenced
}
}
else
{
c.Value = singularValue.ToString();
}
}
// push undo to change list
public void AddUndo(object sender, string change)
{
Cell copy = sender as Cell;
// the three conditions that allow for a push are as follows:
// if list is empty or the cell attempting to push is not the same as undoRedoCell or the cell is a different cell entirely,
// then push to list
if (undoStack.Count == 0 || undoRedoCell != copy || (copy.RowIndex != undoChangeList[undoChangeList.Count - 1].Item1.RowIndex || copy.ColumnIndex != undoChangeList[undoChangeList.Count - 1].Item1.ColumnIndex))
{
// copy column and row from sender cell
int column = copy.ColumnIndex;
int row = copy.RowIndex;
Cell temp = new SpreadsheetCell(column, row); // get corresponding cell from spreadsheet
// copy property values from sender cell
temp.Color = copy.Color;
temp.Text = copy.Text;
string menuChange = string.Empty;
// set string to be passed to tuple
if (change == "text" || change == "color")
{
menuChange = change;
}
else
{
menuChange = "cell";
}
// create new tuple with created cell and string
Tuple <Cell, string> stackValue;
stackValue = new Tuple <Cell, string>(temp, menuChange);
undoChangeList.Add(stackValue); // push tuple to undo change list
// only pushes to undo stack if the change is a text change
// colors are done elsewhere due to needing to allow for group changes
if (change == "text")
{
List <Cell> list = new List <Cell>();
list.Add(copy);
undoStack.Add(list);
}
}
string cellChange = undoChangeList[undoChangeList.Count - 1].Item2; // get string from pushed tuple
OnAddUndo(this, cellChange); // fire event that notifies UI of change
undoRedoCell = null; // reset undoRedoCell to null
}
/// <summary>
/// Subscribe Dependent Cells as Listeners
/// </summary>
/// <param name="listener"></param>
/// <param name="variables"></param>
private void SubscribeDependencies(SpreadsheetCell listener, string[] variables)
{
foreach (string variable in variables)
{
SpreadsheetCell variableCell = GetCell(variable);
if (variableCell != null)
{
variableCell.DependencyChanged += listener.OnDependencyChanged;
}
}
}
// 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;
}
#pragma warning restore SA1130 // Use lambda syntax
/// <summary>
/// Initializes a new instance of the <see cref="Spreadsheet"/> class.It creates an array of 2D
/// rows and columns .
/// </summary>
/// <param name="rowCount"> Takes number of rows in order to initalize in the 2d array.
/// </param>
/// <param name="columnCount"> Takes numbers of columns in order to initalize in the 2d array.
/// </param>
public Spreadsheet(int rowCount, int columnCount)
{
this.ArrayOfCells = new Cell[rowCount, columnCount];
for (int i = 0; i < this.NumberOfRows; i++)
{
for (int j = 0; j < this.NumberOfColumns; j++)
{
Cell cell = new SpreadsheetCell(i, j);
cell.PropertyChanged += this.OnSpreadsheetPropertyChanged;
this.ArrayOfCells[i, j] = cell;
}
}
}
/// <summary>
/// Sets a variable name and value for dictionary of ExpressionTree
/// </summary>
/// <param name="expressionTree">ExpressionTree being evaluated</param>
/// <param name="varName">Name of the variable</param>
private void SetCellVariable(SpreadsheetEngine.ExpressionTree expressionTree, string varName)
{
SpreadsheetCell cell = this.GetCell(varName);
if (double.TryParse(cell.CellValue, out double cellValue))
{
expressionTree.SetVariable(varName, cellValue);
}
else
{
expressionTree.SetVariable(varName, 0.0);
}
}
// 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;
}
public SpreadSheet(int row, int column)
{
cells = new SpreadsheetCell[row, column];
for (int i = 0; i < row; ++i)
{
for (int j = 0; j < column; ++j)
{
cells[i, j] = new SpreadsheetCell(i, j);
//subscribe so that the spreadsheet class knows when a cell value is changed
cells[i, j].PropertyChanged += Cells_PropertyChanged;
}
}
}
/// <summary>
/// This method implements when a cell's Value changes.
/// it gets updates in the DataGridView.
/// Event Handler.
/// </summary>
/// <param name="sender"> for the cell.</param>
/// <param name="e">for thecell. </param>
private void OnCellPropertyChanged(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell updateCell = sender as SpreadsheetCell;
if (e.PropertyName == "Value")
{
// Modify the value in the ColumnIndex cell of the RowIndex row.
this.dataGridView1.Rows[updateCell.RowIndex].Cells[updateCell.ColumnIndex].Value = updateCell.Value;
}
else if (e.PropertyName == "Color")
{
this.dataGridView1.Rows[updateCell.RowIndex].Cells[updateCell.ColumnIndex].Style.BackColor = Color.FromArgb((int)updateCell.BGColor);
}
}
public Spreadsheet(int columns, int rows)
{
columnCount = columns;
rowCount = rows;
cells = new SpreadsheetCell[columns, rows]; //allocating memory
for (int i = 0; i < columns; i++)
{
for (int j = 0; j < rows; j++)
{
cells[i, j] = new SpreadsheetCell(i, j); //creates a cell with its position in the array.
cells[i, j].PropertyChanged += Spreadsheet_PropertyChanged; //tells the cell to call Spreadsheet_propertyChanged when PropertyChanged is called.
}
}
}
/// <summary>
/// Cells in UI grid values are set to the values of the logic engine cell.
/// </summary>
/// <param name="sender">sender.</param>
/// <param name="e">e.</param>
private void MainSpreadSheet_PropertyChangedValue(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell temp = sender as SpreadsheetCell;
if (e.PropertyName == "Value")
{
SpreadsheetCell cell = (SpreadsheetCell)sender;
this.dataGridView1[cell.ColumnIndex, cell.RowIndex].Value = cell.Value;
}
if (e.PropertyName == "BGColor")
{
this.dataGridView1.Rows[temp.RowIndex].Cells[temp.ColumnIndex].Style.BackColor = Color.FromArgb((int)temp.BGColor);
}
}
/// <summary>
/// method that invokes the property changed event.
/// </summary>
/// <param name="sender">object being changed.</param>
/// <param name="e">property being changed.</param>
protected void OnCellPropertyChanged(object sender, PropertyChangedEventArgs e)
{
SpreadsheetCell sheetCell = (SpreadsheetCell)sender;
if (e.PropertyName == "Color")
{
this.CellPropertyChanged("Color", new PropertyChangedEventArgs(sheetCell.RowIndex.ToString() + ","
+ sheetCell.ColIndex.ToString() + "," + sheetCell.BGColor));
return;
}
if (e.PropertyName == "Value")
{
this.CellPropertyChanged(this, new PropertyChangedEventArgs(sheetCell.RowIndex.ToString() + ","
+ sheetCell.ColIndex.ToString() + "," + sheetCell.Value));
return;
}
if (sheetCell.Text.Length == 0 || sheetCell.Text[0] != '=')
{
sheetCell.Value = sheetCell.Text;
if (sheetCell.VarDict.Count > 0)
{
foreach (KeyValuePair <string, double> index in sheetCell.VarDict.ToList())
{
sheetCell.UnmatchTreeToCell(this.GetCellText(index.Key));
}
}
this.CellPropertyChanged(sheetCell, new PropertyChangedEventArgs("Text"));
}
else
{
sheetCell.BuildTree(sheetCell.Text.Substring(1));
if (this.CheckReference(sheetCell))
{
foreach (KeyValuePair <string, double> index in sheetCell.VarDict.ToList())
{
sheetCell.MatchTreeToCell(this.GetCellText(index.Key));
}
sheetCell.Value = sheetCell.EvaluateExpression();
}
this.CellPropertyChanged(this, new PropertyChangedEventArgs(sheetCell.RowIndex.ToString() + ","
+ sheetCell.ColIndex.ToString() + "," + sheetCell.Value));
}
}
public void Clearsheet()
{
int row = cells.GetLength(0);
int column = cells.GetLength(1);
cells = new SpreadsheetCell[row, column];
for (int i = 0; i < row; ++i)
{
for (int j = 0; j < column; ++j)
{
cells[i, j] = new SpreadsheetCell(i, j);
//subscribe so that the spreadsheet class knows when a cell value is changed
cells[i, j].PropertyChanged += Cells_PropertyChanged;
}
}
}
/// <summary>
/// Called when a Cell's Property Changes to Update Spreadsheet
/// </summary>
/// <param name="sender">Cell whose property changed</param>
/// <param name="e">Type of property change</param>
private void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
{
if (e.PropertyName == "CellText")
{
// Clear Dictionary of Dependencies
RemoveCellDependency(sender as SpreadsheetCell);
// Build Expression Tree and Evaluate Cell Value
EvaluateSpreadsheetCell(sender as SpreadsheetCell);
}
else if (e.PropertyName == "BGColor")
{
// Tell UI to Update Cell Color
SpreadsheetCell cellChanged = sender as SpreadsheetCell;
CellPropertyChanged(cellChanged, new PropertyChangedEventArgs("BGColorChanged"));
}
}
//returns a list of the used cells
public List <Cell> GetUsedCells()
{
List <Cell> returnedCells = new List <Cell>();
for (int i = 0; i < columnCount; i++)
{
for (int j = 0; j < rowCount; j++)
{
SpreadsheetCell tempCell = GetCell(i, j) as SpreadsheetCell;
if (tempCell.Value != null)
{
returnedCells.Add(tempCell); //copies the cell at that position in the array.
}
}
}
return(returnedCells);
}
/// <summary>
/// Adds a cell to a list of cells that depend on another cell
/// </summary>
/// <param name="listenerCell">New cell to add</param>
/// <param name="variables">List of existing dependencies</param>
private void AddCellDependency(SpreadsheetCell listenerCell, string[] variables)
{
foreach (string variable in variables)
{
SpreadsheetCell variableCell = this.GetCell(variable);
if (variableCell != null)
{
if (!this.dependentCells.ContainsKey(variableCell))
{
this.dependentCells[variableCell] = new HashSet <SpreadsheetCell>();
}
this.dependentCells[variableCell].Add(listenerCell);
}
}
}
// push change to stack
public void AddRedo(object sender, string change)
{
Cell cell = sender as SpreadsheetCell;
// copy column and row from sender cell
int column = cell.ColumnIndex;
int row = cell.RowIndex;
Cell temp = new SpreadsheetCell(column, row); // create new cell to be used in tuple
// copy property values from sender cell to tuple cell
temp.Color = cell.Color;
temp.Text = cell.Text;
Tuple <Cell, string> tuple = new Tuple <Cell, string>(temp, change); // create new tuple
redoChangeList.Add(tuple); // push tuple to redo change list
OnAddRedo(sender, change); // fire event that notifies UI of change
}
public event PropertyChangedEventHandler CellPropertyChanged; //event field
//Constructor -- initializes sheet and bound variables, creates cells, and subscribes to events
public SpreadsheetClass(int nRows, int nCols)
{
sheet = new SpreadsheetCell[nRows][];//init rows array (1st dimension)
for (int i = 0; i < nRows; i++)
{
sheet[i] = new SpreadsheetCell[nCols];//init cols array (2nd dimension)
}
for (int i = 0; i < nRows; i++)
{
for (int j = 0; j < nCols; j++) //initialize cells & locations
{
SpreadsheetCell nCell = SpreadsheetCell.CreateCell(i, j); //make each cell
sheet[i][j] = nCell; //assign to correct place
nCell.PropertyChanged += OnCellPropertyChanged; //subscribe to events
}
}
columnCount = nCols;
rowCount = nRows;//set both bounds
}
/// <summary>
/// Will trigger when a cell property has changed.
/// </summary>
/// <param name="sender"> SpreadsheetCell. </param>
/// <param name="e"> arguments. </param>
public void CellPropertyChanged(object sender, EventArgs e)
{
if (sender.GetType() == typeof(SpreadsheetCell))
{
SpreadsheetCell cell = sender as SpreadsheetCell;
if (this.Variables.ContainsKey(cell.Index))
{
if (double.TryParse(cell.Value, out double val))
{
this.Variables[cell.Index] = val;
}
else
{
this.Variables[cell.Index] = 0;
}
this.ExpressionCell.Value = this.Evaluate().ToString();
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="Spreadsheet"/> class.
/// </summary>
/// <param name="rows">The number of rows of cells to add to the spreadsheet.</param>
/// <param name="columns">The number of columns of cells to add to the spreadsheet.</param>
public Spreadsheet(int rows, int columns)
{
SpreadsheetCell[,] cells = new SpreadsheetCell[rows, columns];
// Initialize each cell within the array.
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
// Create new cell, subscribe to property changed event, add to 2D array.
SpreadsheetCell cell = new SpreadsheetCell(i, j);
cell.PropertyChanged += this.Sheet_PropertyChanged;
cells[i, j] = cell;
}
}
// Update member variables.
this.Cells = cells;
this.rowCount = rows;
this.columnCount = columns;
}
public Spreadsheet(int columns, int rows, List <Cell> cellList)
{
columnCount = columns;
rowCount = rows;
cells = new SpreadsheetCell[columns, rows]; //allocating memory
for (int i = 0; i < columns; i++)
{
for (int j = 0; j < rows; j++)
{
cells[i, j] = new SpreadsheetCell(i, j); //creates a cell with its position in the array.
cells[i, j].PropertyChanged += Spreadsheet_PropertyChanged; //tells the cell to call Spreadsheet_propertyChanged when PropertyChanged is called.
}
}
foreach (Cell cell in cellList)
{
cells[cell.ColumnIndex, cell.RowIndex] = cell as SpreadsheetCell;
cells[cell.ColumnIndex, cell.RowIndex].PropertyChanged += Spreadsheet_PropertyChanged; //tells the cell to call Spreadsheet_propertyChanged when PropertyChanged is called.
}
}
/// <summary>
/// this function loads the xml file.
/// </summary>
/// <param name="stream"> file name.</param>
public void Load(Stream stream)
{
Cell temporyCell = new SpreadsheetCell(-1, -1);
// Create a file to read
XmlReader file = XmlReader.Create(stream);
// Clear all cells before loading
foreach (Cell cell in this.ArrayOfCells)
{
cell.BGColor = 0xFFFFFFFF;
cell.Text = string.Empty;
}
// Read through the file
while (!file.EOF)
{
if (file.NodeType == XmlNodeType.Element && file.Name == "cell")
{
temporyCell = this.ArrayOfCells[int.Parse(file.GetAttribute("row")), int.Parse(file.GetAttribute("column"))];
file.Read();
}
else if (file.NodeType == XmlNodeType.Element && file.Name == "BGColor")
{
temporyCell.BGColor = uint.Parse(file.ReadElementContentAsString());
}
else if (file.NodeType == XmlNodeType.Element && file.Name == "Text")
{
temporyCell.Text = file.ReadElementContentAsString();
}
else
{
file.Read();
}
}
// Close the file
file.Close();
}
private Cell undoRedoCell; // keep track of the cell currently being worked on in the undo/redo process to avoid overlap
// constructor
public Spreadsheet(int x, int y)
{
sheet = new SpreadsheetCell[x, y]; // create new spreadsheet with dimensions x, y
undoStack = new List <List <Cell> >();
undoChangeList = new List <Tuple <Cell, string> >();
redoStack = new List <List <Cell> >();
redoChangeList = new List <Tuple <Cell, string> >();
// fill in spreadsheet with cell objects
for (int i = 0; i < x; i++)
{
for (int j = 0; j < y; j++)
{
sheet[i, j] = new SpreadsheetCell(i, j); // create new cell
sheet[i, j].TextChanged += CellTextChanged; // subscribe to this cell's text event notification
sheet[i, j].ValueChanged += CellValueChanged; // subscribe to this cell's value event notification
sheet[i, j].ColorChanged += CellColorChanged; // subscribe to this cell's color event notification
sheet[i, j].Undo += AddUndo; // subscribe to this cell's undo event notification
}
}
columnCount = x;
rowCount = y;
}
/// <summary>
/// Evaluates Cell's Value based on Entered Text
/// </summary>
/// <param name="cell"></param>
private void EvaluateSpreadsheetCell(SpreadsheetCell cell)
{
if (cell is SpreadsheetCell currentCell && currentCell != null)
{
// Empty Cell
if (string.IsNullOrEmpty(currentCell.CellText))
{
currentCell.CellValue = string.Empty;
this.OnPropertyChanged(cell, "CellChanged");
}
// Non-Formula
else if (currentCell.CellText[0] != '=')
{
currentCell.CellValue = currentCell.CellText;
this.OnPropertyChanged(cell, "CellChanged");
}
// Formula
else
{
// Build the Expression Tree Based on Cell Text
SpreadsheetEngine.ExpressionTree expressionTree = new SpreadsheetEngine.ExpressionTree(currentCell.CellText.Substring(1));
// Get the Variable Names from the Expression Tree
string[] variableNames = expressionTree.GetVariableNames();
foreach (string variable in variableNames)
{
SpreadsheetCell variableCell = this.GetCell(variable);
// Check for Bad / Self References
if (!CheckValidReference(currentCell, variableCell) || CheckSelfReference(currentCell, variableCell))
{
return;
}
// Adjust Variable Values
if (variableCell.CellValue != string.Empty && !variableCell.CellValue.Contains(" "))
{
expressionTree.SetVariable(variable, Convert.ToDouble(variableCell.CellValue));
}
else
{
// Set Default Variable Value to 0.0
expressionTree.SetVariable(variable, 0.0);
}
}
// Mark Cells Dependent on the One Being Changed
AddCellDependency(currentCell, variableNames);
// Check Dependent Cells for Circular References
foreach (string variable in variableNames)
{
SpreadsheetCell variableCell = this.GetCell(variable);
if (CheckCircularReference(variableCell, currentCell))
{
currentCell.CellValue = "!(circular reference)";
this.OnPropertyChanged(currentCell, "CellChanged");
return;
}
}
// If no Errors, Evaluate the Formula and Update the Value
currentCell.CellValue = expressionTree.Evaluate().ToString();
this.OnPropertyChanged(cell, "CellChanged");
}
// Update the Dependent Cells of Cell Being Changed
if (dependentCells.ContainsKey(currentCell))
{
foreach (SpreadsheetCell dependentCell in dependentCells[currentCell])
{
EvaluateSpreadsheetCell(dependentCell);
}
}
}
}