Solved

Export to Excel C# modification

Posted on 2011-09-14
7
450 Views
Last Modified: 2013-12-17
I will post the code I have that works to export a GridView to Excel in C#.  What I am attempting to do and I cannot figure out how to do, is remove COLUMNS from the exported GridView.  I can remove ROWS easily, but I cannot figure out how to remove COLUMNS at the top from the following code:


 public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1));
        HttpContext.Current.Response.Cache.SetValidUntilExpires(false);
        HttpContext.Current.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches);


        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                table.GridLines = gv.GridLines;


                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
0
Comment
Question by:zintech
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Rick
ID: 36537063
You could do something like this.

// Create a new gridview:

  GridView grd;
  grd = new GridView();


// Create a new DataTable to hold the data to be exported to Excel and remove the desired column(s):

  DataTable dt2 = new DataTable();

  dt2.Merge(dt);
  dt2.Columns.Remove("B");


// Bind the new GridView to the new DataTable:

  grd.DataSource = dt2;
  grd.DataBind();


// Export to Excel:

  Response.Clear();
  Response.AddHeader("content-disposition", "attachment;filename=Avukat.xls");
  Response.Charset = "";
  Response.ContentType = "application/vnd.xls";
 
  System.IO.StringWriter stringWrite = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
 
  grd.RenderControl(htmlWrite);
 
  Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
  Response.Write(stringWrite.ToString());
  Response.End();

// Dispose of the new DataTable and Gridview:

  dt2.Dispose();
  grd.Dispose();


0
 
LVL 13

Expert Comment

by:Rick
ID: 36537252
Or you could do something like this:


// Hide the desired column(s):      

       GridView1.Columns[1].Visible = false;

// Export to Excel

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=Avukat.xls");
        Response.Charset = "";

        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView2.RenderControl(htmlWrite);
        Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
        Response.Write(stringWrite.ToString());
        Response.End();

// Reset column visibility:

        GridView2.Columns[1].Visible = true;


    public override void VerifyRenderingInServerForm(Control control)
    {
    }
0
 

Author Comment

by:zintech
ID: 36537811
One thing to note that I failed to mention in my original post - The GridView I am using has the first two columns, which I am trying not to export, not visible in the GridView.  So all teh user sees on the screen are the last two columns, which I want to display.  The two I want hidden are in fact hidden from the user on the screen.  However, when I export the gridview to Excel, it shows the two collumns that are hidden that I do not want the user to see
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 13

Expert Comment

by:Rick
ID: 36537918
Are you hiding the first two columns using CSS?
0
 

Author Comment

by:zintech
ID: 36537988
No.  I go into the properties section of the GridView in ASP.NET, and set the Visible property to False for each of the first two columns.
0
 
LVL 13

Accepted Solution

by:
Rick earned 500 total points
ID: 36538032
You are copying the contents of the GridView to a table (including the columns you don't want to display), then exporting that table to Excel.

Try this simpler code.


protected void Button1_Click(object sender, EventArgs e)
    {
       

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=Avukat.xls");
        Response.Charset = "";

        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView2.RenderControl(htmlWrite);
        Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
        Response.Write(stringWrite.ToString());
        Response.End();


    }

    public override void VerifyRenderingInServerForm(Control control)
    {
    }

Open in new window

0
 

Author Comment

by:zintech
ID: 36538116
What Namespace needs to be imported to use the Response object?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Selenium: Transitioning from unit testing to automated testing 4 55
Problem to Office 1 39
Data organization issue 7 36
Coding for the first time 9 62
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question