C#使用Linq to csv读取.csv文件数据3_源码改造_支持设置标题行&标题映射&自动打印并忽略错误行数据&定义数据格式
使用csv文件存储数据比excel更加轻量级，支持的场景更加丰富，可以完全自定义，不受excel约束。 但是对csv文件的解析和读取，网上的资料又不是很多，这里通过拿来linq to csv的源码并在其基础上进行扩展，以支持遇到的一些问题。
主要扩展的功能点：
1-支持设置标题行 （源码仅支持设置首行是否为标题行）
2-支持设置标题跟数据的映射关系 （源码通过下标设置数据关系，这一点如果标题行过多时，数位置是很头疼的，尤其是在多列中再插入一列...而通过标题跟数据映射就很简单了，自动寻找下标）
2-自动打印错误行数据并忽略错误行数据（这点和源码差不多）
3-定义数据格式（和源码一致）
我的前两篇关于csv的帮助文章也可以参考下：
C#使用Linq to csv读取.csv文件数据2_处理含有非列名数据的方法（说明信息等）
改造完整代码如下：
1-Csv文件类特性标记：
/// <summary> /// Csv文件类特性标记 /// </summary> [System.AttributeUsage(System.AttributeTargets.Field | System.AttributeTargets.Property, AllowMultiple = false)] public class CsvColumnAttribute : System.Attribute { /// <summary> /// 标题 /// </summary> public string Title { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 单元格是否要检查空数据（true为检查，为空的行数据不添加） /// </summary> public bool IsCheckContentEmpty { get; set; } public CsvColumnAttribute() : this(false) { } public CsvColumnAttribute(bool isCheckEmpty) : this(null, null, isCheckEmpty) { } public CsvColumnAttribute(string title) : this(title, null, false) { } public CsvColumnAttribute(string title, string outputFormat) : this(title, outputFormat, false) { } public CsvColumnAttribute(string title, bool isCheckEmpty) : this(title, null, isCheckEmpty) { } public CsvColumnAttribute(string title, string outputFormat, bool isCheckEmpty) { Title = title; OutputFormat = outputFormat; IsCheckContentEmpty = isCheckEmpty; } }
2-CsvFileDescription：
public class CsvFileDescription { public CsvFileDescription() : this(0) { } public CsvFileDescription(int titleRawIndex) : this(',', titleRawIndex, Encoding.UTF8) { } public CsvFileDescription(char separatorChar, int titleRawIndex, Encoding encoding) { SeparatorChar = separatorChar; TitleRawIndex = titleRawIndex; Encoding = encoding; } /// <summary> /// CSV文件字符编码 /// </summary> public Encoding Encoding { get; set; } /// <summary> /// 分隔符（默认为（,）,也可以是其他分隔符如(\t)） /// </summary> public char SeparatorChar { get; set; } /// <summary> /// 标题所在行位置（默认为0，没有标题填-1） /// </summary> public int TitleRawIndex { get; set; } }
3-字段映射类：
/// <summary> /// 字段映射类 /// </summary> public class CsvFieldMapper { /// <summary> /// 属性信息 /// </summary> public PropertyInfo PropertyInfo { get; set; } /// <summary> /// 标题 /// </summary> public string CSVTitle { get; set; } /// <summary> /// 标题下标位置 /// </summary> public int CSVTitleIndex { get; set; } /// <summary> /// 字符输出格式（数字和日期类型需要） /// </summary> public string OutputFormat { get; set; } /// <summary> /// 单元格是否要检查空数据（true为检查，为空的行数据不添加） /// </summary> public bool IsCheckContentEmpty { get; set; } public static List<CsvFieldMapper> GetModelFieldMapper<T>() { List<CsvFieldMapper> fieldMapperList = new List<CsvFieldMapper>(100); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); CsvColumnAttribute csvColumnAttribute = null; int beginTitleIndex = 0; foreach (var tPropertyInfo in tPropertyInfoList) { csvColumnAttribute = (CsvColumnAttribute)tPropertyInfo.GetCustomAttribute(typeof(CsvColumnAttribute)); if (csvColumnAttribute != null) { fieldMapperList.Add(new CsvFieldMapper { PropertyInfo = tPropertyInfo, CSVTitle = csvColumnAttribute.Title, CSVTitleIndex = beginTitleIndex, OutputFormat = csvColumnAttribute.OutputFormat, IsCheckContentEmpty = csvColumnAttribute.IsCheckContentEmpty }); beginTitleIndex++; } } return fieldMapperList; } }
4-CsvHelper帮助类：
public class CsvHelper { /// <summary> /// 日志 /// </summary> private ILogger _Logger { get; set; } public CsvHelper(ILogger<CsvHelper> logger) { this._Logger = logger; } public List<T> Read<T>(string filePath, CsvFileDescription fileDescription) where T : class, new() { List<T> tList = new List<T>(50 * 10000); T t = null; int currentRawIndex = 0; if (File.Exists(filePath)) { using (StreamReader streamReader = new StreamReader(filePath, fileDescription.Encoding)) { List<CsvFieldMapper> CsvFieldMapperList = CsvFieldMapper.GetModelFieldMapper<T>(); string rawValue = null; string[] rawValueArray = null; PropertyInfo propertyInfo = null; string propertyValue = null; bool rawReadEnd = false; do { rawValue = streamReader.ReadLine(); if (!string.IsNullOrEmpty(rawValue)) { //替换字符串含有分隔符为{分隔符}，最后再替换回来 if (rawValue.Contains("\"")) { rawValue = rawValue.Replace("\"\"", "{引号}"); int yhExecCount = 0; int yhBeginIndex = 0; int yhEndIndex = 0; string yhText = null; do { yhBeginIndex = rawValue.GetIndexOfStr("\"", (yhExecCount * 2) + 1); yhEndIndex = rawValue.GetIndexOfStr("\"", (yhExecCount * 2) + 2); if (yhBeginIndex > -1 && yhEndIndex > -1) { yhText = rawValue.Substring(yhBeginIndex, (yhEndIndex - yhBeginIndex + 1)); string newYHText = yhText.Replace(fileDescription.SeparatorChar.ToString(), "{分隔符}"); rawValue = rawValue.Replace(yhText, newYHText); } yhExecCount++; } while (yhBeginIndex > -1); } rawValueArray = rawValue.Split(fileDescription.SeparatorChar); //标题行 if (currentRawIndex == fileDescription.TitleRawIndex) { foreach (var csvFieldMapper in CsvFieldMapperList) { csvFieldMapper.CSVTitleIndex = -1; for (int i = 0; i < rawValueArray.Length; i++) { if (rawValueArray[i].Equals(csvFieldMapper.CSVTitle, StringComparison.OrdinalIgnoreCase)) { csvFieldMapper.CSVTitleIndex = i; break; } } } } //内容行 else if (currentRawIndex > fileDescription.TitleRawIndex) { t = new T(); bool isExistException = false; foreach (var CsvFieldMapper in CsvFieldMapperList) { try { if (CsvFieldMapper.CSVTitleIndex >= 0 && CsvFieldMapper.CSVTitleIndex <= rawValueArray.Length - 1) { propertyInfo = CsvFieldMapper.PropertyInfo; propertyValue = rawValueArray[CsvFieldMapper.CSVTitleIndex]; if (!string.IsNullOrEmpty(propertyValue)) { propertyValue = propertyValue.Trim('"'); propertyValue = propertyValue.Replace("{引号}", "\""); propertyValue = propertyValue.Replace("{分隔符}", fileDescription.SeparatorChar.ToString()); if (CsvFieldMapper.IsCheckContentEmpty && propertyValue.IsNullOrEmpty()) { t = null; break; } else { TypeHelper.SetPropertyValue(t, propertyInfo.Name, propertyValue); } } } } catch (Exception e) { isExistException = true; this._Logger.LogWarning(e, $"第{currentRawIndex}行数据{propertyValue}转换属性{propertyInfo.Name}-{propertyInfo.PropertyType.Name}失败！文件路径：{filePath}"); break; } } if (isExistException == false && t != null) { tList.Add(t); } } } else { rawReadEnd = true; } currentRawIndex++; } while (rawReadEnd == false); } } return tList; } /// <summary> /// 写入文件 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="path"></param> /// <param name="tList"></param> /// <param name="fileDescription"></param> public void WriteFile<T>(string path, List<T> tList, CsvFileDescription fileDescription) where T : class, new() { if (!string.IsNullOrEmpty(path)) { string fileDirectoryPath = null; if (path.Contains("\\")) { fileDirectoryPath = path.Substring(0, path.LastIndexOf('\\')); } else { fileDirectoryPath = path.Substring(0, path.LastIndexOf('/')); } if (!Directory.Exists(fileDirectoryPath)) { Directory.CreateDirectory(fileDirectoryPath); } int dataCount = tList.Count; Dictionary<int, CsvFieldMapper> fieldMapperDic = CsvFieldMapper.GetModelFieldMapper<T>().ToDictionary(m => m.CSVTitleIndex); int titleCount = fieldMapperDic.Keys.Count(); string[] rawValueArray = new string[titleCount]; StringBuilder rawValueBuilder = new StringBuilder(); string rawValue = null; T t = null; PropertyInfo propertyInfo = null; int currentRawIndex = 0; int tIndex = 0; using (StreamWriter streamWriter = new StreamWriter(path, false, fileDescription.Encoding)) { do { try { rawValue = ""; #if DEBUG if (currentRawIndex % 10000 == 0) { this._Logger.LogInformation($"已写入文件：{path}，数据量：{currentRawIndex + 1}"); } #endif if (currentRawIndex >= fileDescription.TitleRawIndex) { //清空数组数据 for (int i = 0; i < titleCount; i++) { rawValueArray[i] = ""; } if (currentRawIndex > fileDescription.TitleRawIndex) { t = tList[tIndex]; tIndex++; } foreach (var fieldMapperItem in fieldMapperDic) { //写入标题行 if (currentRawIndex == fileDescription.TitleRawIndex) { rawValueArray[fieldMapperItem.Key] = fieldMapperItem.Value.CSVTitle; } //真正的数据从标题行下一行开始写 else { propertyInfo = fieldMapperItem.Value.PropertyInfo; object propertyValue = propertyInfo.GetValue(t); string formatValue = null; if (propertyValue != null) { if (propertyInfo.PropertyType is IFormattable && !string.IsNullOrEmpty(fieldMapperItem.Value.OutputFormat)) { formatValue = ((IFormattable)propertyValue).ToString(fieldMapperItem.Value.OutputFormat, null); } else { formatValue = propertyValue.ToString(); } //如果含有换行符，替换为空格 formatValue = formatValue.Replace("\r\n"," ").Replace("\r", " ").Replace("\n", " "); //如果属性值含有分隔符，则使用双引号包裹 if (formatValue.Contains(fileDescription.SeparatorChar.ToString())) { if (formatValue.Contains("\"")) { formatValue = formatValue.Replace("\"", "\"\""); } formatValue = $"\"{formatValue}\""; } rawValueArray[fieldMapperItem.Key] = formatValue; } } } rawValue = string.Join(fileDescription.SeparatorChar, rawValueArray); } rawValueBuilder.Append(rawValue + "\r\n"); } catch (Exception e) { this._Logger.LogWarning(e, $"(异常)Excel第{currentRawIndex + 1}行，数据列表第{tIndex + 1}个数据写入失败！rawValue：{rawValue}"); throw; } currentRawIndex++; } while (tIndex < dataCount); streamWriter.Write(rawValueBuilder.ToString()); streamWriter.Close(); streamWriter.Dispose(); } } } /// <summary> /// 写入文件(JObject对象使用) /// </summary> /// <typeparam name="T"></typeparam> /// <param name="path"></param> /// <param name="tList"></param> /// <param name="fileDescription"></param> public void WriteFileForJObject<T, TCollection>(string path, TCollection tList, Dictionary<string, string> fieldNameAndShowNameDic, CsvFileDescription fileDescription) where TCollection : List<T> where T : new() { if (!string.IsNullOrEmpty(path)) { string fileDirectoryPath = null; if (path.Contains("\\")) { fileDirectoryPath = path.Substring(0, path.LastIndexOf('\\')); } else { fileDirectoryPath = path.Substring(0, path.LastIndexOf('/')); } if (!Directory.Exists(fileDirectoryPath)) { Directory.CreateDirectory(fileDirectoryPath); } int dataCount = tList.Count; int titleCount = fieldNameAndShowNameDic.Count; string[] rawValueArray = new string[titleCount]; StringBuilder rawValueBuilder = new StringBuilder(); string rawValue = null; T t = default(T); JProperty propertyInfo = null; int currentRawIndex = 0; int tIndex = 0; Dictionary<int, JProperty> indexPropertyDic = this.GetIndexPropertyDicFromJObject(tList.FirstOrDefault(), fieldNameAndShowNameDic.Keys.ToList()); using (StreamWriter streamWriter = new StreamWriter(path, false, fileDescription.Encoding)) { do { try { rawValue = ""; #if DEBUG if (currentRawIndex % 10000 == 0) { this._Logger.LogInformation($"已写入文件：{path}，数据量：{currentRawIndex + 1}"); } #endif if (currentRawIndex >= fileDescription.TitleRawIndex) { //清空数组数据 for (int i = 0; i < titleCount; i++) { rawValueArray[i] = ""; } if (currentRawIndex > fileDescription.TitleRawIndex) { t = tList[tIndex]; tIndex++; } int fieldIndex = -1; foreach (var fieldMapperItem in fieldNameAndShowNameDic) { fieldIndex++; //写入标题行 if (currentRawIndex == fileDescription.TitleRawIndex) { rawValueArray[fieldIndex] = fieldMapperItem.Value; } //真正的数据从标题行下一行开始写 else { propertyInfo = indexPropertyDic[fieldIndex]; object propertyValue = JObject.FromObject(t).SelectToken(fieldMapperItem.Key); string formatValue = null; if (propertyValue != null) { formatValue = propertyValue.ToString(); //如果属性值含有分隔符，则使用双引号包裹 if (formatValue.Contains(fileDescription.SeparatorChar.ToString())) { if (formatValue.Contains("\"")) { formatValue = formatValue.Replace("\"", "\"\""); } formatValue = $"\"{formatValue}\""; } rawValueArray[fieldIndex] = formatValue; } } } rawValue = string.Join(fileDescription.SeparatorChar, rawValueArray); } rawValueBuilder.Append(rawValue + "\r\n"); } catch (Exception e) { this._Logger.LogWarning(e, $"(异常)Excel第{currentRawIndex + 1}行，数据列表第{tIndex + 1}个数据写入失败！rawValue：{rawValue}"); throw; } currentRawIndex++; } while (tIndex < dataCount); streamWriter.Write(rawValueBuilder.ToString()); streamWriter.Close(); streamWriter.Dispose(); } } } /// <summary> /// 根据属性名顺序获取对应的属性对象 /// </summary> /// <param name="fieldNameList"></param> /// <returns></returns> private Dictionary<int, JProperty> GetIndexPropertyDicFromJObject<T>(T t, List<string> fieldNameList) { Dictionary<int, JProperty> indexPropertyDic = new Dictionary<int, JProperty>(fieldNameList.Count); JObject jObj = JObject.FromObject(t); List<JProperty> tPropertyInfoList = jObj.Properties().ToList(); JProperty propertyInfo = null; for (int i = 0; i < fieldNameList.Count; i++) { propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase)); indexPropertyDic.Add(i, propertyInfo); } return indexPropertyDic; } }
示例：
使用类：
/// <summary> /// CSV文件数据 /// </summary> public class CSVModel { /// <summary> /// 公司账号 /// </summary> [CsvColumn(Title = "Company Account")] public string CompanyAccount { get; set; } /// <summary> /// 支付账号商家代码 /// </summary> [CsvColumn(Title = "Merchant Account")] public string MerchantAccount { get; set; } }
CsvFileDescription csvFileDescription = new CsvFileDescription(3); List<CSVModel> item1List = new CsvHelper().Read<OrderTransaction.Adyen.CSVModel>("/test.csv", csvFileDescription);
*感谢您的阅读。喜欢的、有用的就请大哥大嫂们高抬贵手“推荐一下”吧！你的精神 支持是博主强大的写作动力。欢迎转载！
*博主的文章是自己平时开发总结的经验，由于博主的水平不高，不足和错误之处在所难免，希望大家能够批评指出。
*我的博客： http://www.cnblogs.com/lxhbky/
*博主的文章是自己平时开发总结的经验，由于博主的水平不高，不足和错误之处在所难免，希望大家能够批评指出。
*我的博客： http://www.cnblogs.com/lxhbky/
浙公网安备 33010602011771号