Convertir datable en CSV stream

"151930920 mise à jour" Update 1

Je l'ai modifié pour utiliser StreamWriter à la place, ajouter une option pour vérifier si vous avez besoin des en-têtes de colonne dans votre sortie.

public static bool DataTableToCSV(DataTable dtSource, StreamWriter writer, bool includeHeader)
{
    if (dtSource == null || writer == null) return false;

    if (includeHeader)
    {
        string[] columnNames = dtSource.Columns.Cast<DataColumn>().Select(column => "\"" + column.ColumnName.Replace("\"", "\"\"") + "\"").ToArray<string>();
        writer.WriteLine(String.Join(",", columnNames));
        writer.Flush();
    }

    foreach (DataRow row in dtSource.Rows)
    {
        string[] fields = row.ItemArray.Select(field => "\"" + field.ToString().Replace("\"", "\"\"") + "\"").ToArray<string>();
        writer.WriteLine(String.Join(",", fields));
        writer.Flush();
    }

    return true;
}

Comme vous pouvez le voir, vous pouvez choisir la sortie initiale StreamWriter, si vous utilisez StreamWriter (Stream BaseStream), vous pouvez écrire csv dans MemeryStream, FileStream, etc.

origine

j'ai un facile compatible avec la fonction csv, il me sert bien:

    public static void DataTableToCsv(DataTable dt, string csvFile)
    {
        StringBuilder sb = new StringBuilder();

        var columnNames = dt.Columns.Cast<DataColumn>().Select(column => "\"" + column.ColumnName.Replace("\"", "\"\"") + "\"").ToArray();
        sb.AppendLine(string.Join(",", columnNames));

        foreach (DataRow row in dt.Rows)
        {
            var fields = row.ItemArray.Select(field => "\"" + field.ToString().Replace("\"", "\"\"") + "\"").ToArray();
            sb.AppendLine(string.Join(",", fields));
        }

        File.WriteAllText(csvFile, sb.ToString(), Encoding.Default);
    }

si vous pouvez transformer votre datatable en un IEnumerable cela devrait fonctionner pour vous...

    Response.Clear();
    Response.Buffer = true;

    Response.AddHeader("content-disposition", "attachment;filename=FileName.csv");
    Response.Charset = "";
    Response.ContentType = "application/text";
    Response.Output.Write(ExampleClass.ConvertToCSV(GetListOfObject(), typeof(object)));
    Response.Flush();
    Response.End();



public static string ConvertToCSV(IEnumerable col, Type type)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder header = new StringBuilder();

            // Gets all  properies of the class
            PropertyInfo[] pi = type.GetProperties();

            // Create CSV header using the classes properties
            foreach (PropertyInfo p in pi)
            {
                header.Append(p.Name + ",");
            }

            sb.AppendLine(header.ToString().Remove(header.Length));

            foreach (object t in col)
            {
                StringBuilder body = new StringBuilder();

                // Create new item
                foreach (PropertyInfo p in pi)
                {
                    object o = p.GetValue(t, null);
                    body.Append(o.ToString() + ",");
                }

                sb.AppendLine(body.ToString().Remove(body.Length));
            }
            return sb.ToString();
        }

Je ne sais pas si cela s'est converti de VB à C# ok mais si vous ne voulez pas de citations autour de vos nombres, vous pourriez comparer le type de données comme suit..

public string DataTableToCSV(DataTable dt)
{
    StringBuilder sb = new StringBuilder();
    if (dt == null)
        return "";

    try {
        // Create the header row
        for (int i = 0; i <= dt.Columns.Count - 1; i++) {
            // Append column name in quotes
            sb.Append("\"" + dt.Columns[i].ColumnName + "\"");
            // Add carriage return and linefeed if last column, else add comma
            sb.Append(i == dt.Columns.Count - 1 ? "\n" : ",");
        }


        foreach (DataRow row in dt.Rows) {
            for (int i = 0; i <= dt.Columns.Count - 1; i++) {
                // Append value in quotes
                //sb.Append("""" & row.Item(i) & """")

                // OR only quote items that that are equivilant to strings
                sb.Append(object.ReferenceEquals(dt.Columns[i].DataType, typeof(string)) || object.ReferenceEquals(dt.Columns[i].DataType, typeof(char)) ? "\"" + row[i] + "\"" : row[i]);

                // Append CR+LF if last field, else add Comma
                sb.Append(i == dt.Columns.Count - 1 ? "\n" : ",");
            }
        }
        return sb.ToString;
    } catch (Exception ex) {
        // Handle the exception however you want
        return "";
    }

}

si vous souhaitez diffuser le CSV vers l'utilisateur sans créer de fichier, alors j'ai trouvé la méthode la plus simple. Vous pouvez utiliser n'importe quelle extension/méthode pour créer la fonction ToCsv () (qui renvoie une chaîne basée sur le DataTable donné).

        var report = myDataTable.ToCsv();
        var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(report);

        Response.Buffer = true;
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=report.csv");
        Response.ContentType = "text/csv";
        Response.BinaryWrite(bytes);
        Response.End();

j'ai utilisé le code suivant, pillé sur le blog de quelqu'un (pls pardonne manque de citation). Il prend soin des citations, newline et virgule d'une manière assez élégante en citant sur chaque valeur de champ.

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.      
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <returns>Resulting CSV-style string</returns>
    public static string ToCSV(this DataTable table)
    {
        return ToCSV(table, ",", true);
    }

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <param name="includeHeader">true - include headers<br/>
    /// false - do not include header column</param>
    /// <returns>Resulting CSV-style string</returns>
    public static string ToCSV(this DataTable table, bool includeHeader)
    {
        return ToCSV(table, ",", includeHeader);
    }

    /// <summary>
    /// Converts the passed in data table to a CSV-style string.
    /// </summary>
    /// <param name="table">Table to convert</param>
    /// <param name="includeHeader">true - include headers<br/>
    /// false - do not include header column</param>
    /// <returns>Resulting CSV-style string</returns>
     public static string ToCSV(this DataTable table, string delimiter, bool includeHeader)
    {
        var result = new StringBuilder();

        if (includeHeader)
        {
            foreach (DataColumn column in table.Columns)
            {
                result.Append(column.ColumnName);
                result.Append(delimiter);
            }

            result.Remove(--result.Length, 0);
            result.Append(Environment.NewLine);
        }

        foreach (DataRow row in table.Rows)
        {
            foreach (object item in row.ItemArray)
            {
                if (item is DBNull)
                    result.Append(delimiter);
                else
                {
                    string itemAsString = item.ToString();
                    // Double up all embedded double quotes
                    itemAsString = itemAsString.Replace("\"", "\"\"");

                    // To keep things simple, always delimit with double-quotes
                    // so we don't have to determine in which cases they're necessary
                    // and which cases they're not.
                    itemAsString = "\"" + itemAsString + "\"";

                    result.Append(itemAsString + delimiter);
                }
            }

            result.Remove(--result.Length, 0);
            result.Append(Environment.NewLine);
        }

        return result.ToString();
    }

Vous pouvez essayer d'utiliser quelque chose comme cela. Dans ce cas, j'ai utilisé une procédure stockée pour obtenir plus de tables de données et exporter tous les au format CSV.

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace bo
{
class Program
{
    static private void CreateCSVFile(DataTable dt, string strFilePath)
    {
        #region Export Grid to CSV
        // Create the CSV file to which grid data will be exported.
        StreamWriter sw = new StreamWriter(strFilePath, false);
        int iColCount = dt.Columns.Count;

        // First we will write the headers.

        //DataTable dt = m_dsProducts.Tables[0];
        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();

        #endregion
    }
    static void Main(string[] args)
    {
        string strConn = "connection string to sql";
        string direktorij = @"d:";
        SqlConnection conn = new SqlConnection(strConn); 
        SqlCommand command = new SqlCommand("sp_ado_pos_data", conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add('@skl_id', SqlDbType.Int).Value = 158;
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            string datoteka  = (string.Format(@"{0}tablea{1}.csv", direktorij, i));
            DataTable tabela = ds.Tables[i];
            CreateCSVFile(tabela,datoteka );
            Console.WriteLine("Generišem tabelu {0}", datoteka);
        }
        Console.ReadKey();
    }
  }
}

public void CreateCSVFile(DataTable dt, string strFilePath,string separator)
        {               
            #region Export Grid to CSV
            // Create the CSV file to which grid data will be exported.

            StreamWriter sw = new StreamWriter(strFilePath, false); 
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {    
                sw.Write(dt.Columns[i]);    
                if (i < iColCount - 1)
                {
                    sw.Write(separator);
                }  
            }    

            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(separator);
                    }
                }
                sw.Write(sw.NewLine);
            }

            sw.Close();
            #endregion
        }

la réponse de BFree a fonctionné pour moi. J'avais besoin de poster le ruisseau à droite du navigateur. J'imagine que c'est une alternative courante. J'ai ajouté ce qui suit au code principal() de BFree pour faire ceci:

//StreamReader reader = new StreamReader(stream);
//Console.WriteLine(reader.ReadToEnd());

string fileName = "fileName.csv";
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", fileName));
stream.Position = 0;
stream.WriteTo(HttpContext.Current.Response.OutputStream);