how to hide and unhide the columns of an excel sheet using asp.net

Question

I am having one functionality which need to be implemented.

We are binding the Grid data to the excel export and it is working fine. But i got the new requirement where i have to hide the columns in the excel export. After that when user open the excel sheet he should have the option to UN-hide again the columns which we hide through code.

Edit:

I have a gridview control on my .aspx page which I am exporting to excel using the following code:

 public static void Export(string filename, GridView grid)
    {

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("content-disposition",
            string.Format("attachment; filename={0}", filename.Replace(" ", "") + ".xls"));
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.ContentType = "application/vnd.xls";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            grid.HeaderStyle.BackColor = System.Drawing.Color.Cyan;
            GridViewRow row = new GridViewRow(0, 1, DataControlRowType.DataRow, DataControlRowState.Normal);
            TableCell cell = new TableCell();
            cell.Text = String.Format("{0}", Heading[count]);
            cell.ColumnSpan = grid.Rows[1].Cells.Count;
            cell.Attributes.Add("style", "background-color: white; color: black;text-align:left;");
            cell.Attributes.Add("class", "yellow");
            row.Cells.Add(cell);
            grid.Controls[0].Controls.AddAt(0, row);
            grid.RenderControl(htw);
            DataTable dt = new DataTable();
            DataRow dr;
            dt.Columns.Add(new System.Data.DataColumn(" ", typeof(String)));
            dr = dt.NewRow();
            dr[0] = " ";
            dt.Rows.Add(dr);
            GridView gvSpace = new GridView();
            gvSpace.DataSource = dt;
            gvSpace.GridLines = 0;
            gvSpace.DataBind();
            gvSpace.RenderControl(htw);
            grid.HeaderStyle.BackColor = System.Drawing.Color.Cyan;
            HttpContext.Current.Response.Write(@"<style> .sborder { color : Black;border : 1px Solid Black; } .yellow {background-color:yellow;color:black;} </style> ");
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();
            HttpContext.Current.Response.Flush();
    }

The requirement is to hide some columns (in RowDataBound or a similar event) before the gridview is exported to excel and the user who exported the file should be able to unhide the hidden columns after opening the file in Microsoft Excel. As the grid view is rendenred as html i have tried using display:none, this is hiding the column but I am not able to unhide it in the Microsoft excel.

So how can I hide grid view columns before exporting it to excel and unhide the columns when I open the file in Microsoft excel?

Answer 1

I work with excel a lot at work and it is much easier to use EPPlus. You can add it to your project from NuGet.

All you have to do in order to hide/unhide a column with EPPlus is:

worksheet.Column(columnPosition).Hidden = true/false;

where columnPosition is the index of the column you want to hide.

I wrote a method at work that takes a gridview as a peram and turns it into a datatable. You can hide the gridview column in the onrowdatabound event and pass the gridview to the method. Then you can generate the excel file using EPPlus and unhide the column if needed(it will probably already be unhidden).

If you want the method I can update this post with it next time i am at work.