Export ASP.Net GridView to Excel with formatting using C# and VB.Net

Hi Tevin,

Refer below sample.

You need to assigned same color during exporting which color you have used onrowdatabound.

HTML

<asp:GridView runat="server" ID="GridView1" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" runat="server" OnClick="btnExcelExportPassedDue_Click" />

Namespaces

C#

using System.Data;
using System.IO;

VB.Net

Imports System.Data
Imports System.IO

Code

C#

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

        BindGrid();
    }
}

private void BindGrid()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Country",typeof(string)) });
    dt.Rows.Add(1, "John Hammond", "United States");
    dt.Rows.Add(2, "Mudassar Khan", "India");
    dt.Rows.Add(3, "Suzanne Mathews", "France");
    dt.Rows.Add(4, "Robert Schidner", "Russia");
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    try
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            DataRow row = ((DataRowView)e.Row.DataItem).Row;
            if (row["Id"].ToString() == "1" || row["Id"].ToString() == "3")
            {
                e.Row.BackColor = System.Drawing.Color.Green;
                e.Row.ForeColor = System.Drawing.Color.White;
            }

            else
            {
                e.Row.BackColor = System.Drawing.Color.Blue;
                e.Row.ForeColor = System.Drawing.Color.White;
            }
        }
    }
    catch (Exception ex)
    {

    }
}

protected void btnExcelExportPassedDue_Click(object sender, EventArgs e)
{
    try
    {
        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 = System.Drawing.Color.White;
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                cell.BackColor = GridView1.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                row.BackColor = System.Drawing.Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = System.Drawing.Color.Green;
                        cell.ForeColor = System.Drawing.Color.White;
                    }
                    else
                    {
                        cell.BackColor = System.Drawing.Color.Blue;
                        cell.ForeColor = System.Drawing.Color.White;
                    }
                    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();
        }
    }

    catch (Exception ex)
    {

    }
}

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

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)Handle Me.Load
        If Not Me.IsPostBack Then
            BindGrid()
        End If
    End Sub

    Private Sub BindGrid()
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
        dt.Rows.Add(1, "John Hammond", "United States")
        dt.Rows.Add(2, "Mudassar Khan", "India")
        dt.Rows.Add(3, "Suzanne Mathews", "France")
        dt.Rows.Add(4, "Robert Schidner", "Russia")
        GridView1.DataSource = dt
        GridView1.DataBind()
    End Sub

    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
        Try
            If e.Row.RowType = DataControlRowType.DataRow Then
                Dim row As DataRow = (CType(e.Row.DataItem, DataRowView)).Row
                If row("Id").ToString() = "1" OrElse row("Id").ToString() = "3" Then
                    e.Row.BackColor = System.Drawing.Color.Green
                    e.Row.ForeColor = System.Drawing.Color.White
                Else
                    e.Row.BackColor = System.Drawing.Color.Blue
                    e.Row.ForeColor = System.Drawing.Color.White
                End If
            End If
        Catch ex As Exception
        End Try
    End Sub

    Protected Sub btnExcelExportPassedDue_Click(ByVal sender As Object, ByVal e As EventArgs)
        Try
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
            Response.Charset = ""
            Response.ContentType = "application/vnd.ms-excel"

            Using sw As StringWriter = New StringWriter()
                Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
                GridView1.AllowPaging = False
                Me.BindGrid()
                GridView1.HeaderRow.BackColor = System.Drawing.Color.White

                For Each cell As TableCell In GridView1.HeaderRow.Cells
                    cell.BackColor = GridView1.HeaderStyle.BackColor
                Next

                For Each row As GridViewRow In GridView1.Rows
                    row.BackColor = System.Drawing.Color.White

                    For Each cell As TableCell In row.Cells

                        If row.RowIndex Mod 2 = 0 Then
                            cell.BackColor = System.Drawing.Color.Green
                            cell.ForeColor = System.Drawing.Color.White
                        Else
                            cell.BackColor = System.Drawing.Color.Blue
                            cell.ForeColor = System.Drawing.Color.White
                        End If

                        cell.CssClass = "textmode"
                    Next
                Next

                GridView1.RenderControl(hw)
                Dim style As String = "<style> .textmode { } </style>"
                Response.Write(style)
                Response.Output.Write(sw.ToString())
                Response.Flush()
                Response.[End]()
            End Using

        Catch ex As Exception
        End Try
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    End Sub

Screenshot