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.
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.
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 />
<br />
<asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
<br />
<br />
<asp:Button ID="btnExportPDF" runat="server" Text="Export To PDF" OnClick="ExportToPDF" />
<br />
<br />
<asp:Button ID="btnExportCSV" runat="server" Text="Export To CSV" OnClick="ExportToCSV" />
Namespaces
You will need to import the following namespaces.
C#
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.
C#
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 Using
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.
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.
C#
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 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.
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.
C#
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")
Next
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 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.
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.
C#
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 Using
End Using
End Using
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.
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.
C#
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)
Next
'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)
Next
'Append new line character.
sb.Append(vbCrLf)
Next
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.
Screenshots
Exporting
Exported Word Document
Exported Excel file
Exported PDF file
Exported CSV (Text) file
Demo
Downloads