/// <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>
/// Whenever a different cell is selected this event fires
/// Places all the info of the cell in desinated textboxes
/// </summary>
private void SelectionChanged(SpreadsheetPanel sender)
{
int col;
int row;
cells.GetSelection(out col, out row); //get col row
string name = getCellName(col, row); // get the name of the cell
if (ss.GetCellContents(name) is Formula) //Set contentsTextbox, valueTextbox, cellnameTextbox
{
content.Text = "=" + ss.GetCellContents(name).ToString();
}
else
{
content.Text = ss.GetCellContents(name).ToString();
}
cellName.Text = name;
if (ss.GetCellValue(name) != null)
{
cellValue.Text = ss.GetCellValue(name).ToString();
}
else
{
cellValue.Text = "";
}
content.Focus();
content.SelectAll();
}
/// <summary>
/// gets the contents of the cell
/// </summary>
/// <param name="row"></param>
/// <param name="col"></param>
/// <returns></returns>
private string getCellContents(int row, int col)
{
string cell = getCellName(row, col);
if (SSModel.GetCellContents(cell) is Formula)
{
return("=" + SSModel.GetCellContents(cell).ToString());
}
return(SSModel.GetCellContents(cell).ToString());
}
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"));
}
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"));
}
/// <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();
}
}
/// <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>
/// This function will update the values that are displayed in the control forms at the top of the SS
/// It is called every time a new cell is selected.
/// </summary>
/// <param name="ss"></param>
private void DisplayControlsOnSelection(SpreadsheetPanel ss)
{
int row, col;
// getting the current cell.
ss.GetSelection(out col, out row);
// setting the text value of the CellName input to be the current selection
CellName.Text = "" + Convert.ToChar(col + 65) + (row + 1);
// getting the contents of the current cell
object contents = spread.GetCellContents(CellName.Text);
//StringContents exists as the actual display string. contents exists as just a placeholder.
string StringContents;
// if it is a formula, convert the contents to a string and prepend it with a '='
if (contents.GetType() == typeof(Formula))
{
StringContents = "=" + (string)contents.ToString();
}
// else just convert it to a string
else
{
StringContents = contents.ToString();
}
// set the inputs at the top to hold the correct values.
CellContents.Text = StringContents;
CellValue.Text = GetCellValueAsString(CellName.Text);
}
/// <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;
}
public void TestGeneralSetAndGetCellContents()
{
AbstractSpreadsheet s = buildSheet();
Assert.AreEqual(s.GetCellContents("A1").GetType(), typeof(Formula));
Assert.AreEqual("", s.GetCellContents("A2"));
Assert.AreEqual("Hello World", s.GetCellContents("A3"));
Assert.AreEqual(5.0, s.GetCellContents("B1"));
Assert.AreEqual(s.GetCellContents("B2").GetType(), typeof(Formula));
Assert.AreEqual(s.GetCellContents("B3").GetType(), typeof(Formula));
Assert.AreEqual(s.GetCellContents("C1").GetType(), typeof(Formula));
Assert.AreEqual(6.0, s.GetCellContents("C2"));
Assert.AreEqual("", s.GetCellContents("C3"));
}
//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>
/// Hooks GetCellContents<string></string> event
/// Pulls desired contents from model, passes it to view
/// </summary>
/// <param name="name"></param>
private void HandleGetCellContents(string name)
{
object contents = model.GetCellContents(name); //pull the contents
if (contents is Formula) //if form, append "=" for proper syntax
{
view.FormulaEditBox = "=" + contents.ToString();
}
else
{
view.FormulaEditBox = contents.ToString();
}
}
/// <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();
}
/// <summary>
/// Helper method used to update the content textbox of the specified cell
/// Updates the content textbox of the selected cell
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
private void update_content_textbox(int col, int row)
{
string cellName = getCellName(col, row);
object content = spreadsheet.GetCellContents(cellName);
object value = spreadsheet.GetCellValue(cellName);
if (content is Formula)
{
cell_content_textbox.Text = "=" + content.ToString();
}
else if (value is FormulaError)
{
cell_content_textbox.Text = "=" + content.ToString();
}
else
{
cell_content_textbox.Text = content.ToString();
}
}
//
//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>
/// Gets the cell contents. Cell contents are either a formula, double or string
/// </summary>
/// <param name="col"></param>
/// <param name="row"></param>
/// <returns></returns>
public object GetCellContents(int col, int row)
{
return(ss.GetCellContents(DigitToVar(col, row)));
}
// 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);
}
}
// 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();
}
