代码生成CSV文件
简介:
逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号），其文件以纯文本形式存储表格数据（数字和文本）。纯文本意味着该文件是一个字符序列，不含必须像二进制数字那样被解读的数据。CSV文件由任意数目的记录组成，记录间以某种换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。通常，所有记录都有完全相同的字段序列。
文件格式：
sep=, //标记分隔符(这里是","号)
A1,A2,A3,A4,A5 //表头
1,2,3,4,5 //以下的每一行表示一条记录
11,22,33,44,55
111,222,333,444,555
…
应用场景：
CSV是一种通用的、相对简单的文件格式，被用户、商业和科学广泛应用。最广泛的应用是在程序之间转移表格数据，而这些程序本身是在不兼容的格式上进行操作的（往往是私有的和/或无规范的格式）。因为大量程序都支持某种CSV变体，至少是作为一种可选择的输入/输出格式。常用于数据的导入与导出。
文件规则：
1、开头是不留空，以行为单位
2、可含或不含列名，含列名则居文件第一行
3、一行数据不跨行，无空行
4、以半角逗号（即,）作分隔符，列为空也要表达其存在
5、列内容如存在半角引号（即"），替换成半角双引号（""）转义，即用半角引号（即""）将该字段值包含起来
6、文件读写时引号，逗号操作规则互逆
7、内码格式不限，可为 ASCII、Unicode 或者其他
8、不支持特殊字符
C# CVS文件导出类：
publicclassCsvExport
{
///<summary>
/// To keep the ordered list of column names
///</summary>
List<string> _fields = newList<string>();
///<summary>
/// The list of rows
///</summary>
List<Dictionary<string, object>> _rows = newList<Dictionary<string, object>>();
///<summary>
/// The current row
///</summary>
Dictionary<string, object> _currentRow { get { return _rows[_rows.Count - 1]; } }
///<summary>
/// The string used to separate columns in the output
///</summary>
privatereadonlystring _columnSeparator;
///<summary>
/// Whether to include the preamble that declares which column separator is used in the output
///</summary>
privatereadonlybool _includeColumnSeparatorDefinitionPreamble;
///<summary>
/// Initializes a new instance of the <see cref="Jitbit.Utils.CsvExport"/> class.
///</summary>
///<param name="columnSeparator">
/// The string used to separate columns in the output.
/// By default this is a comma so that the generated output is a CSV file.
///</param>
///<param name="includeColumnSeparatorDefinitionPreamble">
/// Whether to include the preamble that declares which column separator is used in the output.
/// By default this is <c>true</c> so that Excel can open the generated CSV
/// without asking the user to specify the delimiter used in the file.
///</param>
public CsvExport(string columnSeparator = ",", bool includeColumnSeparatorDefinitionPreamble = true)
{
_columnSeparator = columnSeparator;
_includeColumnSeparatorDefinitionPreamble = includeColumnSeparatorDefinitionPreamble;
}
///<summary>
/// Set a value on this column
///</summary>
publicobjectthis[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>
publicvoid AddRow()
{
_rows.Add(newDictionary<string, object>());
}
///<summary>
/// Add a list of typed objects, maps object properties to CsvFields
///</summary>
publicvoid AddRows<T>(IEnumerable<T> list)
{
if (list.Any())
{
foreach (var obj in list)
{
AddRow();
var values = obj.GetType().GetProperties();
foreach (var value in values)
{
this[value.Name] = value.GetValue(obj, null);
}
}
}
}
///<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>
///<param name="columnSeparator">
/// The string used to separate columns in the output.
/// By default this is a comma so that the generated output is a CSV document.
///</param>
publicstaticstring MakeValueCsvFriendly(object value, string columnSeparator = ",")
{
if (value == null) return"";
if (value isINullable && ((INullable)value).IsNull) return"";
if (value isDateTime)
{
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().Trim();
if (output.Contains(columnSeparator) || output.Contains("\"") || output.Contains("\n") || output.Contains("\r"))
output = '"' + output.Replace("\"", "\"\"") + '"';
if (output.Length > 30000) //cropping value for stupid Excel
{
if (output.EndsWith("\""))
{
output = output.Substring(0, 30000);
if (output.EndsWith("\"") && !output.EndsWith("\"\"")) //rare situation when cropped line ends with a '"'
output += "\""; //add another '"' to escape it
output += "\"";
}
else
output = output.Substring(0, 30000);
}
return output;
}
///<summary>
/// Outputs all rows as a CSV, returning one string at a time
///</summary>
privateIEnumerable<string> ExportToLines()
{
if (_includeColumnSeparatorDefinitionPreamble) yieldreturn"sep=" + _columnSeparator;
// The header
yieldreturnstring.Join(_columnSeparator, _fields);
// The rows
foreach (Dictionary<string, object> row in _rows)
{
foreach (string k in _fields.Where(f => !row.ContainsKey(f)))
{
row[k] = null;
}
yieldreturnstring.Join(_columnSeparator, _fields.Select(field => MakeValueCsvFriendly(row[field], _columnSeparator)));
}
}
///<summary>
/// Output all rows as a CSV returning a string
///</summary>
publicstring Export()
{
StringBuilder sb = newStringBuilder();
foreach (string line in ExportToLines())
{
sb.AppendLine(line);
}
return sb.ToString();
}
///<summary>
/// Exports to a file
///</summary>
publicvoid ExportToFile(string path)
{
File.WriteAllLines(path, ExportToLines(), Encoding.UTF8);
}
///<summary>
/// Exports to a file
///</summary>
publicvoid ExportToFile(string path, Encoding encoding)
{
File.WriteAllLines(path, ExportToLines(), encoding);
}
///<summary>
/// Exports as raw UTF8 bytes
///</summary>
publicbyte[] ExportToBytes()
{
var data = Encoding.UTF8.GetBytes(Export());
returnEncoding.UTF8.GetPreamble().Concat(data).ToArray();
}
}
//具体的调用：
CsvExport myExport = newCsvExport(); //创建对象
foreach (var data in list)
{
myExport.AddRow(); //添加行
foreach (var property in data)
{
myExport[property.Name] = property.Value; //设置列头和每一行对应的数据
}
}
myExport.ExportToFile(filePath, Encoding.Default); //按照编码导出csv文件
