public void TestGetNamesOfAllNonemptyCells()
{
AbstractSpreadsheet s = buildSheet();
// Remove some content
s.SetContentsOfCell("A1", "");
s.SetContentsOfCell("B1", "");
s.SetContentsOfCell("B2", "");
s.SetContentsOfCell("C1", "");
IEnumerator <string> e = s.GetNamesOfAllNonemptyCells().GetEnumerator();
e.MoveNext();
string val1 = e.Current;
e.MoveNext();
string val2 = e.Current;
e.MoveNext();
string val3 = e.Current;
Assert.IsTrue(val1 == "A3" || val1 == "B3" || val1 == "C2");
Assert.IsTrue(val2 == "A3" || val2 == "B3" || val2 == "C2");
Assert.IsTrue(val3 == "A3" || val3 == "B3" || val3 == "C2");
}
public void TestSetContentsOfCellOverrideStr()
{
AbstractSpreadsheet s = buildSheet();
s.SetContentsOfCell("A1", "hi"); // Set formula to string
s.SetContentsOfCell("B1", "hello"); // Set double to string
s.SetContentsOfCell("A3", "test"); // Set string to string
Assert.AreEqual("hi", s.GetCellContents("A1"));
Assert.AreEqual("hello", s.GetCellContents("B1"));
Assert.AreEqual("test", s.GetCellContents("A3"));
}
public void TestSetContentsOfCellOverrideFormula()
{
AbstractSpreadsheet s = buildSheet();
s.SetContentsOfCell("A1", "=B1"); // Set formula to formula
s.SetContentsOfCell("B1", "=C3"); // Set double to formula
s.SetContentsOfCell("A3", "=C2"); // Set string to formula
Assert.AreEqual(s.GetCellContents("A1").GetType(), typeof(Formula));
Assert.AreEqual(s.GetCellContents("B1").GetType(), typeof(Formula));
Assert.AreEqual(s.GetCellContents("A3").GetType(), typeof(Formula));
}
public void TestSetContentsOfCellOverrideDbl()
{
AbstractSpreadsheet s = buildSheet();
s.SetContentsOfCell("A1", "5.5"); // Set formula to double
s.SetContentsOfCell("B1", "8.3"); // Set double to double
s.SetContentsOfCell("A3", "5.3"); // Set string to double
Assert.AreEqual(5.5, s.GetCellContents("A1"));
Assert.AreEqual(8.3, s.GetCellContents("B1"));
Assert.AreEqual(5.3, s.GetCellContents("A3"));
}
/// <summary>
/// Button to enter content into cell.
/// </summary>
private void enter_Click(object sender, EventArgs e)
{
int col;
int row;
cells.GetSelection(out col, out row); //Get COL & ROW #
string value = content.Text;
string name = getCellName(col, row); //From col and row # get the right name
try
{
ISet <string> set = ss.SetContentsOfCell(name, value); //Sets the contents of cell
foreach (string s in set) //recalculate each cell that depends on the new cells value
{
getColRow(s, out col, out row);
cells.SetValue(col, row, ss.GetCellValue(s).ToString());
}
cellValue.Text = ss.GetCellValue(name).ToString(); //Show the value in the cell box
}
//Display error messages\\
catch (FormulaFormatException) { MessageBox.Show("You have made a syntax error in your formula"); }
catch (CircularException) { MessageBox.Show("It appears you have created a circular formula. Please revise your equation and try again"); }
docName_TextChanged(sender, e); //Display on the gui that it has been modified by *
}
//
//Background Worker Events
//
/// <summary>
/// This background worker event sets the contents of a cell in the spreadsheet
/// </summary>
/// <param name="sender">The bg_worker</param>
/// <param name="e">DoWorkEvent</param>
private void SetCell(object sender, DoWorkEventArgs e)
{
int col, row;
grid_widget.GetSelection(out col, out row);
Point coordinate = new Point(col, row);
string cellName = letters[col] + (row + 1);
if (!coordinateMap.ContainsKey(coordinate))
{
coordinateMap.Add(coordinate, cellName);
}
try
{
// Stores the list of dependencies to be accessed to recalculate dependent cells
dependencies = spreadsheet.SetContentsOfCell(coordinateMap[coordinate], input);
e.Result = spreadsheet.GetCellValue(coordinateMap[coordinate].ToString());
}
catch (FormulaFormatException)
{
MessageBox.Show("Invalid formula format.");
}
catch (CircularException)
{
MessageBox.Show("Circular Dependency found.");
}
}
public void TestGetCellValueFormulaError()
{
AbstractSpreadsheet s = buildSheet();
s.SetContentsOfCell("B1", "apple");
FormulaError val = (FormulaError)s.GetCellValue("A1");
Assert.AreEqual("Lookup of a variable failed", val.Reason); // Lookup of B1 should fail since it does not map to number
}
/// <summary>
/// Handles ContentsChangeEvent.
/// </summary>
private void HandleContentsChange(SpreadsheetPanel panel)
{
panel.GetSelection(out int x, out int y);
string CellName = (((char)('A' + x)).ToString()) + (y + 1).ToString();
ISet <string> dependents = spreadsheet.SetContentsOfCell(CellName, window.CellContents);
window.CellValue = spreadsheet.GetCellValue(CellName).ToString();
foreach (string s in dependents)
{
window.Update(s, spreadsheet.GetCellValue(s).ToString());
}
}
/// <summary>
/// Will add a cell and add its value to the cell value for the
/// spreadsheet panel.
///
/// </summary>
/// <param name="p"></param>
/// <returns></returns>
private void WriteCellContents(SpreadsheetPanel p)
{
string contents = ContentsBox.Text;
int row, col;
p.GetSelection(out col, out row);
string cellName = GetCellName(col, row);
ISet <string> cells;
MethodInvoker setValueTextBox = new MethodInvoker(() =>
{
cellValueTextBox.Text = spreadsheet.GetCellValue(cellName).ToString();
});
try
{
//Method that may throw the exception
cells = spreadsheet.SetContentsOfCell(cellName, contents);
this.Invoke(setValueTextBox);
// Iterates through and updates the SpreadsheetPanel to show the value of all cells that
// may or may not have changed value due to updating this cell. (Will update this selected cell as well)
DisplayCellValues(cells, p);
}
catch (CircularException)
{
MessageBox.Show("There are one or more circular references where a cell refers to its own " +
"cell either directly or indirectly. To fix this, change the references or remove them.",
this.Name, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
catch (FormulaFormatException)
{
MessageBox.Show("An incorrect reference to another cell was found. Check the cell name. " +
"Only the cells available in this spreadsheet can be referenced.",
this.Name, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
/// <summary>
/// Handles when the contents box is changed
/// </summary>
private void HandleContentsBoxChange()
{
try
{
foreach (string s in spreadsheet.SetContentsOfCell(activeCell, window.Contents))
{
setCell(s);
}
setCell(activeCell);
}
catch (Exception e)
{
window.Message = "Cannot change cell contents to " + window.Contents + " because: " + e.Message;
}
RefreshTextBoxes();
}
/// <summary>
/// Changes the display of the cells after the value of a cell is set. Uses similar logic
/// If will simply find the cells that need to be changed and change them.
/// If an exception is thrown when setting the contents of a cell, the function will show a message.
/// </summary>
/// <param name="ss"></param>
private void DisplayPanelOnSet(SpreadsheetPanel ss)
{
string contents = CellContents.Text;
HashSet <string> CellsToChange = new HashSet <string>();
// try getting the cells to change. If there is an error when processing the cell changes,
// it will catch the exceptions and show a message.
try
{
// this is where the exeception should throw. Getting only the cells whose value should change when the content
// of the current cell is set.
CellsToChange = new HashSet <string>(spread.SetContentsOfCell(CellName.Text, contents));
// If the exception doesn't throw, set the CellValue input box to be the value of the currently selected cell.
CellValue.Text = spread.GetCellValue(CellName.Text).ToString();
}
catch (FormulaFormatException E)
{
MessageBox.Show("Invalid Formula");
}
catch (InvalidNameException)
{
MessageBox.Show("Invalid Formula");
}
if (contents == "")
{
CellsToChange.Add(CellName.Text);
}
// Loop through CellsToChange if it exists, and updating the displayed values of the cells.
foreach (string cell in CellsToChange)
{
int cellCol = cell[0];
cellCol -= 65;
string cellRowStr = cell.Substring(1);
int.TryParse(cellRowStr, out int cellRow);
cellRow -= 1;
ss.SetValue(cellCol, cellRow, GetCellValueAsString(cell));
}
}
/// <summary>
/// This method given the row and col updates the Model and catches
/// Circular exception and invalid formula exception.
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
private bool updateModel(int row, int col)
{
try
{
string content = textBox_content.Text;
SSModel.SetContentsOfCell(getCellName(row, col), content);
updateSpreadsheetGUI();
textBox_content.Text = getCellContents(row, col);
textBox_value.Text = SSModel.GetCellValue(getCellName(row, col)).ToString();
spreadsheetPanel1.SetValue(col, row, textBox_value.Text);
cellChanged = false;
return(true);
}
catch (FormulaFormatException e)
{
// SSModel.SetContentsOfCell(getCellName(row, col), "");
// textBox_content.Text = "";
MessageBox.Show(textBox_content.Text.Substring(1) + " is an invalid formula please change it");
return(false);
}
catch (CircularException e)
{
MessageBox.Show("Found a Circular Depenedency");
textBox_content.Text = "";
spreadsheetPanel1.SetValue(col, row, SSModel.GetCellValue(getCellName(row, col)).ToString());
return(true);
}
catch (Exception e)
{
MessageBox.Show("Sorry an Unexpected error occurred " + e.Message);
return(true);
}
}
/// <summary>
/// Helper method that sets the contents of the actual spreadsheet
/// Called when user enters in new content
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
private void assign_content(int col, int row)
{
string given_val = cell_content_textbox.Text;
IEnumerable <string> dependentCells;
string cellName = getCellName(col, row);
try
{
dependentCells = spreadsheet.SetContentsOfCell(cellName, given_val);
update_Dependent_Cells(dependentCells);
}
catch (CircularException)
{
MessageBox.Show("WARNING: Circular dependency present");
}
catch (FormulaFormatException)
{
MessageBox.Show("WARNING: Invalid Format for Formula");
}
//update_all_textboxes();
savedRecently = false;
}
/// <summary>
/// Updates cell content/value when text is entered into the textbox and the enter key is pressed.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void textBox1_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter)
{
// Get rid of ANNOYING DING
e.SuppressKeyPress = true;
// Store the previous contents of cell if later needed.
string OldContents = Spreadsheet.GetCellContents(CurrentCellName).ToString();
// Try to set the contents of cell to the entered text.
try
{
Spreadsheet.SetContentsOfCell(CurrentCellName, CellContentsField.Text);
}
// Supposed to catch argument exception (which is supposed to cause formula error in formula class), something is wrong here sadly :((((((
catch (ArgumentException)
{
MessageBox.Show(null, "Error, error in formula calculation.", "Error");
Spreadsheet.SetContentsOfCell(CurrentCellName, OldContents);
CellContentsField.Text = OldContents;
return;
}
// If an invalid formula is entered, an error message pops up and the cell returns to its original contents.
catch (FormulaFormatException)
{
MessageBox.Show(null, "Error, formula format is invalid.", "Error");
Spreadsheet.SetContentsOfCell(CurrentCellName, OldContents);
CellContentsField.Text = OldContents;
return;
}
// If a circular exception is encountered, an error message is show and the cell returns to its original contents.
catch (CircularException)
{
MessageBox.Show(null, "Error, circular dependcy exists, check your formulas.", "Error");
Spreadsheet.SetContentsOfCell(CurrentCellName, OldContents);
CellContentsField.Text = OldContents;
return;
}
// Get the value of the current cell in the spreadsheet.
object CellType = Spreadsheet.GetCellValue(CurrentCellName);
// If a formula error was returned, an error message is shown and the cell returns to its original contents.
if (CellType is FormulaError)
{
MessageBox.Show(null, "Error, formlua returns FormulaError.", "Error");
Spreadsheet.SetContentsOfCell(CurrentCellName, OldContents);
CellContentsField.Text = OldContents;
return;
}
string CellValue = "";
// If no errors are encountered, store the cell value as a string.
try
{
CellValue = CellType.ToString();
}
catch (NullReferenceException)
{
}
// Update the spreadsheetpanel with the new value.
SpreadSheetPanel.SetValue(CurrentColumn, CurrentRow, CellValue);
// Update corresponding label.
this.CellValueDisplayLabel.Text = CellValue;
Update();
GetSelectionValues(SpreadSheetPanel);
}
}
/// <summary>
/// Caller method to set cells
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
/// <param name="contents"></param>
public List <string> SetCellContents(int col, int row, string contents)
{
return(ss.SetContentsOfCell(DigitToVar(col, row), contents).ToList());
}
private void EnterPressed(SpreadsheetPanel ss)
{
int row, col;
ss.GetSelection(out col, out row);
String name = CellNameField.Text;
ISet <String> altered = null;
ISet <String> circular = null;
Model.Model.Edit(name, ContentField.Text);
try
{
altered = sheet.SetContentsOfCell(name, ContentField.Text);
circular = new HashSet <string>();
HashSet <string> visited = new HashSet <string>();
//CS 3505 changes ////////////////////////////////////
foreach (string cell in sheet.GetDirectDependents(name))
{
if (!visited.Contains(cell))
{
CircularDependencyCheck(cell, cell, visited, circular);
}
circular.Add(cell);
}
foreach (String s in altered)
{
updateCell(s, ss);
}
foreach (string c in circular)
{
sheet.SetCellCircularStatus(c, false);
}
// //end ////////////////////////////////////////////////
displaySelection(ss);
}
catch (FormulaFormatException e)
{
MessageBox.Show("The formula you entered is not valid.");
}
catch (CircularException e)
{
MessageBox.Show("The formula you entered results in a circular exception.");
}
catch (Exception e)
{
MessageBox.Show("Unable to change cell value; " + e.Message);
}
}
// For setting a spreadsheet cell.
public IEnumerable <string> Set(AbstractSpreadsheet sheet, string name, string contents)
{
List <string> result = new List <string>(sheet.SetContentsOfCell(name, contents));
return(result);
}
// Every time the selection changes, this method is called with the
// Spreadsheet as its parameter. display the value of that cell
private void displaySelection(SpreadsheetPanel ss)
{
// if still computing, nothing will happen
if (bg_worker.IsBusy)
{
return;
}
int row, col;
String value;
ss.GetSelection(out col, out row);
ss.GetValue(col, row, out value);
// if we are on a different cell/ have left the previous cell, it will set the contents of the cell
// else, does nothing
if (row != prevRow || col != prevCol)
{
//a string containing the name of the previous cell, the cell whose value is changing
string name = column[prevCol] + (prevRow + 1);
//if there was no change to the value, move to new cell and nothing else
if (cells.GetCellContents(name).ToString() == toolStripTextBox1.Text)
{
// makes it so this cell is now the 'previous' cell
prevRow = row;
prevCol = col;
//overrides the name to be the new cell, so that it's contents can be put into the text box.
name = column[prevCol] + (prevRow + 1);
// set the text box content to what is currently stored in the newly selected cell.
textUpdate(name);
return;
}
// sets the value of that cell to what is currently in the text box
//updates other cells connected to this cell
try
{
// if there is a formula format exception, catch it and create a message.
changed = cells.SetContentsOfCell(name, toolStripTextBox1.Text);
}
catch (FormulaFormatException)
{
MessageBox.Show("Invalid Formula Entered By User: "******"Please Enter Valid Value");
return;
}
bg_worker.RunWorkerAsync();
// displays the value of the changed cell
ss.SetValue(prevCol, prevRow, cells.GetCellValue(name).ToString());
//stores the change
changes.Insert(0, name + " : " + cells.GetCellContents(name).ToString());
changeAmount++;
//if there's more than 10 changes, remove the extra ones
if (changes.Count == 11)
{
changes.RemoveAt(10);
}
// makes it so this cell is now the 'previous' cell
prevRow = row;
prevCol = col;
//overrides the name to be the new cell, so that it's contents can be put into the text box.
name = column[prevCol] + (prevRow + 1);
// set the text box content to what is currently stored in the newly selected cell.
textUpdate(name);
}
}