/// <summary>
/// Read a CSV file into a table
/// </summary>
/// <param name="filename">Filename of CSV file</param>
/// <param name="headerRow">True if the first row contains column names</param>
/// <param name="scanRows">The number of rows to scan for types when building a DataTable (0 to scan the whole file)</param>
/// <returns>System.Data.DataTable object that contains the CSV data</returns>
public static DataTable ReadCSVFile(string filename, bool headerRow, int scanRows)
{
using (CSVReaderHelper reader = new CSVReaderHelper(new FileInfo(filename)))
{
reader.ScanRows = scanRows;
return(reader.CreateDataTable(headerRow));
}
}
/// <summary>
/// 读取到DataTable
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public DataTable ToDataTable(string fileName)
{
if (string.IsNullOrEmpty(fileName))
{
throw new ArgumentNullException(nameof(fileName));
}
if (!File.Exists(fileName))
{
throw new FileNotFoundException("无效的文件路径", fileName);
}
var ext = Path.GetExtension(fileName);
if (string.IsNullOrEmpty(ext) || !".csv".Equals(ext, StringComparison.OrdinalIgnoreCase))
{
throw new NotSupportedException("不支持的文件类型！");
}
var dataTable = new DataTable();
using (var reader = new CSVReaderHelper(new StreamReader(fileName, FileHelper.GetFileEncodeType(fileName))))
{
// 读取并创建表格头部
int rowIndex = 0;
var headRowCells = ReadRowCells(rowIndex, reader.ReadRow());
var usedHeadCells = new List <DataCell>();
foreach (var c in headRowCells)
{
if (!string.IsNullOrWhiteSpace(c.Value) && !dataTable.Columns.Contains(c.Value)) // 重复列就不读取
{
usedHeadCells.Add(c);
var dc = new DataColumn(c.Value);
dataTable.Columns.Add(dc);
}
}
int emptyRowCount = 0;
while (true)
{
rowIndex++;
var row = reader.ReadRow(); // 第二行开始是数据
if (row != null && row.Count > 0)
{
if (!IsEmptyRow(row))
{
emptyRowCount = 0;
var dr = dataTable.NewRow();
foreach (var item in usedHeadCells)
{
if (dataTable.Columns.Count > item.ColumnIndex)
{
dr[item.ColumnIndex] = row[item.ColumnIndex];
}
}
dataTable.Rows.Add(dr);
}
else
{
emptyRowCount++;
if (emptyRowCount >= 3)
{
break;
}
}
}
else
{
break;
}
}
}
return(dataTable);
}
