Hide column from grid while exporting to excel

Question

I want to hide/remove one column when the data exported to excel..But that should be visible in grid. I have tried several solutions but not able find out the exact problem.

Currently excel is generating but unable to hide the column... Can anybody please help regarding this ?? Thanks in advance.

Here is my grid code:

     @(Html.Kendo().Grid<Database.Model.UserSummaryInfo>()
                    .Name("Grid")
                    .Columns(col =>
                    {
                    col.Bound(c => c.ApplicationUserId).Hidden();
                    col.Bound(c => c.MemberId).Title("Member ID");
                    col.Bound(c => c.Visit).Title("Visit");
                    col.Bound(c => c.CreatedDate).Title("Visit Start Date");
                    col.Bound(c => c.LogInCount).Title("LogIn Count");
                    col.Bound(c => c.SurveyStatus).Title(" Survey Status");
                    col.Bound(c => 
c.ApplicationUserId).HeaderTemplate(@<text>Action</text>).ClientTemplate("# 
        if(SurveyStatus == 'Did Not Attempt') { #" + "<a  class='btn btn-primary 
        disabled' style='display: none;' href='" + Url.Action("TestDetails", 
        "Admin") + "?id=#= TestSummaryId #&Year=#=Year#'" + " >Details</a>" + "# 
        }else{#" + "<a  class='btn btn-primary enabled' style='width:60px' href='" 
        + Url.Action("TestDetails", "Admin") + "?id=#= ApplicationUserId 
         #&Year=#=Year #&testSummaryId=#=TestSummaryId#'" + ">Details</a>" + "# 
         }#")

         .HeaderHtmlAttributes(new { style = "text-align: center;font-size:18px" 
           });
                    })
        .ToolBar(toolbar => toolbar.Template(@<text>
                    <div class="pull-left index-header">Test Summary</div>
                    <button type="button" class="btn btn-primary rounded pull- 
          right margin-right-10" onclick="clearFiter()"><i class="fa fa-times- 
          circle-o margin-right-5"></i> Clear Filter</button>
                    <a style="padding-right:5px;" class="k-button-icontext k-grid- 
          excel btn btn-primary pull-right  margin-right-10" href="#"><span 
          class="fa fa-file-excel-o"></span>Export to Excel</a>
         </text>))
        .Excel(excel => excel
        .FileName(DateTime.Now.Date.ToShortDateString() + " " + 
         "GetUserSummary.xlsx")


        .AllPages(false)

        .ProxyURL(Url.Action("Excel_Export_Save", "Admin")))
        .Pageable(paging => paging.PageSizes(new int[] { 100, 500, 1000 
          }).Refresh(true).ButtonCount(5).Info(true).Input(true))
        .Sortable(sortable =>
        {
          sortable.SortMode(GridSortMode.SingleColumn);
        })
        .Groupable()
        .Scrollable(s => s.Height("auto"))
        .Filterable(filterable => filterable.Operators(operators => 
         operators.ForNumber(nmbr => nmbr.Clear().IsEqualTo("Is equal 
          to").IsLessThan("Less than").IsGreaterThan("Greater 
         than").IsNotEqualTo("Not equal to")).ForString(str => 
         str.Clear().Contains("Contains").IsEqualTo("Is equal 
         to").StartsWith("Starts with").IsNotEqualTo("Is not equal 
         to")).ForDate(date => date.Clear().IsGreaterThan("Is 
          after").IsLessThan("Is Before").IsGreaterThanOrEqualTo("Is after or 
          equal to").IsLessThanOrEqualTo("Is before or equal to"))))
        .Resizable(resize => resize.Columns(true))

        .Events(e => e.ExcelExport("Hidecolumn"))

        .DataSource(datasource =>
        datasource
        .Ajax()
        .Sort(sort => {
          sort.Add(c => c.MemberId).Ascending();
          sort.Add(c => c.Visit).Ascending();
        })
        .PageSize(10)
        .Read(read => read.Action("GetUserSummaryList", "Admin"))
        )
                            )
                        </div>
                    </div>
                </div>
                <!-- End Content -->
            </form>
        </div>

        <script>
               var exportFlag = false;
               $("#Grid").data("kendoGrid").bind("excelExport", function (e) {
                debugger;
                if (!exportFlag) {
                  e.sender.hideColumn(2);
                  e.preventDefault();
                  exportFlag = true;
                  setTimeout(function () {
                    e.sender.saveAsExcel();
                  });
                } else {
                  e.sender.showColumn(2);
                  exportFlag = false;
                }
              });


                function Hidecolumn(e) {

                  e.sender.hideColumn(2);
                 }

        </script>

Answer 1

What you would need to do is to bind to the export function and then hide the columns similar to how you have above:

var exportFlag = false;
$("#grid").data("kendoGrid").bind("excelExport", function (e) {
    if (!exportFlag) {
        e.sender.hideColumn(1);
        e.preventDefault();
        exportFlag = true;
        setTimeout(function () {
            e.sender.saveAsExcel();
        });
    } else {
        e.sender.showColumn(1);
        exportFlag = false;
    }
});

The exportFlag is due to stopping a recursive loop, because the saveAsExcel method fires the excelExport event.

Answer 2

Added this function to the excelExport event. We are knockout / jQuery but the logic should work for you.

It simply goes through all the rows in the sheet and deletes cells based on our excelHidden property.

    self.resultsExcelExport = function(e) {
        var sheet = e.workbook.sheets[0];
    
        for (var i = this.columns.length - 1; i >= 0; i--) {
            if (this.columns[i].excelHidden) {
                sheet.columns.splice(i, 1);
                sheet.rows.forEach(function(row) {
                    row.cells.splice(i, 1);
                })
            }
        }
    }

In the grid columns object add a property "excelHidden: true" to signal which columns to hide.

    {
        field: "Id",
        title: 'Download',
        excelHidden: true,
    },

UPDATE: Well that worked until I tried grouping a column. So had to lookup via title to find the correct column number to remove. Now looks like this.

    function(e) {
        var hiddenColumnsByTitle = {};
        var hiddenColumns = [];
        var sheet = e.workbook.sheets[0];
    
        this.columns.forEach(function(column) {
            if (column.excelHidden) {
                hiddenColumnsByTitle[column.title] = true;
            }
        })
    
        for (var r = 0; r < sheet.rows.length; r++) {
            var row = sheet.rows[r];
            if (row.type === "header") {
                row.cells.forEach(function(headerCell, index) {
                    var columnTitle = headerCell.value;
                    if (hiddenColumnsByTitle[columnTitle]) {
                        hiddenColumns.push(index);
                    }
                })
                break;
            }
        }
    
        for (var i = hiddenColumns.length - 1; i >= 0; i--) {
            var hiddenColumn = hiddenColumns[i];
            sheet.columns.splice(hiddenColumn, 1);
            sheet.rows.forEach(function(row) {
                row.cells.splice(hiddenColumn, 1);
            })
        }
    }