Adsence750x90

Sunday, September 20, 2009

DataTable to CSV or Text File in Download format

Download Reports in CSV or Text Fromat in C# ( C Sharp)

Some special situations programmers want to download user reports in .csv or .txt format from database. This code snippet is a solution for that situations. first to take data from database in DataTable. Below class file is helpful to overcome this problam

first Example this wrote in a separate class file.

use system.io;


public class DownloadReports
{
public DownloadReports()
{
}

public void TextFormat(DataTable objData,string filename)
{
MemoryStream mm = getStream(objData);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "text/plain";
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.txt;", filename));
HttpContext.Current.Response.BinaryWrite(mm.ToArray());
HttpContext.Current.Response.End();
}

public void ExcelFormat(DataTable objData, string filename)
{
MemoryStream mm = getStream(objData);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "application/Ms-Excel";
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.csv;", filename));
HttpContext.Current.Response.BinaryWrite(mm.ToArray());
HttpContext.Current.Response.End();
}

private System.IO.MemoryStream getStream(DataTable objData)
{
System.IO.MemoryStream mm = new MemoryStream();
if (objData.Rows.Count > 0)
{
StreamWriter sw = new StreamWriter(mm);
sw.AutoFlush = true;
sw.WriteLine(); sw.WriteLine();
foreach (DataColumn dcols in objData.Columns)
{
sw.Write(dcols.ColumnName.ToString() + ",");
}
sw.WriteLine();
sw.WriteLine();
foreach (DataRow drow in objData.Rows)
{
for (int i = 0; i < objData.Columns.Count; i++)
{
sw.Write(drow[i].ToString() + ",");
}
sw.WriteLine();
}
}
return mm;
}
}


2nd option wrote in ASPX.CS file


public class DataTableToCsvAndTxtFormat
{
public DataTableToCsvAndTxtFormat()
{
}
private MemoryStream GetStream()
{
DataTable objData = new DataTable();
MemoryStream mm = new MemoryStream();
DBHelper objDBHelper=new DBHelper ();
string cmd = string.Format("SELECT Column1,Column2,Column3,Column4 FROM Table");
objData = objDBHelper.GetReaderTable(cmd);
if (objData.Rows.Count > 0)
{
objData.Columns[0].ColumnName = "First Name";
objData.Columns[1].ColumnName = "Last Name";
objData.Columns[2].ColumnName = "Email";
objData.Columns[3].ColumnName = "Mobile Number";

StreamWriter sw = new StreamWriter(mm);
sw.AutoFlush = true;
sw.WriteLine(); sw.WriteLine();
foreach (DataColumn dcols in objData.Columns)
{
sw.Write(dcols.ColumnName.ToString() + ",");
}
sw.WriteLine();
sw.WriteLine();
foreach (DataRow drow in objData.Rows)
{
for (int i = 0; i < objData.Columns.Count; i++)
{
sw.Write(drow[i].ToString() + ",");
}
sw.WriteLine();
}
}
}
public void DownloadTextFormat()
{
MemoryStream mm = GetStream();
Response.Clear();
Response.ContentType = "text/plain";
Response.AddHeader("content-disposition", "attachment; filename=Reports.txt;");
Response.BinaryWrite(mm.ToArray());
Response.End();
}
public void DownloadCsvFromat()
{
MemoryStream mm = GetStream();
Response.Clear();
Response.ContentType = "application/Ms-Excel";
Response.AddHeader("content-disposition", "attachment; filename=Reports.csv;");
Response.BinaryWrite(mm.ToArray());
Response.End();
}
}

3 comments:

Silvercode said...

thanx gud post

Anonymous said...

thank you is a good and useful code snippet for programmers who want to download datatable in csv or txt format. i have a blog that contain Asp.Net Interview Questions am covering all topic in this blogger

Anonymous said...

This generates invalid CSV if the data itself contains commas. You need to quote items that contain the separator.