//Use ExpressionEngine to evaluate cell's text value and set its Value
public void EvaluateCell(Cell cell)
{
//typecast cell into Instance cell so we can change its value
CellInstance instance = cell as CellInstance;
if (string.IsNullOrEmpty(cell.Text))
{
//just make value the empty string
instance.SetValue("");
//fire off the property changed event for "Value"
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
//if it is an equation and not just equals sign evaluate it
else if (cell.Text[0] == '=' && cell.Text.Length > 1)
{
bool error = false;
//create instance of expression
Expression exp = new Expression();
//set string expression
exp.stringExpression = cell.Text.Substring(1);
//get variables so we can set them in the spreadsheet dictionary
List<string> expressionVariables = exp.getVariables();
//go through each variable and add it to the spreadsheet variables
foreach (string variable in expressionVariables)
{
//check for errors
//check if self ref
if(variable == cell.Name)
{
instance.SetValue("!(self reference)");
error = true;
break;
}
//check if cell exists
if(GetCell(variable) == null)
{
instance.SetValue("!(bad reference)");
error = true;
break;
}
//check if circular ref
if(IsCircularReference(variable, cell.Name))
{
//just set current cell to show error message (design choice)
instance.SetValue("!(circular reference)");
error = true;
break;
}
Cell variableCell = GetCell(variable);
//variable to store value if one exists
double variableValue;
//if cell value is null set to zero in expression
if (string.IsNullOrEmpty(variableCell.Value))
{
exp.setVariable(variable, 0);
}
else if (!double.TryParse(variableCell.Value, out variableValue))
{
//this cell isnt a value
exp.setVariable(variable, 0);
}
else
{
//is a value so set it normal
exp.setVariable(variable, variableValue);
}
}
if (error)
{
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
return;
}
//now variables are set
//evaluate expression and set the cells value to the result
instance.SetValue(exp.Evaluate().ToString());
//fire off the cell changed event for cell value
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
else
{
// this means its not an expression so just set it to the value of the text
instance.SetValue(cell.Text);
CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
}
//now need to evaluate all cells that this cell references (recursively)
if (_references.ContainsKey(instance.Name))
{
foreach (string cellName in _references[instance.Name])
{
EvaluateCell(GetCell(cellName));
}
}
}
private bool hasCircularRef(Cell cell, string cell_name)
{
List<bool> falses = new List<bool>();
if (cell.dependents.Count == 0)
{
return false;
}
else
{
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
if (dependent != cell_name)
{
falses.Add(hasCircularRef(this.getCell(row, col), cell_name));
}
else//the original cell depends on this cell
{
//SSCell dependent_cell = this.getCell(row, col) as SSCell;
//.setValueNoTrigger("circ ref");
//cell.dependents.Remove(cell_name);
return true;
}
}
foreach (bool f in falses)
{
if(f == true)
{
return true;
}
}
return false;
}
}
protected void OnCellPropertyChanged(Cell c, string text)
{
PropertyChangedEventHandler handler = CellPropertyChanged;
if (handler != null)
{
handler(c, new PropertyChangedEventArgs(text));
}
}
public UndoRedoCollection(string Input, Cell InputCell, int OldColor)
{
//copy data to our native cell
m_cell = InputCell;
//copy data to native variables
m_text = Input;
OrigColor = OldColor;
}
public UndoRedoCollection(string Input, Cell InputCell, string OldData)
{
//copy data to our native cell
m_cell = InputCell;
//copy data to our native variables
m_text = Input;
OrigText = OldData;
}
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 Stack<CmdCollection> RedoStack; // Will store all redo commands
public Spreadsheet(int numRows, int numColumns, int DefaultCellColor)
{
cellArr = new Cell[numRows, numColumns];
width = numColumns;
height = numRows;
for (int i = 0; i < numRows; i++) // Initializes Rows
{
for (int j = 0; j < numColumns; j++) // Initializes Columns
{
cellArr[i, j] = new Cell(i, j, DefaultCellColor); // Creates a new cell with it specific row and column indicies
cellArr[i, j].PropertyChanged += detect_PropertyChanged; // Subscribes the detect_PropertyChanged to each cell contained
}
}
UndoStack = new Stack<CmdCollection>();
RedoStack = new Stack<CmdCollection>();
StartCellColor = DefaultCellColor; // Tells the cells which color to initially be
}