Hi SajidHussa,
Using the below link i have created the sample.
Check this example. Now please take its reference and correct your code.
For this sample i have used library iTextSharp for PDF and ClosedXML for Excel file generation.
HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.Pager span
{
text-align: center;
color: #999;
display: inline-block;
width: 20px;
background-color: #A1DCF2;
margin-right: 3px;
line-height: 150%;
border: 1px solid #3AC0F2;
}
.Pager a
{
text-align: center;
display: inline-block;
width: 20px;
background-color: #3AC0F2;
color: #fff;
border: 1px solid #3AC0F2;
margin-right: 3px;
line-height: 150%;
text-decoration: none;
}
/*Displays UP arrow*/
.arrow-up
{
width: 0;
height: 0;
border-left: 5px solid transparent;
border-right: 5px solid transparent;
border-bottom: 10px solid black;
display: inline-block;
}
/*Displays DOWN arrow*/
.arrow-down
{
width: 0;
height: 0;
border-left: 5px solid transparent;
border-right: 5px solid transparent;
border-top: 10px solid black;
display: inline-block;
}
</style>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
var pagerIndex = 1;
var direction = 'ASC';
var column = 'CustomerID';
$(function () {
GetCustomers(1, '', 'ASC', column);
$("#txtSearch").on('keyup', function () {
GetCustomers(1, $(this).val(), direction, column);
});
$("#tblCustomers th").on('click', function () {
$.each($("#tblCustomers th"), function () {
$(this).find('.arrow').hide();
});
direction = direction == 'ASC' ? 'DESC' : 'ASC';
$(this).find('.arrow').show();
if (direction == 'ASC') {
$(this).find('.arrow').removeClass('arrow-down');
$(this).find('.arrow').addClass('arrow-up');
} else if (direction == 'DESC') {
$(this).find('.arrow').removeClass('arrow-up');
$(this).find('.arrow').addClass('arrow-down');
}
column = $(this).find('a').data('sort');
GetCustomers(pagerIndex, $("#txtSearch").val(), direction, column);
});
$('.Pager').on("click", ".page", function () {
pagerIndex = parseInt($(this).attr('page'));
GetCustomers(pagerIndex, $("#txtSearch").val());
});
});
function GetCustomers(pageIndex, searchTerm, sortDirection, sortColumn) {
var obj = {};
obj.pageIndex = $.trim(pageIndex);
obj.searchTerm = $.trim(searchTerm);
obj.sortDirection = $.trim(sortDirection);
obj.sortColumn = $.trim(sortColumn);
$.ajax({
type: "POST",
url: "Default.aspx/GetCustomers",
data: JSON.stringify(obj),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
error: function (response) {
alert(response.d);
}
});
}
var row;
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
if (row == null) {
row = $("[id*=tblCustomers] tr:last-child").clone(true);
}
$("[id*=tblCustomers] tr").not($("[id*=tblCustomers] tr:first-child")).remove();
if (customers.length > 0) {
$.each(customers, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("CustomerID").text());
$("td", row).eq(1).html($(this).find("ContactName").text());
$("td", row).eq(2).html($(this).find("City").text());
$("[id*=tblCustomers]").append(row);
row = $("[id*=tblCustomers] tr:last-child").clone(true);
});
} else {
var empty_row = row.clone(true);
$("td:first-child", empty_row).attr("colspan", $("td", row).length);
$("td:first-child", empty_row).attr("align", "center");
$("td:first-child", empty_row).html("No records found for the search criteria.");
$("td", empty_row).not($("td:first-child", empty_row)).remove();
$("[id*=tblCustomers]").append(empty_row);
}
var pager = xml.find("Pager");
$(".Pager").ASPSnippets_Pager({
ActiveCssClass: "current",
PagerCssClass: "pager",
PageIndex: parseInt(pager.find("PageIndex").text()),
PageSize: parseInt(pager.find("PageSize").text()),
RecordCount: parseInt(pager.find("RecordCount").text())
});
};
</script>
<script type="text/javascript">
$(function () {
$('#btnCSV').on('click', function () {
$('body').prepend("<form method='post' action='Handler.ashx' id='tempCSV'>" +
"<input type='hidden' name='data' value='CSV' ></form>");
$('#tempCSV').submit();
$("#tempCSV").remove();
});
$('#btnWord').on('click', function () {
$('body').prepend("<form method='post' action='Handler.ashx' id='tempWord'>" +
"<input type='hidden' name='data' value='Word' ></form>");
$('#tempWord').submit();
$("#tempWord").remove();
});
$('#btnExcel').on('click', function () {
$('body').prepend("<form method='post' action='Handler.ashx' id='tempExcel'>" +
"<input type='hidden' name='data' value='Excel' ></form>");
$('#tempExcel').submit();
$("#tempExcel").remove();
});
$('#btnPDF').on('click', function () {
$('body').prepend("<form method='post' action='Handler.ashx' id='tempPdf'>" +
"<input type='hidden' name='data' value='PDF' ></form>");
$('#tempPdf').submit();
$("#tempPdf").remove();
});
$('#btnPrint').on('click', function () {
$.ajax({
type: "POST",
url: "Default.aspx/Print",
data: {},
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var printWindow = window.open('', '', 'height=400,width=800');
printWindow.document.write('<html><head>');
printWindow.document.write('</head><body >');
printWindow.document.write(response.d);
printWindow.document.write('</body></html>');
printWindow.document.close();
setTimeout(function () {
printWindow.print();
}, 500);
},
error: function (response) {
alert(response.d);
}
});
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<center>
<table>
<tr>
<td><input id="btnCSV" type="button" value="CSV" /></td>
<td><input id="btnWord" type="button" value="Word" /></td>
<td><input id="btnExcel" type="button" value="Excel" /></td>
<td><input id="btnPDF" type="button" value="PDF" /></td>
<td><input id="btnPrint" type="button" value="Print" /></td>
</tr>
</table>
<br />
Name : <input type="text" name="name" id="txtSearch" /><br /><br />
<table id="tblCustomers" width="25%">
<tr>
<th><a href="#" data-sort="CustomerID">CustomerID</a> <span class="arrow arrow-up"></span></th>
<th><a href="#" data-sort="ContactName">Contact Name</a> <span class="arrow"></span></th>
<th><a href="#" data-sort="City">City</a> <span class="arrow"></span></th>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
</tr>
</table><br />
<div class="Pager"></div>
</center>
</form>
</body>
</html>
Namespaces
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
VB.Net
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.Services
Code
C#
private static int PageSize = 10;
[WebMethod]
public static string GetCustomers(int pageIndex, string searchTerm, string sortDirection, string sortColumn)
{
string query = "[GetCustomersPageWise_Sort]";
SqlCommand cmd = new SqlCommand(query);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm);
cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
cmd.Parameters.AddWithValue("@PageSize", PageSize);
cmd.Parameters.AddWithValue("@SortDirection", sortDirection);
cmd.Parameters.AddWithValue("@SortColumn", sortColumn);
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
return GetData(cmd, pageIndex).GetXml();
}
private static DataSet GetData(SqlCommand cmd, int pageIndex)
{
string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataSet ds = new DataSet())
{
sda.Fill(ds, "Customers");
DataTable dt = new DataTable("Pager");
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageSize");
dt.Columns.Add("RecordCount");
dt.Rows.Add();
dt.Rows[0]["PageIndex"] = pageIndex;
dt.Rows[0]["PageSize"] = PageSize;
dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
ds.Tables.Add(dt);
return ds;
}
}
}
}
[WebMethod]
public static string Print()
{
DataTable dt = new DataTable();
string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlCommand cmd = new SqlCommand("SELECT CustomerID, ContactName, City FROM Customers");
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
}
}
string gridHtml = "<table border='1'><tr>";
for (int k = 0; k < dt.Columns.Count; k++)
{
gridHtml += "<th>" + dt.Columns[k].ColumnName + "</th>";
}
gridHtml += "</tr>";
for (int i = 0; i < dt.Rows.Count; i++)
{
gridHtml += "<tr>";
for (int k = 0; k < dt.Columns.Count; k++)
{
gridHtml += "<td>" + dt.Rows[i][k].ToString().Trim() + "</td>";
}
gridHtml += "</tr>";
}
gridHtml += "</table>";
return gridHtml;
}
VB.Net
Private Shared PageSize As Integer = 10
<WebMethod()> _
Public Shared Function GetCustomers(ByVal pageIndex As Integer, ByVal searchTerm As String, ByVal sortDirection As String, ByVal sortColumn As String) As String
Dim query As String = "[GetCustomersPageWise_Sort]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", PageSize)
cmd.Parameters.AddWithValue("@SortDirection", sortDirection)
cmd.Parameters.AddWithValue("@SortColumn", sortColumn)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
<WebMethod()> _
Public Shared Function Print() As String
Dim dt As DataTable = New DataTable()
Dim conString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim cmd As SqlCommand = New SqlCommand("SELECT CustomerID, ContactName, City FROM Customers")
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
sda.Fill(dt)
End Using
End Using
Dim gridHtml As String = "<table border='1'><tr>"
For k As Integer = 0 To dt.Columns.Count - 1
gridHtml += "<th>" & dt.Columns(k).ColumnName & "</th>"
Next
gridHtml += "</tr>"
For i As Integer = 0 To dt.Rows.Count - 1
gridHtml += "<tr>"
For k As Integer = 0 To dt.Columns.Count - 1
gridHtml += "<td>" & dt.Rows(i)(k).ToString().Trim() & "</td>"
Next
gridHtml += "</tr>"
Next
gridHtml += "</table>"
Return gridHtml
End Function
Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Using con As SqlConnection = New SqlConnection(strConnString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds, "Customers")
Dim dt As DataTable = New DataTable("Pager")
dt.Columns.Add("PageIndex")
dt.Columns.Add("PageSize")
dt.Columns.Add("RecordCount")
dt.Rows.Add()
dt.Rows(0)("PageIndex") = pageIndex
dt.Rows(0)("PageSize") = PageSize
dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
Handler
C#
<%@ WebHandler Language="C#" Class="Handler" %>
using ClosedXML.Excel;
using System;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Web;
public class Handler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string strQuery = "SELECT CustomerID, ContactName, City FROM Customers";
DataTable dt = GetData(strQuery);
if (dt.Rows.Count > 0)
{
if (context.Request.Form["data"] == "CSV")
{
context.Response.Clear();
context.Response.Buffer = true;
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.csv");
context.Response.Charset = "";
context.Response.ContentType = "application/text";
StringBuilder sb = new StringBuilder();
for (int k = 0; k < dt.Columns.Count; k++)
{
sb.Append(dt.Columns[k].ColumnName + ',');
}
sb.Append("\r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
}
sb.Append("\r\n");
}
context.Response.Output.Write(sb.ToString());
context.Response.Flush();
context.Response.End();
}
else if (context.Request.Form["data"] == "Word")
{
string gridHtml = "<table border='1'><tr>";
for (int k = 0; k < dt.Columns.Count; k++)
{
gridHtml += "<th>" + dt.Columns[k].ColumnName + "</th>";
}
gridHtml += "</tr>";
for (int i = 0; i < dt.Rows.Count; i++)
{
gridHtml += "<tr>";
for (int k = 0; k < dt.Columns.Count; k++)
{
gridHtml += "<td>" + dt.Rows[i][k].ToString().Trim() + "</td>";
}
gridHtml += "</tr>";
}
gridHtml += "</table>";
context.Response.Clear();
context.Response.Buffer = true;
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.doc");
context.Response.Charset = "";
context.Response.ContentType = "application/vnd.ms-word";
context.Response.Output.Write(gridHtml);
context.Response.Flush();
context.Response.End();
}
else if (context.Request.Form["data"] == "Excel")
{
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Customers");
context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "";
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(context.Response.OutputStream);
context.Response.Flush();
context.Response.End();
}
}
}
else if (context.Request.Form["data"] == "PDF")
{
Document pdfDoc = new Document(PageSize.A4.Rotate(), 10, 10, 10, 10);
PdfWriter.GetInstance(pdfDoc, System.Web.HttpContext.Current.Response.OutputStream);
pdfDoc.Open();
PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
PdfTable.SpacingBefore = 20f;
for (int columns = 0; columns <= dt.Columns.Count - 1; columns++)
{
PdfPCell PdfPCell = new PdfPCell(new Phrase(new Chunk(dt.Columns[columns].ColumnName, FontFactory.GetFont("ARIAL", 20))));
PdfTable.AddCell(PdfPCell);
}
for (int rows = 0; rows <= dt.Rows.Count - 1; rows++)
{
for (int column = 0; column <= dt.Columns.Count - 1; column++)
{
PdfPCell PdfPCell = new PdfPCell(new Phrase(new Chunk(dt.Rows[rows][column].ToString(), FontFactory.GetFont("ARIAL", 10))));
PdfTable.AddCell(PdfPCell);
}
}
pdfDoc.Add(PdfTable);
pdfDoc.Close();
context.Response.ContentType = "application/pdf";
context.Response.AddHeader("content-disposition", "attachment; filename=Customers.pdf");
context.Response.Write(pdfDoc);
context.Response.Flush();
context.Response.End();
}
}
}
private DataTable GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
public bool IsReusable
{
get { return false; }
}
}
VB.Net
<%@ WebHandler Language="VB" Class="Handler" %>
Imports ClosedXML.Excel
Imports System
Imports System.Text
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports System.Web
Public Class Handler : Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim strQuery As String = "SELECT CustomerID, ContactName, City FROM Customers"
Dim dt As DataTable = GetData(strQuery)
If dt.Rows.Count > 0 Then
If context.Request.Form("data") = "CSV" Then
context.Response.Clear()
context.Response.Buffer = True
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.csv")
context.Response.Charset = ""
context.Response.ContentType = "application/text"
Dim sb As StringBuilder = New StringBuilder()
For k As Integer = 0 To dt.Columns.Count - 1
sb.Append(dt.Columns(k).ColumnName + ","c)
Next
sb.Append(vbCrLf)
For i As Integer = 0 To dt.Rows.Count - 1
For k As Integer = 0 To dt.Columns.Count - 1
sb.Append(dt.Rows(i)(k).ToString().Replace(",", ";") + ","c)
Next
sb.Append(vbCrLf)
Next
context.Response.Output.Write(sb.ToString())
context.Response.Flush()
context.Response.End()
ElseIf context.Request.Form("data") = "Word" Then
Dim gridHtml As String = "<table border='1'><tr>"
For k As Integer = 0 To dt.Columns.Count - 1
gridHtml += "<th>" & dt.Columns(k).ColumnName & "</th>"
Next
gridHtml += "</tr>"
For i As Integer = 0 To dt.Rows.Count - 1
gridHtml += "<tr>"
For k As Integer = 0 To dt.Columns.Count - 1
gridHtml += "<td>" & dt.Rows(i)(k).ToString().Trim() & "</td>"
Next
gridHtml += "</tr>"
Next
gridHtml += "</table>"
context.Response.Clear()
context.Response.Buffer = True
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.doc")
context.Response.Charset = ""
context.Response.ContentType = "application/vnd.ms-word"
context.Response.Output.Write(gridHtml)
context.Response.Flush()
context.Response.End()
ElseIf context.Request.Form("data") = "Excel" Then
Using wb As XLWorkbook = New XLWorkbook()
wb.Worksheets.Add(dt, "Customers")
context.Response.Clear()
context.Response.Buffer = True
context.Response.Charset = ""
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
context.Response.AddHeader("content-disposition", "attachment;filename=Customers.xlsx")
Using MyMemoryStream As MemoryStream = New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(context.Response.OutputStream)
context.Response.Flush()
context.Response.End()
End Using
End Using
ElseIf context.Request.Form("data") = "PDF" Then
Dim pdfDoc As Document = New Document(PageSize.A4.Rotate(), 10, 10, 10, 10)
PdfWriter.GetInstance(pdfDoc, System.Web.HttpContext.Current.Response.OutputStream)
pdfDoc.Open()
Dim PdfTable As PdfPTable = New PdfPTable(dt.Columns.Count)
PdfTable.SpacingBefore = 20.0F
For columns As Integer = 0 To dt.Columns.Count - 1
Dim PdfPCell As PdfPCell = New PdfPCell(New Phrase(New Chunk(dt.Columns(columns).ColumnName, FontFactory.GetFont("ARIAL", 20))))
PdfTable.AddCell(PdfPCell)
Next
For rows As Integer = 0 To dt.Rows.Count - 1
For column As Integer = 0 To dt.Columns.Count - 1
Dim PdfPCell As PdfPCell = New PdfPCell(New Phrase(New Chunk(dt.Rows(rows)(column).ToString(), FontFactory.GetFont("ARIAL", 10))))
PdfTable.AddCell(PdfPCell)
Next
Next
pdfDoc.Add(PdfTable)
pdfDoc.Close()
context.Response.ContentType = "application/pdf"
context.Response.AddHeader("content-disposition", "attachment; filename=Customers.pdf")
context.Response.Write(pdfDoc)
context.Response.Flush()
context.Response.End()
End If
End If
End Sub
Private Function GetData(ByVal query As String) As DataTable
Dim conString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As DataTable = New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
Screenshots
CSV
Excel
Word
PDF
Print
