This is a migrated thread and some comments may be shown as answers.

Exporting all the columns to Excel

6 Answers 323 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Reid
Top achievements
Rank 2
Reid asked on 14 Jan 2019, 11:55 PM
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.

6 Answers, 1 is accepted

Sort by
0
Alex Hajigeorgieva
Telerik team
answered on 17 Jan 2019, 03:05 PM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Reid
Top achievements
Rank 2
answered on 30 Jan 2019, 05:35 PM

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.

 

0
Reid
Top achievements
Rank 2
answered on 05 Feb 2019, 03:55 PM

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.

 

 

0
Accepted
Alex Hajigeorgieva
Telerik team
answered on 08 Feb 2019, 04:25 PM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
Ingerid
Top achievements
Rank 1
Veteran
answered on 03 Nov 2020, 11:21 AM
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>
0
Alex Hajigeorgieva
Telerik team
answered on 06 Nov 2020, 10:25 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Tags
Grid
Asked by
Reid
Top achievements
Rank 2
Answers by
Alex Hajigeorgieva
Telerik team
Reid
Top achievements
Rank 2
Ingerid
Top achievements
Rank 1
Veteran
Share this question
or