gridview data export to excel in asp net

Question

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  lt  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      lt style gt   textmode   mso-number-format        lt  style gt         Response Write style         Response Output Write sw ToString         Response End

User · Answer

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();
    }

User · Answer

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

User · Answer

The Best way is to use closedxml  Below is the link for reference  https   closedxml codeplex com wikipage title Adding 20DataTable 20as 20Worksheet amp 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt meta http-equiv   Content-Type   content   text html  charset utf-8     gt             HttpContext Current Response Write stringWriter ToString             HttpContext Current Response End

[c#] gridview data export to excel in asp.net

Examples related to c#

Examples related to asp.net

Examples related to excel