.Net core 使用NPOI 直接导入Excel到数据库（即不先将Excel保存到服务器再读取文件到数据库）
1 /// <summary> 2 /// 导入信息 3 /// </summary> 4 /// <param name="file"></param> 5 /// <returns></returns> 6 /// /Public/PublicPool/ImportCustomer 7 public ResultData ImportCustomer(IFormFile file) 8 { 9 ResultData resultData = new ResultData(); 10 if (file.Length > 0) 11 { 12 DataTable dt = new DataTable(); 13 string strMsg; 14 //利用IFormFile里面的OpenReadStream()方法直接读取文件流 15 dt = ExcelHelper.ExcelToDatatable(file.OpenReadStream(), Path.GetExtension(file.FileName), out strMsg); 16 if (!string.IsNullOrEmpty(strMsg)) 17 { 18 resultData.Code = -1; 19 resultData.Msg = strMsg; 20 return resultData; 21 } 22 if (dt.Rows.Count > 0) 23 { 24 } 25 else 26 { 27 resultData.Code = -1; 28 resultData.Msg = "Excel导入表无数据!"; 29 } 30 return resultData; 31 } 32 33 using NPOI.HSSF.UserModel; 34 using NPOI.SS.UserModel; 35 using NPOI.XSSF.UserModel; 36 using System; 37 using System.Collections.Generic; 38 using System.Data; 39 using System.IO; 40 using System.Text; 41 42 namespace CRM.Common 43 { 44 public static class ExcelHelper 45 { 46 /// <summary> 47 /// 将Excel单表转为Datatable 48 /// </summary> 49 /// <param name="stream"></param> 50 /// <param name="fileType"></param> 51 /// <param name="strMsg"></param> 52 /// <param name="sheetName"></param> 53 /// <returns></returns> 54 public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null) 55 { 56 strMsg = ""; 57 DataTable dt = new DataTable(); 58 ISheet sheet = null; 59 IWorkbook workbook = null; 60 try 61 { 62 #region 判断excel版本 63 //2007以上版本excel 64 if (fileType == ".xlsx") 65 { 66 workbook = new XSSFWorkbook(stream); 67 } 68 //2007以下版本excel 69 else if (fileType == ".xls") 70 { 71 workbook = new HSSFWorkbook(stream); 72 } 73 else 74 { 75 throw new Exception("传入的不是Excel文件！"); 76 } 77 #endregion 78 if (!string.IsNullOrEmpty(sheetName)) 79 { 80 sheet = workbook.GetSheet(sheetName); 81 if (sheet == null) 82 { 83 sheet = workbook.GetSheetAt(0); 84 } 85 } 86 else 87 { 88 sheet = workbook.GetSheetAt(0); 89 } 90 if (sheet != null) 91 { 92 IRow firstRow = sheet.GetRow(0); 93 int cellCount = firstRow.LastCellNum; 94 for (int i = firstRow.FirstCellNum; i < cellCount; i++) 95 { 96 ICell cell = firstRow.GetCell(i); 97 if (cell != null) 98 { 99 string cellValue = cell.StringCellValue.Trim(); 100 if (!string.IsNullOrEmpty(cellValue)) 101 { 102 DataColumn dataColumn = new DataColumn(cellValue); 103 dt.Columns.Add(dataColumn); 104 } 105 } 106 } 107 DataRow dataRow = null; 108 //遍历行 109 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++) 110 { 111 IRow row = sheet.GetRow(j); 112 dataRow = dt.NewRow(); 113 if (row == null || row.FirstCellNum < 0) 114 { 115 continue; 116 } 117 //遍历列 118 for (int i = row.FirstCellNum; i < cellCount; i++) 119 { 120 ICell cellData = row.GetCell(i); 121 if (cellData != null) 122 { 123 //判断是否为数字型，必须加这个判断不然下面的日期判断会异常 124 if (cellData.CellType == CellType.Numeric) 125 { 126 //判断是否日期类型 127 if (DateUtil.IsCellDateFormatted(cellData)) 128 { 129 dataRow[i] = cellData.DateCellValue; 130 } 131 else 132 { 133 dataRow[i] = cellData.ToString().Trim(); 134 } 135 } 136 else 137 { 138 dataRow[i] = cellData.ToString().Trim(); 139 } 140 } 141 } 142 dt.Rows.Add(dataRow); 143 } 144 } 145 else 146 { 147 throw new Exception("没有获取到Excel中的数据表！"); 148 } 149 } 150 catch (Exception ex) 151 { 152 strMsg = ex.Message; 153 } 154 return dt; 155 } 156 } 157 }
网上找了好多都没有直接保存导数据库的方法，自己研究了IFormFile类后尝试了一下没想到意外的成功了~~~~~~~~~~
