Export DataSet or DataTable to Word, Excel, PDF and CSV Formats

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;

Function to get the results in datatable

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

}

}

As you can see above I am passing the query to the GetData function and it returns the results as datatable back.

Export to Word

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

}

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.

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

}

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.

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

}

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.

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

}