第二种:利用 Npoi 插件,只需要在项目里引用DLL即可,无需在服务器安装任何插件。
/// <summary>
/// 根据路径读取Excel放入DataTable里
/// </summary>
/// <param name="path"></param>
/// <param name="error"></param>
/// <returns></returns>
public DataTable ExcelToDS(string path,out string error)
{
if (System.IO.Path.GetExtension(path) == ".xls")
{
return Excel2003(path,out error);
}
else
{
return Excel2007(path,out error);
}
}
#region 导入03
private DataTable Excel2003(string path, out string error)
{
error = "";
DataTable dt = new DataTable();
try
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
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] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
if (j == 26 || j == 27 && !string.IsNullOrEmpty(dr[2].ToString()))//开始时间,结束时间
{
if (!IsDate(dr[j].ToString()))
{
error = "时间格式不正确,请输入正确的时间格式“YYYY/MM/DD”";
}
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
error = ex.Message;
}
return dt;
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region 导入07
private DataTable Excel2007(string path, out string error)
{
error = "";
DataTable dt = new DataTable();
try
{
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
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] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
if ((j == 26 || j == 27) && !string.IsNullOrEmpty(dr[2].ToString()))//开始时间,结束时间
{
if (!IsDate(dr[j].ToString()))
{
error = "时间格式不正确,请输入正确的时间格式“YYYY/MM/DD”";
}
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
error = ex.Message;
}
return dt;
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
/// <summary>
/// 判断输入是否为日期类型
/// </summary>
/// <param name="s">待检查数据</param>
/// <returns></returns>
public static bool IsDate(string s)
{
if (s == null)
{
return false;
}
else
{
try
{
DateTime d = DateTime.Parse(s);
return true;
}
catch
{
return false;
}
}
}