一、下载引用
目前官网不能直接下载到引用的dll，需要自己打包（我没有自己打包，我有现成的DLL，地址：https://files.cnblogs.com/files/dengxixi/NPOIdll.7z），即：NPOI.dll，NPOI.OOXML.dll，NPOI.OpenXml4Net.dll，ICSharpCode.SharpZipLib.dll。（解压密码是：123456）
二、创建MVC项目，页面代码：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>使用NPOI导入excel</title>
<script src="Scripts/jquery-1.8.2.min.js"></script>
<style>
.myFileUpload {
position: absolute;
display: block;
100px;
height: 40px;
opacity: 0;
}
</style>
</head>
<body>
<div class="container">
<form class="form-horizontal" action="~/Home/Upload" role="form" method="post" enctype="multipart/form-data">
<table style="margin:5px;height:70px;">
<tr>
<td>请选择文件：</td>
<td width="5px;"></td>
<td><input type="file" id="fileUpload" name="fileUpload"></td>
<td><input id="fileText" type="text" class="myFileUpload" disabled="disabled" /></td>
<td><button type="submit">上传</button></td>
</tr>
</table>
</form>
</div>
</body>
</html>
三、EXCEL转为为datatable类，网上百度的，改了下，因为没有判断excel的版本
public class ExcelHelper { /// <summary>读取excel 到datatable /// 默认第一行为表头，导入第一个工作表 /// </summary> /// <param name="strFileName">excel文档路径</param> /// <returns></returns> public static DataTable ExcelToDataTable(string strFileName) { DataTable dt = new DataTable(); FileStream file = null; IWorkbook Workbook = null; try { using (file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))//C#文件流读取文件 { if (strFileName.IndexOf(".xlsx") > 0) //把xlsx文件中的数据写入Workbook中 Workbook = new XSSFWorkbook(file); else if (strFileName.IndexOf(".xls") > 0) //把xls文件中的数据写入Workbook中 Workbook = new HSSFWorkbook(file); if (Workbook != null) { ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); //得到Excel工作表的行 IRow headerRow = sheet.GetRow(0); //得到Excel工作表的总列数 int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { //得到Excel工作表指定行的单元格 ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } } return dt; } } catch (Exception) { if (file != null) { file.Close();//关闭当前流并释放资源 } return null; } } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="strFileName">Excel文件全路径(服务器路径)</param> /// <param name="SheetName">要获取数据的工作表名称</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex) { IWorkbook Workbook = null; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { if (strFileName.IndexOf(".xlsx") > 0) Workbook = new XSSFWorkbook(file); else if (strFileName.IndexOf(".xls") > 0) Workbook = new HSSFWorkbook(file); ISheet sheet = Workbook.GetSheet(SheetName); return RenderDataTableFromExcel(Workbook, SheetName, HeaderRowIndex); } } /// <summary> /// 从Excel中获取数据到DataTable /// </summary> /// <param name="workbook">要处理的工作薄</param> /// <param name="SheetName">要获取数据的工作表名称</param> /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex) { ISheet sheet = workbook.GetSheet(SheetName); DataTable table = new DataTable(); try { IRow headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; #region 循环各行各列,写入数据到DataTable for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); if (cell == null) { dataRow[j] = null; } else { //dataRow[j] = cell.ToString(); switch (cell.CellType) { case CellType.Blank: dataRow[j] = null; break; case CellType.Boolean: dataRow[j] = cell.BooleanCellValue; break; case CellType.Numeric: dataRow[j] = cell.ToString(); break; case CellType.String: dataRow[j] = cell.StringCellValue; break; case CellType.Error: dataRow[j] = cell.ErrorCellValue; break; case CellType.Formula: default: dataRow[j] = "=" + cell.CellFormula; break; } } } table.Rows.Add(dataRow); //dataRow[j] = row.GetCell(j).ToString(); } #endregion } catch (System.Exception ex) { table.Clear(); table.Columns.Clear(); table.Columns.Add("出错了"); DataRow dr = table.NewRow(); dr[0] = ex.Message; table.Rows.Add(dr); return table; } finally { //sheet.Dispose(); workbook = null; sheet = null; } #region 清除最后的空行 for (int i = table.Rows.Count - 1; i > 0; i--) { bool isnull = true; for (int j = 0; j < table.Columns.Count; j++) { if (table.Rows[i][j] != null) { if (table.Rows[i][j].ToString() != "") { isnull = false; break; } } } if (isnull) { table.Rows[i].Delete(); } } #endregion return table; } }
四、调用方法
public string Upload(HttpPostedFileBase fileUpload) { if (fileUpload == null) { return "文件为空"; } try { //将硬盘路径转化为服务器路径的文件流 string fileName = Path.Combine(Request.MapPath("~/SaveFile"), Path.GetFileName(fileUpload.FileName)); //NPOI得到EXCEL的第一种方法 fileUpload.SaveAs(fileName); DataTable dtData = ExcelHelper.ExcelToDataTable(fileName); //得到EXCEL的第二种方法(第一个参数是文件流,第二个是excel标签名,第三个是第几行开始读0算第一行) DataTable dtData2 = ExcelHelper.RenderDataTableFromExcel(fileName, fileUpload.FileName, 0); return "导入成功"; } catch { return "导入失败"; } }
五、调试截图及excel 数据截图：
以上就是把excel转化为datatable的方法。转化为datatable之后就可以进行插入数据库或者显示到页面了。