Export ASP.Net GridView to Excel and PDF using jQuery DataTable

Hi firasataries

Please refer below sample code.

HTML

<div style="width: 500px">
    <asp:GridView ID="gvCustomers" runat="server" CssClass="display compact" AutoGenerateColumns="false">
        <Columns>
            <asp:BoundField DataField="CustomerID" HeaderText="ID" />
            <asp:BoundField DataField="ContactName" HeaderText="Name" />
            <asp:BoundField DataField="City" HeaderText="City" />
            <asp:BoundField DataField="Country" HeaderText="Country" />
            <asp:HyperLinkField DataTextField="CustomerID" HeaderText="" />
        </Columns>
    </asp:GridView>
</div>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.html5.min.js"></script>
<script type="text/javascript">
    $(function () {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: OnSuccess,
            failure: function (response) {
                alert(response.d);
            },
            error: function (response) {
                alert(response.d);
            }
        });
    });
    function OnSuccess(response) {
        $("[id*=gvCustomers]").DataTable({
            bLengthChange: true,
            lengthMenu: [[5, 10, -1], [5, 10, "All"]],
            bFilter: true,
            bSort: true,
            bPaginate: true,
            data: response.d,
            dom: 'Bfrtip',
            columns: [{ 'data': 'CustomerID' },
                    { 'data': 'ContactName' },
                    { 'data': 'City' },
                    { 'data': 'Country' },
                    { 'data': 'CustomerID',
                        "render": function (data, type, row, meta) {
                            var navigateUrl = "Home.aspx?CustomerId=" + data;
                            if (type === 'display') {
                                data = '<a href="' + navigateUrl + '" target="_blank">' + data + '</a>';
                            }
                            return data;
                        }
                    }],
            buttons: [
            {
                extend: 'excel', text: 'Excel', className: 'exportExcel',
                filename: 'Customers', exportOptions: { modifier: { page: 'all'} }
            },
            { extend: 'pdf', text: 'PDF', className: 'exportExcel', filename: 'Customers',
                exportOptions: { modifier: { page: 'all' }, columns: ':visible' }, orientation: 'landscape', pageSize: 'LEGAL'
            }
        ]
        });
    };
</script>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dummy = new DataTable();
        dummy.Columns.Add("CustomerID");
        dummy.Columns.Add("ContactName");
        dummy.Columns.Add("City");
        dummy.Columns.Add("Country");
        dummy.Rows.Add();
        gvCustomers.DataSource = dummy;
        gvCustomers.DataBind();

        //Required for jQuery DataTables to work.
        gvCustomers.UseAccessibleHeader = true;
        gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader;
    }
}

[WebMethod]
public static List<Customer> GetCustomers()
{
    List<Customer> customers = new List<Customer>();
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT CustomerID, ContactName, City, Country FROM Customers", con))
        {
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(new Customer
                    {
                        CustomerID = sdr["CustomerID"].ToString(),
                        ContactName = sdr["ContactName"].ToString(),
                        City = sdr["City"].ToString(),
                        Country = sdr["Country"].ToString()
                    });
                }
            }
            con.Close();
        }
    }
    return customers;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dummy As DataTable = New DataTable()
        dummy.Columns.Add("CustomerID")
        dummy.Columns.Add("ContactName")
        dummy.Columns.Add("City")
        dummy.Columns.Add("Country")
        dummy.Rows.Add()
        gvCustomers.DataSource = dummy
        gvCustomers.DataBind()
        gvCustomers.UseAccessibleHeader = True
        gvCustomers.HeaderRow.TableSection = TableRowSection.TableHeader
    End If
End Sub

<WebMethod()>
Public Shared Function GetCustomers() As List(Of Customer)
    Dim customers As List(Of Customer) = New List(Of Customer)()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT CustomerID, ContactName, City, Country FROM CustomerVBs", con)
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    customers.Add(New Customer With {
                        .CustomerID = sdr("CustomerID").ToString(),
                        .ContactName = sdr("ContactName").ToString(),
                        .City = sdr("City").ToString(),
                        .Country = sdr("Country").ToString()
                    })
                End While
            End Using
            con.Close()
        End Using
    End Using
    Return customers
End Function

Screenshot