/// <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 *
}
/// <summary>
/// Gets cell contents and value from the selected cell and displays them on their corresponding GUI elements.
/// </summary>
/// <param name="panel"></param>
private void GetSelectionValues(SpreadsheetPanel panel)
{
// Get row and column values from the GUI and convert them to correspond with the spreadsheet.
panel.GetSelection(out CurrentColumn, out CurrentRow);
CurrentCellName = GetCellName(CurrentColumn, CurrentRow);
// Update cell name on GUI
CellNameDisplayLabel.Text = CurrentCellName;
// Update cell content on GUI
CellContentsField.AcceptsReturn = true;
// Get contents of selected cell.
object CellContents = Spreadsheet.GetCellContents(CurrentCellName);
// If cell is empty, display empty cell.
if (CellContents.ToString() == "")
{
CellValueDisplayLabel.Text = "";
CellContentsField.Text = "";
}
// If formula, add an = to beginning of content string.
else if (CellContents is Formula)
{
CellContentsField.Text = "=" + Spreadsheet.GetCellContents(CurrentCellName).ToString();
// Update cell value on GUI
CellValueDisplayLabel.Text = Spreadsheet.GetCellValue(CurrentCellName).ToString();
}
else
{
CellContentsField.Text = Spreadsheet.GetCellContents(CurrentCellName).ToString();
// Update cell value on GUI
CellValueDisplayLabel.Text = Spreadsheet.GetCellValue(CurrentCellName).ToString();
}
}
/// <summary>
/// Controller constructor for opening file.
/// </summary>
public Controller(ISpreadsheetGUI window, string filename)
{
this.window = window;
TextReader reader = new StreamReader(filename);
spreadsheet = new Spreadsheet(reader, new Regex(@"^[a-zA-Z]+[1-9][0-9]*$"));
reader.Close();
save = filename;
foreach (string s in spreadsheet.GetNamesOfAllNonemptyCells())
{
window.Update(s, spreadsheet.GetCellValue(s).ToString());
}
string CellName = (((char)('A')).ToString()) + (1).ToString();
window.CellName = CellName;
window.CellValue = spreadsheet.GetCellValue(CellName).ToString();
window.CellContents = spreadsheet.GetCellContents(CellName).ToString();
window.NewEvent += HandleNew;
window.CloseEvent += HandleClose;
window.KeyDownEvent += HandleKeyDown;
window.SelectionChangeEvent += HandleSelectionChange;
window.ContentsChangeEvent += HandleContentsChange;
window.SaveCheckEvent += HandleSaveCheck;
window.OpenEvent += HandleOpen;
window.SaveEvent += HandleSave;
window.SavingEvent += HandleSaving;
}
/// <summary>
/// Refreshes what is in the text boxes
/// </summary>
private void RefreshTextBoxes()
{
window.CellName = activeCell;
window.Value = spreadsheet.GetCellValue(activeCell).ToString();
if (spreadsheet.GetCellContents(activeCell).GetType() == typeof(Formulas.Formula))
{
window.Contents = "=" + spreadsheet.GetCellContents(activeCell).ToString();
}
else
{
window.Contents = spreadsheet.GetCellContents(activeCell).ToString();
}
}
public void Formula1(AbstractSpreadsheet ss)
{
Set(ss, "a1", "= a2 + a3");
Set(ss, "a2", "= b1 + b2");
Assert.IsInstanceOfType(ss.GetCellValue("a1"), typeof(FormulaError));
Assert.IsInstanceOfType(ss.GetCellValue("a2"), typeof(FormulaError));
Set(ss, "a3", "5.0");
Set(ss, "b1", "2.0");
Set(ss, "b2", "3.0");
VV(ss, "a1", 10.0, "a2", 5.0);
Set(ss, "b2", "4.0");
VV(ss, "a1", 11.0, "a2", 6.0);
}
public void Formula1(AbstractSpreadsheet ss)
{
Set(ss, "A1", "= A2 + A3");
Set(ss, "A2", "= B1 + B2");
Assert.IsInstanceOfType(ss.GetCellValue("A1"), typeof(FormulaError));
Assert.IsInstanceOfType(ss.GetCellValue("A2"), typeof(FormulaError));
Set(ss, "A3", "5.0");
Set(ss, "B1", "2.0");
Set(ss, "B2", "3.0");
VV(ss, "A1", 10.0, "A2", 5.0);
Set(ss, "B2", "4.0");
VV(ss, "A1", 11.0, "A2", 6.0);
}
//
//You can use the following additional attributes as you write your tests:
//
//Use ClassInitialize to run code before running the first test in the class
//[ClassInitialize()]
//public static void MyClassInitialize(TestContext testContext)
//{
//}
//
//Use ClassCleanup to run code after all tests in a class have run
//[ClassCleanup()]
//public static void MyClassCleanup()
//{
//}
//
//Use TestInitialize to run code before running each test
//[TestInitialize()]
//public void MyTestInitialize()
//{
//}
//
//Use TestCleanup to run code after each test has run
//[TestCleanup()]
//public void MyTestCleanup()
//{
//}
//
#endregion
// Verifies cells and their values, which must alternate.
public void VV(AbstractSpreadsheet sheet, params object[] constraints)
{
for (int i = 0; i < constraints.Length; i += 2)
{
if (constraints[i + 1] is double)
{
Assert.AreEqual((double)constraints[i + 1], (double)sheet.GetCellValue((string)constraints[i]), 1e-9);
}
else
{
Assert.AreEqual(constraints[i + 1], sheet.GetCellValue((string)constraints[i]));
}
}
}
/// <summary>
/// Handles SelectionChangeEvent.
/// </summary>
private void HandleSelectionChange(SpreadsheetPanel panel)
{
panel.GetSelection(out int x, out int y);
string CellName = (((char)('A' + x)).ToString()) + (y + 1).ToString();
window.CellName = CellName;
window.CellValue = spreadsheet.GetCellValue(CellName).ToString();
string equals = "";
if (spreadsheet.GetCellContents(CellName).GetType() == typeof(Formula))
{
equals = "=";
}
window.CellContents = equals + spreadsheet.GetCellContents(CellName).ToString();
}
/// <summary>
/// helper method that redraws the spread sheet panel
/// </summary>
private void updateSpreadsheetGUI()
{
//save the current cell because method below might change these
int saveRow = row;
int saveCol = col;
foreach (string cell in SSModel.GetNamesOfAllNonemptyCells())
{
setRowAndCol(cell);
spreadsheetPanel1.SetValue(col, row, SSModel.GetCellValue(cell).ToString());
}
//done updating spreading sheet set row and col to
//intial values
row = saveRow;
col = saveCol;
}
public void DivisionByZero1(AbstractSpreadsheet ss)
{
Set(ss, "A1", "4.1");
Set(ss, "B1", "0.0");
Set(ss, "C1", "= A1 / B1");
Assert.IsInstanceOfType(ss.GetCellValue("C1"), typeof(FormulaError));
}
public void StringArgument(AbstractSpreadsheet ss)
{
Set(ss, "A1", "4.1");
Set(ss, "B1", "hello");
Set(ss, "C1", "= A1 + B1");
Assert.IsInstanceOfType(ss.GetCellValue("C1"), typeof(FormulaError));
}
public void EmptyArgument(AbstractSpreadsheet ss)
{
Set(ss, "A1", "4.1");
Set(ss, "C1", "= A1 + B1");
object cellVal = ss.GetCellValue("C1");
Assert.IsInstanceOfType(cellVal, typeof(FormulaError));
}
/// <summary>
/// Gets the cell value
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
/// <returns></returns>
public object GetCellValue(int col, int row)
{
FormulaError fe = new FormulaError();
//Check if the cell value is a formula error
if (ss.GetCellValue(DigitToVar(col, row)) is FormulaError)
{
//We return the Formula error in order to access the reason
fe = (FormulaError)ss.GetCellValue(DigitToVar(col, row));
return(fe);
}
//else we return the data
else
{
return(ss.GetCellValue(DigitToVar(col, row)));
}
}
/// <summary>
/// Returns the input cell value
/// </summary>
/// <param name="name"></param>
private void HandleGetCellValue(string name)
{
object value = model.GetCellValue(name);
string valueString = valueToString(value)[0];
view.FormulaEditBox = valueString;
}
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>
/// Event method called when a cell is clicked on the spreadsheet panel object
/// </summary>
/// <param name="p"></param>
private void onCellClicked(SpreadsheetPanel p)
{
int row, col;
p.GetSelection(out col, out row);
string cellName = GetCellName(col, row);
object cellContents = spreadsheet.GetCellContents(cellName);
if (cellContents is Formula)
{
cellContents = "=" + cellContents;
}
ContentsBox.Text = cellContents.ToString();
cellValueTextBox.Text = spreadsheet.GetCellValue(cellName).ToString();
ContentsBox.Focus();
}
//Open file
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
//makes the spreadsheet have the values of the saved file.
//have to figure out how to let the user choose a file.
// Referenced MSDN library for Filter syntax
openFileDialog1.Filter = "Spreadsheet Files (*.sprd)|*.sprd|All Files (*.*)|*.*";
openFileDialog1.DefaultExt = ".sprd";
openFileDialog1.FileName = "";
openFileDialog1.Title = "Open";
openFileDialog1.ShowDialog();
// get the name of the selected file
string nameOfFile = openFileDialog1.FileName;
//if the filename is empty, do nothing
if (nameOfFile == "")
{
return;
}
// if i can't open that file type, explain why
if (openFileDialog1.DefaultExt != "sprd")
{
MessageBox.Show("Unable to open file of Type " + openFileDialog1.DefaultExt);
return;
}
//creates error message if trying to leave when there's a change.
if (cells.Changed)
{
DialogResult result = MessageBox.Show("Your spreadsheet has unsaved changed. Opening " +
"this file will overwrite any changes that have not been saved. Open anyways?",
"Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
// if 'No' then we don't want to open the file so return
if (result == DialogResult.No)
{
return;
}
}
spreadsheetPanel1.Clear();
cells = new Spreadsheet(nameOfFile, cells.IsValid, cells.Normalize, cells.Version);
// updates the visual information/graphics to display the cell info.
foreach (string name in cells.GetNamesOfAllNonemptyCells())
{
// gets the column and row of the cell
int col = revColumn[name.Substring(0, 1)];
int row = Int32.Parse(name.Substring(1)) - 1;
// sets the value of the cell
spreadsheetPanel1.SetValue(col, row, cells.GetCellValue(name).ToString());
}
//sets the textbox value as the selected cell
string name2 = column[prevCol] + (prevRow + 1);
toolStripTextBox1.Text = cells.GetCellContents(name2).ToString();
}
/// <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));
}
}
//
//Events
//
/// <summary>
/// Given a spreadsheet, find the current selected cell and
/// create a popup that contains the information from that cell
/// </summary>
/// <param name="ss"></param>
private void DisplaySelection(SpreadsheetGridWidget ss)
{
int row, col;
ss.GetSelection(out col, out row);
string name = letters[col] + (row + 1); //Row+1 is necessary since the grid starts at (0,0), but the cell names start at A1
//If the user clicks on a cell, add that cell to the Dictionary that maps the grid (x,y) to its proper "letter/number" name.
Point coordinate = new Point(col, row);
if (!coordinateMap.ContainsKey(coordinate))
{
coordinateMap.Add(coordinate, name);
}
// Cell Name and Cell Value are displayed in respective text boxes
cellName_Textbox.Text = coordinateMap[coordinate];
cellValue_Textbox.Text = spreadsheet.GetCellValue(name).ToString();
//Cell contents are displayed in the textbox when the user selects the cell.
input_Textbox.Text = spreadsheet.GetCellContents(name).ToString();
}
/// <summary>
/// Opens a previously saved document
/// </summary>
private void open_Click(object sender, EventArgs e)
{
int num = 1;
if (ss.Changed) //check if current document has been modified
{
saveCheck(out num);
}
if (num == 0)
{
save_Click(sender, e); num = 1;
} //user wishes to save
if (num == 1) //open the document
{
try{
openFileDialog1.Filter = "Spreadsheet Files (.ss)|*.ss|All Files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;
openFileDialog1.FileName = "";
DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
if (result == DialogResult.OK) // Test result.
{
string file = openFileDialog1.FileName;
using (TextReader open = new StreamReader(file))
{
ss = new Spreadsheet(open);
cells.Clear();
int col, row;
foreach (string s in ss.GetNamesOfAllNonemptyCells()) //Calculate all the cell values
{
getColRow(s, out col, out row);
cells.SetValue(col, row, ss.GetCellValue(s).ToString());
}
docName.Text = System.IO.Path.GetFileNameWithoutExtension(file); //set document name to opened file name
ssName = file;
savedas = true;
}
docName_TextChanged(sender, e);
}
SelectionChanged(cells);
}
//Error Message\\
catch (SpreadsheetReadException) { MessageBox.Show("The file you tried to open may be corrupt"); }
}
}
/// <summary>
/// deals with open a new file
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
//check if orignal file has been changed if so ask if they want to save.
String message = "Would you like to save before closing?";
var result1 = MessageBox.Show(message, "Closing", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result1 == DialogResult.Yes)
{
saveFile();
}
//With help from www.dotnetperls.com
//show dialog
DialogResult result = openFileDialog1.ShowDialog();
//check result
if (result == DialogResult.OK)
{
string fileName = openFileDialog1.FileName;
try
{
//make new spreadsheet
SSModel = new Spreadsheet(fileName, s => Regex.IsMatch(s, @"^[a-zA-Z][1-9][0-9]?$"), s => s.ToUpper(), "ps6");
//fill in the cells in the GUI
foreach (String cellName in SSModel.GetNamesOfAllNonemptyCells())
{
setRowAndCol(cellName);
spreadsheetPanel1.SetValue(col, row, SSModel.GetCellValue(cellName).ToString());
}
//set up the spreadsheet
spreadsheetPanel1.SetSelection(0, 0);
displayCurrentCell(0, 0);
}catch (Exception ex)
{
MessageBox.Show("An error occured:\n " + ex.Message);
}
}
}
public SimpleSpreadsheetGUI()
{
this.grid_widget = new SpreadsheetGridWidget();
coordinateMap = new Dictionary <Point, string>();
spreadsheet = new Spreadsheet(s => true, s => s.ToUpper(), "six");
labelTimer = new Timer();
// Call the AutoGenerated code
InitializeComponent();
//Start a timer for Auto-Save.
timer = new Timer();
timer.Interval = 30_000; //30 seconds defined for the interval.
timer.Enabled = true;
timer.Tick += new System.EventHandler(AutoSave);
// Add event handler and select a start cell
grid_widget.SelectionChanged += DisplaySelection;
grid_widget.SetSelection(0, 0, false);
cellName_Textbox.Text = "A1";
cellValue_Textbox.Text = spreadsheet.GetCellValue("A1").ToString();
}
/// <summary>
/// Helper method to update the value textbox of specified cell
/// Also updates the visible value on the selected cell
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
private void update_value_textbox(int col, int row)
{
string cellName = getCellName(col, row);
object value = spreadsheet.GetCellValue(cellName);
if (value is string)
{
cell_value_textbox.Text = (string)spreadsheet.GetCellValue(cellName);
spreadsheetPanel1.SetValue(col, row, (string)spreadsheet.GetCellValue(cellName));
}
else if (value is double)
{
cell_value_textbox.Text = spreadsheet.GetCellValue(cellName).ToString();
spreadsheetPanel1.SetValue(col, row, spreadsheet.GetCellValue(cellName).ToString());
}
else if (value is FormulaError)
{
cell_value_textbox.Text = "FormulaError";
spreadsheetPanel1.SetValue(col, row, "FormulaError");
}
}
public void TestGetCellValueStr()
{
AbstractSpreadsheet s = buildSheet();
Assert.AreEqual("Hello World", s.GetCellValue("A3"));
}
public void TestGetCellValueFormula()
{
AbstractSpreadsheet s = buildSheet();
Assert.AreEqual(8.0, s.GetCellValue("A1"));
}
// 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);
}
}
public void TestGetCellValueDbl()
{
AbstractSpreadsheet s = buildSheet();
Assert.AreEqual(5.0, s.GetCellValue("B1"));
}
// Every time the selection changes, this method is called with the
// Spreadsheet as its parameter. We display the current time in the cell.
private void displaySelection(SpreadsheetPanel ss)
{
// Used for periodically checking for cirular dependencies
//if (Stopwatch.IsRunning == false)
// Stopwatch.Start();
//if(Stopwatch.ElapsedMilliseconds 60000)
//{
// PeriodicCircCheck(ss);
// Stopwatch.Reset();
//}
int row, col;
String value;
if (connected == true)
{
Model.Model.Unfocus();
}
ss.GetSelection(out col, out row);
ss.GetValue(col, row, out value);
CellNameField.Text = (((Char)((65) + (col))) + "" + (row + 1));
if (connected == true)
{
Model.Model.Focus(CellNameField.Text);
}
spreadsheetPanel1.GetFirstSelection(out int fcol, out int frow);
int xpos = LABEL_COL_WIDTH + (col - fcol) * DATA_COL_WIDTH + 1;
int ypos = LABEL_ROW_HEIGHT + (row - frow) * DATA_ROW_HEIGHT + 1;
ContentField.Location = new Point(xpos, ypos);
ContentField.BringToFront();
ContentField.Focus();
ContentsBar.Text = ContentField.Text;
if (sheet.GetCellValue(CellNameField.Text) is FormulaError err)
{
ss.SetValue(col, row, "#REF ");
CellValueField.Text = "#REF ";
}
else
{
ss.SetValue(col, row, sheet.GetCellValue(CellNameField.Text) + "");
CellValueField.Text = sheet.GetCellValue(CellNameField.Text) + "";
}
if (sheet.GetCellContents(((Char)((65) + (col))) + "" + (row + 1)) is Formula form)
{
ContentField.Text = "=" + sheet.GetCellContents(((Char)((65) + (col))) + "" + (row + 1));
}
else
{
ContentField.Text = "" + sheet.GetCellContents(((Char)((65) + (col))) + "" + (row + 1));
}
//CS3505 changes///////////////////////////////////////////////////
//Checks to see if a circular dependency was found and display #REF in the selected cell on update if it was
if (isCircular == true)
{
MessageBox.Show("The formula you entered results in a circular exception.");
ss.SetValue(col, row, "#REF ");
CellValueField.Text = "#REF ";
isCircular = false;
}
//end//////////////////////////////////////////////////////////////
ContentField.Focus();
}
