Solved

Export to Excel C# modification

Posted on 2011-09-14
7
447 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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.
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…

773 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