Vinit Patel

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

Posted on: October 3, 2009


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

}

image

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

}

image1

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

}

image2

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

}

image3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Calendar

October 2009
M T W T F S S
« Sep   Dec »
 1234
567891011
12131415161718
19202122232425
262728293031  

Stats Of Blog

  • 7,092 hits

Cluster Map

%d bloggers like this: