New to Telerik UI for ASP.NET Core? Download free 30-day trial

Exporting Checked Columns Only in the Grid

Environment

Product Telerik UI for ASP.NET Core Grid
Progress Telerik UI for ASP.NET Core version Created with the 2022.3.1109 version

Description

How can I create Excel documents by exporting the checked columns only in the Telerik UI for ASP.NET Core Grid component?

Solution

To achieve the desired scenario:

  1. To handle the Excel export of the Grid, subscribe to the ExcelExport event.
  2. Enable the rows' persistence upon selection by using the .PersistSelection configuration method.
  3. Within the handler, obtain the fields of the columns that you are going to add by using the client-side .columns() method the Grid provides and map them to a key-value pair by using the .map() method.
  4. Push the cell headers from the previously obtained column fields.
  5. From there, get the selected Grid rows by using the built-in .selectedKeyNames() method, traverse through each of the items, and push their column values with the help of the previously obtained Grid column fields.
  6. Create a common function that will be responsible for creating the workbook document.
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.js"></script> // To take full advantage of the Excel export   feature, download the JSZip library and include the file.

    @(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
        .Name("grid")
        .Columns(columns =>
        {
            columns.Select().Width(50);
            columns.Bound(p => p.ProductName);
            columns.Bound(p => p.UnitPrice).Width(100);
            columns.Bound(p => p.UnitsInStock).Width(100);
            columns.Bound(p => p.Discontinued).Width(100);
        })
        .ToolBar(t => t.Excel())
        .Excel(e => e.AllPages(true))
        .Pageable()
        .PersistSelection()
        .Sortable()
        .Events(events => events.ExcelExport("onExcelExport"))
        .DataSource(dataSource => dataSource
            .Ajax()
            .Model(model => model.Id(p => p.ProductID))
            .Read(read => read.Action("Selection_Read", "Grid"))
        )
    )
    <script>
        function onExcelExport(e) {
            e.preventDefault();
            var grid = e.sender;

            var gridColumns = e.sender.columns.map(column => {
                return {value: column.field}; // Map the columns names to an object that will be later passed to the workbook rows.
            }).filter(item => item.value != undefined); // Filter the grid columns in order to remove the select row.

            var selectedIds = grid.selectedKeyNames(); // Get the selected rows.
            var selectedDataItems = e.data.filter((el) => {
                return selectedIds.some((f) => {
                    return f == el.ProductID; // Filter the selected data items based on the id field.
                })
            })

            var rows = [{ // Predefine the cell headers that will be included in the excel file.
                cells: gridColumns // Pass in the previously obtained columns.
            }];
            var rowValues = selectedDataItems.forEach(item => { // Traverse through each of the selected items.
                cells = []; // Store the to-be-defined cells.
                gridColumns.forEach(field => { // Loop through each of the column fields.
                   console.log(field.value);
                   cells.push({
                       value: item[field.value]  // Push the selected item's respective column values.
                   })
                })
                rows.push({cells: cells}); // Push the rows.
            });
            excelExport(rows) // Call a common function for creating the excel workbook whilst passing the previosly pushed rows.
        }
        function excelExport(rows) {
            var workbook = new kendo.ooxml.Workbook({ // Create a Worbook.
                sheets: [
                    {
                        columns: [
                            { autoWidth: true },
                            { autoWidth: true },
                            { autoWidth: true },
                            { autoWidth: true },
                            { autoWidth: true },
                            { autoWidth: true },
                        ],
                        title: "Orders",
                        rows: rows // Specify the rows from the function argument.
                    }
                ]
            });
            kendo.saveAs({ dataURI: workbook.toDataURL(), fileName: "Test.xlsx" }); // Export the Excel file.
        }
    </script>

For the complete implementation of the suggested approach, refer to the Telerik REPL example on exporting checked columns only in the Grid.

See Also

In this article