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.



Exported 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.



Exported Excel Document



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.



Exported 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.



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