Export Data to Excel in CSV format using .NET


Export Data to excel in CSV format. .NET code to export CSV data. Free .NET code to export data to excel. .NET way of exporting data to excel. Explanation of .NET code to export data to excel in CSV.

In case anyone of you are attempting to export the data to excel, you can directly use my code below.

I am using Datatable for this purpose. After fetching the values from the database. I am filling the data table and then looping through to fill in the arrays and export the array of data to excel.

ProduceCSV is the method which is used to join the arrays and actually transfer the data to CSV.

You need to set Response.ContentType = "application/vnd.ms-excel" to tell the compiler that the request type of export is Excel.

Code to export data to CSV file


using System;
using System.Data;
using System.IO;

public class DataTableHelper
{

///
/// Can stream DataTable to Browser, directly, you need to set
///
/// Response.Clear();
/// Response.Buffer= true;
/// Response.ContentType = "application/vnd.ms-excel";
/// Response.AddHeader("Content-Disposition", "inline;filename=Clientes.xls"); Response.Charset = "";
/// this.EnableViewState = false
/// // ACTUAL CODE
/// ProduceCSV(dt, Response.Output, true);
///
public static void ProduceCSV(DataTable dt, System.IO.TextWriter httpStream, bool WriteHeader)
{
if(WriteHeader)
{
string[] arr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
arr[i] = dt.Columns[i].ColumnName;
arr[i] = GetWriteableValue(arr[i]);
}

httpStream.WriteLine(string.Join(",", arr));
}

for(int j = 0; j<dt.Rows.Count; j++)
{
string[] dataArr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
object o = dt.Rows[j][i];
dataArr[i] = GetWriteableValue(o);
}
httpStream.WriteLine(string.Join(",", dataArr));
}
}

#region CSV Producer
public static void ProduceCSV(DataTable dt, System.IO.StreamWriter file, bool WriteHeader)
{
if(WriteHeader)
{
string[] arr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
arr[i] = dt.Columns[i].ColumnName;
arr[i] = GetWriteableValue(arr[i]);
}

file.WriteLine(string.Join(",", arr));
}

for(int j = 0; j<dt.Rows.Count; j++)
{
string[] dataArr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
object o = dt.Rows[j][i];
dataArr[i] = GetWriteableValue(o);
}
file.WriteLine(string.Join(",", dataArr));
}
}

public static string GetWriteableValue(object o)
{
if(o==null || o == Convert.DBNull)
return "";
else if(o.ToString().IndexOf(",")==-1)
return o.ToString();
else
return "\"" + o.ToString() + "\"";

}
#endregion
}


Related Articles

Client Side Script to Export to Excel 2007

This article explains the work around for Export to Excel 2007. The earlier vbScript we used for Exporting to Excel 2003 relied on HTMLProject which is no more supported in Excel 2007. Here is the work around.

More articles: Export to Excel

Comments

Author: Paul Maltar03 Jun 2005 Member Level: Bronze   Points : 0

I used the code that you supplied and built the project on my local machine and it worked perfectly.
I then created a form that would generate a query to pull data from my database and again everything worked perfectly.
Next I wanted to have data download when the button was click but in a different window so I called a simple java script to open a new window and download the data and again everything worked prefect.
I have now pushed the entire project live and it errors.
When the button is clicked to download the csv file the new window opens and closes it self without throwing an error or exporting any data. Do you have any idea why this may happen? I can browse to the page that is opening in the popup window and it will again work perfectly but as a popup it fails everytime only on the production server.

Thanks

Author: Cy Surflex 21 Sep 2006 Member Level: Bronze   Points : 0

if you change

Response.AddHeader("Content-Disposition", "inline;filename=Clientes.xls"); Response.Charset = "";

to

Response.AddHeader("Content-Disposition", "attachment;filename=Clientes.xls"); Response.Charset = "";

it will force the user to download the file, instead of displaying it embedded in the browser (in IE)

Author: Srini Chavadam26 Dec 2006 Member Level: Bronze   Points : 0

Hi,

The piece of code you have provided here was really useful and I was able to implement the same code to export my report data to excel files.

But, when I click on 'Open' button on File Open Dialog box, it failing to open as it is not found in the default temparary internet folder.

Do you any solution for the same? If so could you please help me out?

Thanks & Regards
Srinivas



Author: Soapy29 Dec 2007 Member Level: Bronze   Points : 0

Here's a complete code example:
http://soapylamp.com/webdevelopment/codeExample6502.html?id=832

Author: Thakar makwana30 Sep 2008 Member Level: Gold   Points : 1

I have to do same thing which u did here....any one have code with example ??

I need it urgent...

Cheers

Author: Narayanan Sukumaran01 Oct 2008 Member Level: Gold   Points : 2

Hi Thakar makwana,
I am not clear, what you need exactly.
Please see the below code for export to excel

exportButton_click:

Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
DataGrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();

Author: QI05 May 2010 Member Level: Bronze   Points : 2

I used the code below to export data to csv in browser, but it also generated html source code at the end of the csv. How could I avoid it? Thanks for help.


DataSet ds2 = new DataSet();
ds2 = BindData(ViewState["Center"].ToString());

#region /* produceCSVto browser */
//Can stream DataTable to Browser, directly, you need to set ///
Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "inline;filename=ServiceExport.xls");
Response.Charset = "";
this.EnableViewState = false; /// // ACTUAL CODE ///
//StreamWriter writer = new StreamWriter(Response.OutputStream);
ProduceCSVWeb(ds2.Tables[0], Response.Output, true);
#endregion
ds2.Clear();
ds2.Dispose();

Guest Author: xenon17 Jan 2012

Based on this very usefull code, I had the same problem regarding the HTML source code at the end of the csv. This is how I got ride of it:

HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
//For french specific characters to be shown correctly
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;

HttpContext.Current.Response.AddHeader("Content-Disposition", "inline;filename=" + file_name);

Fonctions_Globales.DataTableHelper.ProduceCSV(dataview.Table, HttpContext.Current.Response.Output, true);

// No Response.end() in order to avoid exception
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.Close();



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: