/// <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(); }