public Spreadsheet(int row, int col)
{
m_Cells = new SSCell[row, col];
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
m_Cells[i, j] = new SSCell(i, j);
m_Cells[i, j].PropertyChanged += OnCellChange;//if cell changes, OnCellTextChange function will be executed
}
}
m_numRows = row;
m_numCols = col;
}
public Spreadsheet(int row, int col)
{
m_Cells = new SSCell[row, col];
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
m_Cells[i, j] = new SSCell(i, j);
m_Cells[i, j].PropertyChanged += OnCellChange;//if cell changes, OnCellTextChange function will be executed
}
}
m_numRows = row;
m_numCols = col;
}
/// <summary>
/// looks up the value of the given variable if the varible does not
/// have a value the cells value will be set to a formula error
/// </summary>
/// <param name="variable"></param>
/// <returns></returns>
protected double Lookup(string variable)
{
//need to get the value of the variable
if (nameContentDict.ContainsKey(variable))
{
SSCell currentCell = nameContentDict[variable];
//first see if the contents of the cell is a double
if (currentCell.Value is double)
{
return((double)currentCell.Value);
}
}
// if variable is not in the dictionary or cell value is not a double
// throw an exception
// this will be caught in the Evalute method in the Formula class
// and it will return a FormulaError object
throw new ArgumentException();
}
private void cellSaving(SSCell cell)
{
if (cell.Text != "" && cell.Text != null)//cell has text in it
{
this.m_CellsToSave.Add(cell);
}
else if (cell.BGColor != -1)//basic white
{
this.m_CellsToSave.Add(cell);
}
else//check to see if cell is in and remove it because it is worthless
{
//if cell text == "" or null and BGColor is white, remove this cell if it exists
if (this.m_CellsToSave.Contains(cell))
{
this.m_CellsToSave.Remove(cell);
}
}
}
/// <summary>
/// Creates and adds the cell and to the Dictionary using the cells name as its Key. If the dictionary
/// contains the name and the content of that cell is a Formula and is being replaced with a double or string
/// then depedencys need to be readjusted and are.
/// </summary>
/// <param name="name">name if the cell</param>
/// <param name="obj">Contents of the cell</param>
private void AddToGraphAndDict(string name, Object obj)
{
//check to see if cell already exist, if its old content is a Formula and is being rplaced by something
//other than a formula than replace dependees.
if (nameContentDict.ContainsKey(name))
{
// if the old content is a formula remove dependicies (replace with an empty hashset)
// unless the new content is also a formula then do not remove the dependences
if (nameContentDict[name].Content is Formula && !(obj is Formula))//if obj is formula keep depdendcies
{
depenGraph.ReplaceDependees(name, new HashSet <string>());
}
nameContentDict.Remove(name);
}
SSCell textCell = new SSCell(name, obj, Lookup);
nameContentDict.Add(name, textCell);
}
/// <summary>
/// If the formula parameter is null, throws an ArgumentNullException.
///
/// Otherwise, if name is null or invalid, throws an InvalidNameException.
///
/// Otherwise, if changing the contents of the named cell to be the formula would cause a
/// circular dependency, throws a CircularException. (No change is made to the spreadsheet.)
///
/// Otherwise, the contents of the named cell becomes formula. The method returns a
/// Set consisting of name plus the names of all other cells whose value depends,
/// directly or indirectly, on the named cell.
///
/// For example, if name is A1, B1 contains A1*2, and C1 contains B1+A1, the
/// set {A1, B1, C1} is returned.
/// </summary>
protected override ISet <string> SetCellContents(string name, Formula formula)
{
//check if name is valid
IsValidInput(name, formula);
//Save state of old dependency graph
SSCell oldCell = null;
IEnumerable <string> oldDependees = new HashSet <string>();
if (nameContentDict.TryGetValue(name, out oldCell))
{
oldDependees = depenGraph.GetDependees(oldCell.Name);
}
depenGraph.ReplaceDependees(name, formula.GetVariables());
//check for circular dependiencies
try
{
//check for circular dependeinces
ISet <string> returnSet = GetAllDepdendents(name);
// if we get here no circular dependency was found
AddToGraphAndDict(name, formula);
//reevaluate all dependents
foreach (string cellName in returnSet)
{
nameContentDict[cellName].ReEvaluate(Lookup);
}
return(returnSet);
}
catch (CircularException)
{
//restore dependecy graph
depenGraph.ReplaceDependees(name, oldDependees);
throw new CircularException();
}
}
private void cellSaving(SSCell cell)
{
if (cell.Text != "" && cell.Text != null)//cell has text in it
{
this.m_CellsToSave.Add(cell);
}
else if (cell.BGColor != -1)//basic white
{
this.m_CellsToSave.Add(cell);
}
else//check to see if cell is in and remove it because it is worthless
{
//if cell text == "" or null and BGColor is white, remove this cell if it exists
if (this.m_CellsToSave.Contains(cell))
{
this.m_CellsToSave.Remove(cell);
}
}
}
/// <summary>
/// Creates and adds the cell and to the Dictionary using the cells name as its Key. If the dictionary
/// contains the name and the content of that cell is a Formula and is being replaced with a double or string
/// then depedencys need to be readjusted and are.
/// </summary>
/// <param name="name">name if the cell</param>
/// <param name="obj">Contents of the cell</param>
private void AddToGraphAndDict(string name, Object obj)
{
//check to see if cell already exist, if its old content is a Formula and is being rplaced by something
//other than a formula than replace dependees.
if (nameContentDict.ContainsKey(name))
{
// if the old content is a formula remove dependicies (replace with an empty hashset)
// unless the new content is also a formula then do not remove the dependences
if (nameContentDict[name].Content is Formula && !(obj is Formula))//if obj is formula keep depdendcies
{
depenGraph.ReplaceDependees(name, new HashSet<string>());
}
nameContentDict.Remove(name);
}
SSCell textCell = new SSCell(name, obj, Lookup);
nameContentDict.Add(name, textCell);
}
//when a cell changes in this spreadsheet, this will happen
public void OnCellChange(object sender, PropertyChangedEventArgs e)
{
SSCell cell = sender as SSCell;
string text = cell.Text;
//add this cell to cells to be saved
if (cell != null)
{
this.cellSaving(cell);
}
if (e.PropertyName == "Text")
{
//tell all providers of this cell that you no longer need them since we are starting over
foreach (string provider in cell.providers)
{
//name of sender cell that just changed
string cell_name = "" + (char)(cell.columnIndex + 'A') + (cell.rowIndex + 1).ToString(); //ex. G20
int col = provider[0] - 'A'; //col index of variable (provider_cell) value
int row = Convert.ToInt32(provider.Substring(1)) - 1; //row index of variable (provider_cell) value
SSCell provider_cell = getCell(row, col) as SSCell;
//if provider_cell has this cell as a dependent
if (provider_cell != null && provider_cell.dependents.Contains(cell_name))
{
//tell this provider_cell that this cell doesn't depend on it anymore
provider_cell.dependents.Remove(cell_name);
}
}
if (text.Length < 1)
{
//empty cell
cell.expr = new Expression("");//reset expression;
cell.setValue(cell.Text);
if (PropertyChanged != null) //check if anyone is subscribed
{
PropertyChanged(sender, new PropertyChangedEventArgs("Text")); //spreadsheet should be notified of this change
}
}
else if (text[0] != '=')
{
cell.expr = new Expression("");//reset expression;
cell.setValue(cell.Text);
if (PropertyChanged != null) //check if anyone is subscribed
{
PropertyChanged(sender, new PropertyChangedEventArgs("Text")); //spreadsheet should be notified of this change
}
}
else //deal with equation
{
bool fail = false;
//adjust the cell's expression
try
{
cell.expr = new Expression(text.Substring(1));
}
catch
{
cell.expr = new Expression("");
cell.setValue("INVALID EQ");
fail = true;
}
bool bad_cell_ref = false;
//find variables in the table & define all variables that need to be defined
foreach (string key in cell.expr.m_keys)
{
if (key.Length < 2)
{
cell.setValue("!(bad cell ref)");
bad_cell_ref = true;
continue;
}//invalid variable, keep set at 0;
int col = key[0] - 'A';//col index of variable (provider_cell) value
int row = -1;
try //check if
{
row = Convert.ToInt32(key.Substring(1)) - 1; //row index of variable (provider_cell) value
}
catch
{
//this will make the provider_cell null and we will skip this variable
row = -1;
}
//provider_cell will provide (this)cell with its value
SSCell provider_cell = getCell(row, col) as SSCell;
//if this cell doesn't exist. set value to 0 & check next variable in the expression
if (null == provider_cell)
{
bad_cell_ref = true;
continue;
}
//name of cell whose text just changed
string cell_name = "" + (char)(cell.columnIndex + 'A') + (cell.rowIndex + 1).ToString();//ex. "B2"
//name of provider_cell
string provider_cell_name = "" + (char)(provider_cell.columnIndex + 'A') + (provider_cell.rowIndex + 1).ToString();
//provider cell keeps track of the cells that depend on it
provider_cell.dependents.Add(cell_name);
//cell keeps track of the cells that provide for it
cell.providers.Add(provider_cell_name);
double valx = 0;
try
{
//convert value string to double and define in dict
valx = Convert.ToDouble(provider_cell.Value);
}
catch
{
//if cell's value cannot be converted into a double
valx = 0;
}
cell.expr.define(key, valx);//define variable in the expression classes dictionary which lives in the Cell class
}
if (!fail)
{
//this is the value
if (bad_cell_ref)
{
cell.setValue("!(bad cell ref)");
}
else
{
string val = (cell.expr.evalTree()).ToString();
//set value of the cell
cell.setValue(val);//this will trigger a value propertychange
}
}
if (PropertyChanged != null) //if someone subscribed
{
PropertyChanged(sender, new PropertyChangedEventArgs("Text")); //tell the world(UI spread sheet) that this cell's text just changed
}
}
}
else if (e.PropertyName == "Value")
{
string cell_name = "" + (char)(cell.columnIndex + 'A') + (cell.rowIndex + 1).ToString();
//if you directly depend on yourself
if (cell.dependents.Contains(cell_name))
{
//remove from itself from the dependents
cell.dependents.Remove(cell_name);
//reset cell name and we will come back to this function without the self ref
cell.setValue("!(self reference)");
}
else if (hasCircularRef(cell, cell_name))
{
cell.setValue("!(circular reference)");
//foreach (string dependent in cell.dependents)
//{
// int col = dependent[0] - 'A';//col of dependent cell
// int row = Convert.ToInt32(dependent.Substring(1)) - 1;//row of dependent cell
// SSCell dependent_cell = getCell(row, col) as SSCell;
// //redefine the variable in the dependent cell's dictionary
// dependent_cell.expr.define(cell_name, 0);
// //reset value
// //if (!hasCircularRef(dependent_cell, dependent))
// //{
// dependent_cell.setValueNoTrigger("circular reference");
// //}
// //if someone is subscribed tell them about this change (spreadsheet is subscribed to this event)
// if (PropertyChanged != null)
// {
// //tell the world(spread sheet) that the dependent cell needs to be updated
// PropertyChanged(dependent_cell, new PropertyChangedEventArgs("Value"));
// //notice we pass in the dependent cell and not "sender"
// }
//}
//on value change: tell all dependent cells about the change
}
else
{
//on value change: tell all dependent cells about the change
foreach (string dependent in cell.dependents)//notify all that depend on you
{
//the name of the cell that is notifying its dependents of the change
//string cell_name = "" + (char)(cell.columnIndex + 'A') + (cell.rowIndex + 1).ToString();
int col = dependent[0] - 'A'; //col of dependent cell
int row = Convert.ToInt32(dependent.Substring(1)) - 1; //row of dependent cell
double new_value = 0;
//try-catch: check to see if cell's val can be converted into a double
try
{
new_value = Convert.ToDouble(cell.Value);
}
catch
{
//if incompatible with doubles, set to 0
new_value = 0;
}
//this is the dependent cell
SSCell dependent_cell = getCell(row, col) as SSCell;
//redefine the variable in the dependent cell's dictionary
dependent_cell.expr.define(cell_name, new_value);
//reset value
dependent_cell.setValue(dependent_cell.expr.evalTree().ToString());
//if someone is subscribed tell them about this change (spreadsheet is subscribed to this event)
if (PropertyChanged != null)
{
//tell the world(spread sheet) that the DEPENDENT cell needs to be updated
PropertyChanged(dependent_cell, new PropertyChangedEventArgs("Value"));
//notice we pass in the dependent cell and not "sender"
}
}
}
}
else if (e.PropertyName == "BGColor")
{
//spreadsheet has been notified, but now we need spreadsheet to tell the things that subscribe to it what has just happened
//the spreadsheet can't just keep the color change a secret to itself
if (PropertyChanged != null)
{
PropertyChanged(sender, new PropertyChangedEventArgs("BGColor"));
}
}
}
