Export only Gridview Data to Excel format in Asp.net C#

Question

In my page I have two textbox controls, where I am gating the date from the Calendar Extender & in the export to excel Button I am going to export the Gridview data to excel sheet. When I am gating the excel sheet its show the the textbox & button also from which i am export the excel Sheet.

I have written the export code in the Export Button. Like:-

    protected void Export_to_Excel_Click(object sender, EventArgs e)
    {
        try
        {

            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
            Response.ContentType = "application/ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            Grd_MidData.AllowPaging = false;
            bindgriddata();
            //Change the Header Row back to white color
            Grd_MidData.HeaderRow.Style.Add("background-color", "#FFFFFF");
            //Applying stlye to gridview header cells
            for (int i = 0; i < Grd_MidData.HeaderRow.Cells.Count; i++)
            {
                Grd_MidData.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
            }
            Grd_MidData.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.Flush();
        }
        catch (Exception ee)
        {

        }
    }

When I am gating the excel sheet it shows the gridview data along with the two text box & the button from where I am doing the filtering.

So any one suggest me, How to show only the gridview data but not show the Textbox & button on the excel sheet.

Almost all answer here write a (HtmlTextWriter) string or have Interop code. DO NOT USE EITHER. This will cause you problems later on with DateTime and Decimal formatting. Also Excel will give a warning because you are not generating a "real" Excel file but a HTML page with an .xls extension. Start using a specialized library for creating Excel files, like EPPlus. Example here and here. — VDWWD
– VDWWD, Commented Dec 21, 2018 at 9:25

Rajesh Paliwal · Accepted Answer · 2016-05-09 10:22:12Z

1

You have to bind the data to your excel code, using below code

    bindgriddata();    
    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
    Response.ContentType = "application/ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter ht = new HtmlTextWriter(sw);
    Grd_MidData.RenderControl(ht);
    Response.Write(sw.ToString());
    Response.End();

it is working for me.

answered May 9, 2016 at 10:22

Rajesh Paliwal

1061 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sabyasachi Over a year ago

Thank you #Rajesh. I have use this code. only one changes i have done that is instead on Response.End(); I have use Response.Flush();. But the problem is remain there. When i am downloading the excel sheet, it's shows the Gridview data along with the textbox & Button also. Like we do print screen, it shows the full page data... So how can i remove that 2 text box & button from the sheet.

Sabyasachi · Accepted Answer · 2016-05-09 11:03:46Z

0

Here the gridview data is showing along with the From date: To Date: textbox & the button also. So how can i remove these field from the sheet.

answered May 9, 2016 at 11:03

Sabyasachi

1212 gold badges4 silver badges13 bronze badges

1 Comment

Rajesh Paliwal Over a year ago

Hello this is because of RenderControl you are using in code, by this property data are Render as it is in Excel sheet.

Rajesh Paliwal · Accepted Answer · 2016-05-10 08:56:20Z

you can use close xml to generate excel it will give more functionlity to create excel and formatting.

protected void Export_to_Excel_Click(object sender, EventArgs e)
{
    bindgriddata();
    Grd_MidData.DataSource = objDS;  // Dataset
    Grd_MidData.DataBind();

    using (XLWorkbook wb = new XLWorkbook())
    {
        try
        {
            //creating worksheet
            var ws = wb.Worksheets.Add("Report");

            //adding columms header
            int columnscount = objDS.Tables[0].Columns.Count;
            char a = 'A';
            for (int j = 1; j <= columnscount; j++)
            {
                string str = a + "1";
                ws.Cell(str).Value = objDS.Tables[0].Columns[j - 1].ColumnName.ToString();
                ws.Cell(str).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                a++;
            }
            ws.Columns().AdjustToContents();

            //formatting columns header 
            var rngheaders = ws.Range("A1:J1");
            rngheaders.FirstRow().Style
                .Font.SetBold()
                .Font.SetFontSize(12)
                .Font.SetFontColor(XLColor.Black)
                .Fill.SetBackgroundColor(XLColor.DeepSkyBlue)
                .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center)
                .Border.OutsideBorder = XLBorderStyleValues.Thin;


            ////adding data to excel
            int k = 2;
            foreach (DataRow row in objDS.Tables[0].Rows)
            {
                char b = 'A';
                string str = b + "" + k;
                for (int i = 0; i < objDS.Tables[0].Columns.Count; i++)
                {
                    ws.Cell(str).Value = row[i].ToString();
                    ws.Cell(str).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    ws.Cell(str).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
                    b++;
                    str = b + "" + k;
                }
                k++;
            }

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=Customer.xlsx");
        }
        catch { }

        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }

}

Community · Accepted Answer · 2017-05-23 12:02:32Z

0

I had the same issue when exporting a GridView. I added a try/catch and exported the message to Excel and then realized that it was generating an error. I solved it thanks to the following answer: GridView must be placed inside a form tag with runat="server" even after the GridView is within a form tag

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 12, 2017 at 18:35

Daniel Reyes

11 bronze badge

1 Comment

Cullub Over a year ago

Could you incorporate the gist of that answer into this scenario? If this question is a duplicate, you can flag the question and mark it as such.

Collectives™ on Stack Overflow

Export only Gridview Data to Excel format in Asp.net C#

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related