Month: September 2008 Page 1 of 3

This is the class that you can use to create a CSV file from DataTable. Basically what it does is to iterate each column and row in datatable and separate them with comma.

Class/Function:

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;

namespace BusinessLayer
{
    public class CSVBuilder
    {
        public static string BuildCSVDocument(DataTable table)
        {
            StringBuilder   builder = new StringBuilder();
            DataColumn      col;

            // append header
            for (int index = 0; index < table.Columns.Count; index++)
            {
                col = table.Columns[index];
                builder.AppendFormat("{0},", col.ColumnName);
            }

            builder.AppendLine();

            // append rows
            foreach (DataRow row in table.Rows)
            {
                object[] values = row.ItemArray;

                for (int index = 0; index < values.Length; index++)
                {
                    object value = row[index];
                    string valueString;

                    if (value is DateTime)
                    {
                        valueString = Convert.ToDateTime(value).ToString("dd/MM/yyyy hh:mm");
                    }
                    else if (value is string)
                    {
                        valueString = value.ToString().Replace("'", "`").Replace(",", "");
                    }
                    else
                    {
                        valueString = value.ToString();
                    }

                    builder.AppendFormat("{0},", valueString);
                }

                builder.AppendLine();
            }

            return builder.ToString();
        }

        public static void StreamCSV(DataTable table, HttpResponse response, string fileName)
        {
            // convert the extract to CSV
            string csv = BuildCSVDocument(table);

            // Send it to browser
            response.ClearContent();
            response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
            response.ContentType = "text/csv";

            // Write to the stream
            StreamWriter sw = new StreamWriter(response.OutputStream);
            sw.Write(csv);
            sw.Close();

            response.Flush();
            response.Close();
        }
    }
}

Usage

 private void GenerateMyCsv()
    {
        lblErrorOrderID.Visible = false;

        try
        {
            tdsReports.MissingBatchNumberSelectDataTable dtBatch =
                ReportFactory.GetMissingBatch(txtOrderID.Text);

            //display no results returned message
            if (dtBatch.Rows.Count != 0)
            {
               CSVBuilder.StreamCSV(dtBatch , Response, "MyCSV.csv");
            }

        }
        catch (ApplicationException ex)
        {
            lblErrorOrderID.Text = ex.Message;
            lblErrorOrderID.Visible = true;
        }
        catch (Exception ex)
        {
            lblErrorOrderID.Text = ex.Message;
            lblErrorOrderID.Visible = true;
        }
    }