/// <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);
}
