Post
Ask Question

How To Export GridView Data In Word, Excel And PDF Format Using ASP.NET

Introduction

In this article, I will demonstrate how to export GridView data into Word, Excel, and pdf files using ASP.NET.

I will use the jQuery plugin to search, sort, and paginate the data.

Step 1

Open SQL Server 2014 and create a database table.

  1. CREATE TABLE [dbo].[EmployeeList](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Position] [nvarchar](50) NULL,  
  5.     [Office] [nvarchar](50) NULL,  
  6.     [Age] [int] NULL,  
  7.     [Start_Date] [nvarchar](50) NULL,  
  8.     [Salary] [nvarchar](50) NULL,  
  9.  CONSTRAINT [PK_EmployeeList] PRIMARY KEY CLUSTERED   
  10. (  
  11.     [ID] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  13. ) ON [PRIMARY]  
  14.   
  15. GO   
  1. Create procedure [dbo].[spGetAllEmployeeList]  
  2. as  
  3. begin  
  4. select ID,Name,Position,Office,Age,Start_Date,Salary from [dbo].[EmployeeList]  
  5. end  
ASP.NET
 
Step 2

Open Visual Studio 2015 and click on New Project

Screenshot-1

ASP.NET

After clicking on New Project, one window will appear. Select Web from the left panel to choose ASP.NET Web Application, give a meaningful name to your project, then click OK.

ASP.NET

 After clicking on OK, one more window will appear. Choose Empty check on Web Forms checkbox and click on OK, as shown in the below screenshot.

ASP.NET

After clicking on OK, the project will get created with the name as ExportWordExcelAndPDF_Demo.

Step 3

Right-click on web config file to add the database connection.

  1. <connectionStrings>  
  2.   <add name="DBCS" connectionString="data source=DESKTOP-M021QJH\SQLEXPRESS; database=SampleDB; integrated security=true;"/>  
  3. </connectionStrings>   

Step 4

Right-click on the project on Solution Explorer, select Add, choose New Item, and click on it.

Screenshot-1

ASP.NET

Another window will appear. Select web from the left panel and choose Web Form, give it a meaningful name and click on Add. The Web Form will be added to the project.

Screenshot-2

ASP.NET

Step 5

Click on Tools >> NuGet Package Manager >> Manage NuGet Packages for Solution.

Screenshot for NuGet Package

ASP.NET

After that, a window will appear. Choose Browse >> type bootstrap and install the relevant package from the list.

Similarly, type jQuery and install the latest version of jQuery package in your project along with the jQuery validation file from NuGet and then, close the NuGet Solution.

ASP.NET

Keep the required bootstrap and jQuery files while delete the remaining files if not using. Or you can download from and add in project.

ASP.NET

Step 6

Add the following styles and scripts in head section of the Web Form.

  1. <link href="Content/bootstrap.min.css" rel="stylesheet" />  
  2.     <script src="scripts/jquery-3.3.1.min.js"></script>  
  3.     <script src="scripts/bootstrap.min.js"></script>  
  4.     <link href="Content/dataTables.bootstrap4.min.css" rel="stylesheet" />  
  5.     <script src="scripts/dataTables.bootstrap4.min.js"></script>  
  6.     <script src="scripts/jquery.dataTables.min.js"></script>  
  7.     <script type="text/javascript">  
  8.         $(document).ready(function () {  
  9.             $("#EmployeeGridViewList").prepend($("<thead></thead>").append($(this).find("tr:first"))).dataTable();  
  10.         });  
  11.     </script>  
  12.     <style>  
  13.         .btnMargin {  
  14.             margin-bottom: 10px !important;  
  15.         }  
  16.     </style>  

Step 7

Design the Web Form using HTML, Bootstrap, and ASP.NET buttons and GridView control.

  1. <body>  
  2.     <form id="form1" runat="server">  
  3.         <div class="container py-4">  
  4.             <h5 class="text-uppercase text-center">How to export gridview data in word,excel and Pdf format using asp.net</h5>  
  5.             <div class="card">  
  6.                 <div class="card-header bg-primary text-white">  
  7.                     <h5 class="card-title text-uppercase">Employees List</h5>  
  8.                 </div>  
  9.                 <div class="card-body">  
  10.                     <asp:Button ID="btnExportToWord" CssClass="btnMargin btn btn-outline-primary rounded-0" runat="server" Text="ExportToWord" OnClick="btnExportToWord_Click" />  
  11.                     <asp:Button ID="btnExportToExcel" CssClass="btnMargin btn btn-outline-primary rounded-0" runat="server" Text="ExportToExcel" OnClick="btnExportToExcel_Click" />  
  12.                     <asp:Button ID="btnExportToPDF" CssClass="btnMargin btn btn-outline-primary rounded-0" runat="server" Text="ExportToPDF" OnClick="btnExportToPDF_Click" />  
  13.                     <asp:GridView ID="EmployeeGridViewList" CssClass="table table-bordered" runat="server"></asp:GridView>  
  14.                 </div>  
  15.             </div>  
  16.         </div>  
  17.     </form>  
  18. </body>  

Step 8

Right-click on Web Form, select view code, and click on it.

Add namespace

  1. using System;  
  2. using System.IO;  
  3. using System.Web.UI;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Configuration;  
  7. using System.Web;  
  8. using iTextSharp.text;  
  9. using iTextSharp.text.html.simpleparser;  
  10. using iTextSharp.text.pdf;  

Bind GridView with the database

  1. protected void Page_Load(object sender, EventArgs e)  
  2.         {  
  3.             if (!IsPostBack)  
  4.             {  
  5.                 BindGridView();  
  6.             }  
  7.         }  
  8.   
  9.         private void BindGridView()  
  10.         {  
  11.             string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  12.             using (SqlConnection con = new SqlConnection(CS))  
  13.             {  
  14.                 SqlCommand cmd = new SqlCommand("spGetAllEmployeeList", con);  
  15.                 cmd.CommandType = CommandType.StoredProcedure;  
  16.                 con.Open();  
  17.                 EmployeeGridViewList.DataSource = cmd.ExecuteReader();  
  18.                 EmployeeGridViewList.DataBind();  
  19.             }  
  20.         }  

Add the following method to export Word and Excel format.

  1. public override void VerifyRenderingInServerForm(Control control)  
  2. {  
  3. }  

Step 9

Double click on ExportToWord control. Write the following code.

  1. protected void btnExportToWord_Click(object sender, EventArgs e)  
  2.         {  
  3.             Response.ClearContent();  
  4.             Response.AppendHeader("content-disposition", "attachment; filename=Employees.doc");  
  5.             Response.ContentType = "application/word";  
  6.             StringWriter stringWriter = new StringWriter();  
  7.             HtmlTextWriter htw = new HtmlTextWriter(stringWriter);  
  8.             EmployeeGridViewList.HeaderRow.Style.Add("background-color", "#FFFFFF");  
  9.             EmployeeGridViewList.RenderControl(htw);  
  10.             Response.Write(stringWriter.ToString());  
  11.             Response.End();  
  12.         }  

Step 10

Double click on ExportToExcel control. Write the following code.

  1. protected void btnExportToExcel_Click(object sender, EventArgs e)  
  2.         {  
  3.             Response.ClearContent();  
  4.             Response.AppendHeader("content-disposition", "attachment; filename=Employees.xls");  
  5.             Response.ContentType = "application/excel";  
  6.             StringWriter stringWriter = new StringWriter();  
  7.             HtmlTextWriter htw = new HtmlTextWriter(stringWriter);  
  8.             EmployeeGridViewList.HeaderRow.Style.Add("background-color", "#FFFFFF");  
  9.             EmployeeGridViewList.RenderControl(htw);  
  10.             Response.Write(stringWriter.ToString());  
  11.             Response.End();  
  12.         }  

Step 11

Double-click on ExportToPDF control. Write the following code. To export the PDF format, we need to add itextsharp.dll from here - https://sourceforge.net/projects/itextsharp/

  1. protected void btnExportToPDF_Click(object sender, EventArgs e)  
  2.         {  
  3.             Response.ContentType = "application/pdf";  
  4.             Response.AddHeader("content-disposition","attachment;filename=Employees.pdf");  
  5.             Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  6.             StringWriter sw = new StringWriter();  
  7.             HtmlTextWriter hw = new HtmlTextWriter(sw);  
  8.             EmployeeGridViewList.RenderControl(hw);  
  9.             StringReader sr = new StringReader(sw.ToString());  
  10.             Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);  
  11.             #pragma warning disable CS0612 // Type or member is obsolete  
  12.             HTMLWorker htmlparser = new HTMLWorker(pdfDoc);  
  13.             #pragma warning restore CS0612 // Type or member is obsolete  
  14.             PdfWriter.GetInstance(pdfDoc, Response.OutputStream);  
  15.             pdfDoc.Open();  
  16.             htmlparser.Parse(sr);  
  17.             pdfDoc.Close();  
  18.             Response.Write(pdfDoc);  
  19.             Response.End();  
  20.             EmployeeGridViewList.AllowPaging = true;  
  21.             EmployeeGridViewList.DataBind();  
  22.         }  

Complete code of Web Form

  1. using System;  
  2. using System.IO;  
  3. using System.Web.UI;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Configuration;  
  7. using System.Web;  
  8. using iTextSharp.text;  
  9. using iTextSharp.text.html.simpleparser;  
  10. using iTextSharp.text.pdf;  
  11.   
  12. namespace ExportWordExcelCSVAndPDF_Demo  
  13. {  
  14.     public partial class EmployeesList : System.Web.UI.Page  
  15.     {  
  16.         protected void Page_Load(object sender, EventArgs e)  
  17.         {  
  18.             if (!IsPostBack)  
  19.             {  
  20.                 BindGridView();  
  21.             }  
  22.         }  
  23.   
  24.         private void BindGridView()  
  25.         {  
  26.             string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  27.             using (SqlConnection con = new SqlConnection(CS))  
  28.             {  
  29.                 SqlCommand cmd = new SqlCommand("spGetAllEmployeeList", con);  
  30.                 cmd.CommandType = CommandType.StoredProcedure;  
  31.                 con.Open();  
  32.                 EmployeeGridViewList.DataSource = cmd.ExecuteReader();  
  33.                 EmployeeGridViewList.DataBind();  
  34.             }  
  35.         }  
  36.   
  37.         protected void btnExportToWord_Click(object sender, EventArgs e)  
  38.         {  
  39.             Response.ClearContent();  
  40.             Response.AppendHeader("content-disposition", "attachment; filename=Employees.doc");  
  41.             Response.ContentType = "application/word";  
  42.             StringWriter stringWriter = new StringWriter();  
  43.             HtmlTextWriter htw = new HtmlTextWriter(stringWriter);  
  44.             EmployeeGridViewList.HeaderRow.Style.Add("background-color", "#FFFFFF");  
  45.             EmployeeGridViewList.RenderControl(htw);  
  46.             Response.Write(stringWriter.ToString());  
  47.             Response.End();  
  48.         }  
  49.   
  50.         protected void btnExportToExcel_Click(object sender, EventArgs e)  
  51.         {  
  52.             Response.ClearContent();  
  53.             Response.AppendHeader("content-disposition", "attachment; filename=Employees.xls");  
  54.             Response.ContentType = "application/excel";  
  55.             StringWriter stringWriter = new StringWriter();  
  56.             HtmlTextWriter htw = new HtmlTextWriter(stringWriter);  
  57.             EmployeeGridViewList.HeaderRow.Style.Add("background-color", "#FFFFFF");  
  58.             EmployeeGridViewList.RenderControl(htw);  
  59.             Response.Write(stringWriter.ToString());  
  60.             Response.End();  
  61.         }  
  62.   
  63.         public override void VerifyRenderingInServerForm(Control control)  
  64.         {  
  65.   
  66.         }  
  67.   
  68.         protected void btnExportToPDF_Click(object sender, EventArgs e)  
  69.         {  
  70.             Response.ContentType = "application/pdf";  
  71.             Response.AddHeader("content-disposition","attachment;filename=Employees.pdf");  
  72.             Response.Cache.SetCacheability(HttpCacheability.NoCache);  
  73.             StringWriter sw = new StringWriter();  
  74.             HtmlTextWriter hw = new HtmlTextWriter(sw);  
  75.             EmployeeGridViewList.RenderControl(hw);  
  76.             StringReader sr = new StringReader(sw.ToString());  
  77.             Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);  
  78.             #pragma warning disable CS0612 // Type or member is obsolete  
  79.             HTMLWorker htmlparser = new HTMLWorker(pdfDoc);  
  80.             #pragma warning restore CS0612 // Type or member is obsolete  
  81.             PdfWriter.GetInstance(pdfDoc, Response.OutputStream);  
  82.             pdfDoc.Open();  
  83.             htmlparser.Parse(sr);  
  84.             pdfDoc.Close();  
  85.             Response.Write(pdfDoc);  
  86.             Response.End();  
  87.             EmployeeGridViewList.AllowPaging = true;  
  88.             EmployeeGridViewList.DataBind();  
  89.         }  
  90.     }  
  91. }  

Step 12

Run the project by pressing Ctrl+F5.

ASP.NET

Conclusion

In this article, I have explained how to export GridView data into Word, Excel and PDF step by step.

I hope it will be helpful.


Similar Articles