NET Core导入Excel表格到数据库
1、NuGet包：NPOI
2、利用接口的IFormFile获取Excel表格文件
#region ****** /// <summary> ///****** /// </summary> /// <returns></returns> [HttpPost] public ActionResult<SysStatus> daotru(IFormFile fct) { SysStatus status = new SysStatus(); try { DataTable dt = new DataTable(); PictureMethods Picture = new PictureMethods(); string strMsg = ""; //利用IFormFile里面的OpenReadStream()方法直接读取文件流 dt = PictureMethods.ExcelToDatatable(fct.OpenReadStream(), Path.GetExtension(fct.FileName), out strMsg); if (!string.IsNullOrEmpty(strMsg)) { status.Code = 5; status.Message = strMsg; } else { if (dt.Rows.Count > 0) { if (service.Posdate(dt, department, _userSession.GetCurrentUserId().ToString())) { status.Code = 1; status.Message = "数据导入成功"; } else { status.Code = 2; status.Message = "数据导入失败"; } } else { status.Code = 3; status.Message = "Excel导入表无数据!"; } } } } catch (Exception ex) { status.Code = 4; status.Message = ex.Message; } return status; } #endregion
3、表格中的数据导入数据库中
#region excel表格数据加载数据库表 /// <summary> /// 将Excel单表转为Datatable /// </summary> /// <param name="stream"></param> /// <param name="fileType"></param> /// <param name="strMsg"></param> /// <param name="sheetName"></param> /// <returns></returns> public static DataTable ExcelToDatatable(Stream stream, string fileType, out string strMsg, string sheetName = null) { strMsg = ""; DataTable dt = new DataTable(); ISheet sheet = null; IWorkbook workbook = null; try { #region 判断excel版本 //2007以上版本excel if (fileType == ".xlsx") { workbook = new XSSFWorkbook(stream); } //2007以下版本excel else if (fileType == ".xls") { workbook = new HSSFWorkbook(stream); } else { throw new Exception("传入的不是Excel文件！"); } #endregion if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; for (int i = firstRow.FirstCellNum; i < cellCount; i++) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue.Trim(); switch (cellValue)//改变头部名称为英文 { case "证书类型": cellValue = "CertificateType"; break; case "姓名": cellValue = "CraftsmanName"; break; case "性别": cellValue = "CraftsmanSex"; break; case "学历": cellValue = "CraftsmanEducation"; break; case "手机号": cellValue = "CraftsmanPhone"; break; case "身份证号": cellValue = "IdCard"; break; case "证书编号": cellValue = "CertificateId"; break; case "证书有效期": cellValue = "CertificateTimes"; break; case "户籍地址": cellValue = "CzAddress"; break; case "常住地址": cellValue = "HjAddress"; break; case "所属乡镇": cellValue = "Township"; break; case "证书链接": cellValue = "CertificateUrl"; break; case "头像": cellValue = "CraftsmanUrl"; break; case "身份证正面": cellValue = "IcFrontUrl"; break; case "身份证反面": cellValue = "IcBackUrl"; break; }
//switch中是为了将中文名替换成英文名 if (!string.IsNullOrEmpty(cellValue)) { DataColumn dataColumn = new DataColumn(cellValue); dt.Columns.Add(dataColumn); } } } DataRow dataRow = null; //遍历行 for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++) { IRow row = sheet.GetRow(j); dataRow = dt.NewRow(); if (row == null || row.FirstCellNum < 0) { continue; } //遍历列 for (int i = row.FirstCellNum; i < cellCount; i++) { ICell cellData = row.GetCell(i); if (cellData != null) { //判断是否为数字型，必须加这个判断不然下面的日期判断会异常 if (cellData.CellType == CellType.Numeric) { //判断是否日期类型 if (DateUtil.IsCellDateFormatted(cellData)) { dataRow[i] = cellData.DateCellValue; } else { dataRow[i] = cellData.ToString().Trim(); } } else { dataRow[i] = cellData.ToString().Trim(); } } } dt.Rows.Add(dataRow); } } else { throw new Exception("没有获取到Excel中的数据表！"); } } catch (Exception ex) { strMsg = ex.Message; } return dt; } #endregion
