/// <summary>
/// users can still click-select cells
/// </summary>
/// <remarks>
/// This is actually trickier than it seems. We need to update the
/// cell value and cell content txt boxes, update col,row,
/// and ensure that focus is passed to the panel.
/// Deselecting should release(write) the edit box text to the cell
/// </remarks>
/// <param name="sender"></param>
private void Panel_SelectionChanged(SpreadsheetPanel sender)
{
Panel.GetSelection(out col, out row);
CellNameBox.Text = this.ColRow_To_string(col, row);
CellVal.Text = this.PrintableValue(sheetModel.GetCellValue(CellNameBox.Text));
string To_cell_content_box;
Panel.GetValue(col, row, out To_cell_content_box);
CellContents.Text = To_cell_content_box;
CellContents.Select(0, CellContents.Text.Length);
this.Panel.Select();
PanelFocus = true;
}
private void SendSelectedUpdate(SpreadsheetPanel ssp)
{
BoxContents.Focus();
CellSelectionChange(ssp);
if (_selection == null)
{
return;
}
// This should send the update to the server (do we need to do this in the try block?)
var selected = new SelectCell();
selected.SetCellName(_selection);
// Tell the server that this client selected a new cell.
// Client ID not needed prior to selecting a cell (not mentioned in protocol document) < - Double check this
_clientController.SendUpdatesToServer(selected);
}
/// <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));
}
}
// Event Handler for Saving the spreadsheet as a new .sprd file.
// added an additional parameter for 'Piping' in the FileName
private void saveAsMenuItem_Click(object sender, EventArgs e, SpreadsheetPanel panel, ref string FileName)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Spreadsheet files (*.sprd)|*.sprd";
saveFileDialog1.Title = "Save Spreadsheet File";
saveFileDialog1.ShowDialog();
// This try block will catch an error caused by pressing cancel
// instead of saving.
try
{
panel.getSS().Save(saveFileDialog1.FileName);
FileName = saveFileDialog1.FileName;
this.Text = FileName.Substring(FileName.LastIndexOf("\\") + 1);
}
catch (SS.SpreadsheetReadWriteException)
{
saveFileDialog1.Dispose();
}
}
/// <summary>
/// Operations occuring when a new cell is selected.
/// </summary>
/// <param name="s"></param>
void ChangeCellBoxDetails(SpreadsheetPanel s)
{
//Get the address for the selected cell
spreadsheetPanel1.GetSelection(out column, out row);
spreadsheetPanel1.GetValue(column, row, out content);
int rowDisplay = row + 1;
CellNameTextBox.Text = cellName = columnNames[column] + rowDisplay;
value = ss.GetCellValue(cellName).ToString();
//Change the textboxes above the spreadsheet panel
if (ss.GetCellContents(cellName).GetType() == typeof(Formula))
{
CellContentTextBox.Text = "=" + ss.GetCellContents(cellName);
}
else
{
CellContentTextBox.Text = ss.GetCellContents(cellName).ToString();
}
CellValueTextBox.Text = value;
}
private void DisplayUpdate(SpreadsheetPanel ss)
{
if (_spreadsheet == null)
{
return;
}
int col, row;
ss.GetSelection(out col, out row);
// Convert col, row index to spreadsheet cell names.
var cellName = ((char)(col + 65)) + (row + 1).ToString(CultureInfo.InvariantCulture);
// Displays selected cell's name
CellNameBox.Invoke(new Action(() => { CellNameBox.Text = cellName; }));
// No need to fetch the value from the spreadsheet again, just copy it from
// the spreadsheetpanel. This avoids reworking the FormulaError message.
string value;
ss.GetValue(col, row, out value);
ValueBox.Invoke(new Action(() => { ValueBox.Text = value; }));
}
/// <summary>
/// Helper method that updates all text boxes when something changes
/// </summary>
/// <param name="s"></param>
private void updateTextBox(SpreadsheetPanel s)
{
String name;
cellValueTextBox.Focus();
//Assigns the cell name textbox to name for the selected cell
name = getCellName();
cellContentsBox.Text = name;
//Checks the cell if its a formula so it knows to add a '=' or not in front of the value
if (ss.GetCellContents(name).GetType() == typeof(Formula))
{
cellValueTextBox.Text = "=" + ss.GetCellContents(name).ToString();
}
else
{
cellValueTextBox.Text = ss.GetCellContents(name).ToString();
}
cellValueWindow.Text = ss.GetCellValue(name).ToString();
}
/// <summary>
/// Handles the button press of the set button and sets the oject in the contentBox to the correct cell
/// </summary>
/// <param name="sp"></param>
/// <param name="contents"></param>
private void SetContents(SpreadsheetPanel sp, string contents)
{
window.TestHelp2();
int row, col;
String coordinate;
HashSet <string> set = new HashSet <string>();
sp.GetSelection(out col, out row);
char c = (char)(col + 65);
coordinate = c.ToString() + (row + 1).ToString();
try {
set = model.setContents(coordinate, contents);
}
catch (Exception e)
{
window.Message = "Error:" + " " + e.Message;
}
foreach (String s in set)
{
int newCol = (int)s.First <char>() - 65;
int newRow;
int.TryParse(s.Substring(1), out newRow);
newRow = newRow - 1;
sp.SetValue(newCol, newRow, model.getValue(s));
window.valueBox = model.getValue(s);
window.contentBox = model.getContents(s);
}
char ch = (char)(col + 65);
String newCoordinate = c.ToString() + (row + 1).ToString();
UpdateBoxes(sp, model.getContents(newCoordinate), model.getValue(newCoordinate), newCoordinate);
}
/// <summary>
/// Sets the formula box to the value of the selected cell and sets focus to the formula box.
/// Prepares for user input to cell.
/// </summary>
/// <param name="sender"></param>
private void MakeWriteable(SpreadsheetPanel sender)
{
if (connected)
{
sender.GetSelection(out int col, out int row);
string cellName = GetCellName(col, row);
int[] loc = GetCellPosition(this.previousSelection);
int myCol = loc[0];
int myRow = loc[1];
//spreadsheetPanel1.SetUnfocus(this.previousSelection, myRow, myCol);
string unfocusMessage = "unfocus " + ((char)3);
SendMessage(unfocusMessage);
//string focusMessage = "focus " + cellName + ((char)3);
//SendMessage(focusMessage);
//set the contents of the formula box and set focus to it.
FormulaBox.Text = ss1.GetCellContents(cellName).ToString();
this.ActiveControl = FormulaBox;
}
}
// Event Handler for go button
// Go Button just sets the cells value to whatever is in the contents Text Box
// added an additional parameter for 'Piping' in the active spreadsheetPanel
private void goBtn_Click(object sender, EventArgs e, SpreadsheetPanel panel)
{
int col, row;
panel.GetSelection(out col, out row);
string selectedCell = cellName(col, row);
cellAddress(selectedCell, out col, out row);
try
{
updateCells(panel, selectedCell, ContentTxtBox.Text);
ValueTxtBox.Text = panel.getSS().GetCellValue(selectedCell).ToString();
string value;
panel.GetValue(col, row, out value);
ContentTxtBox.Text = value;
}
catch (Exception ex)
{
MessageBox.Show("Formula Formatting Error: " + ex.Message);
}
ContentTxtBox.Focus();
}
/// <summary>
/// When cell is selected, get contents and val to put in text boxes
/// </summary>
public void cellSelect(SpreadsheetPanel ss)
{
//Gets cell name
ss.GetSelection(out int x, out int y);
string cellName = coordsToCell(x, y);
//Gets contents and value
object contents = sheet.GetCellContents(cellName);
object value = sheet.GetCellValue(cellName);
//Gets contents if formula
if (contents is Formula)
{
contents = "=" + contents.ToString();
}
//Gets value if formula error
if (value is FormulaError)
{
value = "FormulaError";
}
//Returns contents and value
form.endCellSelect(contents.ToString(), value.ToString(), cellName);
}
public Form1(ClientController Controller, StaticState ss, Form f)
{
InitializeComponent();
controller = Controller;
cellboxX = 0;
cellboxY = 0;
state = ss;
sss = spreadsheetPanel1;
currentCol = cellboxY;
currentRow = cellboxX;
controller.RegisterSpreadsheetEditHandler(ChangeCellContents);
controller.RegisterErrorHandler(circularErrorDisplay);
controller.RegisterLoginErrorHandler(loginErrorDisplay);
//Register displaySelection as listener to selectionChanged
spreadsheetPanel1.SelectionChanged += displaySelection;
//Select cell A1
spreadsheetPanel1.SetSelection(0, 0);
//Move cursor to ContentsField
cellContentsField.Focus();
//create backing structure
sheet = new SS.Spreadsheet(s => Regex.IsMatch(s, "^[A-Z]{1}[1-9]{1}[0-9]?$"), s => s.ToUpper(), "ps6");
//IsChanged is false initially
isChanged = false;
FormClosing += new FormClosingEventHandler(Options_OnClosing);
this.ClientSizeChanged += new EventHandler(changed_size);
this.cellContentsField.TextChanged += new EventHandler(contentstextbox_changed);
F = f;
}
/// <summary>
/// Replaces the current value text box to the looked-up value of the cell
/// </summary>
private void SetCellValueBox()
{
SpreadsheetPanel panel = window.GetSpreadsheetPanel();
// locates the current cell in the grid and converts it to a variable
panel.GetSelection(out int col, out int row);
string cellName = ConvertRowColToCellName(row, col);
// set the "value" object to the value of the variable
object value = sheet.GetCellValue(cellName);
// if value is a string or double then convert the object to a string
if (value is string || value is double)
{
window.ValueBoxText = value.ToString();
}
// else text box value will be set to Error
else
{
window.ValueBoxText = "#CellError";
}
}
/// <summary>
/// Every time the selection changes, this method is called with the
/// Spreadsheet as its parameter. We display the current time in the cell.
/// </summary>
/// <param name="sender"></param>
private void displaySelection(SpreadsheetPanel sender)
{
int row, col;
String value;
sender.GetSelection(out col, out row);
sender.GetValue(col, row, out value);
// set the cellname on my spreadsheet and result value of cell content
CellName.Text = convertToCellName(col, row);
CellValue.Text = myspreadsheet.GetCellValue(CellName.Text).ToString();
// if the cell content is formula, then compute it.
Object tempcontent = myspreadsheet.GetCellContents(CellName.Text);
if (tempcontent is Formula)
{
CellContent.Text = "=" + tempcontent;
}
else
{
CellContent.Text = tempcontent.ToString();
}
}
/// <summary>
/// When we edit the contents of the cell.
/// </summary>
private void HandleCellChanged(SpreadsheetPanel ss)
{
int row, col;
String value;
string val2;
ss.GetSelection(out col, out row);
ss.GetValue(col, row, out value);
window.CellName = this.LocationToCellName(row, col);
ss.GetValue(col, row, out val2);
window.Value = val2;
string val;
ss.GetValue(col, row, out val);
if (val == null)
{
window.Value = "";
}
if (ss.GetValue(col, row, out value))
{
try
{
object ssContents = model.GetCellContents(LocationToCellName(row, col));
string stringCont = ssContents.ToString();
if (ssContents is Formula)
{
stringCont = "=" + stringCont;
}
window.Content = stringCont;
}
catch
{
window.Content = "";//If tbe cell is empty, the content box should also be empty.
}
}
}
// Method for updating all cells effected by changing a cell
private void updateCells(SpreadsheetPanel sspanel, string startingCell, string startingCellValue)
{
int col, row;
cellAddress(startingCell, out col, out row);
//catches a bug where if you set the cell = to itself
if (startingCellValue.Count() > 0 && startingCellValue.Substring(1).ToUpper() == startingCell)
{
MessageBox.Show("Formula Formatting Error: You're Dumb.. Can't set the contents of a cell to itself");
spreadsheetPanel1.SetValue(col, row, "");
sspanel.getSS().SetContentsOfCell(startingCell, "");
ContentTxtBox.Text = "";
}
// don't want to update any cells containing ""
else //if (startingCellValue != "")
{
// update the set
foreach (string s in sspanel.getSS().SetContentsOfCell(startingCell, startingCellValue))
{
cellAddress(s, out col, out row);
if (object.ReferenceEquals(sspanel.getSS().GetCellValue(s).GetType(), typeof(FormulaError)))
{
sspanel.SetValue(col, row, "=" + sspanel.getSS().GetCellContents(s).ToString());
}
else
{
sspanel.SetValue(col, row, sspanel.getSS().GetCellValue(s).ToString());
}
}
// adds a * to the form title when the spreadsheet has been modified
if (sspanel.getSS().Changed == true && this.Text.Substring(this.Text.Count() - 1) != "*")
{
this.Text += " *";
}
}
}
/// <summary>
/// when the selection of a spreadsheet is changed, updates values of name, contents, and value of spreadsheet.
///
/// </summary>
/// <param name="sender"></param>
private void OnSelectionChanged(SpreadsheetPanel sender)
{
// Get where we are in the spreadsheet
sender.GetSelection(out int col, out int row);
// Update name textBox
textBoxCellName.Text = (ConvertCellName(col, row));
// Update value textBox
sender.GetValue(col, row, out string val);
textBoxCellValue.Text = val;
// Update contents textBox
textBoxCellContents.Text = sender.GetContents(col, row);
// Focus the input onto the contents textbox
textBoxCellContents.Focus();
// Sending selection changed to server via JSon
RequestTypeSelection r = new RequestTypeSelection("selectCell", spreadsheetPanel1.ConvertCellName(col, row));
string request = JsonConvert.SerializeObject(r) + "\n";
NC.SendData(request);
}
/// <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>
/// Called when enter is pressed while cell editor text box is selected.
/// </summary>
/// <param name="sender">The Spreadsheet Panel containing the cell.</param>
private void SpreadsheetPanel_CellEditEnter(SpreadsheetPanel sender)
{
try
{
if (_spreadsheet == null)
{
return;
}
// Send edit message to Server
networkController.Edit(GetSelectedCellName(), spreadsheetPanel.cellInputTextBox.Text);
// Set the contents of the cell, and update the values of any dependents.
RefreshCellValues(_spreadsheet.SetContentsOfCell(GetSelectedCellName(),
spreadsheetPanel.cellInputTextBox.Text));
// Moving cell selection down if cell edit is valid
spreadsheetPanel.MoveSelectionDown();
SpreadsheetPanel_SelectionChanged(spreadsheetPanel);
spreadsheetPanel.cellInputTextBox.Clear();
}
catch (CircularException)
{
MessageBox.Show(Resources.SpreadsheetForm_inputTextBox_Circular_Dependency,
Resources.SpreadsheetForm_inputTextBox_Invalid_Cell_Input);
}
catch (InvalidNameException)
{
MessageBox.Show(Resources.SpreadsheetForm_inputTextBox_Invalid_Cell_Name,
Resources.SpreadsheetForm_inputTextBox_Invalid_Cell_Input);
}
catch (FormulaFormatException)
{
MessageBox.Show(Resources.SpreadsheetForm_inputTextBox_Invalid_Formula,
Resources.SpreadsheetForm_inputTextBox_Invalid_Cell_Input);
}
}
/// <summary>
/// Updates cell values in the spreadsheet panel
/// </summary>
public static void updateCells(Spreadsheet sheet, SpreadsheetPanel ss, List <string> cells)
{
object contents;
object value;
foreach (string cellName in cells)
{
contents = sheet.GetCellContents(cellName);
value = sheet.GetCellValue(cellName);
//Gets contents if formula
if (contents is Formula)
{
contents = "=" + contents.ToString();
}
//Gets value if formula error
if (value is FormulaError)
{
value = "FormulaError";
}
//Updates cell values and returns contents and value
cellToCoords(cellName, out int x, out int y);
ss.SetValue(x, y, value.ToString());
}
}
// 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)
{
int row, col;
String value;
ss.GetSelection(out col, out row);
ss.GetValue(col, row, out value);
// Setting the cell name textboxes based on whatever is selected
BoxCellName.Text = columnRowToCellNameConverter(col, row);
BoxCellValue.Text = spreadsheet.GetCellContents(BoxCellName.Text).ToString();
Object temp = spreadsheet.GetCellContents(BoxCellName.Text);
// checking if the value entered into the cell are of type formula
if (temp is Formula)
{
BoxCellContent.Text = "=" + temp;
}
else
{
BoxCellContent.Text = temp.ToString();
}
}
/// <summary>
/// Sets the focus to the contentTextBox as well as putting the cursing at the end of the contents in the
/// contentTextBox.
/// </summary>
/// <param name="ss"></param>
private void FocusOnContentTextBox(SpreadsheetPanel ss)
{
contentTextBox.Focus();
contentTextBox.SelectionStart = contentTextBox.Text.Length;
}
public static void setSS(this SpreadsheetPanel panel, Spreadsheet spreadsheet)
{
ss = spreadsheet;
}
private void DisplayCurrentCellNameDelegate(SpreadsheetPanel sender)
{
DisplayCurrentCellName();
}
public static Spreadsheet getSS(this SpreadsheetPanel panel)
{
return(ss);
}
private void SetCellContentsBoxDelegate(SpreadsheetPanel sender)
{
SetCellContentsBox();
}
private void SetCellValueBoxDelegate(SpreadsheetPanel sender)
{
SetCellValueBox();
}
public static void setSelectedCell(this SpreadsheetPanel panel, string SelectedCell)
{
sCell = SelectedCell;
}
public static string getSelectedCell(this SpreadsheetPanel panel)
{
return(sCell);
}
private void SendFocusToServerDelegate(SpreadsheetPanel sender)
{
SendFocusToServer();
}