CODEWITHSUNDEEP
c#asp.netexcel
Vikash Chauhan
Vikash Chauhan

Reputation: 792

gridview data export to excel in asp.net

i tried to transfer grid view data to excel .... But the output is a blank excel sheet.How to solve this problem?Is there any code to transfer grid view value to excel sheet with data base?

protected void btnexcel_Click1(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    gvdetails.AllowPaging = false;
    gvdetails.DataBind(); 
    gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
    gvdetails.HeaderRow.Cells[0].Style.Add("background-color", "green");
    gvdetails.HeaderRow.Cells[1].Style.Add("background-color", "green");
    gvdetails.HeaderRow.Cells[2].Style.Add("background-color", "green");
    for (int i = 0; i < gvdetails.Rows.Count;i++ )
    {
        GridViewRow row = gvdetails.Rows[i];
        row.BackColor = System.Drawing.Color.White;
        row.Attributes.Add("class", "textmode");
        if (i % 2 != 0)
        {
            row.Cells[0].Style.Add("background-color", "#C2D69B");
            row.Cells[1].Style.Add("background-color", "#C2D69B");
            row.Cells[2].Style.Add("background-color", "#C2D69B");
        }
    }

    string style = @"<style> .textmode { mso-number-format:\@; } </style>"; 
    Response.Write(style); 

    Response.Output.Write(sw.ToString());
    Response.End();
}

Upvotes: 9

Views: 114585

Answers (7)

Taran
Taran

Reputation: 3231

The Best way is to use closedxml. Below is the link for reference

https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

and you can simple use 

    var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GeDataTable();//refer documentaion

wb.Worksheets.Add(dt);

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");

using (var ms = new System.IO.MemoryStream()) {
    wb.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);
    ms.Close();
}

Response.End();

Upvotes: 0

Such
Such

Reputation: 285

I think it will help you

string filename = String.Format("Results_{0}_{1}.xls", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());
        if (!string.IsNullOrEmpty(GRIDVIEWNAME.Page.Title))
            filename = GRIDVIEWNAME.Page.Title + ".xls";

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);


        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.Charset = "";

        System.IO.StringWriter stringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);



        System.Web.UI.HtmlControls.HtmlForm form = new System.Web.UI.HtmlControls.HtmlForm();
        GRIDVIEWNAME.Parent.Controls.Add(form);
        form.Controls.Add(GRIDVIEWNAME);
        form.RenderControl(htmlWriter);

        HttpContext.Current.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
        HttpContext.Current.Response.Write(stringWriter.ToString());
        HttpContext.Current.Response.End();

Upvotes: 3

Black Cloud
Black Cloud

Reputation: 481

may be problem in data binding in export excel . check that data properly bin to a gridview or not.

Use this code for export grid view in excel sheet and note that you must add iTextSharp dll in you project.

protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;

        // Re-Bind data to GridView 

        using (CompMSEntities1 CompObj = new CompMSEntities1())
        {
            Start = Convert.ToDateTime(txtStart.Text);
            End = Convert.ToDateTime(txtEnd.Text);

            GridViewSummaryReportCategory.DataSource = CompObj.SP_Category_Summary(Start, End);
            SP_Category_Summary_Result obj1 = new SP_Category_Summary_Result();
            GridView1.DataBind();
           GridView1.Visible = true;
            ExportTable.Visible = true;
        }

        //Change the Header Row back to white color

        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");


       GridView1.Style.Add(" font-size", "10px");




        //Apply style to Individual Cells

        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
        GGridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");

        for (int i = 1; i < GridView1.Rows.Count; i++)
        {
            GridViewRow row = GridView1.Rows[i];

            //Change Color back to white

            row.BackColor = System.Drawing.Color.White;

            //Apply text style to each Row

        //    row.Attributes.Add("class", "textmode");

            //Apply style to Individual Cells of Alternating Row

            if (i % 2 != 0)
            {
                row.Cells[0].Style.Add("background-color", "#C2D69B");
                row.Cells[1].Style.Add("background-color", "#C2D69B");
                row.Cells[2].Style.Add("background-color", "#C2D69B");
                row.Cells[3].Style.Add("background-color", "#C2D69B");
                row.Cells[4].Style.Add("background-color", "#C2D69B");
            }
        }
        GridView1.RenderControl(hw);

        //style to format numbers to string

        string style = @"<style> .textmode { mso-number-format:\@; } </style>";

        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

Upvotes: 3

Jeff B
Jeff B

Reputation: 545

Something else to check is make sure viewstate is on (I just solved this yesterday). If you don't have viewstate on, the gridview will be blank until you load it again.

Upvotes: 0

Ratna
Ratna

Reputation: 2319

Your sheet is blank because your string writer in null. Here is what may help

System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);

    GridView1.RenderControl(htmlWrite);

Here is the full code

protected void Button1_Click(object sender, EventArgs e)
{
    Response.Clear();

    Response.AddHeader("content-disposition", "attachment;
    filename=FileName.xls");


    Response.ContentType = "application/vnd.xls";

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);

    GridView1.RenderControl(htmlWrite);

    Response.Write(stringWrite.ToString());

    Response.End();

}

Upvotes: 13

Pradip
Pradip

Reputation: 1537

Instead of doing all these.. cant you use a simpler approach as shown below.

Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
            Response.ContentType = "application/excel";
            System.IO.StringWriter sw = new System.IO.StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();

You can get the entire walkthrough here

Upvotes: 0

शेखर
शेखर

Reputation: 17614

I don't there there is any DataSource for the gridview
Though you have DataBind in your code as

gvdetails.DataBind();

Upvotes: -1

Related Questions