Solved

Export to Excel C# modification

Posted on 2011-09-14
7
446 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Two different visual studio versions 3 23
Host asp.net pages 5 26
Exit the loop 4 40
Where can I ask about my ASP.NET MVC project? 6 23
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This is about my first experience with programming Arduino.
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 …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now