1.NuGet获取NPOI
2.创建MVC项目
3.前端实现(form的提交)
<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>
3.ExcelToDataTable
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; } }
其实也可以自己定义一个类去接收数据，通过LINQ to Excel的方式
public string Upload(HttpPostedFileBase fileUpload) { if (fileUpload == null) { return "文件为空"; } try { //将硬盘路径转化为服务器路径的文件流 string fileName = Path.Combine(Request.MapPath("~/Excel"), Path.GetFileName(fileUpload.FileName)); //NPOI得到EXCEL的第一种方法 fileUpload.SaveAs(fileName); DataTable dtData = ExcelHelper.ExcelToDataTable(fileName); //得到EXCEL的第二种方法 DataTable dtData2 = ExcelHelper.RenderDataTableFromExcel(fileName, fileUpload.FileName, 0); return "导入成功"; } catch { return "导入失败"; } }