.Net All About

.Net All About

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

}

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

About these ads

October 24, 2009 - Posted by | .NET, Asp.net, C# | , ,

16 Comments »

  1. Nice! Thanks!

    Comment by flor | November 6, 2009 | Reply

    • Thnx men….
      For your valuable comment!

      Comment by yrbyogi | November 7, 2009 | Reply

  2. I’m able to save the file into XLS format. But when tried to open it shows ‘External table is not in the expected format’ using the below provider. Please help.

    OleDbConnection con = new OleDbConnection(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strFileName + “;Extended Properties=\”Excel 8.0;HDR=Yes;\””);

    Comment by Please help | November 9, 2009 | Reply

    • Ask you users to open up the excel file and manually “Save As” to XL
      format. The exported excel is in HTML format, this is evident if yo
      open it up in a text editor, all nice HTMLs. But “real” excel file
      are all hex/binary, so you must ask your users to manually “Save As
      Excel format after they edit.

      I think that is a small price to pay for having the functionality

      Comment by yrbyogi | November 9, 2009 | Reply

  3. How to do all this in a windows application

    Comment by Max | October 20, 2010 | Reply

    • But what do you want to do in window application??
      if you will want to export data in excel then simply use the excel api to do export…
      googling/binging will give you lots of example…

      Comment by yrbyogi | October 22, 2010 | Reply

  4. Hi

    I tryied this application in C# website but it giving me error cannot find the namesape

    Error 1 The type or namespace name ‘iTextSharp’ could not be found (are you missing a using directive or an assembly reference?) G:\WebSite2\Default3.aspx.cs 16 7 G:\WebSite2\
    how to fix it?

    Comment by Amit Singh | April 10, 2011 | Reply

    • Here we are using the common control of iTextSharp which is using for exporting data

      Comment by yrbyogi | April 12, 2011 | Reply

  5. this code giving me error :
    Control ‘GridView1′ of type ‘GridView’ must be placed inside a form tag with runat=server.
    when I am exporting data from grid view to word from my website please help me to fix it.

    Comment by Amit Singh | April 11, 2011 | Reply

    • you just place the gridview inside the form tag.
      for example

      –your gridview code

      Comment by yrbyogi | April 12, 2011 | Reply

  6. what type is that Response variable? There is no definition in your sample code

    Comment by steffen | September 26, 2011 | Reply

    • oh I feel so stupid now. Didn’t saw that this is ASP.NET. Using a FileStream instead of Response (and changing the .Write to .Flush and .End to .Close) does the trick :/

      Comment by steffen | September 26, 2011 | Reply

    • Have you check the Response class?

      Click here to know about response class

      Comment by yrbyogi | September 26, 2011 | Reply

  7. nice work…..

    Comment by javed | March 12, 2012 | Reply

  8. nice work…thanks alot…

    Comment by muhammad usman khan | March 12, 2012 | Reply

  9. Thank you very much man, This is really a great article.

    Comment by Randhir Yadav | May 16, 2012 | Reply


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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: