Hey pankajom,

Please refer below sample.

HTML

<asp:Button Text="Export" runat="server" OnClick="Export" />

Namespaces

C#

using System.Data;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Configuration
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports System.Reflection

Code

C#

private static DataSet GetDataSet()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, Name Country FROM Customers"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    DataSet ds = new DataSet();
                    ds.Tables.Add(dt);
                    return ds;
                }
            }
        }
    }
}

private static void DataSetToExcel(DataSet ds, Boolean generateIdentity)
{
    Excel.Application xlApp = new Excel.Application();
    xlApp.Visible = false;
    Excel.Workbook wb = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    for (int k = 0; k < ds.Tables.Count; k++)
    {
        System.Data.DataTable dt = ds.Tables[k];
        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        ws.Name = dt.TableName;
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (i == 0)
                {
                    ws.Cells[1, j + 1] = dt.Columns[j].ColumnName;
                }
                ws.Cells[i + 2, j + 1] = (j == 0 && generateIdentity) ? (i + 1).ToString() : dt.Rows[i][j].ToString();
            }
        }
    }
    wb.Password = "123";
    wb.Close();
}

protected void Export(object sender, EventArgs e)
{
    DataSetToExcel(GetDataSet(), false);
}

VB.Net

Private Shared Function GetDataSet() As DataSet
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, Name Country FROM Customers")
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Dim ds As DataSet = New DataSet()
                    ds.Tables.Add(dt)
                    Return ds
                End Using
            End Using
        End Using
    End Using
End Function

Private Shared Sub DataSetToExcel(ByVal ds As DataSet, ByVal generateIdentity As Boolean)
    Dim xlApp As Excel.Application = New Excel.Application()
    xlApp.Visible = False
    Dim wb As Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
    For k As Integer = 0 To ds.Tables.Count - 1
        Dim dt As System.Data.DataTable = ds.Tables(k)
        Dim ws As Excel.Worksheet = CType(wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value), Excel.Worksheet)
        ws.Name = dt.TableName

        For i As Integer = 0 To dt.Rows.Count - 1

            For j As Integer = 0 To dt.Columns.Count - 1

                If i = 0 Then
                    ws.Cells(1, j + 1) = dt.Columns(j).ColumnName
                End If

                ws.Cells(i + 2, j + 1) = If((j = 0 AndAlso generateIdentity), (i + 1).ToString(), dt.Rows(i)(j).ToString())
            Next
        Next
    Next

    wb.Password = "123"
    wb.Close()
End Sub

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    DataSetToExcel(GetDataSet(), False)
End Sub

Screenshot