ASP.Net MVC利用NPOI导入导出Excel
因近期项目遇到所以记录一下：
1、导出Excel
首先引用NPOI包 （NPOI自行搜索下载）
（Action一定要用FileResult）
1 [Localization] 2 /// <summary> 3 /// 导出Table的数据 4 /// </summary> 5 /// <returns></returns> 6 public FileResult ExportExcel() 7 { 8 //string schoolname = "401"; 9 //创建Excel文件的对象 10 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 11 //添加一个sheet 12 NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); 13 //获取list数据 14 //DataTable dt = new DataTable(); 15 //给sheet1添加第一行的头部标题 16 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); 17 row1.CreateCell(0).SetCellValue(Resources.Common.ApplicationID); 18 row1.CreateCell(1).SetCellValue(Resources.Common.Order); 19 row1.CreateCell(2).SetCellValue(Resources.Common.Operation); 20 row1.CreateCell(3).SetCellValue(Resources.Common.Plant); 21 row1.CreateCell(4).SetCellValue(Resources.Common.WorkCenter); 22 row1.CreateCell(5).SetCellValue(Resources.Common.Quantity); 23 row1.CreateCell(6).SetCellValue(Resources.Common.WorkHour); 24 row1.CreateCell(7).SetCellValue(Resources.Common.Reason); 25 row1.CreateCell(8).SetCellValue("ECO#"); 26 row1.CreateCell(9).SetCellValue(Resources.Common.Remark); 27 row1.CreateCell(10).SetCellValue(Resources.Common.Applicant); 28 row1.CreateCell(11).SetCellValue(Resources.Common.ApplicationStatus); 29 row1.CreateCell(12).SetCellValue(Resources.Common.ApplicationRemark); 30 31 //将数据逐步写入sheet1各个行 32 for (int i = 0; i < dt_ex.Rows.Count; i++) 33 { 34 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); 35 rowtemp.CreateCell(0).SetCellValue(dt_ex.Rows[i]["ApplicationID"].ToString()); 36 rowtemp.CreateCell(1).SetCellValue(dt_ex.Rows[i]["OrderNo"].ToString()); 37 rowtemp.CreateCell(2).SetCellValue(dt_ex.Rows[i]["OperationNo"].ToString()); 38 rowtemp.CreateCell(3).SetCellValue(dt_ex.Rows[i]["Plant"].ToString()); 39 rowtemp.CreateCell(4).SetCellValue(dt_ex.Rows[i]["WorkCenter"].ToString()); 40 rowtemp.CreateCell(5).SetCellValue(dt_ex.Rows[i]["Finished_Qty"].ToString()); 41 rowtemp.CreateCell(6).SetCellValue(dt_ex.Rows[i]["WorkHour"].ToString()); 42 rowtemp.CreateCell(7).SetCellValue(dt_ex.Rows[i]["ReasonDescription"].ToString()); 43 rowtemp.CreateCell(8).SetCellValue(dt_ex.Rows[i]["ECO_No"].ToString()); 44 rowtemp.CreateCell(9).SetCellValue(dt_ex.Rows[i]["Remark"].ToString()); 45 rowtemp.CreateCell(10).SetCellValue(dt_ex.Rows[i]["name"].ToString()); 46 rowtemp.CreateCell(11).SetCellValue(dt_ex.Rows[i]["Description"].ToString()); 47 rowtemp.CreateCell(12).SetCellValue(dt_ex.Rows[i]["ApplicationRemark"].ToString()); 48 } 49 // 写入到客户端 50 System.IO.MemoryStream ms = new System.IO.MemoryStream(); 51 book.Write(ms); 52 ms.Seek(0, SeekOrigin.Begin); 53 return File(ms, "application/vnd.ms-excel", "Application.xls"); 54 }
前台直接写就可实现：
1 <div class="row"> 2 <div class="form-group col-sm-offset-1"> 3 <a class="btn btn-link" href="@Url.Action("ExportExcel","Search" )">@Resources.Common.Download</a> 4 </div> 5 </div>
2、导入Excel
首先说一些前台吧，mvc上传注意必须加 new { enctype = "multipart/form-data" }：
1 <td> 2 @using(@Html.BeginForm("ImportStu", "ProSchool", FormMethod.Post, new { enctype = "multipart/form-data" })) 3 { 4 <text>选择上传文件：（工作表名为“Sheet1”，“电脑号”在A1单元格。）</text> 5 <input name="file" type="file" id="file" /> 6 <input type="submit" name="Upload" value="批量导入第一批电脑派位名册" /> 7 } 8 </td>
后台实现：只传路径得出DataTable：
1 /// <summary> 2 /// Excel导入 3 /// </summary> 4 /// <param name="filePath"></param> 5 /// <returns></returns> 6 public DataTable ImportExcelFile(string filePath) 7 { 8 HSSFWorkbook hssfworkbook; 9 #region//初始化信息 10 try 11 { 12 using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 13 { 14 hssfworkbook = new HSSFWorkbook(file); 15 } 16 } 17 catch (Exception e) 18 { 19 throw e; 20 } 21 #endregion 22 23 using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0)) 24 { 25 DataTable table = new DataTable(); 26 IRow headerRow = sheet.GetRow(0);//第一行为标题行 27 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells 28 int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 29 30 //handling header. 31 for (int i = headerRow.FirstCellNum; i < cellCount; i++) 32 { 33 DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); 34 table.Columns.Add(column); 35 } 36 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) 37 { 38 IRow row = sheet.GetRow(i); 39 DataRow dataRow = table.NewRow(); 40 41 if (row != null) 42 { 43 for (int j = row.FirstCellNum; j < cellCount; j++) 44 { 45 if (row.GetCell(j) != null) 46 dataRow[j] = GetCellValue(row.GetCell(j)); 47 } 48 } 49 50 table.Rows.Add(dataRow); 51 } 52 return table; 53 } 54 55 }
3、补充一个类
1 /// <summary> 2 /// 根据Excel列类型获取列的值 3 /// </summary> 4 /// <param name="cell">Excel列</param> 5 /// <returns></returns> 6 private static string GetCellValue(ICell cell) 7 { 8 if (cell == null) 9 return string.Empty; 10 switch (cell.CellType) 11 { 12 case CellType.BLANK: 13 return string.Empty; 14 case CellType.BOOLEAN: 15 return cell.BooleanCellValue.ToString(); 16 case CellType.ERROR: 17 return cell.ErrorCellValue.ToString(); 18 case CellType.NUMERIC: 19 case CellType.Unknown: 20 default: 21 return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number 22 case CellType.STRING: 23 return cell.StringCellValue; 24 case CellType.FORMULA: 25 try 26 { 27 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); 28 e.EvaluateInCell(cell); 29 return cell.ToString(); 30 } 31 catch 32 { 33 return cell.NumericCellValue.ToString(); 34 } 35 } 36 }
得到DataTable后，就想怎么操作就怎么操作了
