Editing in Spreadsheet control

22 Jul 20266 minutes to read

You can edit the contents of a cell directly in the cell or by typing in the formula bar. By default, the editing feature is enabled in the spreadsheet. Use the allowEditing property to enable or disable the editing feature.

Edit cell

You can start editing in one of the following ways:

  • Double-click a cell to enter edit mode.
  • Press the F2 key to edit the active cell.
  • Use the formula bar to edit the cell content.
  • Press the Backspace or Space key to clear the cell content and enter edit mode.
  • Use the startEdit method to programmatically start editing the active cell.

Save cell

When a cell is in an editable state, you can save the edited content in one of the following ways:

  • Click any cell other than the cell being edited.
  • Press the Enter or Tab key to save the edited cell content.
  • Use the endEdit method to programmatically save the edited cell content.

Cancel editing

You can cancel editing without saving the changes in one of the following ways:

  • Press the Esc key to exit edit mode and restore the original cell content.
  • Use the closeEdit method to programmatically cancel editing without saving the changes.

Prevent editing and cell saving

The following example demonstrates how to prevent cell editing and cell saving. Editing is prevented in column E by setting the cancel argument to true in the cellEdit event. Saving edited changes is prevented in column D by setting the cancel argument to true in the beforeCellSave event and using the closeEdit method.

@Html.EJS().Spreadsheet("spreadsheet").ShowSheetTabs(false).Created("created").CellEdit("cellEdit").BeforeCellSave("beforeCellSave").Sheets(sheet =>
{
    sheet.SelectedRange("C7").Name("Movie List").Ranges(ranges =>
    {
        ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
    }).Rows(row =>
    {
        row.Index(10).Cells(cell =>
        {
            cell.Index(3).Value("Total Amount:").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold }).Add();
            cell.Formula("=SUM(E2:E10").Add();
        }).Add();
    }).Columns(column =>
    {
        column.Width(120).Add();
        column.Width(180).Add();
        column.Width(100).Add();
        column.Width(120).Add();
        column.Width(120).Add();
    }).Add();
}).Render()

<script>

    function created() {
        this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:E1');
        this.cellFormat({ textAlign: 'center' }, 'A2:A10');
        this.cellFormat({ textAlign: 'center' }, 'C2:C10');
        this.numberFormat('$#,##0.00', 'D2:D10');
        this.numberFormat('$#,##0.00', 'E2:E11');
    }
    function cellEdit(args) {
        // Preventing the editing in 5th(Amount) column.
        if (args.address.includes('E')) { args.cancel = true; }
    }
    function beforeCellSave(args) {
        // Prevent saving the edited changes in 4th(Rate) column.
        if (args.address.includes('D')) {
            args.cancel = true;
            // Manually removes the editable state without saving the changes. Use `endEdit` method if you want to save the changes.
            this.closeEdit();
        }
    }
</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { ItemCode= "I231",  ItemName= "Chinese Combo Noodle",  Quantity= "2",  Rate= "125",  Amount= "=PRODUCT(C2,D2)"},
        new { ItemCode= "I245",  ItemName= "Chinese Combo Rice",  Quantity= "3",  Rate= "125",  Amount= "=PRODUCT(C3,D3)"},
        new { ItemCode= "I237",  ItemName= "Amritsari Chola",  Quantity= "2",  Rate= "225",  Amount= "=PRODUCT(C4,D4)"},
        new { ItemCode= "I291",  ItemName= "Asian Mixed Entree Platt",  Quantity= "3",  Rate= "165",  Amount= "=PRODUCT(C5,D5)"},
        new { ItemCode= "I268",  ItemName= "Chinese Combo Chicken",  Quantity= "3",  Rate= "125",  Amount= "=PRODUCT(C6,D6)"},
        new { ItemCode= "I251",  ItemName= "Chivas Regal",  Quantity= "1",  Rate= "325",  Amount= "=PRODUCT(C7,D7)"},
        new { ItemCode= "I256",  ItemName= "Chicken Drumsticks",  Quantity= "2",  Rate= "180",  Amount= "=PRODUCT(C8,D8)"},
        new { ItemCode= "I232",  ItemName= "Manchow Soup",  Quantity= "2",  Rate= "160",  Amount= "=PRODUCT(C9,D9)"},
        new { ItemCode= "I290",  ItemName= "Schezuan Chicken",  Quantity= "3",  Rate= "180",  Amount= "=PRODUCT(C10,D10)"},
    };
    ViewBag.DefaultData = data;
    return View();
}

After running the sample, verify that cells in column E cannot enter edit mode and that edited changes in column D are not saved.

Limitations

  • Text overflow in cells is not supported while editing.

See Also