/// <summary> /// 读取CSV文件内容并转为DataTable /// </summary> /// <param name="fileName">完整路径文件名</param> /// <param name="separator">分隔符,默认为标准的英文,</param> /// <param name="firstLineIsHeader">第一行是否为表头,默认为否</param> /// <param name="encoding">编码类型</param> /// <param name="fieldList">字段列表字典(csv字段(无表头用C1,C2,C3,..格式),DataTable字段)</param> /// <param name="fieldListOnly">仅按照字段列表字典导入</param> /// <returns>DataTable自定义列名或以C1-CN开头的列名</returns> public static DataTable ToDataTable(string fileName, string separator = ",", bool firstLineIsHeader = false, Encoding encoding = null, Dictionary <string, string> fieldList = null, bool fieldListOnly = false) { var dt = new DataTable(); var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); var sr = new StreamReader(fs, encoding ?? EncodingUtil.Detect(fs)); //记录每次读取的一行记录 var line = ""; //记录每行记录中的各字段内容 string[] arr; //标示列数 var headColumnCount = 0; var lineColumnCount = 0; //字段是否已经添加 var isColumnAdded = false; //字段和列的对应关系,仅从指定列导入时会用到 var dicFieldIndex = new Dictionary <string, int>(); //逐行读取CSV中的数据 while ((line = sr.ReadLine()) != null) { var spr = separator.ToCharArray(); arr = line.Split(spr); if (firstLineIsHeader) { firstLineIsHeader = false; isColumnAdded = true; headColumnCount = GetLength(arr, separator); #region 生成DataTable数据列 //根据指定列名创建 if (fieldList != null && fieldListOnly) { foreach (var field in fieldList) { var dc = new DataColumn(field.Value); dt.Columns.Add(dc); //映射CSV的字段列索引 for (int i = 0; i < headColumnCount; i++) { if (ConvertColumnName(ReadSpecialCharacter(arr, i, separator), fieldList: fieldList).Equals(field.Value, StringComparison.OrdinalIgnoreCase)) { dicFieldIndex.Add(field.Value, i); } } } } else { //根据第一行实际列数,进行匹配映射来创建 for (int i = 0; i < headColumnCount; i++) { var dc = new DataColumn(ConvertColumnName(ReadSpecialCharacter(arr, i, separator), fieldList: fieldList)); dt.Columns.Add(dc); } } #endregion } else { #region 生成DataTable数据列 if (!isColumnAdded) { isColumnAdded = true; headColumnCount = GetLength(arr, separator); //根据指定列名创建 if (fieldList != null && fieldListOnly) { var fieldListIndex = 0; foreach (var field in fieldList) { var dc = new DataColumn(field.Value); dt.Columns.Add(dc); //按照顺序映射列索引 dicFieldIndex.Add(field.Value, fieldListIndex); fieldListIndex++; } } else { //根据第一行实际列数,进行匹配映射来创建 for (int i = 0; i < headColumnCount; i++) { var dc = new DataColumn(ConvertColumnName("C" + (i + 1), fieldList: fieldList)); dt.Columns.Add(dc); } } } #endregion lineColumnCount = GetLength(arr, separator); // //生成指定列或自动创建列 #region 写数据行 if (fieldList != null && fieldListOnly) { if (lineColumnCount > 0) { var dr = dt.NewRow(); foreach (var d in dicFieldIndex) { for (int j = 0; j < lineColumnCount; j++) { if (j == d.Value) { dr[d.Key] = ReadSpecialCharacter(arr, j, separator); } } } dt.Rows.Add(dr); } } else { //此行的列数要跟表头的列数一致才认为有效 if (lineColumnCount == headColumnCount) { var dr = dt.NewRow(); for (int j = 0; j < lineColumnCount; j++) { dr[j] = ReadSpecialCharacter(arr, j, separator); } dt.Rows.Add(dr); } else { LogUtil.WriteLog("headColumnCount:" + headColumnCount + ",lineColumnCount:" + lineColumnCount + "line:" + line, "CsvUtil.InvalidLine"); } } #endregion } } sr.Close(); fs.Close(); return(dt); }
/// <summary> /// 通过DataTable获得CSV格式数据 /// </summary> /// <param name="dt">数据表</param> /// <param name="fieldList">字段列表字典(字段,描述)</param> /// <param name="separator">分隔符</param> /// <returns>CSV字符串数据</returns> private static StringBuilder GetCsvFormatData(DataTable dt, Dictionary <string, string> fieldList = null, string separator = ",") { var sb = Pool.StringBuilder.Get(); #region 检查字段列表 if (dt != null && fieldList != null) { var keys = fieldList.Keys.ToArray(); for (var i = 0; i < keys.Length; i++) { var hasColumnName = false; foreach (DataColumn dc in dt.Columns) { if (dc.ColumnName.Equals(keys[i], StringComparison.OrdinalIgnoreCase)) { hasColumnName = true; break; } } if (!hasColumnName) { //表内不存在此字段,就不要输出这个列了 fieldList.Remove(keys[i]); } } } #endregion #region 生成dt新表 if (dt != null && fieldList != null && fieldList.Count > 0) { //对DataTable筛选指定字段,并保存为新表 //这些列名,确保DataTable中存在,否则会报错误 var dtNew = dt.DefaultView.ToTable(false, fieldList.Keys.ToArray()); dt = new DataTable(); dt = dtNew.Copy(); } #endregion #region 写出表头 if (dt != null) { foreach (DataColumn dc in dt.Columns) { if (sb.Length > 0 && (fieldList == null || (fieldList != null && fieldList.ContainsKey(dc.ColumnName)))) { sb.Append(separator); } if (fieldList == null || fieldList?.Count == 0) { WriteSpecialCharacter(dc.ColumnName, sb, separator); } else { if (fieldList.ContainsKey(dc.ColumnName)) { WriteSpecialCharacter(dc.ColumnName, sb, separator); } } } if (dt.Rows.Count > 0) { sb.Append("\n"); } } #endregion #region 写出数据 if (dt != null && dt.Rows.Count > 0) { var c = 1; foreach (DataRowView drv in dt.DefaultView) { var i = 1; //var j = 1; try { foreach (DataColumn dc in dt.Columns) { //j++; if (fieldList == null || fieldList?.Count == 0) { WriteSpecialCharacter(drv[dc.ColumnName]?.ToString(), sb, separator); if (i < dt.Columns.Count) { sb.Append(separator); } i++; //LogUtil.WriteLog(j + "," + i + "," + dt.Columns.Count + "," + fieldList?.Count + "," + dc.ColumnName + ":" + drv[dc.ColumnName]?.ToString()); } else { if (fieldList.ContainsKey(dc.ColumnName)) { i++; WriteSpecialCharacter(drv[dc.ColumnName]?.ToString(), sb, separator); if (i < fieldList.Count) { sb.Append(separator); } } //LogUtil.WriteLog(j + "," + i + "," + dt.Columns.Count + "," + fieldList?.Count + ":" + drv[dc.ColumnName]?.ToString()); } } //最后一行不需要输出换行符 if (c < dt.Rows.Count) { sb.Append("\n"); } } catch (Exception ex) { LogUtil.WriteException(ex); continue; } finally { c++; } } } #endregion return(sb); }