POI导入excel或zip文件

**前言:**这是本人工作中的一个SpringBoot项目，有关通过excel模板（或者zip里面有excel）导入数据，先分享给大家，后面还会发布POI导出普通excel、模板excel、单个压缩文件含多个excel、单个excel含多个sheet、模板word。为了让大家能够完全看懂，我尽量把每一步代码都写上，没看懂的骚年们可以留言，我看到必回。 首先添加依赖：

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>

1、Controller:

@RestController @RequestMapping("/law/collection/") @Api(value = "模板导入", tags = {"模板导入接口"}) public class ExcelController { @Autowired IExcelService excelService; @ApiOperation(value = "上传一个附件", notes = "通过表单方式上传一个文件") @PostMapping("excel/upload") public ResultBean upload( @ApiParam(name = "file", value = "附件实体类", required = true) MultipartFile file, @ApiParam(name = "itemType", value = "行政案件类型（1:行政检查；2：行政处罚；3：行政强制；4：行政许可；5：行政征收；6：行政征用）", required = true) Integer itemType, HttpServletRequest request, String userCode) throws Exception { return excelService.upload(file, itemType, userCode); } }

2、Service：

public interface IExcelService { /** * 导入六大类数据 * * @param file * @throws IOException */ ResultBean upload(MultipartFile file, Integer itemType, String userCode) throws Exception; }

3、ExcelServiceImpl:

@Service @Slf4j @Transactional public class ExcelServiceImpl implements IExcelService { //此处自行根据自己的业务添加 @Autowired InsService insService; @Override public ResultBean upload(MultipartFile file, Integer itemType, String userCode) throws Exception { List<String> errorInfos = new ArrayList(); log.info("ContentType>>>>>>>>>>>>>>>>>>>>>>>>>>>" + file.getContentType() + ", OriginalFilename>>>>>>>>>>>>>>>>>>>>>>>>>>>" + file.getOriginalFilename()); String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1); log.info("导入的文件类型为>>>>>>>>>>>>>>>>>>>>>>>>>>>" + fileType.toLowerCase()); if ("zip".equals(fileType.toLowerCase())) { String filePath = attachmentConfig.fileRootPath() + "Formwork" + File.separator + "temp" + File.separator + IdUtil.simpleUUID(); File fileZip = FileUtil.newFile(filePath + File.separator + IdUtil.simpleUUID() + ".zip"); FileUtil.touch(fileZip); file.transferTo(fileZip); File unzipZip = ZipUtil.unzip(fileZip, FileUtil.file(filePath), Charset.forName("GBK")); try { List<File> files = FileUtil.loopFiles(unzipZip); File excelFile = null; Map<String, File> fileMap = new HashMap<>(); for (File tempFile : files) { String[] type = tempFile.getName().split("[.]"); if ("xlsx".equals(type[1].toLowerCase())) { excelFile = tempFile; System.out.println(excelFile.getName()); } else if ("pdf".equals(type[1].toLowerCase())) { fileMap.put(type[0], tempFile); } } ExcelUtil.read07BySax(excelFile, 0, checkRowHandler(errorInfos, itemType)); if (!errorInfos.isEmpty()) { return ResultBean.fail("错误信息", errorInfos); } ExcelUtil.read07BySax(excelFile, 0, insertRowHandler(itemType, userCode, fileMap)); } catch (Exception e) { throw new Exception(e); } finally { System.gc(); FileUtil.del(filePath); } } else if (AttachmentUtil.checkExcel(file)) { ExcelUtil.read07BySax(file.getInputStream(), 0, checkRowHandler(errorInfos, itemType)); if (!errorInfos.isEmpty()) { return ResultBean.fail("错误信息", errorInfos); } ExcelUtil.read07BySax(file.getInputStream(), 0, insertRowHandler(itemType, userCode, new HashMap<>())); } return ResultBean.ok("导入成功！"); } /** * 处理行数据 * * @param errorInfos * @return */ private RowHandler checkRowHandler(List<String> errorInfos, Integer itemType) { return new RowHandler() { @Override public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) { try { if (rowIndex >= 4) {//这里的4是因为模板中的第五行才是填的数据，excel从0行开始算起，后面附上模板，供大家参照 if (checkListNotEmpty(rowlist)) { switch (itemType) { // 行政检查 case 1: insErrorHandler(errorInfos, rowIndex, rowlist); break; case 2: punishErrorHandler(errorInfos, rowIndex, rowlist); break; case 3: enforceErrorHandler(errorInfos, rowIndex, rowlist); break; case 4: allowErrorHandler(errorInfos, rowIndex, rowlist); break; case 5: levyErrorHandler(errorInfos, rowIndex, rowlist); break; case 6: requisitionErrorHandler(errorInfos, rowIndex, rowlist); break; } } } } catch (Exception e) { log.error("异常", e); throw new RuntimeException(e); } } }; } //这里只写上其中一类的导入示例，先根据自己业务判断excel值的有效性（不必太在意里面的实现，只要知道这步是校验数据有效性即可） /** * 1行政检查错误信息 * * @param rowIndex * @param rowlist * @return */ private List<String> insErrorHandler(List<String> errorInfos, int rowIndex, List<Object> rowlist) { // 公共错误信息 publicErrorHandler(errorInfos, rowIndex, rowlist); // 职权来源 if (StringUtils.isNotEmpty(toString(rowlist.get(17)))) { AuthoritySrc authoritySrc = EnumUtil.descOf(AuthoritySrc.class, toString(rowlist.get(17))); if (authoritySrc == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,职权来源类型有误！"); } if ("依委托".equals(rowlist.get(17))) { if (StringUtils.isEmpty(toString(rowlist.get(18)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,委托单位为空！"); } else if (getDept(toString(rowlist.get(18))) == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,委托单位填写错误！"); } } } else { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,职权来源为空！"); } if (StringUtils.isEmpty(toString(rowlist.get(12)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查类别为空！"); }else { CheckCat checkCat = EnumUtil.descOf(CheckCat.class, toString(rowlist.get(12))); if (checkCat == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查类别类型有误！"); } } if (StringUtils.isEmpty(toString(Convert.toDate(rowlist.get(13))))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查日期为空！"); } else { //String format = DateUtil.format (DateUtil.parseDate (Convert.toStr (rowlist.get (13))), "yyyy/MM/dd"); DateTime dateTime = DateUtil.parseDate(Convert.toStr(rowlist.get(13))); String format = DateUtil.format(dateTime, "yyyy/MM/dd"); String checkYear = DateUtil.format(dateTime, "yyyy"); if (checkYear.equals("1900") || !checkDate(format)) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查日期格式有误（正确示例：2019/01/01）！"); } } if (StringUtils.isEmpty(toString(rowlist.get(14)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查内容为空！"); } if (StringUtils.isEmpty(toString(rowlist.get(15)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查结果为空！"); } if (StringUtils.isEmpty(toString(rowlist.get(16)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查决定为空！"); }else { CheckDecision checkDecision = EnumUtil.descOf(CheckDecision.class, toString(rowlist.get(16))); if (checkDecision == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,检查决定类型有误！"); } } return errorInfos; } //校验完成通过后，就是具体的插入数据的操作了 /** * 1行政检查插入数据库 * * @param rowlist */ @SuppressWarnings("Duplicates") private void insValueHandler(List<Object> rowlist, Integer itemType, String userCode, Map<String, File> fileMap) throws Exception { InsModel insModel = new InsModel(); insModel.setUserCode(userCode); // 行政相对人名称、类别 insModel.setAdmCounterName(toString(rowlist.get(1))); insModel.setAdmCounterCategory(EnumUtil.descOf(AdmCounterCategory.class, toString(rowlist.get(2)))); // 自然人 if (AdmCounterCategory.NATURAL_PERSON.getCode().equals(insModel.getAdmCounterCategory().getCode())) { insModel.setNaturalPersonModel(setNature(rowlist, itemType)); } else { insModel.setLegalPersonModel(setLegal(rowlist, itemType)); } // 案件名称、决定书(通知书)名称、决定书(通知书)文号 insModel.setCaseName(toString(rowlist.get(9))); insModel.setDecisionName(toString(rowlist.get(10))); insModel.setDecisionNum(toString(rowlist.get(11))); // 职权来源 insModel.setAuthoritySrc(EnumUtil.descOf(AuthoritySrc.class, toString(rowlist.get(17)))); if (AuthoritySrc.ENTRUSTED.getCode().equals(insModel.getAuthoritySrc().getCode())) { DeptMainModel dept = getDept(toString(rowlist.get(18))); insModel.setEntrustDept(dept.getId()); } // 检查类型、日期 insModel.setCheckCat(EnumUtil.descOf(CheckCat.class, toString(rowlist.get(12)))); insModel.setCheckDate(Convert.toDate(rowlist.get(13))); // 检查内容、结果、决定 insModel.setCheckContent(toString(rowlist.get(14))); insModel.setCheckResult(toString(rowlist.get(15))); insModel.setCheckDecision(EnumUtil.descOf(CheckDecision.class, toString(rowlist.get(16)))); insModel.setStatus(EnumUtil.codeOf(Status.class, "1")); insModel.setRemark(toString(rowlist.get(19))); ResultBean<String> insert = insService.insert(insModel);//这里就是插入数据库的操作，具体代码就不写上了 if (ResultCode.BADREQUEST.equals(insert.getStatus())) { log.error("异常", insModel.getCaseName() + insert.getDesc()); throw new RuntimeException(insModel.getCaseName() + insert.getDesc()); } //插入决定书附件 String fileName = toString(rowlist.get(20)); String apprAdmFileId = null; if (StringUtils.isNotEmpty(fileName)) { File pdfFile = fileMap.get(fileName); apprAdmFileId = saveFile(pdfFile, userCode, "1", insModel.getId()); } excelDao.update("law_collection.appr_adm_ins", insModel.getId(), apprAdmFileId); //插入操作痕迹 markService.insertMark(Mark.init(userCode, MarkTypeEnum.IMPORT, insModel.getId())); } //下面的是使用到的私有方法（不必在意具体实现，根据各自实际业务而定） /** * 公共错误信息 * * @param rowIndex * @param rowlist * @return */ private List<String> publicErrorHandler(List<String> errorInfos, int rowIndex, List<Object> rowlist) { if (StringUtils.isEmpty(toString(rowlist.get(1)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,行政相对人名称为空！"); } // 行政相对人类别 AdmCounterCategory admCounterCategory = EnumUtil.descOf(AdmCounterCategory.class, toString(rowlist.get(2))); if (admCounterCategory == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,行政相对人类别有误！"); }else { if (AdmCounterCategory.ORGANIZATION.getCode().equals(admCounterCategory.getCode()) || AdmCounterCategory.BUSINESSMEN.getCode().equals(admCounterCategory.getCode())) { if (StringUtils.isEmpty(toString(rowlist.get(4)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,法定代表人为空！"); } if (StringUtils.isNotEmpty(toString(rowlist.get(5)))) { LegalCardType legalCardType = EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(5))); if (legalCardType == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,法定代表人证件类型有误！"); } } }else { if (StringUtils.isNotEmpty(toString(rowlist.get(7)))) { LegalCardType legalCardType = EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(7))); if (legalCardType == null) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,自然人证件类型有误！"); } } } } if (StringUtils.isEmpty(toString(rowlist.get(9)))) { errorInfos.add("第" + StrUtil.toString(rowIndex + 1) + "行（错误码：" + RandomUtil.randomNumbers(4) + "）,案件名称为空！"); } return errorInfos; } private Boolean checkListNotEmpty(List<Object> list) { boolean flag = false; for (int i = 1; i < list.size(); i++) { if (StrUtil.isNotEmpty(Convert.toStr(list.get(i)))) { flag = true; break; } } return flag; } /** * 字符串判断 * * @param obj * @return */ private static String toString(Object obj) { return null == obj ? "" : obj.toString(); } /** * 获取委托单位数据 * * @param unitFullName */ private DeptMainModel getDept(String unitFullName) { return deptMainDao.get(unitFullName); } /** * 自然人设置 * * @param rowlist * @param itemType * @return */ private NaturalPersonModel setNature(List<Object> rowlist, Integer itemType) { NaturalPersonModel naturalPersonModel = new NaturalPersonModel(); // 自然人证件号码、证件类型 naturalPersonModel.setCardNumber(toString(rowlist.get(8))); naturalPersonModel.setCardType(EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(7)))); // 行政案件类型 naturalPersonModel.setAdmCaseType(EnumUtil.codeOf(AdmCaseType.class, toString(itemType))); return naturalPersonModel; } /** * 法人及其他人、个体工商户的设置 * * @param rowlist * @param itemType * @return */ private LegalPersonModel setLegal(List<Object> rowlist, Integer itemType) { LegalPersonModel legalPersonModel = new LegalPersonModel(); // 统一社会信用代码 legalPersonModel.setCreditCode(toString(rowlist.get(3))); // 法定代表人、证件类型、证件号码 legalPersonModel.setLegalRep(toString(rowlist.get(4))); legalPersonModel.setLegalCardType(EnumUtil.descOf(LegalCardType.class, toString(rowlist.get(5)))); legalPersonModel.setLegalCardNumber(toString(rowlist.get(6))); // 行政案件类型 legalPersonModel.setAdmCaseType(EnumUtil.codeOf(AdmCaseType.class, toString(itemType))); return legalPersonModel; } public static boolean checkDate(String str) { SimpleDateFormat sd = new SimpleDateFormat("yyyy/MM/dd");//括号内为日期格式，y代表年份，M代表年份中的月份（为避免与小时中的分钟数m冲突，此处用M），d代表月份中的天数 try { sd.setLenient(false);//此处指定日期/时间解析是否不严格，在true是不严格，false时为严格 sd.parse(str);//从给定字符串的开始解析文本，以生成一个日期 } catch (Exception e) { return false; } return true; } }

页面效果： 点击上传即可！ 附上模板zip的结构图（本人暂时没发现可以上传zip文件供大家下载），或者excel，其实zip里面也是含excel，只不过我们的业务中还要有附件pdf，没有附件的可以不管，直接上传excel。 有pdf附件的就放在附件的文件夹里，excel放在数据的文件夹里。 下面展示模板的内容： 一行不够展示，分两张截取图，后面的： 到这里就完成了POI导入excel或zip文件，最后提醒小伙伴们自己测试时要根据自己项目的实际业务出发，避免卡在我的某一处业务代码上。好了，有问题记得留言哦。