C＃中的数据表到CSV [英] c# datatable to csv

问题描述

可能有人请告诉我为什么下面的code不工作。数据被保存到csv文件，但是该数据并不分离。它所有存在的每行的第一个单元格内。

Could somebody please tell me why the following code is not working. The data is saved into the csv file, however the data is not separated. It all exists within the first cell of each row.

StringBuilder sb = new StringBuilder();

foreach (DataColumn col in dt.Columns)
{
    sb.Append(col.ColumnName + ',');
}

sb.Remove(sb.Length - 1, 1);
sb.Append(Environment.NewLine);

foreach (DataRow row in dt.Rows)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        sb.Append(row[i].ToString() + ",");
    }

    sb.Append(Environment.NewLine);
}

File.WriteAllText("test.csv", sb.ToString());

感谢。

推荐答案

以下较短的版本中打开在Excel精细，也许你的问题是后面的逗号。

The following shorter version opens fine in Excel, maybe your issue was the trailing comma

StringBuilder sb = new StringBuilder(); 

string[] columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName).
                                  ToArray();
sb.AppendLine(string.Join(",", columnNames));

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

File.WriteAllText("test.csv", sb.ToString());

.NET> = 4.0

和蒂姆指出的那样，如果你是在.NET> = 4，你可以把它更短：

.net >= 4.0

And as Tim pointed out, if you are on .net>=4, you can make it even shorter:

StringBuilder sb = new StringBuilder(); 

IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
                                  Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

由于建议的基督徒，如果你要处理特殊字符转义领域，通过更换循环块：

As suggested by Christian, if you want to handle special characters escaping in fields, replace the loop block by:

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

和最后一个建议，你可以写一行的CSV内容系，而不是作为一个整体文件，以避免在内存中有一个大的文件。

And last suggestion, you could write the csv content line by line instead of as a whole document, to avoid having a big document in memory.

这篇关于C＃中的数据表到CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！