最近做一个项目,需要用到.csv文件,本来用npoi做了一个出来,无奈不能支持.csv格式。后面进行修改,利用了CsvReader,可以读取到csv文件,但是无法进行存储.....最终选择的办法如下:
1、建立类 CsvExport:
public class CsvExport
{
List<string> _fields = new List<string>();
List<Dictionary<string, object>> _rows = new List<Dictionary<string, object>>();
public Dictionary<string, object> _currentRow { get { return _rows[_rows.Count-1 ]; } }
private readonly string _columnSeparator;
private readonly bool _includeColumnSeparatorDefinitionPreamble;
public CsvExport(string columnSeparator = ",", bool includeColumnSeparatorDefinitionPreamble = true)
{
_columnSeparator = columnSeparator;
_includeColumnSeparatorDefinitionPreamble = includeColumnSeparatorDefinitionPreamble;
}
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>());
}
public void 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);
}
}
}
}
public static string MakeValueCsvFriendly(object value, string columnSeparator = ",")
{
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().Trim();
if (output.Length > 30000) //cropping value for stupid Excel
output = output.Substring(0, 30000);
if (output.Contains(columnSeparator) || output.Contains("\"") || output.Contains("\n") || output.Contains("\r"))
output = '"' + output.Replace("\"", "\"\"") + '"';
return output;
}
public IEnumerable<string> ExportToLines()
{
//添加了表头,不需要可以屏蔽
yield return string.Join(_columnSeparator, _fields.Select(f => MakeValueCsvFriendly(f, _columnSeparator)));
//添加了表中的行
foreach (Dictionary<string, object> row in _rows)
{
foreach (string k in _fields.Where(f => !row.ContainsKey(f)))
{
row[k] = null;
}
yield return string.Join(_columnSeparator, _fields.Select(field => MakeValueCsvFriendly(row[field], _columnSeparator)));
}
}
/// <summary>
///
/// </summary>
public string Export()
{
StringBuilder sb = new StringBuilder();
foreach (string line in ExportToLines())
{
sb.AppendLine(line);
}
return sb.ToString();
}
public void ExportToFile(string path)
{
//在已有的文档后面添加新内容
File.AppendAllLines(path, ExportToLines(),Encoding.Default);
//新建文件添加内容
//File.WriteAllLines(path, ExportToLines(), Encoding.UTF8);
}
public byte[] ExportToBytes()
{
var data = Encoding.UTF8.GetBytes(Export());
return Encoding.UTF8.GetPreamble().Concat(data).ToArray();
}
}
用法如下:
csv文件路径为ExcelFileName,要添加进去的内容为SetCommunicationList中的某些列,如果添加整个列表 可以使用addrows函数
private void WriteToExcel(string ExcelFileName, List<CommunicationTable> SetCommunicationList)
{
var myExport = new CsvExport();
int i = 0;
foreach (var obj in SetCommunicationList)
{
myExport.AddRow();
var values = obj.GetType().GetProperties();
foreach (var value in values)
{
if (value.Name.ToString().Equals("ID"))
{
myExport[value.Name] = obj.ID +i;
}
if (value.Name.ToString().Equals("**"))
{
myExport[value.Name] = obj.**;
}
if (value.Name.ToString().Equals("**"))
{
myExport[value.Name] = obj.**;
} if (value.Name.ToString().Equals("**"))
{
myExport[value.Name] = obj.**;
}
}
i++;
}
myExport.ExportToFile(ExcelFileName);