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

More articles: Export to Excel

Comments

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

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: