将DataTable转换为CSV流

目前有一个DataTable,但希望通过WebHandler将其流式传输给用户.FileHelpers有CommonEngine.DataTableToCsv(dt, "file.csv").但是它会将其保存到文件中.如何将其保存到流中呢？当我知道高级列或它们没有改变时,我知道如何做到这一点,但我想直接从数据表生成列标题.

如果我知道列,我只是创建类:

[DelimitedRecord(",")]
public class MailMergeFields
{
    [FieldQuoted()]
    public string FirstName;
    [FieldQuoted()]
    public string LastName;
}

然后使用FileHelperEngine并添加记录:

FileHelperEngine engine = new FileHelperEngine(typeof(MailMergeFields));

MailMergeFields[] merge = new MailMergeFields[dt.Rows.Count + 1];

// add headers
merge[0] = new MailMergeFields();
merge[0].FirstName = "FirstName";
merge[0].LastName = "LastName";

int i = 1;              
// add records
foreach (DataRow dr in dt.Rows)
{
    merge[i] = new MailMergeFields();
    merge[i].FirstName = dr["Forename"];
    merge[i].LastName = dr["Surname"];
    i++;
}

最后写入一个流:

TextWriter writer = new StringWriter();
engine.WriteStream(writer, merge);
context.Response.Write(writer.ToString());

不幸的是,因为我不知道前面的列,我不能事先创建类.

你可以自己写一些东西:

public static class Extensions
{
    public static string ToCSV(this DataTable table)
    {
        var result = new StringBuilder();
        for (int i = 0; i < table.Columns.Count; i++)
        {
            result.Append(table.Columns[i].ColumnName);
            result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
        }

        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(row[i].ToString());
                result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
            }
        }

        return result.ToString();
    }
}

并测试:

  public static void Main()
  {
        DataTable table = new DataTable();
        table.Columns.Add("Name");
        table.Columns.Add("Age");
        table.Rows.Add("John Doe", "45");
        table.Rows.Add("Jane Doe", "35");
        table.Rows.Add("Jack Doe", "27");
        var bytes = Encoding.GetEncoding("iso-8859-1").GetBytes(table.ToCSV());
        MemoryStream stream = new MemoryStream(bytes);

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

编辑:你的评论:

这取决于你希望你的csv格式化,但通常如果文本包含特殊字符,你想用双引号括起来,即:"我的,文本".您可以在创建csv的代码中添加检查以检查特殊字符,如果是,则将文本用双引号括起来.至于.NET 2.0的东西,只需在类中创建它作为辅助方法,或者在方法声明中删除它,并调用它:Extensions.ToCsv(table);

更新1

我已将其修改为使用StreamWriter,添加一个选项以检查输出中是否需要列标题.

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

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

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

    return true;
}

如您所见,您可以选择初始StreamWriter的输出,如果您使用StreamWriter(Stream BaseStream),您可以将csv写入MemeryStream,FileStream等.

起源

我有一个简单的数据表到csv函数,它很好地服务于我:

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

        var columnNames = dt.Columns.Cast().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);
    }