Visual Studio .NET - Export to CSV format

Asked By vamsi n on 24-Oct-08 06:18 AM

Hi All
i am using Asp.Net2.0 & c#
i exported the dataset to csv format

in .aspx page
----------------

private void csvExport()
    {
        string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"].ToString();
        SqlConnection conn = new SqlConnection(strConn);
        SqlDataAdapter da = new SqlDataAdapter("select kanr,jahr,monat from prsadmin.kst_monat", conn);
        DataSet ds1 = new DataSet();
        da.Fill(ds1, "Emp");
        CSVHelper csv = new CSVHelper();
        string strData = csv.Export(ds1, true);
        System.Text.Encoding enc = System.Text.Encoding.ASCII;
        byte[] data = System.Text.Encoding.UTF8.GetBytes(strData);
        Response.Clear();
        Response.AddHeader("Content-Type", "Application/x-msexcel");
        Response.AddHeader("Content-Disposition", "attachment;filename=PrintCSV.csv");
        Response.BinaryWrite(data);
        Response.End();
        Response.Close();

}

in .cs file
--------------
public string Export(DataSet ds, bool exportColumnHeadings)
        {
            string header = string.Empty;
            string body = string.Empty;
            string record = string.Empty;// If you want column to be part of the CSV ...
            if (exportColumnHeadings)
            {
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    header = header + (char)34 + col.ColumnName + (char)34 + ",";
                }
                header = header.Substring(0, header.Length - 1);
            }// Iterate into the rows
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Object[] arr = row.ItemArray;
                for (int i = 0; i <= arr.Length - 1; i++)
                {
                    if (arr[i].ToString().IndexOf(",") > 0)
                    {
                        record = record + (char)34 + arr[i].ToString() + (char)34 + ",";
                    }
                    else
                    {
                        record = record + arr[i].ToString() + ",";
                    }
                }
                body = body + record.Substring(0, record.Length - 1) + Environment.NewLine;
                record = "";
            }
            if (exportColumnHeadings)
            {
                return (header + Environment.NewLine + body);
            }
            else
            {
                return body;
            }
        }

---------
it exported to CSV file but it shows the data as Excel format(ie: seperate cells) it does not combined the data with commas(,)

please help me

Thanking you
Vamsi.

Reply

Binny ch replied to vamsi n on 24-Oct-08 06:27 AM

Tells you how to export a DataSet's DataTable to CSV (excel readable) format.

.NET Classes used :

DataTableHelper

System.IO.StreamWriter

System.Data.DataTable

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
}

read this code

C_A P replied to vamsi n on 24-Oct-08 09:48 AM

This can be used in WebForms as well as in WinForms by simply passing a parameter to the export object's constructor.

For WebForms:

RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Web")

For WinForms:

RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win")

The ExportDetails method has three types of overloads. You can call whichever best suits your requirement.

The following are the overload types:

public void ExportDetails(DataTable DetailsTable, 
  ExportFormat FormatType, string FileName)

public void ExportDetails(DataTable DetailsTable, int[] 
  ColumnList, ExportFormat FormatType, string FileName)

public void ExportDetails(DataTable DetailsTable, int[] 
  ColumnList, string[] Headers, ExportFormat FormatType, string FileName)

Have a glance at the parameters

DetailsTable - DataTable to be exported.
FormatType - Export File Format. Use Export.ExportFormat.CSV for the CSV file or Export.ExportFormat.Excel for the Excel file.
FileName - Export File Name.
- For WebForms simply pass the filename. e.g. EmployeeInfo.xls
- For WinForms pass the filename along with path. e.g. C:\\EmployeeInfo.csv
ColumnList - DataFields of the DataTable to be exported. Specify their ordinals in an integer array.
Headersrs - Custom Headers List for the specified columns in the export file. Specify the names in a string array.

You can call this method in the: ASPButton_Click event, LinkButton_Click event or Page_Load event or wherever you want. All you need to do is set the values to parameters and give a simple call to the required overload type of ExportDetails method.

The following is the code block that demonstrates "exporting specified columns" of a DataTable as an Excel file from WebForm.

Collapse

[Code Behind]

private void btnExport2_Click(object sender, System.EventArgs e)
{
    // Export the details of specified columns

    try
    {
        // Get the datatable to export

        DataTable dtEmployee = ((DataSet) 
             Session["dsEmployee"]).Tables["Employee"].Copy();

        // Specify the column list to export

        int[] iColumns = {1,2,3,5,6};

        // Export the details of specified columns to Excel

        RKLib.ExportData.Export objExport = new 
            RKLib.ExportData.Export("Web");
        objExport.ExportDetails(dtEmployee, 
             iColumns, Export.ExportFormat.Excel, "EmployeesInfo2.xls");
    }
    catch(Exception Ex)
    {
        lblError.Text = Ex.Message;
    }
}

The following is the code block that demonstrates "exporting specified columns" of a DataTable as a CSV file from WindowsForm.

Collapse

private void btnExportCSV_Click(object sender, System.EventArgs e)
{
    // Export the details of specified columns

    try
    {
        lblMessage.Text = "";
        
        // Get the datatable to export

        DataTable dtEmployee = dsEmployee.Tables["Employee"].Copy();

        // Specify the column list to export

        int[] iColumns = {1,2,3,5,6};

        // Export the details of specified columns to CSV

        RKLib.ExportData.Export objExport = new 
            RKLib.ExportData.Export("Win");
        objExport.ExportDetails(dtEmployee, 
            iColumns, Export.ExportFormat.CSV, 
            "C:\\EmployeesInfo.csv");
        lblMessage.Text = "Successfully exported to 
            C:\\EmployeesInfo.csv";
    }
    catch(Exception Ex)
    {
        lblMessage.Text = Ex.Message;
    }
}

try this code to export data to csv file

C_A P replied to vamsi n on 24-Oct-08 09:51 AM

private void OnExportGridToCSV(object sender, System.EventArgs e)
{
	// Create the CSV file to which grid data will be exported.
	StreamWriter sw = new StreamWriter(Server.MapPath("~/GridData.txt"), false);
	// First we will write the headers.
	DataTable dt = m_dsProducts.Tables[0];
	int iColCount = dt.Columns.Count;
	for(int i = 0; i < iColCount; i++)
	{
		sw.Write(dt.Columns[i]);
		if (i < iColCount - 1)
		{
			sw.Write(",");
		}
	}
	sw.Write(sw.NewLine);
	// Now write all the rows.
	foreach (DataRow dr in dt.Rows)
	{
		for (int i = 0; i < iColCount; i++)
		{
			if (!Convert.IsDBNull(dr[i]))
			{
				sw.Write(dr[i].ToString());
			}
			if ( i < iColCount - 1)
			{
				sw.Write(",");
			}
		}
		sw.Write(sw.NewLine);
	}
	sw.Close();
}

read this code

C_A P replied to vamsi n on 24-Oct-08 09:53 AM

Snippets Code

CSVHelper.cs

/// <summary>

/// Export Helper Function - DataGrid to CSV

/// Converted from this article

/// http://www.dotnetbips.com/386541cb-7e0f-491f-b86a-f9bc8be738b8.aspx?articleid=302

/// </summary>

/// <param name="ds"></param>

/// <param name="exportColumnHeadings"></param>

/// <returns></returns>

public string Export(DataSet ds, bool exportColumnHeadings)

string header = string.Empty;

string body = string.Empty;

string record = string.Empty;

// If you want column to be part of the CSV ...

if (exportColumnHeadings)

foreach (DataColumn col in ds.Tables[0].Columns)

header = header + (char)34 + col.ColumnName + (char)34 + ",";

header = header.Substring(0, header.Length - 1);

// Iterate into the rows

foreach (DataRow row in ds.Tables[0].Rows)

Object[] arr = row.ItemArray;

for (int i = 0; i < arr.Length - 1; i++)

if (arr[i].ToString().IndexOf(",") > 0)

record = record + (char)34 + arr[i].ToString() + (char)34 + ",";

else

record = record + arr[i].ToString() + ",";

body = body + record.Substring(0, record.Length - 1) + Environment.NewLine;

record = "";

if (exportColumnHeadings)

return (header + Environment.NewLine + body);

else

return body;

Export.aspx.cs

private void Page_Load(object sender, System.EventArgs e)

// Put user code to initialize the page here

if (!IsPostBack)

BindGrid();

// Retrieve the customer information and bind into the datagrid

void BindGrid()

SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers", "data source=wenching;initial catalog=northwind;user id=sa; password=Passw0rd");

DataSet ds = new DataSet();

da.Fill(ds, "customers");

Session["myds"] = ds;

dgResults.DataSource = ds;

dgResults.DataBind();

private void btnExport_Click(object sender, System.EventArgs e)

DataSet ds = (DataSet)Session["myds"];

CSVHelper csv = new CSVHelper();

string strData = csv.Export(ds, chbExport.Checked);

byte[] data = ASCIIEncoding.ASCII.GetBytes(strData);

Response.Clear();

// Set as Excel as the primary format

Response.AddHeader("Content-Type", "application/Excel");

// Save the output as customer.csv

Response.AddHeader("Content-Disposition", "inline;filename=customer.csv");

Response.BinaryWrite(data);

Response.End();

try this code

C_A P replied to vamsi n on 24-Oct-08 09:54 AM

Private Sub Page_Load(ByVal sender As System.Object,_
             ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds as dataset=filldataset()
        dgBooks.DataSource = ds.Tables("Books")
        dgBooks.DataBind()
            
        'Set Export button properties 
        btnExcel.Dataview = ds.Tables("Books").DefaultView
        btnExcel.FileNameToExport = "Books.xls"
        btnExcel.ExportType = _
          PNayak.Web.UI.WebControls.ExportButton.ExportTypeEnum.Excel
        btnExcel.Separator = _
          PNayak.Web.UI.WebControls.ExportButton.SeparatorTypeEnum.TAB

    End Sub

small change

Santhosh N replied to vamsi n on 24-Oct-08 11:08 AM

I suppose the only change required is "application/vnd.ms-excel"; instead of "Application/x-msexcel" which you specified for Content Type.... Ie, Response.Clear(); Response.AddHeader("Content-Type", "application/vnd.ms-excel"); Response.AddHeader("Content-Disposition", "attachment;filename=PrintCSV.csv"); Response.BinaryWrite(data); Response.End(); Response.Close();