Exporting all the columns to Excel

[Reid]

I would like to know how to export all the columns (or a set) to an Excel file.  I have searched this ans it states the columns must be added as fields.  I added this with visible(false) and the same for the columns but those fields do not get exported.  The Read() method of the datasource is returning all the columns but only a select few are showing by design.

[Alex Hajigeorgieva]

Hi, Reid,

The Kendo UI Grid excel export is designed to export the visible data indeed and that includes the filter, sort, group and page as well.

To export cells which are part of the data source but not of the grid, you may intercept the excelExport event of the grid and use the data to build a new workbook:

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

<script>
  function onExcelExport(e){
     var sheet = e.workbook.sheets[0];
     var data = e.data;
     var gridColumns = e.sender.columns;
     var columns = gridColumns.map(function(col){
    return {
       value: col.title ? col.title : col.field,
       autoWidth:true,
       background: "#7a7a7a",
       color: "#fff"
     };
   });

   var rows = [{cells:columns, type: "header"}];

   for (var i = 0; i < data.length; i++){
     var rowCells = [];
   for(var j=0;j < gridColumns.length;j++){
     var cellValue = data[i][gridColumns[j].field];
       rowCells.push({value: cellValue});
   }
     rows.push({cells: rowCells, type: "data"});
   }
 sheet.rows = rows;
   }
</script>

We have a runnable example and an article here:

https://docs.telerik.com/kendo-ui/knowledge-base/grid-excel-export-entire-data

Let me know in case you have further questions.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Reid]

Hi Alex,

Just wanted to post that I have not gotten to this yet, I got sidetracked.  Thanks for posting it.  I will visit this in the next few days.

 

[Reid]

Hi Alex,

I just implemented this code above and added this to the events declaration of the grid.

.Events(events => events
   .Change("onChange")
   .Edit("onEdit")
   .ExcelExport("onExcelExport")
)

 

But the additional columns not declared in the grid but are contained in the dataset do not export.  Only the visible columns are in the Excel spreadsheet.

I also trying adding an additional column and set it's visibility to false and it still does not get exported.

columns.Bound(o => o.UpdateHistory).Title("Update History").Width(250).Visible(false);

 

The demo you list when displayed in Dojo does not export any additional data, only columns already contained in the grid.

 

Please advise.

 

 

[Alex Hajigeorgieva]

Hello, Reid,

You are correct, indeed, we need to replace some of the logic there to get the desired result. Instead of using the e.sender.columns object, we can take an item and transform it into a column object:

function generateColumns(item) {
        var columnNames = Object.keys(item.toJSON());
        return columnNames.map(function (name) {
            var isIdColumn = name.indexOf("ID") > -1 || name.indexOf("Id") > -1;
            return {
                field: name,
                width: isIdColumn ? 50 : 180,
                title: isIdColumn ? "Id" : name
            };
        });
    }
  function onExcelExport(e){
     var sheet = e.workbook.sheets[0];
      var data = e.data;
      var columnsToExport = generateColumns(data[0]);
      var columns = columnsToExport.map(function(col){
    return {
       value: col.title ? col.title : col.field,
       autoWidth:true,
       background: "#7a7a7a",
       color: "#fff"
     };
   });

   var rows = [{cells:columns, type: "header"}];

   for (var i = 0; i < data.length; i++){
     var rowCells = [];
    for(var j=0;j < columnsToExport.length; j++){
        var cellValue = data[i][columnsToExport[j].field];
       rowCells.push({value: cellValue});
   }
      rows.push({cells: rowCells, type: "data"});
   }
 sheet.rows = rows;
   }
</script>

Let me know if this approach works well in your project too or in case you need to change anything about it.

Regards,
Alex Hajigeorgieva
Progress Telerik

[Ingerid]

Hi! I have the same problem, but my grid also has grouped columns, and the Excel file should reflect this. I managed to solve most of this issue, except autofiltering. Here's my event function

<script>
    function excelExport(e) {
        header1 = this.columns.map(function (kolonne) {
            return {
                value: kolonne.title,
                background: "#7a7a7a",
                color: "#fff",
                colSpan: kolonne.columns == undefined ? 1 : kolonne.columns.length,
                rowSpan: kolonne.columns == undefined ? 2 : 1,
            };
        });
        let rows = [{ cells: header1, type: "header" }]

        let secondaryColumns = [];
        for (var i = 0; i < this.columns.length; i++) {
            if (this.columns[i].columns != undefined) {
                for (var j = 0; j < this.columns[i].columns.length; j++) {
                    secondaryColumns.push(this.columns[i].columns[j]);
                }
            }
        }
        header2 = secondaryColumns.map(function (kolonne) {
            return {
                value: kolonne.title,
                background: "#7a7a7a",
                color: "#fff",
            };
        });
        rows.push({ cells: header2, type: "header" });

        let datakolonner = [];
        for (var i = 0; i < this.columns.length; i++) {
            if (this.columns[i].columns == undefined) {
                datakolonner.push(this.columns[i].field);
            } else {
                for (var j = 0; j < this.columns[i].columns.length; j++) {
                    datakolonner.push(this.columns[i].columns[j].field);
                }
            }
        }

        let data = e.data;
        for (let i = 0; i < data.length; i++) {
            let rowCells = [];
            for (let j = 0; j < datakolonner.length; j++) {
                let cellValue = data[i][datakolonner[j]];
                rowCells.push({ value: cellValue });
            }
            rows.push({ cells: rowCells, type: "data" });
        }

        let sheet = e.workbook.sheets[0];
        sheet.rows = rows;
        sheet.columns = Array.apply(null, Array(datakolonner.length)).map(function () { return { width: 150, autoWidth: false } });
    }
</script>

[Alex Hajigeorgieva]

Hi, Ingerid,

I am pleased to let you know that we have the ability to configure which columns will be exported feature request in development. As of R1 2021 we will introduce a columns exportable property as part of the grid built-in functionality.

Until then, to include the workbook filter by specifying the filter from and to settings:

https://docs.telerik.com/kendo-ui/api/javascript/ooxml/workbook/configuration/sheets.filter

Let us know in case you have further questions.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik