Export DataSet or DataTable to Word Excel PDF and CSV Formats

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

Exporting DataSet or DataTable 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.

Download iTextSharp and XmlWorkerHelper Libraries

You can download the iTextSharp and XmlWorkerHelper libraries from the following links.

iTextSharp

XmlWorkerHelper

Note: You will need to add the reference of iTextSharp and XmlWorkerHelper libraries in your project.

Database

For this article I am making use of the Microsoft’s Northwind Database. You can download it from here.

Download and install Northwind Database

HTML Markup

The HTML Markup consists of:

Buttons – For exporting DataSet or DataTable.

The Buttons have been assigned with OnClick event handlers to perform different exporting operations.

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

<br />

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

<br />

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

<br />

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

Namespaces

You will need to import the following namespaces.

using System.IO;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Web.UI;

using iTextSharp.text;

using iTextSharp.text.pdf;

using iTextSharp.tool.xml;

VB.Net

Imports System.IO

Imports System.Text

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Imports System.Web.UI

Imports iTextSharp.text

Imports iTextSharp.text.pdf

Imports iTextSharp.tool.xml

GetData Method

The GetData method is executed inside each Button click event hander to provide data to be exported which will be discussed later in this article.

This method accepts SQL query as parameter and using SqlDataAdapter data is fetched from the Northwind database and stored in an object of DataTable class.

Finally, the object of DataTable is returned.

private DataTable GetData(string query)

{

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

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlDataAdapter sda = new SqlDataAdapter(query, con))

{

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

return dt;

}

VB.Net

Private Function GetData(ByVal query As String) As DataTable

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

Using con As SqlConnection = New SqlConnection(constr)

Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)

Using dt AsDataTable = New DataTable()

sda.Fill(dt)

Return dt

End Using

End Function

Exporting DataSet or DataTable to Word document

When Export To Word button is clicked, the GetData method is called and SELECT query is passed as parameter.

The data returned from the GetData method is set to the DataTable object.

Then, an object of GridView is created and DataTable object is assigned to the DataSource property of the GridView.

The Response class properties are set.

1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.

Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.

2. ContentType – It informs the Browser about the file type. In this case it is Word document.

Next, the StringWriter and HtmlTextWriter class objects are created and StringWriter object is passed as parameter to HtmlTextWriter.

Finally, StringWriter object is written to the Response which initiates the File download operation.

protected void ExportToWord(object sender, EventArgs e)

{

//Get the data from database into DataTable.

string query = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers";

DataTable dt = this.GetData(query);

//Create a dummy GridView.

GridView gvCustomers = new GridView();

gvCustomers.DataSource = dt;

gvCustomers.DataBind();

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())

{

using (HtmlTextWriter hw = new HtmlTextWriter(sw))

{

gvCustomers.RenderControl(hw);

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

Response.Flush();

Response.End();

}

VB.Net

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

'Get the data from database into DataTable.

Dim query As String = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers"

Dim dt As DataTable = Me.GetData(query)

'Create a dummy GridView.

Dim gvCustomers As GridView = New GridView()

gvCustomers.DataSource = dt

gvCustomers.DataBind()

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()

Using hw As HtmlTextWriter = New HtmlTextWriter(sw)

gvCustomers.RenderControl(hw)

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

Response.Flush()

Response.End()

End Using

End Sub

Exporting DataSet or DataTable to Excel file

When Export To Excel button is clicked, the GetData method is called and SELECT query is passed as parameter.

The data returned from the GetData method is set to the DataTable object.

Then, an object of GridView is created and DataTable object is assigned to the DataSource property of the GridView.

The Response class properties are set.

1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.

Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.

2. ContentType – It informs the Browser about the file type. In this case it is Excel file.

Next, the StringWriter and HtmlTextWriter class objects are created and StringWriter object is passed as parameter to HtmlTextWriter.

After that, 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.

Finally, StringWriter object is written to the Response which initiates the File download operation.

protected void ExportToExcel(object sender, EventArgs e)

{

//Get the data from database into DataTable.

string query = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers";

DataTable dt = this.GetData(query);

//Create a dummy GridView.

GridView gvCustomers = new GridView();

gvCustomers.DataSource = dt;

gvCustomers.DataBind();

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())

{

using (HtmlTextWriter hw = new HtmlTextWriter(sw))

{

foreach (GridViewRow row in gvCustomers.Rows)

{

//Apply text style to each Row.

row.Attributes.Add("class", "textmode");

}

gvCustomers.RenderControl(hw);

//Style to format numbers to string.

string style = @"<style> .textmode { } </style>";

Response.Write(style);

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

Response.Flush();

Response.End();

}

VB.Net

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

'Get the data from database into DataTable.

Dim query As String = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers"

Dim dt As DataTable = Me.GetData(query)

'Create a dummy GridView.

Dim gvCustomers As GridView = New GridView()

gvCustomers.DataSource = dt

gvCustomers.DataBind()

Response.Clear()

Response.Buffer = True

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

Response.Charset = ""

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

Using sw As StringWriter = New StringWriter()

Using hw As HtmlTextWriter = New HtmlTextWriter(sw)

For Each row As GridViewRow In gvCustomers.Rows

'Apply text style to each Row.

row.Attributes.Add("class", "textmode")

gvCustomers.RenderControl(hw)

'Style to format numbers to string.

Dim style As String = "<style> .textmode { } </style>"

Response.Write(style)

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

Response.Flush()

Response.End()

End Using

End Sub

Exporting DataSet or DataTable to PDF

When Export To PDF button is clicked, the GetData method is called and SELECT query is passed as parameter.

The data returned from the GetData method is set to the DataTable object.

Then, an object of GridView is created and DataTable object is assigned to the DataSource property of the GridView.

The Response class properties are set.

1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.

Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.

2. ContentType – It informs the Browser about the file type. In this case it is PDF.

Next, the GridView is rendered into an HTML string using HtmlTextWriter and then, the generated HTML is added to the iTextSharp PDF document using Document class.

After that, the PDF document is opened and the GridView data is written using ParseXHtml method of XmlWorkerHelper class.

Finally, StringWriter object is written to the Response which initiates the File download operation.

protected void ExportToPDF(object sender, EventArgs e)

{

//Get the data from database into DataTable.

string query = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers";

DataTable dt = this.GetData(query);

//Create a dummy GridView.

GridView gvCustomers = new GridView();

gvCustomers.DataSource = dt;

gvCustomers.DataBind();

Response.ContentType = "application/pdf";

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

Response.Cache.SetCacheability(HttpCacheability.NoCache);

using (StringWriter sw = new StringWriter())

{

using (HtmlTextWriter hw = new HtmlTextWriter(sw))

{

gvCustomers.RenderControl(hw);

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

{

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

{

using (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)

'Get the data from database into DataTable.

Dim query As String = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers"

Dim dt As DataTable = Me.GetData(query)

'Create a dummy GridView.

Dim gvCustomers As GridView = New GridView()

gvCustomers.DataSource = dt

gvCustomers.DataBind()

Response.ContentType = "application/pdf"

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

Response.Cache.SetCacheability(HttpCacheability.NoCache)

Using sw As StringWriter = New StringWriter()

Using hw As HtmlTextWriter = New HtmlTextWriter(sw)

gvCustomers.RenderControl(hw)

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

Using pdfDoc As Document = New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0F)

Using 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

Exporting DataSet or DataTable to CSV

When Export To CSV button is clicked, the GetData method is called and SELECT query is passed as parameter.

The data returned from the GetData method is set to the DataTable object.

Then, an object of GridView is created and DataTable object is assigned to the DataSource property of the GridView.

The Response class properties are set.

1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.

Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.

2. ContentType – It informs the Browser about the file type. In this case it is CSV.

Next, the StringBuilder class object is created and a FOR EACH loop is executed over the Columns of the DataTable and a comma separated (delimited) string is generated.

Then, a FOR EACH loop is executed over the Rows and Columns and a comma separated (delimited) string is generated.

Finally, StringBuilder object is written to the Response which initiates the File download operation.

protected void ExportToCSV(object sender, EventArgs e)

{

//Get the data from database into DataTable.

string query = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers";

DataTable dt = this.GetData(query);

Response.Clear();

Response.Buffer = true;

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

Response.Charset = "";

Response.ContentType = "application/text";

StringBuilder sb = new StringBuilder();

foreach (DataColumn column in dt.Columns)

{

//Append data with separator.

sb.Append(column.ColumnName + ',');

}

//Append new line character.

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

foreach (DataRow row in dt.Rows)

{

foreach (DataColumn column in dt.Columns)

{

//Append data with separator.

sb.Append(row[column.ColumnName].ToString() + ',');

}

//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)

'Get the data from database into DataTable.

Dim query As String = "SELECT CustomerID, ContactName, City, PostalCode FROM Customers"

Dim dt As DataTable = Me.GetData(query)

Response.Clear()

Response.Buffer = True

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

Response.Charset = ""

Response.ContentType = "application/text"

Dim sb As StringBuilder = New StringBuilder()

ForEach column As DataColumn In dt.Columns

'Append data with separator.

sb.Append(column.ColumnName + ","c)

'Append new line character.

sb.Append(vbCrLf)

For Each row As DataRow In dt.Rows

For Each column As DataColumn In dt.Columns

'Append data with separator.

sb.Append(row(column.ColumnName).ToString() + ","c)

'Append new line character.

sb.Append(vbCrLf)

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

Response.Flush()

Response.End()

End Sub

Error

The following error occurs when you try to render a control such as GridView to HTML using the RenderControl method.

Server Error in '/ASP.Net' Application.

Control gvCustomers 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 'gvCustomers ' of type 'GridView' must be placed inside a form tag with runat=server.

Solution

The solution to this problem is to override VerifyRenderingInServerForm event handler.

For more details please refer, Exception - Control GridView1 of type GridView must be placed inside a form tag with runat server.

Screenshots

Exporting