Selection in ASP.NET MVC Spreadsheet Control

28 Jul 202618 minutes to read

Selection highlights the selected cell, row, or column in the Spreadsheet. You can select cells using mouse, touch, or keyboard interactions.

Set the mode property in selectionSettings to Single or Multiple to enable selection. Set it to None to disable selection through the user interface.

The following table lists the available selection modes:

Mode Description
Single Allows only one cell to be selected at a time.
Multiple Allows multiple cells, rows, columns, or ranges to be selected.
None Disables selection through the user interface.

NOTE

The default value of the mode property in selectionSettings is Multiple.

The Spreadsheet supports the following types of selection:

  • Cell selection
  • Row selection
  • Column selection

Cell selection

Cell selection allows you to select one or more cells. Use the selectRange() method to select a cell or range programmatically.

You can select cells through the user interface in the following ways:

  • Click a cell or use the arrow keys to navigate to it.
  • To select a range, click a cell and drag across the required cells. Alternatively, hold Shift and use the arrow keys.
  • To select non-adjacent cells or ranges, hold Ctrl and click the required cells.

You can also enter a cell reference or range name in the Name box, located to the left of the formula bar. Alternatively, press Ctrl + G to open the Go To dialog and navigate to a named or unnamed cell range.

Row selection

Row selection allows you to select one or more rows.

You can select rows through the user interface in the following ways:

  • Click a row header to select the row.
  • To select multiple adjacent rows, click a row header and drag across the required row headers. Alternatively, hold Shift and use the arrow keys.
  • To select non-adjacent rows, hold Ctrl and click the required row headers.
  • Use the selectRange() method to select rows programmatically.

The following sample shows the row selection in the spreadsheet, here selecting the 5th row using the selectRange method.

@Html.EJS().Spreadsheet("spreadsheet").Created("createHandler").SelectionSettings(selectionSettings =>
{
   selectionSettings.Mode(SelectionMode.Multiple);
}).Sheets(sheet =>
    {
        sheet.Ranges(ranges =>
        {
            ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();

}).Columns(column =>
{
    column.Width(130).Add();
    column.Width(92).Add();
    column.Width(96).Add();
}).Add();
}).Render()

<script>
    function createHandler() {
        //Applies format to specified range
        this.cellFormat({ fontWeight: 'bold' }, 'A1:D1');
        var colCount = this.getActiveSheet().colCount;
        this.selectRange(ej.spreadsheet.getRangeAddress([4, 0, 4, colCount]));
    }
</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { ExpenseType= "Housing", ProjectedCost= "7000", ActualCost= "7500", Difference= "-500" },
        new { ExpenseType= "Transportation", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Insurance", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Food", ProjectedCost= "2000", ActualCost= "1800", Difference= "200" },
        new { ExpenseType= "Pets", ProjectedCost= "300", ActualCost= "200", Difference= "100" },
        new { ExpenseType= "Personel Care", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Loan", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Tax", ProjectedCost= "200", ActualCost= "200", Difference= "0" },
        new { ExpenseType= "Savings", ProjectedCost= "1000", ActualCost= "900", Difference= "100" },
        new { ExpenseType= "Total", ProjectedCost= "13500", ActualCost= "13600", Difference= "-100" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Column selection

Column selection allows you to select one or more columns.

You can select columns through the user interface in the following ways:

  • Click a column header to select the column.
  • To select multiple adjacent columns, click a column header and drag across the required column headers. Alternatively, hold Shift and use the arrow keys.
  • To select non-adjacent columns, hold Ctrl and click the required column headers.
  • Use the selectRange() method to select columns programmatically.

The following sample shows the column selection in the spreadsheet, here selecting the 3rd column using the selectRange method.

@Html.EJS().Spreadsheet("spreadsheet").Created("createHandler").SelectionSettings(selectionSettings =>
{
   selectionSettings.Mode(SelectionMode.Multiple);
}).Sheets(sheet =>
    {
        sheet.Ranges(ranges =>
        {
            ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();

}).Columns(column =>
{
    column.Width(130).Add();
    column.Width(92).Add();
    column.Width(96).Add();
}).Add();
}).Render()

<script>
    function createHandler() {
        //Applies format to specified range
        this.cellFormat({ fontWeight: 'bold' }, 'A1:D1');
        var rowCount = this.getActiveSheet().rowCount;
        this.selectRange(ej.spreadsheet.getRangeAddress([0, 2, rowCount, 2]));
    }
</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { ExpenseType= "Housing", ProjectedCost= "7000", ActualCost= "7500", Difference= "-500" },
        new { ExpenseType= "Transportation", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Insurance", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Food", ProjectedCost= "2000", ActualCost= "1800", Difference= "200" },
        new { ExpenseType= "Pets", ProjectedCost= "300", ActualCost= "200", Difference= "100" },
        new { ExpenseType= "Personel Care", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Loan", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Tax", ProjectedCost= "200", ActualCost= "200", Difference= "0" },
        new { ExpenseType= "Savings", ProjectedCost= "1000", ActualCost= "900", Difference= "100" },
        new { ExpenseType= "Total", ProjectedCost= "13500", ActualCost= "13600", Difference= "-100" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Get selected cell values

You can select single or multiple cells, rows, or columns using mouse and keyboard interactions. You can also programmatically perform selections using the selectRange method. This selection behavior is controlled by the selectionSettings property. Finally, you can retrieve the selected cell values as a collection using the getData method.

The following example demonstrates how to retrieve the values from the selected cells as a collection programmatically:

@Html.EJS().Button("getSelectedCellValues").Content("Get Selected Cell Values").Render()
@Html.EJS().Spreadsheet("spreadsheet").Sheets((sheet) =>
{
    sheet.Ranges((ranges) =>
    {
        ranges.DataSource(@ViewBag.DefaultData).Add();
    }).Add();
}).Render()

<script>

    document.getElementById("getSelectedCellValues").addEventListener('click', function () {
        var spreadsheet = document.getElementById("spreadsheet").ej2_instances[0];
        var sheet = spreadsheet.getActiveSheet();
        var selectedRange = sheet.selectedRange;
        var index = ej.spreadsheet.getRangeIndexes(selectedRange);
        var cellRange = ej.spreadsheet.getSwapRange(index);
        var swappedRange = ej.spreadsheet.getRangeAddress(cellRange);
        var valueObject = [];
        var range = sheet.name + '!' + swappedRange;
        // Get the collection of selected cell values by using the getData() method.
        spreadsheet.getData(range).then((cells) => {
            cells.forEach((cell) => {
                valueObject.push(ej.base.isNullOrUndefined(cell.value) ? '' : cell.value);
            });
            console.log("Collection of selected cell values:", valueObject);
        });
    });

</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { CustomerName= "Romona Heaslip",  Model= "Taurus",  Color= "Aquamarine",  PaymentMode= "Debit Card",  DeliveryDate= "07/11/2015",  Amount= "8529.22" },
        new { CustomerName= "Clare Batterton",  Model= "Sparrow",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/13/2016",  Amount= "17866.19" },
        new { CustomerName= "Eamon Traise",  Model= "Grand Cherokee",  Color= "Blue",  PaymentMode= "Net Banking",  DeliveryDate= "09/04/2015",  Amount= "13853.09" },
        new { CustomerName= "Julius Gorner",  Model= "GTO",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/15/2017",  Amount= "2338.74" },
        new { CustomerName= "Jenna Schoolfield",  Model= "LX",  Color= "Yellow",  PaymentMode= "Credit Card",  DeliveryDate= "10/08/2014",  Amount= "9578.45" },
        new { CustomerName= "Marylynne Harring",  Model= "Catera",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "7/01/2017",  Amount= "19141.62" },
        new { CustomerName= "Vilhelmina Leipelt",  Model= "7 Series",  Color= "Goldenrod",  PaymentMode= "Credit Card",  DeliveryDate= "12/20/2015",  Amount= "6543.30" },
        new { CustomerName= "Barby Heisler",  Model= "Corvette",  Color= "Red",  PaymentMode= "Credit Card",  DeliveryDate= "11/24/2014",  Amount= "13035.06" },
        new { CustomerName= "Karyn Boik",  Model= "Regal",  Color= "Pink",  PaymentMode= "Debit Card",  DeliveryDate= "05/12/2014",  Amount= "18488.80" },
        new { CustomerName= "Jeanette Pamplin",  Model= "S4",  Color= "Fuscia",  PaymentMode= "Net Banking",  DeliveryDate= "12/30/2014",  Amount= "12317.04" },
        new { CustomerName= "Cristi Espinos",  Model= "TL",  Color= "Aquamarine",  PaymentMode= "Credit Card",  DeliveryDate= "12/18/2013",  Amount= "6230.13" },
        new { CustomerName= "Issy Humm",  Model= "Club Wagon",  Color= "Pink",  PaymentMode= "Cash On Delivery",  DeliveryDate= "02/02/2015",  Amount= "9709.49" },
        new { CustomerName= "Tuesday Fautly",  Model= "V8 Vantage",  Color= "Crimson",  PaymentMode= "Debit Card",  DeliveryDate= "11/19/2014",  Amount= "9766.10" },
        new { CustomerName= "Rosemaria Thomann",  Model= "Caravan",  Color= "Violet",  PaymentMode= "Net Banking",  DeliveryDate= "02/08/2014",  Amount= "7685.49" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Disable Selection

Set the mode property in selectionSettings to None to disable selection through the user interface.

The following example demonstrates how to disable UI selection in the Spreadsheet.

@Html.EJS().Spreadsheet("spreadsheet").Created("createHandler").CellEdit("cellEdit").SelectionSettings(selectionSettings =>
{
   selectionSettings.Mode(SelectionMode.None);
}).Sheets(sheet =>
    {
        sheet.Ranges(ranges =>
        {
            ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();

}).Columns(column =>
{
    column.Width(130).Add();
    column.Width(92).Add();
    column.Width(96).Add();
}).Add();
}).Render()

<script>
    function createHandler() {
        //Applies format to specified range
        this.cellFormat({ fontWeight: 'bold' }, 'A1:D1');
    }
    function cellEdit (args){
        args.cancel = true;
    }
</script>
public ActionResult Index()
{
    List<object> data = new List<object>()
    {
        new { ExpenseType= "Housing", ProjectedCost= "7000", ActualCost= "7500", Difference= "-500" },
        new { ExpenseType= "Transportation", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Insurance", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Food", ProjectedCost= "2000", ActualCost= "1800", Difference= "200" },
        new { ExpenseType= "Pets", ProjectedCost= "300", ActualCost= "200", Difference= "100" },
        new { ExpenseType= "Personel Care", ProjectedCost= "500", ActualCost= "500", Difference= "0" },
        new { ExpenseType= "Loan", ProjectedCost= "1000", ActualCost= "1000", Difference= "0" },
        new { ExpenseType= "Tax", ProjectedCost= "200", ActualCost= "200", Difference= "0" },
        new { ExpenseType= "Savings", ProjectedCost= "1000", ActualCost= "900", Difference= "100" },
        new { ExpenseType= "Total", ProjectedCost= "13500", ActualCost= "13600", Difference= "-100" },
    };
    ViewBag.DefaultData = data;
    return View();
}

Limitations

  • Selecting all cells using Ctrl + A is not supported. Use the Select All button in the top-left corner of the worksheet instead.