Export GridView To Word Excel PDF CSV Formats in ASPNet

In this article I will explain with an example, how to export GridView to Word, Excel, PDF and CSV formats in ASP.Net using C# and VB.Net.

Exporting GridView to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting to PDF iTextSharp library will be used.

This article will illustrate how to export all Pages of GridView with Paging Enabled to Word, Excel, PDF and CSV file along with formatting i.e. Styles and Colors in ASP.Net.

Download iTextSharp and XmlWorkerHelper Libraries

The iTextSharp and XmlWorkerHelper Libraries are available in the attached sample of this article.

Database

For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.

Download and install Northwind Database

HTML Markup

The following HTML Markup consists of an ASP.Net GridView control with three BoundField columns and four Button controls.

Paging has been enabled for the GridView control. The AllowPaging property is set to True and the OnPageIndexChanging event has been specified for the GridView.

The Buttons have been assigned OnClick event handlers which will perform the operation for exporting GridView to Word, Excel, PDF and CSV files.

<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"

Font-Names="Arial" Font-Size="10" RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White"

AlternatingRowStyle-ForeColor="#000" runat="server" AutoGenerateColumns="false"

AllowPaging="true" OnPageIndexChanging="OnPageIndexChanging">

<asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px"/>

<asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px"/>

<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px"/>

</Columns>

</asp:GridView>

<br/>

<asp:Button ID="btnExcel" runat="server" Text="Export To Excel" OnClick="ExportToExcel" Width="120"/>

<asp:Button ID="btnWord" runat="server" Text="Export To Word" OnClick="ExportToWord" Width="120"/>

<asp:Button ID="btnCSV" runat="server" Text="Export To CSV" OnClick="ExportToCSV" Width="120"/>

<asp:Button ID="btnPDF" runat="server" Text="Export To PDF" OnClick="ExportToPDF" Width="120"/>

Namespaces

You will need to import the following namespaces.

using System.IO;

using System.Text;

using System.Data;

using System.Drawing;

using System.Data.SqlClient;

using System.Configuration;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.tool.xml;

VB.Net

Imports System.IO

Imports System.Text

Imports System.Data

Imports System.Drawing

Imports System.Data.SqlClient

Imports System.Configuration

Imports iTextSharp.text

Imports iTextSharp.text.pdf

Imports iTextSharp.tool.xml

Binding the GridView

Inside the Page Load event, the GridView is populated with records from the Customers Table of the Northwind Database.

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

this.BindGrid();

}

private void BindGrid()

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))

{

using (SqlDataAdapter sda = new SqlDataAdapter())

{

cmd.Connection = con;

sda.SelectCommand = cmd;

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

VB.Net

Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Me.BindGrid()

End If

End Sub

Private Sub BindGrid()

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As New SqlConnection(constr)

Using cmd As New SqlCommand("SELECT * FROM Customers")

Using sda As New SqlDataAdapter()

cmd.Connection = con

sda.SelectCommand = cmd

Using dt As New DataTable()

sda.Fill(dt)

GridView1.DataSource = dt

GridView1.DataBind()

End Using

End Sub

Implement Paging in GridView

The OnPageIndexChanging event handles the Pagination in the GridView.

Inside the OnPageIndexChanging event handler, the GridView’s PageIndex property is updated and the GridView is again populated from Database by making call to the BindGrid method.

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)

{

GridView1.PageIndex = e.NewPageIndex;

this.BindGrid();

}

VB.Net

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)

GridView1.PageIndex = e.NewPageIndex

Me.BindGrid()

End Sub

Exporting GridView to Excel with Formatting

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is an Excel file.

The Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database by making call to the BindGrid method.

Then a loop is executed over the GridView rows and the style and formatting are applied to the Row and Alternating Row of the GridView control.

Note: The colors are applied to individual cell of each Row and not to the whole Row as if this is not done then the color will spread on all cells of the Excel sheet.

CSS class named textmode is applied to each cell of the GridView. This class makes sure that all the contents are rendered in Text format (mso number format).

Note: The mso number format style prevents large numbers from getting converted to exponential values.

protected void ExportToExcel(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

using (StringWriter sw = new StringWriter())

{

HtmlTextWriter hw = new HtmlTextWriter(sw);

//To Export all pages.

GridView1.AllowPaging = false;

this.BindGrid();

GridView1.HeaderRow.BackColor = Color.White;

foreach (TableCell cell in GridView1.HeaderRow.Cells)

{

cell.BackColor = GridView1.HeaderStyle.BackColor;

}

foreach (GridViewRow row in GridView1.Rows)

{

row.BackColor = Color.White;

foreach (TableCell cell in row.Cells)

{

if (row.RowIndex % 2 == 0)

{

cell.BackColor = GridView1.AlternatingRowStyle.BackColor;

}

else

{

cell.BackColor = GridView1.RowStyle.BackColor;

}

cell.CssClass = "textmode";

}

GridView1.RenderControl(hw);

//Style to format numbers to string.

string style = @"<style> .textmode { mso-number-format:\@; } </style>";

Response.Write(style);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub ExportToExcel(sender As Object, e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")

Response.Charset = ""

Response.ContentType = "application/vnd.ms-excel"

Using sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

'To Export all pages.

GridView1.AllowPaging = False

Me.BindGrid()

GridView1.HeaderRow.BackColor = Color.White

For Each cell As TableCell In GridView1.HeaderRow.Cells

cell.BackColor = GridView1.HeaderStyle.BackColor

For Each row As GridViewRow In GridView1.Rows

row.BackColor = Color.White

For Each cell As TableCell In row.Cells

If row.RowIndex Mod 2 = 0 Then

cell.BackColor = GridView1.AlternatingRowStyle.BackColor

Else

cell.BackColor = GridView1.RowStyle.BackColor

End If

cell.CssClass = "textmode"

GridView1.RenderControl(hw)

'Style to format numbers to string.

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

Exporting GridView to Word document with Formatting

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is a Word document.

The Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database by making call to the BindGrid method.

Then a loop is executed over the GridView rows and the style and formatting are applied to the Row and Alternating Row of the GridView control.

Finally, GridView is rendered as Word document and written to the Response which initiates the File download.

protected void ExportToWord(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.doc");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-word";

using (StringWriter sw = new StringWriter())

{

HtmlTextWriter hw = new HtmlTextWriter(sw);

//To Export all pages.

GridView1.AllowPaging = false;

this.BindGrid();

GridView1.HeaderRow.BackColor = Color.White;

foreach (TableCell cell in GridView1.HeaderRow.Cells)

{

cell.BackColor = GridView1.HeaderStyle.BackColor;

}

foreach (GridViewRow row in GridView1.Rows)

{

row.BackColor = Color.White;

foreach (TableCell cell in row.Cells)

{

if (row.RowIndex % 2 == 0)

{

cell.BackColor = GridView1.AlternatingRowStyle.BackColor;

}

else

{

cell.BackColor = GridView1.RowStyle.BackColor;

}

GridView1.RenderControl(hw);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub ExportToWord(ByVal sender As Object, ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.doc")

Response.Charset = ""

Response.ContentType = "application/vnd.ms-word"

Using sw As StringWriter = New StringWriter()

Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

'To Export all pages.

GridView1.AllowPaging = False

Me.BindGrid()

GridView1.HeaderRow.BackColor = Color.White

For Each cell As TableCell In GridView1.HeaderRow.Cells

cell.BackColor = GridView1.HeaderStyle.BackColor

For Each row As GridViewRow In GridView1.Rows

row.BackColor = Color.White

For Each cell As TableCell In row.Cells

If row.RowIndex Mod 2 = 0 Then

cell.BackColor = GridView1.AlternatingRowStyle.BackColor

Else

cell.BackColor = GridView1.RowStyle.BackColor

End If

GridView1.RenderControl(hw)

Response.Output.Write(sw.ToString())

Response.Flush()

Response.End()

End Using

End Sub

Exporting GridView to CSV file

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is a CSV file.

The Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database by making call to the BindGrid method.

Then a loop is executed over the Columns and Rows of the GridView and a comma separated (delimited) string is generated.

Finally, comma separated (delimited) string is written to the Response which initiates the File download.

protected void ExportToCSV(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv");

Response.Charset = "";

Response.ContentType = "text/csv";

//To Export all pages.

GridView1.AllowPaging = false;

this.BindGrid();

StringBuilder sb = new StringBuilder();

foreach (TableCell cell in GridView1.HeaderRow.Cells)

{

//Append data with separator.

sb.Append(cell.Text + ',');

}

//Append new line character.

sb.Append("\r\n");

foreach (GridViewRow row in GridView1.Rows)

{

foreach (TableCell cell in row.Cells)

{

//Append data with separator.

sb.Append(cell.Text + ',');

}

//Append new line character.

sb.Append("\r\n");

}

Response.Output.Write(sb.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub ExportToCSV(ByVal sender As Object, ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv")

Response.Charset = ""

Response.ContentType = "text/csv"

GridView1.AllowPaging = False

Me.BindGrid()

Dim sb As StringBuilder = New StringBuilder()

For Each cell As TableCell In GridView1.HeaderRow.Cells

'Append data with separator.

sb.Append(cell.Text & ",")

'Append new line character.

sb.Append(vbCr & vbLf)

For Each row As GridViewRow In GridView1.Rows

For Each cell As TableCell In row.Cells

'Append data with separator.

sb.Append(cell.Text & ",")

'Append new line character.

sb.Append(vbCr & vbLf)

Response.Output.Write(sb.ToString())

Response.Flush()

Response.End()

End Sub

Exporting GridView to PDF with Formatting

When the Export Button is clicked, first the Content Type is set in the Response Stream in order to specify the Browser that the downloading file is a PDF file.

The Paging is disabled for the GridView by setting the AllowPaging property to False and the GridView is again populated with records from the Database by making call to the BindGrid method.

First, the GridView is rendered into an HTML string using HtmlTextWriter and then the generated HTML is added to the iTextSharp PDF Document, which is then export to PDF through the Response Stream.

protected void ExportToPDF(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.pdf");

Response.Charset = "";

Response.ContentType = "application/pdf";

//To Export all pages.

GridView1.AllowPaging = false;

this.BindGrid();

using (StringWriter sw = new StringWriter())

{

using (HtmlTextWriter hw = new HtmlTextWriter(sw))

{

GridView1.RenderControl(hw);

StringReader sr = new StringReader(sw.ToString());

Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

PdfWriter writer = PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

pdfDoc.Open();

XMLWorkerHelper.GetInstance().ParseXHtml(writer, pdfDoc, sr);

pdfDoc.Close();

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.Write(pdfDoc);

Response.End();

}

VB.Net

Protected Sub ExportToPDF(ByVal sender As Object, ByVal e As EventArgs)

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.pdf")

Response.Charset = ""

Response.ContentType = "application/pdf"

'To Export all pages.

GridView1.AllowPaging = False

Me.BindGrid()

Using sw As StringWriter = New StringWriter()

Using hw As HtmlTextWriter = New HtmlTextWriter(sw)

GridView1.RenderControl(hw)

Dim sr As StringReader = New StringReader(sw.ToString())

Dim pdfDoc As Document = New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)

Dim writer As PdfWriter = PdfWriter.GetInstance(pdfDoc, Response.OutputStream)

pdfDoc.Open()

XMLWorkerHelper.GetInstance().ParseXHtml(writer, pdfDoc, sr)

pdfDoc.Close()

Response.Cache.SetCacheability(HttpCacheability.NoCache)

Response.Write(pdfDoc)

Response.End()

End Using

End Sub

Error

When you run the application first time and click on export you might receive the following error.

Server Error in '/ASP.Net' Application.

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

The solution to the above exception is to override VerifyRenderingInServerForm event handler, thus informing the Compiler that the control has been rendered explicitly.

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}

VB.Net

Public Overrides Sub VerifyRenderingInServerForm(control As Control)

' Verifies that the control is rendered

End Sub

Screenshots

GridView with Paging enabled