/// <summary>
/// 获取导入的EXCEL数据
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable GetImportData(string path)
{
this.InitializeWorkbook(path);
var sheet = hssfworkbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var table = new DataTable();
DataRow tableRow = null;
while (rows.MoveNext())
{
var row = (HSSFRow)rows.Current;
if (row.RowNum != 0)//数据行
{
tableRow = table.NewRow();
table.Rows.Add(tableRow);
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
var cellValue = cell.ExtToString().Trim();
if (row.RowNum == 0)
{ //标题（列表）
if (!table.Columns.Contains(cellValue) && !string.IsNullOrWhiteSpace(cellValue))
{
table.Columns.Add(cellValue);
}
}
else if (cell.ColumnIndex < table.Columns.Count)
{ //数据行
tableRow[cell.ColumnIndex] = cellValue;
}
}
}
return(table);
}
/// <summary>
/// 获取导入的EXCEL数据
/// </summary>
/// <param name="path">路径</param>
/// <param name="itemList"></param>
/// <returns></returns>
public DataTable GetImportData(string path, List <ColumnValidItem> itemList)
{
this.InitializeWorkbook(path);
var sheet = hssfworkbook.GetSheetAt(0);
var rows = sheet.GetRowEnumerator();
var table = new DataTable();
DataRow tableRow = null;
ColumnValidItem validItem = null;
while (rows.MoveNext())
{
var row = (HSSFRow)rows.Current;
if (row.RowNum != 0)//数据行
{
tableRow = table.NewRow();
table.Rows.Add(tableRow);
}
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
var cellValue = cell.ExtToString().Trim();
if (row.RowNum == 0)//标题（列表）
{
if (!table.Columns.Contains(cellValue) && !string.IsNullOrEmpty(cellValue))
{
table.Columns.Add(cellValue);
}
}
else if (cell.ColumnIndex < table.Columns.Count)//数据行
{
validItem = itemList.FirstOrDefault(v => v.Name == table.Columns[cell.ColumnIndex].ColumnName);
if (validItem != null)
{
try
{
switch (validItem.ValueType)
{
case EnumValueType.String:
tableRow[cell.ColumnIndex] = cell.StringCellValue.Trim();
break;
case EnumValueType.DateTime:
DateTime currDate = DateTime.MinValue;
if (cell.ToString().Contains("-") || cell.ToString().Contains("/"))
{
currDate = cell.DateCellValue;
}
tableRow[cell.ColumnIndex] = currDate.ToShortDateString();
break;
default:
tableRow[cell.ColumnIndex] = cellValue;
break;
}
}
catch
{
tableRow[cell.ColumnIndex] = cellValue;
}
}
}
}
}
return(table);
}