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