On many occasion there’s a need to export dataset or datatable to Word, Excel, PDF or CSV (Text) formats. In this article I am explaining how to achieve the same in ASP.Net.
To export GridView to Word, Excel, PDF or CSV (Text) refer my article Export GridView To Word/Excel/PDF/CSV in ASP.Net
For this tutorial I am using NorthWind Database which can be downloaded from here
Once that is downloaded you can attach the same to your SQL Server.
Connection String
Below is my connection string to the database.
<add name="conString" connectionString="Data Source=.\SQLEXPRESS;
database=Northwind;Integrated Security=true"/>
Namespaces
C#
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.IO
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html
Imports iTextSharp.text.html.simpleparser
Function to get the results in datatable
Next I am using my very own GetData function to get the desired data from the database which is given below.
C#
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString = System.Configuration.ConfigurationManager.
ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
VB.Net
Private Function GetData(ByVal cmd As SqlCommand) As DataTable
Dim dt As New DataTable()
Dim strConnString As [String] = System.Configuration _
.ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
Dim sda As New SqlDataAdapter()
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
sda.SelectCommand = cmd
sda.Fill(dt)
Return dt
Catch ex As Exception
Throw ex
Finally
con.Close()
sda.Dispose()
con.Dispose()
End Try
End Function
As you can see above I am passing the query to the GetData function and it returns the results as datatable back. More details on the above function refer my article Parameterized Queries ADO.Net
Export to Word
Below is the code to export the datatable to Word Format. It first fills the datatable using the GetData function and then binds it to a dummy GridView and then the dummy GridView is rendered as Word document.
C#
protected void ExportToWord(object sender, EventArgs e)
{
//Get the data from database into datatable
string strQuery = "select CustomerID, ContactName, City, PostalCode" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=DataTable.doc");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-word ";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
VB.Net
Protected Sub ExportToWord(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportWord.Click
'Get the data from database into datatable
Dim strQuery As String = "select CustomerID, ContactName, City, " & _
"PostalCode from customers"
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
'Create a dummy GridView
Dim GridView1 As New GridView()
GridView1.AllowPaging = False
GridView1.DataSource = dt
GridView1.DataBind()
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", _
"attachment;filename=DataTable.doc")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-word "
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
GridView1.RenderControl(hw)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()
End Sub
The figure below displays the generated Microsoft Word Document.
Export to Excel
Below is the code to export the datatable to Excel Format. It first fills the datatable using the GetData function and then binds it to a dummy GridView and then the dummy GridView is rendered as Excel Workbook. Also you will notice I applied textmode style to all the rows so that it in rendered as text.
C#
protected void ExportToExcel(object sender, EventArgs e)
{
//Get the data from database into datatable
string strQuery = "select CustomerID, ContactName, City, PostalCode" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=DataTable.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
for (int i = 0; i < GridView1.Rows.Count; i++)
{
//Apply text style to each Row
GridView1.Rows[i].Attributes.Add("class", "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(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
'Get the data from database into datatable
Dim strQuery As String = "select CustomerID, ContactName, City, " & _
"PostalCode from customers"
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
'Create a dummy GridView
Dim GridView1 As New GridView()
GridView1.AllowPaging = False
GridView1.DataSource = dt
GridView1.DataBind()
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", _
"attachment;filename=DataTable.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
For i As Integer = 0 To GridView1.Rows.Count - 1
'Apply text style to each Row
GridView1.Rows(i).Attributes.Add("class", "textmode")
Next
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 Sub
The figure below displays the generated Microsoft Excel Sheet.
Export to Portable Document Format (PDF)
Below is the code to export the datatable to PDF Format. It first fills the datatable using the GetData function and then binds it to a dummy GridView and then the dummy GridView is rendered as PDF document using the iTextSharp Library which is a free open source library and can be downloaded from here
.
C#
protected void ExportToPDF(object sender, EventArgs e)
{
//Get the data from database into datatable
string strQuery = "select CustomerID, ContactName, City, PostalCode" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
//Create a dummy GridView
GridView GridView1 = new GridView();
GridView1.AllowPaging = false;
GridView1.DataSource = dt;
GridView1.DataBind();
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition",
"attachment;filename=DataTable.pdf");
Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.RenderControl(hw);
StringReader sr = new StringReader(sw.ToString());
Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
pdfDoc.Open();
htmlparser.Parse(sr);
pdfDoc.Close();
Response.Write(pdfDoc);
Response.End();
}
VB.Net
Protected Sub ExportToPDF(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportPDF.Click
'Get the data from database into datatable
Dim strQuery As String = "select CustomerID, ContactName, City," _
& " PostalCode from customers"
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
'Create a dummy GridView
Dim GridView1 As New GridView()
GridView1.AllowPaging = False
GridView1.DataSource = dt
GridView1.DataBind()
Response.ContentType = "application/pdf"
Response.AddHeader("content-disposition", _
"attachment;filename=DataTable.pdf")
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
GridView1.RenderControl(hw)
Dim sr As New StringReader(sw.ToString())
Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)
Dim htmlparser As New HTMLWorker(pdfDoc)
PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
pdfDoc.Open()
htmlparser.Parse(sr)
pdfDoc.Close()
Response.Write(pdfDoc)
Response.End()
End Sub
The figure below displays the generated PDF Document.
Export to Comma Separated Values (CSV)
Below is the code to export the datatable to CSV or Text Format. It first fills the datatable using the GetData function. To export dataset to CSV there is no need of dummy GridView. We just have to loop through the records and append the delimiting character comma.
C#
protected void ExportToCSV(object sender, EventArgs e)
{
//Get the data from database into datatable
string strQuery = "select CustomerID, ContactName, City, PostalCode" +
" from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=DataTable.csv");
Response.Charset = "";
Response.ContentType = "application/text";
StringBuilder sb = new StringBuilder();
for (int k = 0; k < dt.Columns.Count; k++)
{
//add separator
sb.Append(dt.Columns[k].ColumnName + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int k = 0; k < dt.Columns.Count; k++)
{
//add separator
sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
}
//append new line
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 System.EventArgs) Handles btnExportCSV.Click
'Get the data from database into datatable
Dim strQuery As String = "select CustomerID, ContactName, City," & _
" PostalCode from customers"
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", _
"attachment;filename=DataTable.csv")
Response.Charset = ""
Response.ContentType = "application/text"
Dim sb As New StringBuilder()
For k As Integer = 0 To dt.Columns.Count - 1
'add separator
sb.Append(dt.Columns(k).ColumnName + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
For i As Integer = 0 To dt.Rows.Count - 1
For k As Integer = 0 To dt.Columns.Count - 1
'add separator
sb.Append(dt.Rows(i)(k).ToString().Replace(",", ";") + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
Next
Response.Output.Write(sb.ToString())
Response.Flush()
Response.End()
End Sub
The figure below displays the generated CSV File.
This completes the article. You can download the code in VB.Net and C# using the link below
DataTableExport.zip (1.15 mb)