private bool badRef(CellHelper c)
{
if (c.cText == "" || c.cText == null)
{
return(false);
}
else if (c.cText[0] != '=')
{
return(false);
}
ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree
List <string> nameList = tree.GetVariables();
var cellList = new List <CellHelper>();
foreach (string n in nameList)
{
cellList.Add(stringToCell(n));
}
if (cellList.Contains(null)) //This is a check that is implemented in stringToCell by using a try/catch, and if that creates something invalid
{ //It will add a null references to the cellList, which we now check for.
return(true);
}
return(false);
}
private bool selfRef(CellHelper c) //These functions are all pretty similar
{ //first you make sure that there's anything actually in the cell
if (c.cText == "" || c.cText == null)
{
return(false);
}
else if (c.cText[0] != '=') //If there's no "=" then it can't be a circular/bad/self reference
{
return(false);
}
ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree
List <string> nameList = tree.GetVariables(); //All the references in the cell
var cellList = new List <CellHelper>();
foreach (string n in nameList) //This may seem a little unnecessary, but A4: "=B3 * A1 + A4" would need to look at each
{ //cell referenced, not just the first
cellList.Add(stringToCell(n));
}
if (cellList.Contains(c)) //Basically, if c references c
{
return(true);
}
return(false);
}
public Spreadsheet(int rows, int col)
{
cell_array = new CellHelper[rows, col];
RowCount = rows;
ColumnCount = col;
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < col; j++)
{
cell_array[i, j] = new CellHelper(i, j);
cell_array[i, j].PropertyChanged += NotifyCellPropertyChanged;
}
}
}
//INotifyPropertyChanged
private void UpdateCellValue(CellHelper c)
//Will be called when a cell that cell c references is updated, or when a cell itself is updated.
//Will create a new expression tree based on the text, and get the cell values from the spreadsheet.
//This is very similar to what happens when a new expression is added to a cell EXCEPT it doesn't update
//the reference lists because the cell text itself isn't changing, just its value
{
if (c.cText == null || c.cText == "")
{
c.chValue = c.cText;
}
else
{
ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree
List <string> referencedCells = tree.GetVariables(); //This list contains all referenced cells. So "=A1+B2*3" would have ["A1","B2"]
foreach (string c_name in referencedCells)
{
string req_col = c_name.Substring(0, 1); //to get the required column we need the celltext for the first value "=A6" -> "A"
string req_row = c_name.Substring(1); //This will take the rest of the information, there's no length so it could read it "=A15" -> "15
int colInt = Convert.ToChar(req_col) - 65; //gets the index based on the character
int rowInt = Convert.ToInt32(req_row) - 1; //sets the index (and subtracts on so it's (0,49) instead of (1,50), matching the indexes
double cellVal;
if (cell_array[rowInt, colInt].chValue == null || cell_array[rowInt, colInt].chValue == "" ||
cell_array[rowInt, colInt].chValue == "!(bad reference)" || cell_array[rowInt, colInt].chValue == "!(self reference)" ||
cell_array[rowInt, colInt].chValue == "!(circular reference)")
{
cellVal = 0;
}
else
{
cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue);
}
tree.SetVar(c_name, cellVal); //now the tree knows what A2 is
/*(sender as CellHelper).addReference(cell_array[rowInt, colInt]); //We're telling this cell what it references
* cell_array[rowInt, colInt].addReferenceBy((sender as CellHelper)); //The cell we're referencing now knows we're referencing them*/
}
c.chValue = Convert.ToString(tree.Eval());
foreach (CellHelper c2 in c.referencedBy)
{
UpdateCellValue(c2);
}
}
NotifyPropertyChanged(c, new PropertyChangedEventArgs("CellValue"));
}
private bool circularRef(CellHelper c, ISet <CellHelper> hs)
{
if (string.IsNullOrEmpty(c.cText))
{
return(false);
}
if (c.cText[0] != '=')
{
return(false);
}
var tree = new ExpTree(c.cText.Substring(1));
var nameList = tree.GetVariables();
if (hs.Contains(c))
{
return(true);
}
hs.Add(c);
var cellList = new List <CellHelper>();
foreach (var n in nameList)
{
cellList.Add(stringToCell(n));
}
foreach (var ce in cellList)
{
if (string.IsNullOrEmpty(ce?.cText))
{
continue;
}
var new_hs = new HashSet <CellHelper>(hs);
if (circularRef(ce, new_hs))
{
return(true);
}
}
return(false);
}
private bool circularRef(CellHelper c, HashSet <CellHelper> hs)
{
if (c.cText == "" || c.cText == null)
{
return(false);
}
else if (c.cText[0] != '=')
{
return(false);
}
ExpTree tree = new ExpTree(c.cText.Substring(1)); //create an expression tree
List <string> nameList = tree.GetVariables();
if (hs.Contains(c)) //forgot to do this check for a bit which was dumb
{
return(true);
}
hs.Add(c); //forgot this for a while, infinite loop
var cellList = new List <CellHelper>();
foreach (string n in nameList)
{
cellList.Add(stringToCell(n));
}
foreach (CellHelper ce in cellList) //This is the DFS for checking for circular references
{
if (ce != null)
{
if (ce.cText != "" && ce.cText != null)
{
var new_hs = new HashSet <CellHelper>(hs);
//new_hs.Add(ce);
if (circularRef(ce, new_hs)) //if any of them return true, return true
{
return(true);
}
}
}
}
return(false);
}
private void UpdateCellValue(CellHelper c)
{
if (string.IsNullOrEmpty(c.cText))
{
c.chValue = c.cText;
}
else
{
var tree = new ExpTree(c.cText.Substring(1));
var referencedCells = tree.GetVariables();
foreach (var c_name in referencedCells)
{
var req_col = c_name.Substring(0, 1);
var req_row = c_name.Substring(1);
var colInt = Convert.ToChar(req_col) - 65;
var rowInt = Convert.ToInt32(req_row) - 1;
double cellVal;
if (string.IsNullOrEmpty(cell_array[rowInt, colInt].chValue) ||
cell_array[rowInt, colInt].chValue == "!(bad reference)" ||
cell_array[rowInt, colInt].chValue == "!(self reference)" ||
cell_array[rowInt, colInt].chValue == "!(circular reference)")
{
cellVal = 0;
}
else
{
cellVal = Convert.ToDouble(cell_array[rowInt, colInt].chValue);
}
tree.SetVar(c_name, cellVal);
}
c.chValue = Convert.ToString(tree.Eval());
foreach (var c2 in c.referencedBy)
{
UpdateCellValue(c2);
}
}
NotifyPropertyChanged(c, new PropertyChangedEventArgs("CellValue"));
}
private bool badRef(CellHelper c)
{
if (string.IsNullOrEmpty(c.cText))
{
return(false);
}
if (c.cText[0] != '=')
{
return(false);
}
var tree = new ExpTree(c.cText.Substring(1));
var nameList = tree.GetVariables();
var cellList = new List <CellHelper>();
foreach (var n in nameList)
{
cellList.Add(stringToCell(n));
}
return(cellList.Contains(null));
}
public void addReferenceBy(CellHelper c)
{
referencedBy.Add(c);
}
public void removeReferenceBy(CellHelper c)
{
referencedBy.Remove(c);
}
public void addReference(CellHelper c)
{
references.Add(c);
}
public void removeReference(CellHelper c)
{
references.Remove(c);
}
