Selection in ASP.NET Core Spreadsheet Control

28 Jul 202620 minutes to read

Selection highlights the cell, row, or column you select. You can perform selection using mouse, touch, or keyboard interactions. To enable selection, set the mode property in selectionSettings to Single or Multiple. Set it to None to disable UI selection.

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 example demonstrates how to select the fifth row using the selectRange method.

<ejs-spreadsheet id="spreadsheet" created="createHandler">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
            <e-spreadsheet-columns>
                <e-spreadsheet-column width="130"></e-spreadsheet-column>
                <e-spreadsheet-column width="92"></e-spreadsheet-column>
                <e-spreadsheet-column width="96"></e-spreadsheet-column>
            </e-spreadsheet-columns>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<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 IActionResult 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 example demonstrates how to select the third column using the selectRange method.

<ejs-spreadsheet id="spreadsheet" created="createHandler">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
            <e-spreadsheet-columns>
                <e-spreadsheet-column width="130"></e-spreadsheet-column>
                <e-spreadsheet-column width="92"></e-spreadsheet-column>
                <e-spreadsheet-column width="96"></e-spreadsheet-column>
            </e-spreadsheet-columns>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

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

<ejs-button id="getSelectedCellValues" content="Get Selected Cell Values"></ejs-button>
<ejs-spreadsheet id="spreadsheet">
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<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 IActionResult 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.

<ejs-spreadsheet id="spreadsheet" created="createHandler" cellEdit="cellEdit">
    <e-spreadsheet-selectionsettings mode="None"></e-spreadsheet-selectionsettings>
    <e-spreadsheet-sheets>
        <e-spreadsheet-sheet>
            <e-spreadsheet-ranges>
                <e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
            </e-spreadsheet-ranges>
            <e-spreadsheet-columns>
                <e-spreadsheet-column width="130"></e-spreadsheet-column>
                <e-spreadsheet-column width="92"></e-spreadsheet-column>
                <e-spreadsheet-column width="96"></e-spreadsheet-column>
            </e-spreadsheet-columns>
        </e-spreadsheet-sheet>
    </e-spreadsheet-sheets>
</ejs-spreadsheet>

<script>
    function createHandler() {
        //Applies format to specified range
        this.cellFormat({ fontWeight: 'bold' }, 'A1:D1');
    }
    function cellEdit (args){
        args.cancel = true;
    }
</script>
public IActionResult 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.