一、下载引用
目前官网不能直接下载到引用的dll，需要自己打包（我没有自己打包，我有现成的DLL，地址：https://files.cnblogs.com/files/dengxixi/NPOIdll.7z），即：NPOI.dll，NPOI.OOXML.dll，NPOI.OpenXml4Net.dll，ICSharpCode.SharpZipLib.dll（office2007版需要此dll）。
二、创建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; ???????????width: 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之后就可以进行插入数据库或者显示到页面了。
ASP.NET MVC使用NPOI读取excel数据
原文地址：https://www.cnblogs.com/dengxixi/p/9036187.html