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

0 votes

I have one function that has to be put into practice.

Grid data is being bound to an excel export, and everything is going OK. However, I now need to hide the columns in the Excel export because of a new need. The option to UN-hide the columns that were hidden by code should thereafter be available to the user when he opens the Excel page.


Edit:

I am exporting a grid view control from my.aspx page to Excel using the code below:

 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();
    }

Before the gridview is exported to Excel, some columns must be hidden (in RowDataBound or a related event), and the user who exported the file must be able to reveal the hidden columns once the file has been opened in Microsoft Excel. The column is hidden in the grid view because it is rendered as HTML, but I am unable to make it visible in Microsoft Excel.

Oct 29 in Others by Kithuzzz
• 20,660 points
56 views

1 answer to this question.

0 votes

Use this.

For Row:

worksheet_sub.Row(i).Height = 0;

For Column:

worksheet_sub.Column(i).Width= 0;
answered Oct 29 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered 3 days ago in Others by narikkadan
• 37,660 points
8 views
0 votes
1 answer

How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

answered Sep 24 in Others by narikkadan
• 37,660 points
110 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23 in Others by narikkadan
• 37,660 points
254 views
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

You need to re-think this… the while ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
275 views
0 votes
1 answer

Export DataTable to Excel File

Add Interop References. First we need to ...READ MORE

answered Jun 9 in JQuery by gaurav
• 22,040 points
93 views
0 votes
1 answer

Merge and export Excel/Word/PDF to PDF

Use GroupDocs.Merger for .NET API to merge Word, Excel, ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
74 views
0 votes
1 answer

c#, Microsoft Interop Excel , change font style for s selected range

Change your code to this: worksheet.get_Range("B3", "B4").Cells.Font.Name = ...READ MORE

answered Oct 31 in Others by narikkadan
• 37,660 points
43 views
0 votes
1 answer

How to create and download excel document using asp.net

First, download the Open XML Format SDK 2.0. It comes ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
104 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP