/// <summary>
/// 将Csv导入到datatable,第一行为列名
/// </summary>
/// <param name="filePath">Csv的流数据</param>
/// <returns>返回datatable</returns>
public static DataTable CsvToDataTable(Stream stream)
{
DataTable dt = new DataTable();
using (StreamReader sr = new StreamReader(stream, Encoding.Default))
{
string rowStr = null;
bool special = false;
bool isFirst = true;
while (true)
{
string line = sr.ReadLine();
if (line == null)
{
break;
}
rowStr += line;
//获取该行双引号的个数,如果是双数或为0,则为完整一行
//如果该行双引号个数为单数,则是非完整行,需要下次出现单数才是完整行
int remainder = (line.Split(new char[] { '"' }, StringSplitOptions.None).Length - 1) % 2;
if (remainder != 0)
{
if (special)
{
special = false;
}
else
{
rowStr += System.Environment.NewLine;
special = true;
continue;
}
}
else
{
if (special)
{
rowStr += System.Environment.NewLine;
continue;
}
}
//解析完整行的csv数据
string[] rowData = SplitCsvRow(rowStr);
rowStr = null;
if (isFirst)
{
isFirst = false;
foreach (string colName in rowData)
{
dt.Columns.Add(colName);
}
}
else
{
if (rowData.Length == dt.Columns.Count)
{
DataRow row = dt.NewRow();
row.ItemArray = rowData;
dt.Rows.Add(row);
}
else
{
throw new Exception("csv文件列格式错误,");
}
}
}
if (special)
{
throw new Exception("csv文件格式错误");
}
}
return dt;
}
/// <summary>
/// 解析完整行的csv数据
/// </summary>
/// <param name="rowText"></param>
/// <returns></returns>
private static string[] SplitCsvRow(string rowText)
{
List<string> temp = new List<string>();
if (rowText.IndexOf('"') < 0)
{
//无特殊字符,直接按逗号拆分
temp.AddRange(rowText.Split(new char[] { ',' }));
}
else
{
var spArr = rowText.Split(new char[] { ',' });
string cellValue = null;
bool special = false;
//有特殊字符
foreach (var spItem in spArr)
{
cellValue += spItem;
int remainder = (spItem.Split(new char[] { '"' }, StringSplitOptions.None).Length - 1) % 2;
if (remainder != 0)
{
if (special)
{
special = false;
}
else
{
cellValue += ',';
special = true;
continue;
}
}
else
{
if (special)
{
cellValue += ',';
continue;
}
}
if (special)
{
throw new Exception("csv文件行格式错误");
}
temp.Add(cellValue);
cellValue = null;
}
}
return temp.ToArray();
}