Export not visible columns in a GridView to Excel

Question

I have a GridView in an ASP.Net Web page (.Net 4.0, C#) that has some columns that are not visible. The user wants these columns to show up in an Excel export, however. I'm stumped as to how to accomplish this. I've tried making one of the columns both a BoundField and a TemplateField with a Label in it:

<asp:TemplateField HeaderText="Device Comments" SortExpression="DeviceComments" Visible="false">
                <ItemTemplate>
                    <asp:Label runat="server" Text='<%# Eval("DeviceComments") %>' ID="lbDeviceComments" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="DeviceComments" HeaderText="Device Comments" SortExpression="DeviceComments" Visible="false" />

The columns do not show up on the Web page, as expected. However, when I try to make the columns visible in the method that does the export to Excel, they remain invisible. I've tried:

protected void Export2Excel(object sender, EventArgs e)
        {
            Response.ClearContent();
            Response.AppendHeader("content-disposition", "attachment; filename=Mobile.xls");
            Response.ContentType = "application/excel";

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

            GridView currentGridView = new GridView();
            switch (DeviceType())
            {
                case "cell":
                    currentGridView = gvCellResults;
                    break;
                case "loaner":
                    currentGridView = gvCellResults;
                    break;
                case "wireless":
                    currentGridView = gvCellResults;
                    break;
                case "smartphone":
                    currentGridView = gvSmartPhoneResults;
                    break;
            }

            foreach (GridViewRow gridViewRow in currentGridView.Rows)
            {
                gridViewRow.ForeColor = Color.Black;
                HyperLink phonenumber = new HyperLink();
                HyperLink imei = new HyperLink();
                HyperLink employee = new HyperLink();
                foreach (TableCell gridViewRowTableCell in gridViewRow.Cells)
                {
                    phonenumber = (HyperLink)gridViewRowTableCell.FindControl("hrefPhoneNumber");
                    imei = (HyperLink)gridViewRow.Cells[1].FindControl("hrefIMEI");
                    employee = (HyperLink)gridViewRow.Cells[6].FindControl("hrefEmpName");
                    gridViewRowTableCell.Style["forecolor"] = "#000000";
                }
                gridViewRow.Cells[11].Visible = true;
                gridViewRow.Cells[11].Style["display"] = "inline";
                if (gridViewRow.RowType == DataControlRowType.DataRow)
                {
                    for (int columnIndex = 0; columnIndex < gridViewRow.Cells.Count; columnIndex++)
                    {
                        gridViewRow.Cells[columnIndex].Attributes.Add("class", "text");
                        gridViewRow.Cells[columnIndex].Text = gridViewRow.Cells[columnIndex].Text.StripTags();
                    }

                    if (phonenumber != null)
                        gridViewRow.Cells[0].Text = phonenumber.Text;
                    if (imei != null)
                        gridViewRow.Cells[1].Text = imei.Text;
                    if (employee != null)
                        gridViewRow.Cells[6].Text = employee.Text;
                }
            }

            currentGridView.RenderControl(htmlTextWriter);
            string style = @"<style> .text { mso-number-format:\@; } </style> ";
            Response.Write(style);
            Response.Write(stringWriter.ToString());
            Response.End();
        }

But the column does not appear in Excel. I'm sure I'm addressing the right column because I've run it through the debugger and checked the Text property of the cell; they match what is displayed on the Web page for that column. Strangely enough, in the debugger the Visible property of the cell shows true even before I change it to true. What am I doing wrong?

You have not provided any explanation as to how you're exporting to Excel. This answer is dependent on that, please provide the code for that by updating the question. You might also consider that most of the time you should export the underlying data directly rather than exporting the GridView. — mason, Apr 28 '14 at 18:46
show the code you are using for exporting to excel. I have done this without problems. you should really be looking at setting the column (not cell) to be shown. I guess again, depends on your code on how you are making it visible or not — Ahmed ilyas, Apr 28 '14 at 18:46

score 2 · Answer 1 · answered Jun 02 '16 at 14:14

Try making column visible before calling Response.ClearContent(); in my case it worked.

Something like

protected void Export2Excel(object sender, EventArgs e)
    {
        gridViewRow.Cells[11].Visible = true;
        gridViewRow.Cells[11].Style["display"] = "inline";
        Response.ClearContent();
        Response.AppendHeader("content-disposition", "attachment; filename=Mobile.xls");
        Response.ContentType = "application/excel";

        StringWriter stringWriter = new StringWriter();
        HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

        GridView currentGridView = new GridView();
        switch (DeviceType())
        {
            case "cell":
                currentGridView = gvCellResults;
                break;
            case "loaner":
                currentGridView = gvCellResults;
                break;
            case "wireless":
                currentGridView = gvCellResults;
                break;
            case "smartphone":
                currentGridView = gvSmartPhoneResults;
                break;
        }

        foreach (GridViewRow gridViewRow in currentGridView.Rows)
        {
            gridViewRow.ForeColor = Color.Black;
            HyperLink phonenumber = new HyperLink();
            HyperLink imei = new HyperLink();
            HyperLink employee = new HyperLink();
            foreach (TableCell gridViewRowTableCell in gridViewRow.Cells)
            {
                phonenumber = (HyperLink)gridViewRowTableCell.FindControl("hrefPhoneNumber");
                imei = (HyperLink)gridViewRow.Cells[1].FindControl("hrefIMEI");
                employee = (HyperLink)gridViewRow.Cells[6].FindControl("hrefEmpName");
                gridViewRowTableCell.Style["forecolor"] = "#000000";
            }

            if (gridViewRow.RowType == DataControlRowType.DataRow)
            {
                for (int columnIndex = 0; columnIndex < gridViewRow.Cells.Count; columnIndex++)
                {
                    gridViewRow.Cells[columnIndex].Attributes.Add("class", "text");
                    gridViewRow.Cells[columnIndex].Text = gridViewRow.Cells[columnIndex].Text.StripTags();
                }

                if (phonenumber != null)
                    gridViewRow.Cells[0].Text = phonenumber.Text;
                if (imei != null)
                    gridViewRow.Cells[1].Text = imei.Text;
                if (employee != null)
                    gridViewRow.Cells[6].Text = employee.Text;
            }
        }

        currentGridView.RenderControl(htmlTextWriter);
        string style = @"<style> .text { mso-number-format:\@; } </style> ";
        Response.Write(style);
        Response.Write(stringWriter.ToString());
        Response.End();
    }

score 0 · Answer 2 · edited May 23 '17 at 10:34

I'm sure that you are loading the data to display on the GridView at page load or a button click event. I would suggest persisting this data in cache or in memory and then using a library like EPPlus to generate the Excel file with the formatting required. Here is an article that shows how to use EPPlus.

Here is another question on SO that talks about using ashx handlers to create a Excel File that would be helpful.

Export not visible columns in a GridView to Excel

2 Answers2