14 August 2017

How to import GridView to Excel in ASP.NET WebForm with C#

I have a requirement to import a GridView data to MS Excel without using any third party library. The solutions are kinda old stuff actually and they're all over the net, but I still want to share it here on my blog for the benefit of my visitors.

A. First, you need to add VerifyRenderingInServerForm method to confirm that an HtmlForm control is rendered for the specified ASP.NET server control at run time.

    public override void VerifyRenderingInServerForm(Control control)
    {
       
    }
    
B. Then, add the following sample codes to the button click event.
 
    protected void btnConvertToExcel_Click(object sender, EventArgs e)
    {
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("content-disposition", "attachment;filename=SalesReport.xls");
        Response.Charset = "";
        var sw = new StringWriter();
        var hw = new HtmlTextWriter(sw);
        gvSalesReport.AllowPaging = false;
        
        //Call the method in viewing Sales Report in GridView 
        Get_Sales_Report();        
        
       
        for (int i = 0; i <= gvSalesReport.Rows.Count - 1; i++)
        {
            GridViewRow row = gvSalesReport.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 (optional)
            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");                
            }
        }
        gvSalesReport.RenderControl(hw);
        
        //style to format numbers to string
        const string style = "<style>.textmode{mso-number-format:\\@;}</style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

No comments:

Post a Comment