?
Solved

Export to Excel C# modification

Posted on 2011-09-14
7
Medium Priority
?
460 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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 2000 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Today, unlike web development, the mobile landscape is complex enough for a software engineer and Android is posing more challenging environment thanks to its fragmentation issues on hardware and software fronts.
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…
Loops Section Overview

589 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