Editing in Windows Forms Spreadsheet

23 Jul 20267 minutes to read

This section explains the Editing behavior, Data Validation, and Hyperlinks in the Spreadsheet.

Cell Editing

The Spreadsheet control supports editing, allowing you to modify and commit cell values in the workbook.

By default, editing is enabled in the Spreadsheet. To disable editing, set the AllowEditing property to false.

private void spreadsheet_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)
{
    spreadsheet.ActiveGrid.AllowEditing = false;
}

Editing a cell programmatically

Start editing

You can edit a cell programmatically by using the BeginEdit method.

spreadsheet.ActiveGrid.CurrentCell.BeginEdit(true);

End editing

You can end the editing of a cell programmatically in any of the following ways:

  • ValidateAndEndEdit - Validates and ends the edit operation for the current cell. If the cancel argument is true, the current cell remains in edit mode. If validation succeeds, the new value is committed and the active cell moves to the next cell. If validation fails, the old value is restored and the active cell moves to the next cell.

  • EndEdit - Commits and ends the edit operation for the current cell. If true is passed, the new value is committed; otherwise, the old value is restored.

//Validates and ends the edit operation,
spreadsheet.ActiveGrid.CurrentCell.ValidateAndEndEdit();

//Commits the value and ends the edit operation,
spreadsheet.ActiveGrid.CurrentCell.EndEdit(true);

Locking or UnLocking a cell

Locking cells allows you to disable editing and formatting the cells when the sheet is protected. By default, every cell is locked in the worksheet.
When the sheet is protected, you can unlock specific cells to allow editing or formatting.

var worksheet = spreadsheet.ActiveSheet;
var excelStyle = worksheet.Range["A2"].CellStyle;

//To unlock a cell,
excelStyle.Locked = false;

//To lock a cell,
excelStyle.Locked = true;

Properties, Methods, and Events

The order of events when editing and committing a cell value in the Spreadsheet is as follows:

Events Description

CurrentCellBeginEdit

 Occurs when the current cell enters edit mode. This event allows you to cancel entering edit mode.

CurrentCellValueChanged

 Occurs when the current cell value is changed in edit mode.

CurrentCellValidating

 Occurs when the current cell value is going to be validated. It allows you to validate the value and cancel the end editing.

CurrentCellValidated

 Occurs after the current cell value is validated.

CurrentCellEndEdit

 Occurs when the current cell leaves edit mode.

The table below lists the properties associated with Editing.

Properties Description

AllowEditing

 Gets or sets the value indicating whether to allow the editing operation or not.

EditorSelectionBehavior

 Gets or sets a value indicating whether the editor selects all the cell text or moves the caret to the last position.

EditTrigger

 Gets or sets the trigger options that cause cells to enter Edit Mode.

IsEditing

 Gets a value indicating whether the current cell is in edit mode.

The table below lists the methods associated with Editing.

Methods Description

BeginEdit

 Begins the edit operation for the current cell and returns `true` if the current cell enters edit mode.

EndEdit

 Commits and ends the edit operation for the current cell.

ValidateAndEndEdit

 Validates and ends the edit operation of the current cell.

Validate

 Validates the current cell in the SpreadsheetGrid.

Data Validation

Data Validation is a list of rules to limit the type of data or the values that can be entered in the cell.

Applying data validation at runtime

The Spreadsheet allows you to apply data validation rules at runtime for a particular cell or range using the IDataValidation interface.

//Number Validation
IDataValidation validation = spreadsheet.ActiveSheet.Range["A5"].DataValidation;
validation.AllowType = ExcelDataType.Integer;
validation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
validation.FirstFormula = "4";
validation.SecondFormula = "15";
validation.ShowErrorBox = true;
validation.ErrorBoxText = "Accepts values only between 4 to 15";

//Date Validation
IDataValidation validation = spreadsheet.ActiveSheet.Range["B4"].DataValidation;
validation.AllowType = ExcelDataType.Date;
validation.CompareOperator = ExcelDataValidationComparisonOperator.Greater;
validation.FirstDateTime = new DateTime(2016,5,5);
validation.ShowErrorBox = true;
validation.ErrorBoxText = "Enter the date value which is greater than 05/05/2016";

//TextLength Validation
IDataValidation validation = spreadsheet.ActiveSheet.Range["A3:B3"].DataValidation;
validation.AllowType = ExcelDataType.TextLength;
validation.CompareOperator = ExcelDataValidationComparisonOperator.LessOrEqual;
validation.FirstFormula = "4";
validation.ShowErrorBox = true;
validation.ErrorBoxText = "Text length should be lesser than or equal 4 characters";

//List Validation
IDataValidation validation = spreadsheet.ActiveSheet.Range["D4"].DataValidation;
validation.ListOfValues = new string[] { "10", "20", "30" };

//Custom Validation
IDataValidation validation = spreadsheet.ActiveSheet.Range["D4"].DataValidation;
validation.AllowType = ExcelDataType.Formula;
validation.FirstFormula = "=A1+A2>0";
validation.ErrorBoxText = "Sum of the values in A1 and A2 should be greater than zero";

For more reference, please go through the XlsIO UG.

TIPS

If you want to display a ComboBox in a cell of the Spreadsheet, you can apply List Validation to that cell.

A hyperlink is a convenient way to access web pages, files, and other data within a workbook. The Spreadsheet supports adding, editing, and removing Hyperlinks in the workbook.

The Spreadsheet supports adding hyperlinks to a cell, and they can be added to the hyperlinks collection using the IHyperLinks interface.

The Spreadsheet allows you to add the following types of hyperlinks.

  • Web URL
  • Cell or range in a workbook
  • E-mail
  • External files
// Creating a hyperlink for an e-mail,
var range = spreadsheet.ActiveSheet.Range["A5"];
IHyperLink hyperlink1 = spreadsheet.ActiveSheet.HyperLinks.Add(range);
hyperlink1.Type = ExcelHyperLinkType.Url;
hyperlink1.Address = "mailto:[email protected]";
hyperlink1.TextToDisplay = "Send Mail";
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(5, 1));

// Creating a hyperlink for opening files,
var range1 = spreadsheet.ActiveSheet.Range["D5"];
IHyperLink hyperlink2 = spreadsheet.ActiveSheet.HyperLinks.Add(range1);
hyperlink2.Type = ExcelHyperLinkType.File;
hyperlink2.Address = @"C:\Samples\Local";
hyperlink2.TextToDisplay = "File Location";
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(5, 4));

// Creating a hyperlink that refers to another cell in the workbook,
var range2 = spreadsheet.ActiveSheet.Range["C13"];
IHyperLink hyperlink3 = spreadsheet.ActiveSheet.HyperLinks.Add(range);
hyperlink3.Type = ExcelHyperLinkType.Workbook;
hyperlink3.Address = "Sheet2!C23";
hyperlink3.TextToDisplay = "Sample";
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(13, 3));

The Spreadsheet supports editing or removing the hyperlinks from a range by accessing the hyperlinks collection.

//To edit a hyperlink in a cell,
var hyperlink = spreadsheet.ActiveSheet.Range["A5"].Hyperlinks[0];
hyperlink.TextToDisplay = "Sample";
hyperlink.Address = "http://help.syncfusion.com";
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(5, 1));

//To remove a hyperlink in a cell,
spreadsheet.ActiveSheet.Range["A5"].Hyperlinks.RemoveAt(0);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(5, 1));