Adding Sr.No as 1st column while Exporting ASP.Net GridView to Excel using C# and VB.Net

Hi arvindasp,

Add column to 1st position using the SetOrdinal method. Then loop through the rows and set the serial number. Finally use the DataTable to export to Excel file.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:GridView ID="GridView1" runat="server"></asp:GridView>
<br />
<asp:Button Text="Export" runat="server" OnClick="ExportReport" />

Namespaces

C#

using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Configuration;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Configuration

Code

C#

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

private DataTable BindGrid()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter("SELECT Name, Country FROM Customers", con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                dt.Columns.Add("Sr No").SetOrdinal(0);
                foreach (DataRow dr in dt.Rows)
                {
                    dr[0]=(dt.Rows.IndexOf(dr)+1).ToString();
                }
                return dt;
            }
        }
    }
}

protected void ExportReport(object sender, EventArgs e)
{
    DataTable dt = BindGrid();
    GridView dg = new GridView();
    dg.DataSource = dt;
    dg.DataBind();
    string sFileName = "LoayaltyCardList_" + System.DateTime.Now + ".xls";
    sFileName = sFileName.Replace("/", "");

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        dg.AllowPaging = false;
        dg.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in dg.HeaderRow.Cells)
        {
            cell.ForeColor = Color.Black;
            cell.Font.Bold = true;
            cell.Font.Size = 10;
            cell.BackColor = Color.LightCyan;
        }
        foreach (GridViewRow row in dg.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = dg.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = dg.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }
        string ReportTitle = "<table> " +
            "<tr>" +
            "   <td colspan='13' style='background-color:lightyellow ; border:solid;text-align:center;font-size:50px;color:darkblue '><b>" + "Loyalty Card List".ToUpper() + "</b></td>" +
            "</tr>" +
            "<tr>" +
            "   <td style='background-color:lightyellow; border:solid  '><b>" + "Report Date" + "</b></td>" +
            "   <td colspan='12' style='background-color:lightyellow;text-align:left; border:solid  '><b>" + System.DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") + "</b></td>" +
            "</tr>" +
            "<tr>" +
            "   <td style='background-color:lightyellow; border:solid  '><b>" + "Company Name" + "</b></td>" +
            "   <td colspan='12' style='background-color:lightyellow; border:solid  '><b>ASPSnippets Pvt. Ltd.</b></td>" +
            "</tr>" +
            "</table>";
        dg.RenderControl(hw);
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        GridView1.DataSource = Me.BindGrid()
        GridView1.DataBind()
    End If
End Sub

Private Function BindGrid() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter("SELECT Name, Country FROM Customers", con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                dt.Columns.Add("Sr No").SetOrdinal(0)
                For Each dr As DataRow In dt.Rows
                    dr(0) = (dt.Rows.IndexOf(dr) + 1).ToString()
                Next
                Return dt
            End Using
        End Using
    End Using
End Function

Protected Sub ExportReport(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = BindGrid()
    Dim dg As GridView = New GridView()
    dg.DataSource = dt
    dg.DataBind()
    Dim sFileName As String = "LoayaltyCardList_" & System.DateTime.Now & ".xls"
    sFileName = sFileName.Replace("/", "")
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment; filename=" & sFileName)
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"

    Using sw As StringWriter = New StringWriter()
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        dg.AllowPaging = False
        dg.HeaderRow.BackColor = Color.White

        For Each cell As TableCell In dg.HeaderRow.Cells
            cell.ForeColor = Color.Black
            cell.Font.Bold = True
            cell.Font.Size = 10
            cell.BackColor = Color.LightCyan
        Next

        For Each row As GridViewRow In dg.Rows
            row.BackColor = Color.White
            For Each cell As TableCell In row.Cells
                If row.RowIndex Mod 2 = 0 Then
                    cell.BackColor = dg.AlternatingRowStyle.BackColor
                Else
                    cell.BackColor = dg.RowStyle.BackColor
                End If
                cell.CssClass = "textmode"
            Next
        Next

        Dim ReportTitle As String = "<table> " _
            & "<tr>" _
            & "   <td colspan='13' style='background-color:lightyellow ; border:solid;text-align:center;font-size:50px;color:darkblue '><b>" & "Loyalty Card List".ToUpper() & "</b></td>" _
            & "</tr>" _
            & "<tr>" _
            & "   <td style='background-color:lightyellow; border:solid  '><b>" & "Report Date" & "</b></td>" _
            & "   <td colspan='12' style='background-color:lightyellow;text-align:left; border:solid  '><b>" & System.DateTime.Now.ToString("dd-MM-yyyy hh:mm:ss") & "</b></td>" _
            & "</tr>" _
            & "<tr>" _
            & "   <td style='background-color:lightyellow; border:solid  '><b>" & "Company Name" & "</b></td>" _
            & "   <td colspan='12' style='background-color:lightyellow; border:solid  '><b>ASPSnippets Pvt. Ltd.</b></td>" _
            & "</tr>" _
            & "</table>"
        dg.RenderControl(hw)
        Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
    End Using
End Sub

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

Screenshots

Exported Excel