如何创建 CSV Excel 文件 C # ？

我正在寻找一个创建 CSV Excel 文件的类。

预期功能:

  • 用起来非常简单
  • 转义逗号和引号，因此 excel 可以很好地处理它们
  • 以不受时区限制的格式导出日期和日期时间

你知道有什么职业能做出这种事吗？

180648 次浏览
小开

读写 CSV 文件的另一个好的解决方案是 文件助理(开源)。

小开

使用 string. Join 代替所有 foreach 循环怎么样？

小开

您还可以使用 ADO 来完成这项工作: http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx

小开

这门课上得不错。简单易用。我修改了这个类，在导出的第一行包含了一个标题; 我想我可以共享:

用途:

CsvExport myExport = new CsvExport();
myExport.addTitle = String.Format("Name: {0},{1}", lastName, firstName));

班级:

public class CsvExport
{
List<string> fields = new List<string>();


public string addTitle { get; set; } // string for the first row of the export


List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
Dictionary<string, object> currentRow
{
get
{
return rows[rows.Count - 1];
}
}


public object this[string field]
{
set
{
if (!fields.Contains(field)) fields.Add(field);
currentRow[field] = value;
}
}


public void AddRow()
{
rows.Add(new Dictionary<string, object>());
}


string MakeValueCsvFriendly(object value)
{
if (value == null) return "";
if (value is Nullable && ((INullable)value).IsNull) return "";
if (value is DateTime)
{
if (((DateTime)value).TimeOfDay.TotalSeconds == 0)
return ((DateTime)value).ToString("yyyy-MM-dd");
return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
}
string output = value.ToString();
if (output.Contains(",") || output.Contains("\""))
output = '"' + output.Replace("\"", "\"\"") + '"';
return output;


}


public string Export()
{
StringBuilder sb = new StringBuilder();


// if there is a title
if (!string.IsNullOrEmpty(addTitle))
{
// escape chars that would otherwise break the row / export
char[] csvTokens = new[] { '\"', ',', '\n', '\r' };


if (addTitle.IndexOfAny(csvTokens) >= 0)
{
addTitle = "\"" + addTitle.Replace("\"", "\"\"") + "\"";
}
sb.Append(addTitle).Append(",");
sb.AppendLine();
}




// The header
foreach (string field in fields)
sb.Append(field).Append(",");
sb.AppendLine();


// The rows
foreach (Dictionary<string, object> row in rows)
{
foreach (string field in fields)
sb.Append(MakeValueCsvFriendly(row[field])).Append(",");
sb.AppendLine();
}


return sb.ToString();
}


public void ExportToFile(string path)
{
File.WriteAllText(path, Export());
}


public byte[] ExportToBytes()
{
return Encoding.UTF8.GetBytes(Export());
}
}
小开

根据需要使用反射编写的版本略有不同。我不得不向 CSV 导出一个对象列表。以防将来有人用。

public class CsvExport<T> where T: class
{
public List<T> Objects;


public CsvExport(List<T> objects)
{
Objects = objects;
}


public string Export()
{
return Export(true);
}


public string Export(bool includeHeaderLine)
{


StringBuilder sb = new StringBuilder();
//Get properties using reflection.
IList<PropertyInfo> propertyInfos = typeof(T).GetProperties();


if (includeHeaderLine)
{
//add header line.
foreach (PropertyInfo propertyInfo in propertyInfos)
{
sb.Append(propertyInfo.Name).Append(",");
}
sb.Remove(sb.Length - 1, 1).AppendLine();
}


//add value for each property.
foreach (T obj in Objects)
{
foreach (PropertyInfo propertyInfo in propertyInfos)
{
sb.Append(MakeValueCsvFriendly(propertyInfo.GetValue(obj, null))).Append(",");
}
sb.Remove(sb.Length - 1, 1).AppendLine();
}


return sb.ToString();
}


//export to a file.
public void ExportToFile(string path)
{
File.WriteAllText(path, Export());
}


//export as binary data.
public byte[] ExportToBytes()
{
return Encoding.UTF8.GetBytes(Export());
}


//get the csv value for field.
private string MakeValueCsvFriendly(object value)
{
if (value == null) return "";
if (value is Nullable && ((INullable)value).IsNull) return "";


if (value is DateTime)
{
if (((DateTime)value).TimeOfDay.TotalSeconds == 0)
return ((DateTime)value).ToString("yyyy-MM-dd");
return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
}
string output = value.ToString();


if (output.Contains(",") || output.Contains("\""))
output = '"' + output.Replace("\"", "\"\"") + '"';


return output;


}
}

使用示例: (每条评论更新一次)

CsvExport<BusinessObject> csv= new CsvExport<BusinessObject>(GetBusinessObjectList());
Response.Write(csv.Export());
小开

我添加了 ExportToStream，这样 csv 就不必首先保存到硬盘上。

public Stream ExportToStream()
{
MemoryStream stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(Export(true));
writer.Flush();
stream.Position = 0;
return stream;
}
小开

我加了

public void ExportToFile(string path, DataTable tabela)
{


DataColumnCollection colunas = tabela.Columns;


foreach (DataRow linha in tabela.Rows)
{


this.AddRow();


foreach (DataColumn coluna in colunas)


{


this[coluna.ColumnName] = linha[coluna];


}


}
this.ExportToFile(path);


}

以前的代码不适用于旧的.NET 版本。对于3.5版本的框架，请使用以下其他版本:

        public void ExportToFile(string path)
{
bool abort = false;
bool exists = false;
do
{
exists = File.Exists(path);
if (!exists)
{
if( !Convert.ToBoolean( File.CreateText(path) ) )
abort = true;
}
} while (!exists || abort);


if (!abort)
{
//File.OpenWrite(path);
using (StreamWriter w = File.AppendText(path))
{
w.WriteLine("hello");
}


}


//File.WriteAllText(path, Export());
}
小开

有一个用于 CSV 的开源库，你可以使用 nuget: http://joshclose.github.io/CsvHelper/获得它

小开

非常感谢！ 我将类修改为:

  • 使用变量分隔符，而不是代码中的硬编码
  • 取代所有 MakeValueCsvFriendly中的新行(n r n r)

密码:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;


public class CsvExport
{


public char delim = ';';
/// <summary>
/// To keep the ordered list of column names
/// </summary>
List<string> fields = new List<string>();


/// <summary>
/// The list of rows
/// </summary>
List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();


/// <summary>
/// The current row
/// </summary>
Dictionary<string, object> currentRow { get { return rows[rows.Count - 1]; } }


/// <summary>
/// Set a value on this column
/// </summary>
public object this[string field]
{
set
{
// Keep track of the field names, because the dictionary loses the ordering
if (!fields.Contains(field)) fields.Add(field);
currentRow[field] = value;
}
}


/// <summary>
/// Call this before setting any fields on a row
/// </summary>
public void AddRow()
{
rows.Add(new Dictionary<string, object>());
}


/// <summary>
/// Converts a value to how it should output in a csv file
/// If it has a comma, it needs surrounding with double quotes
/// Eg Sydney, Australia -> "Sydney, Australia"
/// Also if it contains any double quotes ("), then they need to be replaced with quad quotes[sic] ("")
/// Eg "Dangerous Dan" McGrew -> """Dangerous Dan"" McGrew"
/// </summary>
string MakeValueCsvFriendly(object value)
{
if (value == null) return "";
if (value is INullable && ((INullable)value).IsNull) return "";
if (value is DateTime)
{
if (((DateTime)value).TimeOfDay.TotalSeconds == 0)
return ((DateTime)value).ToString("yyyy-MM-dd");
return ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss");
}
string output = value.ToString();
if (output.Contains(delim) || output.Contains("\""))
output = '"' + output.Replace("\"", "\"\"") + '"';
if (Regex.IsMatch(output,  @"(?:\r\n|\n|\r)"))
output = string.Join(" ", Regex.Split(output, @"(?:\r\n|\n|\r)"));
return output;
}


/// <summary>
/// Output all rows as a CSV returning a string
/// </summary>
public string Export()
{
StringBuilder sb = new StringBuilder();


// The header
foreach (string field in fields)
sb.Append(field).Append(delim);
sb.AppendLine();


// The rows
foreach (Dictionary<string, object> row in rows)
{
foreach (string field in fields)
sb.Append(MakeValueCsvFriendly(row[field])).Append(delim);
sb.AppendLine();
}


return sb.ToString();
}


/// <summary>
/// Exports to a file
/// </summary>
public void ExportToFile(string path)
{
File.WriteAllText(path, Export());
}


/// <summary>
/// Exports as raw UTF8 bytes
/// </summary>
public byte[] ExportToBytes()
{
return Encoding.UTF8.GetBytes(Export());


}


}
小开

您只需要一个函数就可以完成此操作。 您只需要在解决方案资源管理器中创建一个文件夹，并将 csv 文件存储在那里，然后将该文件导出到用户。

在我的情况下，我有一个文件夹下载。首先，我将所有内容导出到该目录，然后将其导出到用户。对于 response. end 处理，我使用了 ThreadAbortException。因此，它是一个100% 真正和工作的功能，在我的解决方案。

protected void lnkExport_OnClick(object sender, EventArgs e)
{


string filename = strFileName = "Export.csv";


DataTable dt = obj.GetData();


// call the content and load it into the datatable


strFileName = Server.MapPath("Downloads") + "\\" + strFileName;


// creating a file in the downloads folder in your solution explorer


TextWriter tw = new StreamWriter(strFileName);


// using the built in class textwriter for writing your content in the exporting file


string strData = "Username,Password,City";


// above line is the header for your exported file. So add headings for your coloumns in excel(.csv) file and seperate them with ","


strData += Environment.NewLine;


// setting the environment to the new line


foreach (DataRow dr in dt.Rows)
{
strData += dr["Username"].ToString() + "," + dr["Password"].ToString() + "," +      dr["City"].ToString();
strData += Environment.NewLine;
}


// everytime when loop execute, it adds a line into the file
tw.Write(strData);


// writing the contents in file
tw.Close();


// closing the file
Response.Redirect("Downloads/" + filename);


// exporting the file to the user as a popup to save as....
}
小开

请原谅我

但我认为公共开源存储库是一种更好的方式来共享代码，做出贡献，更正，并添加诸如“我修复了这个，我修复了那个”之类的内容

因此，我用 subject-starter 的代码和所有附加内容创建了一个简单的 git 存储库:

Https://github.com/jitbit/csvexport

我自己也添加了一些有用的补丁。每个人都可以添加建议，分享等等。把你的叉子寄给我，我把它们合并回回收。

附言。我把 Chris 所有的版权声明都贴出来了。@ Chris 如果你反对这个主意，告诉我，我会杀了它。

小开

如果有人想要我将其转换为 IEnumable 上的扩展方法:

public static class ListExtensions
{
public static string ExportAsCSV<T>(this IEnumerable<T> listToExport, bool includeHeaderLine, string delimeter)
{
StringBuilder sb = new StringBuilder();


IList<PropertyInfo> propertyInfos = typeof(T).GetProperties();


if (includeHeaderLine)
{
foreach (PropertyInfo propertyInfo in propertyInfos)
{
sb.Append(propertyInfo.Name).Append(",");
}
sb.Remove(sb.Length - 1, 1).AppendLine();
}


foreach (T obj in listToExport)
{
T localObject = obj;


var line = String.Join(delimeter, propertyInfos.Select(x => SanitizeValuesForCSV(x.GetValue(localObject, null), delimeter)));


sb.AppendLine(line);
}


return sb.ToString();
}


private static string SanitizeValuesForCSV(object value, string delimeter)
{
string output;


if (value == null) return "";


if (value is DateTime)
{
output = ((DateTime)value).ToLongDateString();
}
else
{
output = value.ToString();
}


if (output.Contains(delimeter) || output.Contains("\""))
output = '"' + output.Replace("\"", "\"\"") + '"';


output = output.Replace("\n", " ");
output = output.Replace("\r", "");


return output;
}
}
小开

原始类有一个问题，即如果要添加新列，您将在 Export 方法上收到 KeyNotFoundException。例如:

static void Main(string[] args)
{
var export = new CsvExport();


export.AddRow();
export["Region"] = "New York, USA";
export["Sales"] = 100000;
export["Date Opened"] = new DateTime(2003, 12, 31);


export.AddRow();
export["Region"] = "Sydney \"in\" Australia";
export["Sales"] = 50000;
export["Date Opened"] = new DateTime(2005, 1, 1, 9, 30, 0);
export["Balance"] = 3.45f;  //Exception is throwed for this new column


export.ExportToFile("Somefile.csv");
}

为了解决这个问题，并使用了@KeyboardCowboy 使用反射的想法，我修改了代码以允许添加不具有相同列的行。可以使用匿名类的实例。例如:

static void Main(string[] args)
{
var export = new CsvExporter();


export.AddRow(new {A = 12, B = "Empty"});
export.AddRow(new {A = 34.5f, D = false});


export.ExportToFile("File.csv");
}

您可以在这里下载源代码 CsvExporter。随意使用和修改。

现在，如果要编写的所有行都属于同一个类，那么我创建了泛型类 CsvWriter.cs，它具有更好的 RAM 使用性能，非常适合编写大文件。另外，它还允许您向所需的数据类型添加格式化程序。一个使用的例子:

class Program
{
static void Main(string[] args)
{
var writer = new CsvWriter<Person>("Persons.csv");


writer.AddFormatter<DateTime>(d => d.ToString("MM/dd/yyyy"));


writer.WriteHeaders();
writer.WriteRows(GetPersons());


writer.Flush();
writer.Close();
}


private static IEnumerable<Person> GetPersons()
{
yield return new Person
{
FirstName = "Jhon",
LastName = "Doe",
Sex = 'M'
};


yield return new Person
{
FirstName = "Jhane",
LastName = "Doe",
Sex = 'F',
BirthDate = DateTime.Now
};
}
}




class Person
{
public string FirstName { get; set; }


public string LastName { get; set; }


public char Sex  { get; set; }


public DateTime BirthDate { get; set; }
}

提交答案