1.读取CSV文件
/// <summary>
/// 读取CSV文件
/// </summary>
/// <param name="fileName">文件路径</param>
public static DataTable ReadCSV(string fileName)
{
DataTable dt = new DataTable();
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(fs, Encoding.UTF8, false);
//记录每次读取的一行记录
string strLine = null;
//记录每行记录中的各字段内容
string[] arrayLine = null;
//判断，若是第一次，建立表头
bool isFirst = true;
//列的个数
int dtColumns = 0;
//逐行读取CSV文件
while ((strLine = sr.ReadLine()) != null)
{
strLine = strLine.Trim();//去除头尾空格
arrayLine = strLine.Split(',');//分隔字符串，返回数组
if (isFirst) //建立表头
{
dtColumns = arrayLine.Length;//列的个数
for (int i = 0; i < dtColumns; i++)
{
dt.Columns.Add(arrayLine[i]);//每一列名称
}
isFirst = false;
}
else //表内容
{
DataRow dataRow = dt.NewRow();//新建一行
for (int j = 0; j < dtColumns; j++)
{
if (arrayLine.Length > j)
{
dataRow[j] = arrayLine[j];
}
}
dt.Rows.Add(dataRow);//添加一行
}
}
sr.Close();
fs.Close();
return dt;
}
2.Excel导入成DataTble
/// <summary>
/// Excel导入成DataTble
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
if (fileExt == ".xlsx") {
workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") {
workbook = new HSSFWorkbook(fs); } else {
workbook = null; }
if (workbook == null) {
return null; }
ISheet sheet = workbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell">目标单元格</param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank:
return null;
case CellType.Boolean:
return cell.BooleanCellValue;
case CellType.Numeric:
return cell.NumericCellValue;
case CellType.String:
return cell.StringCellValue;
case CellType.Error:
return cell.ErrorCellValue;
case CellType.Formula:
default:
return "=" + cell.CellFormula;
}
}
