Solved

Export to Excel C# modification

Posted on 2011-09-14
7
451 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
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.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

752 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