Export excel with checkbox selection and paging

[Ranjith kumar]

Noticed excel export of selected rows across pages not working, is there a work around for this issue?

[Alexander]

Hi Ranjith,

If you would like to export only the selected rows to Excel, I recommend following the example set in the following Knowledge base:

• Export Checked Columns Only in Grid

Please note that the logic is entirely in JavaScript, which is why the linked resources are for the jQuery suite but the same approach is also applicable to the Telerik UI for ASP.NET Core suite.

That being said particularly for the Telerik UI for ASP.NET Core Grid, you can achieve the desired result by preventing the default excel export event, getting the selected items, and passing them to a workbook instance that will be exported.

It is important to note that to take full advantage of the Excel export feature, it is mandatory to download the JSZip library and include the file before the Kendo UI JavaScript files. This is outlined in step 3 of the Grid Excel Export article:

• Grid Excel Export Documentation

Here is an exemplary configuration:

Grid definition:

<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.js"></script>
@(Html.Kendo().Grid<Kendo.Mvc.Examples.Models.ProductViewModel>()
    .Name("grid")
    .Columns(columns =>
    {
        columns.Select().Width(50);
        ...
    })
    .ToolBar(t=>t.Excel())
    .Excel(e=>e.AllPages(true))
    .Pageable()
    .PersistSelection()
    .Events(ev => ev.ExcelExport("onExcelExport"))
    ...
)

JavaScript:

<script>
    function onExcelExport(e){
        e.preventDefault();
        var grid = e.sender;
        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.
            })
        })
  
        var rows = [{ // Predefine the cell headers that will be included in the excel file.
          cells: [
            { value: "productName" },
            { value: "category" }
          ]
        }];
       
        for (var i = 0; i < selectedDataItems.length; i++) { // Traverse through the selected data items.
            rows.push({ // Push the cells of each data item instance respectively.
              cells: [
                { value: selectedDataItems[i].ProductName },
                { value: selectedDataItems[i].Category.CategoryName }
              ]
            })

        }
        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 }
              ],
              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 your convenience, here is a Telerik REPL that tackles the mentioned above:

• Telerik REPL for ASP.NET Core Example

I hope this helps.

Kind Regards,
Alexander
Progress Telerik

[Ranjith kumar]

Hi Alexander,

How do I set the headers in the exported excel while I use grid with dynamic columns as I don't know the column names prior?

 

Thanks,
Ranjith

[Alexander]

Hi Ranjith,

I noticed that you have opened a separate support thread regarding the mentioned inquiry. Nevertheless, for the benefit of the community I will post my answer here as well:

Nevertheless, I have created a custom solution that achieves the desired outcome using the following approach:

• Subscribe to the Excel Export event.

.Events(events => events.ExcelExport("onGridExcelExport"))

• Within the handler, obtain the to-be-added cell column using the built-in columns method the Grid provides:

  var gridColumns = e.sender.columns.map(column => {
            return {value: column.title}; // 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.

• Get the selected items:

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

• Push the cell headers from the previously obtained column names (highlighted in green):

var rows = [{ // Predefine the cell headers that will be included in the excel file.
            cells: gridColumns // Pass in the previously obtained columns.
}];

• Loop through each of the selected items and push their columns values using the help of the previously obtained Grid columns (highlighted in blue):

 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.
               cells.push({
                   value: item[field.value]  // Push the selected item's respective column values.
               })
            })
            rows.push({cells: cells}); // Push the rows.
});

• Create a common function that will be responsible for creating the workbook document whilst passing the previously instantiated rows (highlighted in orange):

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

Here is a snippet of how the full implementation would look like:

.Events(events => events.ExcelExport("onGridExcelExport"))

<script>
    function onExcelExport(e) {
        e.preventDefault();
        var grid = e.sender;
        
        var gridColumns = e.sender.columns.map(column => {
            return {value: column.title}; // 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.OrderID; // 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.
               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>

I would also like to mention that the mentioned above is considered a rather custom implementation. Thus, it does not guarantee any future drawbacks from arising.