链接：https://pan.baidu.com/s/1apgo_N71BB01go6IPgXcZQ 

提取码：evtv 

 

SpringBoot整合poi实现Excel文件的导入和导出

1、引入Maven依赖

2、添加ExcelFormatUtil工具类

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16</version> </dependency> import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.xssf.usermodel.XSSFCell; import java.math.BigDecimal; import java.text.SimpleDateFormat; public class ExcelFormatUtil { //单元格数据格式判断 public static String getValue(XSSFCell cell){ if (null != cell) { switch (cell.getCellTypeEnum()) { case NUMERIC: // 数字 if (DateUtil.isCellDateFormatted(cell)){ // 日期 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM- dd"); return sdf.format(cell.getDateCellValue()); }// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计 数值 BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); return bd.toPlainString(); case STRING: // 字符串 return cell.getStringCellValue() + ""; case BOOLEAN: // Boolean return cell.getBooleanCellValue() + ""; case FORMULA: // 公式 return cell.getCellFormula() + ""; case BLANK: // 空值 return "";

3、Excel导入应用案例

case ERROR: // 故障 return ""; default: return "未知类型"; } }else{return ""; } } }//excel导入解析 @PostMapping("uploadList") public BaseRespBo uploadList(@RequestParam("file") MultipartFile file){ BaseRespBo rsp = new BaseRespBo(); //String contentType = file.getContentType(); //String fileName = file.getOriginalFilename(); //logger.info("获取文件类型和文件名" + contentType + fileName); if (file == null) { rsp.setCode(ErrorCodeEnum.FILE_IS_EMPTY.getCode()); rsp.setMsg(ErrorCodeEnum.FILE_IS_EMPTY.getMessage()); return rsp; }try {//根据路径获取这个操作excel的实例 XSSFWorkbook wb = null; try {wb = new XSSFWorkbook(file.getInputStream()); } catch (IOException e) { logger.error("Operation excel error:", e); }//根据页面index 获取sheet页 XSSFSheet sheet = wb.getSheetAt(0); //实体类集合 List<FocusPersonDomain> importData = new ArrayList<>(); XSSFRow row = null; //循环sheet页中数据从第二行开始，第一行是标题 for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) { //获取每一行数据 row = sheet.getRow(i); FocusPersonDomain data = new FocusPersonDomain(); // data.setName(row.getCell(0).getStringCellValue()); // data.setIdCard(row.getCell(1).getStringCellValue()); // data.setSex(row.getCell(2).getStringCellValue()); // data.setStatus(row.getCell(3).getStringCellValue()); // data.setNation(row.getCell(4).getStringCellValue()); // data.setHometown(row.getCell(5).getStringCellValue()); // data.setDistrict(row.getCell(6).getStringCellValue()); // data.setAddress(row.getCell(7).getStringCellValue()); // data.setEmployer(row.getCell(8).getStringCellValue()); // data.setPosition(row.getCell(9).getStringCellValue()); // data.setResidence(row.getCell(10).getStringCellValue()); // data.setAddReason(row.getCell(11).getStringCellValue()); data.setName(ExcelFormatUtil.getValue(row.getCell(0)));

4、Excel导出应用案例

data.setIdCard(ExcelFormatUtil.getValue(row.getCell(1))); data.setSex(ExcelFormatUtil.getValue(row.getCell(2))); data.setStatus(ExcelFormatUtil.getValue(row.getCell(3))); data.setNation(ExcelFormatUtil.getValue(row.getCell(4))); data.setHometown(ExcelFormatUtil.getValue(row.getCell(5))); data.setDistrict(ExcelFormatUtil.getValue(row.getCell(6))); data.setAddress(ExcelFormatUtil.getValue(row.getCell(7))); data.setEmployer(ExcelFormatUtil.getValue(row.getCell(8))); data.setPosition(ExcelFormatUtil.getValue(row.getCell(9))); data.setResidence(ExcelFormatUtil.getValue(row.getCell(10))); data.setAddReason(ExcelFormatUtil.getValue(row.getCell(11))); importData.add(data); }Boolean result = focusPersonService.batchSaveOrUpdateFocusPerson(NoRepeatUtil.removeRepeatByFocus Person(importData)); } catch (Exception e) { rsp.setCode(ErrorCodeEnum.OPERATION_EXCEL_ERROR.getCode()); rsp.setMsg(ErrorCodeEnum.OPERATION_EXCEL_ERROR.getMessage()); return rsp; }return rsp; }