1,添加NOPI的引用
2,添加命名空间
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
1/// <summary> 2 /// 导入 3 /// </summary> 4 /// <returns></returns> 5 public ActionResult ImportExcel() 6 { 7 Dictionary<String, Object> result = new Dictionary<String, Object>(); 8 StringBuilder errorMsg = new StringBuilder(); // 错误信息 9 try 10 { 11 string tempFile = Server.MapPath("~/ExcelFiles/");//路径 12 string repath = Request["repath"]; 13 14 //将Excel文件上传到本地服务器 15 HttpPostedFileBase uploadFile = Request.Files[repath];// 获取上传的文件 16 var filePath = string.Format("{0}{1}", tempFile, uploadFile.FileName); // 上传服务器的文件保存路径 17 uploadFile.SaveAs(filePath);//保存文件 18 19 ////Excel文档流转换成DataTable 20 DataTable dt = ExcelToDataTable(filePath, true); 21 22 // 将数据存储到数据库 23 AidMeasureService.ExcelDataSave(dt); 24 25 //将上传的文件删除 26 System.IO.FileInfo nfile = new System.IO.FileInfo(filePath); 27 if (nfile.Exists) 28 { 29 nfile.Delete(); 30 } 31 } 32 catch (Exception ex) 33 { 34 LogHelper.WriteLog("MeasureController.ImportExcel()", ex); 35 result.Add("Success", false); 36 result.Add("ErrorInfo", ex.ToString()); 37 return Json(result); 38 } 39 result.Add("Success", true); 40 return Json(result); 41 } 42 43 /// <summary> 44 /// 将excel导入到datatable 45 /// </summary> 46 /// <param name="filePath">excel路径</param> 47 /// <param name="isColumnName">第一行是否是列名</param> 48 /// <returns>返回datatable</returns> 49 public DataTable ExcelToDataTable(string filePath, bool isColumnName) 50 { 51 DataTable dataTable = null; 52 FileStream fs = null; 53 DataColumn column = null; 54 DataRow dataRow = null; 55 IWorkbook workbook = null; 56 ISheet sheet = null; 57 IRow row = null; 58 ICell cell = null; 59 int startRow = 0; 60 try 61 { 62 using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 63 { 64 // 2007版本 65 if (filePath.IndexOf(".xlsx") > 0) 66 workbook = new XSSFWorkbook(stream); 67 // 2003版本 68 else if (filePath.IndexOf(".xls") > 0) 69 workbook = new HSSFWorkbook(stream); 70 71 if (workbook != null) 72 { 73 sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet 74 dataTable = new DataTable(); 75 if (sheet != null) 76 { 77 int rowCount = sheet.LastRowNum;//总行数 78 if (rowCount > 0) 79 { 80 IRow firstRow = sheet.GetRow(0);//第一行 81 int cellCount = firstRow.LastCellNum;//列数 82 83 //构建datatable的列 84 if (isColumnName) 85 { 86 startRow = 1;//如果第一行是列名，则从第二行开始读取 87 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 88 { 89 cell = firstRow.GetCell(i); 90 if (cell != null) 91 { 92 if (cell.StringCellValue != null) 93 { 94 column = new DataColumn(cell.StringCellValue); 95 dataTable.Columns.Add(column); 96 } 97 } 98 } 99 } 100 else 101 { 102 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) 103 { 104 column = new DataColumn("column" + (i + 1)); 105 dataTable.Columns.Add(column); 106 } 107 } 108 109 //填充行 110 for (int i = startRow; i <= rowCount; ++i) 111 { 112 row = sheet.GetRow(i); 113 if (row == null) continue; 114 115 dataRow = dataTable.NewRow(); 116 for (int j = row.FirstCellNum; j < cellCount; ++j) 117 { 118 cell = row.GetCell(j); 119 if (cell == null) 120 { 121 dataRow[j] = ""; 122 } 123 else 124 { 125 //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) 126 switch (cell.CellType) 127 { 128 case CellType.BLANK: 129 dataRow[j] = ""; 130 break; 131 case CellType.NUMERIC: 132 short format = cell.CellStyle.DataFormat; 133 //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 134 if (format == 14 || format == 31 || format == 57 || format == 58) 135 dataRow[j] = cell.DateCellValue; 136 else 137 dataRow[j] = cell.NumericCellValue; 138 break; 139 case CellType.STRING: 140 dataRow[j] = cell.StringCellValue; 141 break; 142 } 143 } 144 } 145 dataTable.Rows.Add(dataRow); 146 } 147 } 148 } 149 } 150 } 151 return dataTable; 152 } 153 catch (Exception) 154 { 155 if (fs != null) 156 { 157 fs.Close(); 158 } 159 return null; 160 } 161 }