Editing in UWP Spreadsheet (SfSpreadsheet)

23 Jul 20267 minutes to read

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

Editing

SfSpreadsheet supports editing, you can modify and commit cell values in the workbook.

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

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 editing of a cell programmatically in any of the following ways:

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

  • EndEdit - Commits and ends the edit operation for the current cell. When called with the parameter true, the new value is committed; otherwise the old value is reverted.

//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 disables editing and formatting of the cells when the sheet is protected. By default, all cells in the worksheet are locked.
When the sheet is protected, you can unlock cells to allow them to be edited or formatted.

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 SfSpreadsheet:

Events Description
CurrentCellBeginEdit Occurs when the current cell enters into edit mode. This event allows to cancel entering the 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 and cancel the end editing.
CurrentCellValidated Occurs after the current cell is validated.
CurrentCellEndEdit Occurs when the current cell exits 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 value or moves to the last position.
EditTrigger Gets or sets a value indicating any of the trigger options will cause cells to enter Edit Mode.
IsEditing Gets whether the current cell is in edit mode.

The table below lists the methods associated with Editing.

Methods Description
BeginEdit Begins the editing operation of the current cell and returns true if the current cell enters into edit mode.
EndEdit Commits and ends the edit operation of 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 that limit the type of data or the values that can be entered in a cell.

Applying Data Validation at runtime

SfSpreadsheet 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 less than or equal to 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

To show a ComboBox in a cell in SfSpreadsheet, apply List Validation to that cell.

Hyperlinks provide a convenient way to access web pages, files, and data within a worksheet or across other worksheets in a workbook. SfSpreadsheet supports adding, editing, and removing Hyperlinks in the workbook.

SfSpreadsheet supports adding a hyperlink to a cell through the hyperlinks collection using the IHyperlinks interface.

SfSpreadsheet 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 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 to refer 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));

SfSpreadsheet supports editing or removing 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 from a cell,
var hyperlink = spreadsheet.ActiveSheet.Range["A5"].Hyperlinks.RemoveAt(0);
spreadsheet.ActiveGrid.InvalidateCell(GridRangeInfo.Cell(5,1));