Export gridgiew to excel without losing gridlines in excel in asp.net

Description:-

In this article I will explain how to export GridView to Excel without losing GridLines in Exported Excel file in ASP.Net using C#. here in tthis post we will export gridview data to excel file without losing gridlines. here i have created sample code to export gridview data to excel in single sheet.

Create Table:-

CREATE TABLE [dbo].[Customers](
 [ContactName] [varchar](20) NOT NULL,
 [City] [varchar](20) NOT NULL,
 [PostalCode] [int] NOT NULL,
 [Country] [varchar](20) NOT NULL,
 [Phone] [bigint] NOT NULL,
 [Fax] [bigint] NOT NULL,
 [CustomerId] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
 [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Default.aspx:-

    <div>
        <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
            RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
            runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">
            <Columns>
                <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
                <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Fax" HeaderText="Fax" ItemStyle-Width="100px" />
                <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
                <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
    </div>

Web.config:-

<connectionStrings>
    <add connectionString="ConnectionString" name="DBCS" providerName="System.Data.SqlClient"/>   
</connectionStrings>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid();
    }
}

private void BindGrid()
{
    string strConnString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }
        }
    }
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

protected void ExportToExcel(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";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();

        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }

        GridView1.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Author : Umesh Patel

Related Posts