Cannot export hidden columns in Kendo Grid

Question

I want to hide some columns on Kendo Grid and export them to the excel as the visible columns. However, using Hidden(true) or Visible(false) does not make any sense and these fields are not exported. The workarounds on this page is not working. Any idea?

View:

@(Html.Kendo().Grid<ContactViewModel>()
    .Name("Grid")
    .Columns(columns =>
        {
            columns.Bound(m => m.NameSurname).Title("Name Surname").Width("%100");
            columns.Bound(m => m.InstituteName).Title("Institute Name").Width("250px");
            columns.Bound(m => m.CityName).Title("City").Width("145px");
            columns.Bound(m => m.RegionName).Title("Region").Width("145px");
            columns.Bound(m => m.ContactMobile).Title("Mobile").Width("125px");
            columns.Bound(m => m.ContactAddress).Title("Address").Hidden(true); //I want to export these fields
            columns.Bound(m => m.ContactAddress).Title("Address").Visible(false); //I want to export these fields    
        })
    .ToolBar(toolbar =>
        {
            toolbar.Template(@<text>
                <div class="toolbar">                        
                    <button class="btn btn-primary btn-xs pull-right k-button k-button-icontext k-grid-excel">
                        <span class="k-icon k-excel"></span>
                        Liste (xls)
                    </button>
                </div>
            </text>);
        })

    .Excel(excel => excel
        .FileName("List.xlsx")
        .Filterable(true)
        .AllPages(true)
        .ProxyURL(Url.Action("Excel_Export_Save", "Controller"))
    )
    .DataSource(dataSource => dataSource
        .Ajax()
        .Read(read => read.Action("Index_Read", "Controller"))
        .ServerOperation(false) 
        .PageSize(12)
        )
    )
)

score 3 · Answer 1 · answered Mar 31 '15 at 10:05

See this solution Plunker, suggested solution on Telerik website. To show column in your export functionality, bind this 'excelExport' event of that grid.

var exportFlag = false;
$("#grid").data("kendoGrid").bind("excelExport", function (e) {
    if (!exportFlag) {
    //  e.sender.showColumn(0); for demo
    // for your case show column that you want to see in export file
        e.sender.showColumn(5);
        e.sender.showColumn(6);
        e.preventDefault();
        exportFlag = true;
        setTimeout(function () {
            e.sender.saveAsExcel();
        });
    } else {
        e.sender.hideColumn(5);
        e.sender.hideColumn(6);
        exportFlag = false;
    }
});

Demo: Hide First column and show in export file

<!DOCTYPE html>
<html>

<head>
  <base href="http://demos.telerik.com/kendo-ui/grid/excel-export">
  <style>
    html {
      font-size: 12px;
      font-family: Arial, Helvetica, sans-serif;
    }
  </style>
  <title></title>
  <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.common-material.min.css" />
  <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.material.min.css" />
  <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.dataviz.min.css" />
  <link rel="stylesheet" href="http://cdn.kendostatic.com/2015.1.318/styles/kendo.dataviz.material.min.css" />

  <script src="http://cdn.kendostatic.com/2015.1.318/js/jquery.min.js"></script>
  <script src="http://cdn.kendostatic.com/2015.1.318/js/jszip.min.js"></script>
  <script src="http://cdn.kendostatic.com/2015.1.318/js/kendo.all.min.js"></script>
</head>

<body>
  <div id="example">
    <div id="grid" style="width: 900px"></div>
    <script>
      $("#grid").kendoGrid({
        toolbar: ["excel"],
        excel: {
          fileName: "Kendo UI Grid Export.xlsx",
          proxyURL: "http://demos.telerik.com/kendo-ui/service/export",
          filterable: true
        },
        dataSource: {
          type: "odata",
          transport: {
            read: "http://demos.telerik.com/kendo-ui/service/Northwind.svc/Products"
          },
          schema: {
            model: {
              fields: {
                UnitsInStock: {
                  type: "number"
                },
                ProductName: {
                  type: "string"
                },
                UnitPrice: {
                  type: "number"
                },
                UnitsOnOrder: {
                  type: "number"
                },
                UnitsInStock: {
                  type: "number"
                }
              }
            }
          },
          pageSize: 7
        },
        sortable: true,
        pageable: true,
        columns: [{
          width: "10%",
          field: "ProductName",
          title: "Product Name",
          hidden: true
        }, {
          width: "10%",
          field: "UnitPrice",
          title: "Unit Price"
        }, {
          width: "10%",
          field: "UnitsOnOrder",
          title: "Units On Order"
        }, {
          width: "10%",
          field: "UnitsInStock",
          title: "Units In Stock"
        }]
      });
      
      
      var exportFlag = false;
$("#grid").data("kendoGrid").bind("excelExport", function (e) {
    if (!exportFlag) {
     
        e.sender.showColumn(0);
        e.preventDefault();
        exportFlag = true;
        setTimeout(function () {
            e.sender.saveAsExcel();
        });
    } else {
        e.sender.hideColumn(0);
        exportFlag = false;
    }
});
    </script>
  </div>


</body>

</html>

Thanks for reply. But, before creating this issue, I had applied all the steps mentioned on that page. On the other hand, as you see I use wraper instead of javascript for building grid. So, how can I adapt the javascript method to mt grid (@(Html.Kendo().Grid())? — Jack, Mar 31 '15 at 11:15
creating kendo grid code-mvc code will be going to remain as it is with Hidden(true); for columns that you don't want to see — 111, Mar 31 '15 at 11:25
You just try by adding some jquery code: bind 'excelExport' event handler of that grid to show/hide grid column.. as I show you in my example — 111, Mar 31 '15 at 11:28
When I use click event of the excel button the code hits the javascript function while it does not hit for grid. On the other hand, as there is no argument for the button as on grid, I need the working example on my code above. Could you update your answer how to apply this script to my kendo grid in mvc? — Jack, Mar 31 '15 at 12:10
When you click on Export button,add some your jQuery code [using $("#grid").data("kendoGrid").bind("excelExport", function (e) { });] to set show/hide coulmn setting for kendo grid, while URL of the server side proxy [MVC Action Method remain as it is] which will stream the file to the end user. So try to add my first jQuery code snippet. — 111, Mar 31 '15 at 12:22
I call this method from the onClick event of the Excel button. Although it seems to work, in this case the hidden columns appears and it remains after saving the excel file. Because the code cannot pass after this line: $("#grid").data("kendoGrid").bind("excelExport", function (e) {... So, could you please post the code you mean? — Jack, Mar 31 '15 at 13:01

score 1 · Answer 2 · answered Apr 02 '15 at 04:10

I try with this example also, it is same as my previous answer just jQuery binding event will be different.

You just need to do changes in code by adding grid event Events(x => x.ExcelExport("excelExport")) and jQuery function excelExport(e) {}. Also use only Hidden(true) to hide grid column.

ViewModel is something like this :

 public class ContactViewModel
    {
        public string NameSurname { get; set; }

        public string InstituteName { get; set; }

        public string CityName { get; set; }

        public string RegionName { get; set; }

        public string ContactMobile { get; set; }

        public string ContactAddress { get; set; }
    }

Controller will be:

public class TestController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Index_Read([DataSourceRequest]DataSourceRequest request)
        {
            var listOfContactViewModel = new List<ContactViewModel>() { 
            new ContactViewModel(){ NameSurname  = "N1", InstituteName  = "I1", CityName ="C1",RegionName = "R1",ContactMobile = "M1", ContactAddress = "C1" },
            new ContactViewModel(){ NameSurname  = "N2", InstituteName  = "I2", CityName ="C2",RegionName = "R2",ContactMobile = "M2", ContactAddress = "C2" },
            new ContactViewModel(){ NameSurname  = "N3", InstituteName  = "I3", CityName ="C3",RegionName = "R3",ContactMobile = "M3", ContactAddress = "C3" },
            new ContactViewModel(){ NameSurname  = "N4", InstituteName  = "I4", CityName ="C4",RegionName = "R4",ContactMobile = "M4", ContactAddress = "C4" },
            new ContactViewModel(){ NameSurname  = "N5", InstituteName  = "I5", CityName ="C5",RegionName = "R5",ContactMobile = "M5", ContactAddress = "C5" }
            };

            return Json(listOfContactViewModel.ToDataSourceResult(request),JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public ActionResult Excel_Export_Save(string contentType, string base64, string fileName)
        {
            var fileContents = Convert.FromBase64String(base64);
            return File(fileContents, contentType, fileName);
        }
    }

And View for this:

<h2>Index</h2>

@(Html.Kendo().Grid<KendoUIMVC5.Models.ContactViewModel>()
    .Name("Grid")
    .Events(x => x.ExcelExport("excelExport"))
    .Columns(columns =>
        {
            columns.Bound(m => m.NameSurname).Title("Name Surname").Width("%100");
            columns.Bound(m => m.InstituteName).Title("Institute Name").Width("250px");
            columns.Bound(m => m.CityName).Title("City").Width("145px");
            columns.Bound(m => m.RegionName).Title("Region").Width("145px");
            columns.Bound(m => m.ContactMobile).Title("Mobile").Width("125px");
            columns.Bound(m => m.ContactAddress).Title("Address").Hidden(true); //I want to export these fields
            columns.Bound(m => m.ContactAddress).Title("Address").Hidden(false); //I want to export these fields
        })
      .ToolBar(toolbar =>
        {
            toolbar.Template(@<text>
                <div class="toolbar">
                    <button class="btn btn-primary btn-xs pull-right k-button k-button-icontext k-grid-excel">
                        <span class="k-icon k-excel"></span>
                        Liste (xls)
                    </button>
                </div>
            </text>);
        })
    .Excel(excel => excel
                .FileName("List.xlsx")
                .Filterable(true)
                .AllPages(true)
                    .ProxyURL(Url.Action("Excel_Export_Save", "Test"))
            )
        .DataSource(dataSource => dataSource
            .Ajax()
            .Read(read => read.Action("Index_Read", "Test"))
            .ServerOperation(false)
            .PageSize(12)
            )
)


<script type="text/javascript">
    var exportFlag = false;
    function excelExport(e)
    {
        if (!exportFlag) {
            e.sender.showColumn(5);
            e.sender.showColumn(6);
            e.preventDefault();
            exportFlag = true;
            setTimeout(function () {
                e.sender.saveAsExcel();
            });
        } else {
            e.sender.hideColumn(5);
            e.sender.hideColumn(6);
            exportFlag = false;
        }
    }
</script>

'Kendo.Mvc.UI.Fluent.GridEventBuilder' does not contain a definition for 'ExcelExport' and no extension method 'ExcelExport' accepting a first argument of type 'Kendo.Mvc.UI.Fluent.GridEventBuilder' could be found (are you missing a using directive or an assembly reference?) error encountered :( — Jack, Apr 02 '15 at 11:18
may be Kendo.MVC versioning issue, I use v2014.3.1125.545 version of kendo.mvc. View reference of Kendo.MVC dll in object browser, there is class GridEventBuilder with ExcelExport(string handler) method, then it will work . Otherwise you have to use updated version of kendo.mvc — 111, Apr 03 '15 at 04:27
Yes, it seems to be version problem... I think there is no way to perform this by adding a javascript method, etc. is not it? Thanks... — Jack, Apr 03 '15 at 06:19

score -1 · Answer 3 · answered Feb 02 '22 at 16:56

...
columns.Bound(x => x.Id).Visible(false);
columns.Bound(x => x.Siege).Width(150);
columns.Bound(x => x.Societe).Width(150);
columns.Bound(x => x.Matricule).Width(100).Hidden(true);
columns.Bound(x => x.Civilite).Width(80);
...


var exportFlag = false;
$("#myGrid").data("kendoGrid").bind("excelExport", function (e) {

    var grid = e.sender;
    var columns = grid.columns;
      
    if (!exportFlag) {

        $.each(columns, function (index, value) {

            var col = this;
            if (col.hidden == true) {
                col.hidden = false;
            }
        });

        e.preventDefault();
        exportFlag = true;

        setTimeout(function () {
            e.sender.saveAsExcel();
        });
    }
    else {

        $.each(columns, function (index, value) {

            var col = this;
            if (col.hidden == false) {
                col.hidden = true;
            }
        });

        exportFlag = false;
    }
});

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Feb 03 '22 at 00:05

Cannot export hidden columns in Kendo Grid

3 Answers3

Linked