Java导入功能实现
今天记录一下Java实现导入数据到数据，导入失败显示如下，会告诉你哪里出错了。
controller代码实现
@Operation(summary = "导入工单") @PostMapping("/importOrderData") public BaseResponse<String> importOrderData(@RequestParam @Parameter(name = "excelFile", description = "属性excel", required = true) MultipartFile excelFile) throws IOException { return workOrderService.importOrderData(excelFile); }
serviceImpl代码实现
@Override @Transactional public BaseResponse<String> importOrderData(MultipartFile multipartFile) throws IOException { BaseResponse baseResponse = checkFile(multipartFile); if (!baseResponse.resultIsOk()) { return baseResponse; } List<WorkOrderVoImportDto> orderVoImports = EasyExcel.read(multipartFile.getInputStream()) .head(WorkOrderVoImportDto.class).sheet(0).doReadSync(); List<WorkOrder> orders = new ArrayList<>(); List<WorkOrderAudit> orderAudits = new ArrayList<>(); List<StringBuffer> failMsgList = checkPointParam(orderVoImports,orders,orderAudits); if (CollectionUtils.isEmpty(failMsgList)) { this.saveBatch(orders); workOrderAuditService.saveBatch(orderAudits); return BaseResponse.ok(); } return BaseResponse.fail(String.join("", failMsgList)); }
checkFile ,主要是看下你的文件是否有问题
private BaseResponse<String> checkFile(MultipartFile multipartFile) throws IOException { String XLSX = ".xlsx", XLS = ".xlsx"; if (ObjectUtils.isEmpty(multipartFile)) { return BaseResponse.fail("文件不能为空"); } String filename = multipartFile.getOriginalFilename(); assert filename != null; if (!filename.endsWith(XLSX) && !filename.endsWith(XLS)) { return BaseResponse.fail("文件格式不对"); } EasyExcel.read(multipartFile.getInputStream(), new CheckWorkOrderListener()).head(WorkOrderVoImportDto.class).sheet(0).doReadSync(); //校验表头是否一致 String errorMsg = BizContextProvider.get("errorMsg"); if (StringUtils.isNotEmpty(errorMsg)) { return BaseResponse.fail(errorMsg); } return BaseResponse.ok(); }
CheckWorkOrderListener
@Component public class CheckWorkOrderListener extends AnalysisEventListener<WorkOrderVoImportDto> { @Override public void invoke(WorkOrderVoImportDto data, AnalysisContext context) { } @Override public void doAfterAllAnalysed(AnalysisContext context) { } /** * 获取表头 * * @param headMap * @param context */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { //单行表头验证方式 List titleList = getExcelTitle(headMap, context); // 遍历字段进行判断 for (Field field : WorkOrderVoImportDto.class.getDeclaredFields()) { // 获取当前字段上的ExcelProperty注解信息 ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class); // 判断当前字段上是否存在ExcelProperty注解 if (fieldAnnotation != null) { // 存在ExcelProperty注解则根据注解的index索引到表头中获取对应的表头名 Object tile = titleList.get(fieldAnnotation.index()); // 判断表头是否为空或是否和当前字段设置的表头名不相同 if ("".equals(tile.toString()) || !tile.toString().equals(fieldAnnotation.value()[0])) { // 如果为空或不相同，则抛出异常不再往下执行 BizContextProvider.put("errorMsg", "模板表头错误，请检查导入模板!"); return; } } } } /** * 获取表头(一行） * * @param headMap * @param context * @return */ public static List getExcelTitle(Map<Integer, String> headMap, AnalysisContext context) { List keyList = new ArrayList<>(); //遍历获取第一行和第二行表头，存入keyList Set<Integer> integerSet = headMap.keySet(); for (int i1 = 0; i1 < integerSet.size(); i1++) { keyList.add(headMap.get(i1)); } return keyList; } }
WorkOrderVoImportDto 这个是导入的对象，就是你Excel里面的对象，其中@ExcelProperty注解很重要，尤其是value(excel表头名称)和index（Excel排序字段）
@Data @AllArgsConstructor @NoArgsConstructor @ContentRowHeight(20) @ColumnWidth(12) @HeadRowHeight(20) public class WorkOrderVoImportDto { /** * 工单编码 */ @ExcelProperty(value = "工单编码", index = 0) private String workOrderCode; /** * 产品编码 */ @ExcelProperty(value = "产品编码", index = 1) private String productCode; /** * 产品名称 */ @ExcelProperty(value = "产品名称", index = 2) private String productName; /** * 生产数量 */ @ExcelProperty(value = "计划生产量", index = 3) private String productionQuantity; /** * 生产单位 */ @ExcelProperty(value = "单位", index = 4) private String productionUnit; /** * 开始时间 */ @ExcelProperty(value = "计划开始时间", index = 5) //@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") private String startTime; /** * 结束时间 */ @ExcelProperty(value = "计划完成时间", index = 6) //@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss") private String endTime; }
checkPointParam方法主要两个功能，一个是校验，另外一个是组装好待会要插入到数据库的对象
private List<StringBuffer> checkPointParam(List<WorkOrderVoImportDto> orderVoImports,List<WorkOrder> orders,List<WorkOrderAudit> orderAudits) { int index = 1; List<StringBuffer> failMsgList = new ArrayList<>(100); //StringBuffer commonError = new StringBuffer(); // 工单编码是否存在校验 List<String> orderCodes = orderVoImports.stream().map(WorkOrderVoImportDto::getWorkOrderCode).distinct().collect(Collectors.toList()); // if (orderCodes.size() != orderVoImports.size()){ // commonError.append("工单编码存在重复数据,"); // failMsgList.add(commonError); // } // 去查询数据库是否存在此次导入的工单编码 if (CollUtil.isNotEmpty(orderCodes)){ LambdaQueryWrapper<WorkOrder> wrapper = new LambdaQueryWrapper<>(); wrapper.in(WorkOrder::getWorkOrderCode,orderCodes); List<WorkOrder> workOrders = workOrderMapper.selectList(wrapper); if (ObjUtil.isNotEmpty(workOrders)){ List<String> orderCodeList = workOrders.stream().map(WorkOrder::getWorkOrderCode).collect(Collectors.toList()); String orderStr = "工单号：" + orderCodeList + "已存在,"; //commonError.append(orderStr); failMsgList.add(new StringBuffer(orderStr)); } } // 产品编码是否存在校验 List<String> productCodes = orderVoImports.stream().map(WorkOrderVoImportDto::getProductCode).distinct().collect(Collectors.toList()); LambdaQueryWrapper<Product> productWrapper = new LambdaQueryWrapper<>(); productWrapper.in(Product::getProductCode,productCodes); List<Product> products = productMapper.selectList(productWrapper); if (productCodes.size() != products.size()){ List<String> collect = products.stream().map(Product::getProductCode).distinct().collect(Collectors.toList()); if (CollUtil.isNotEmpty(products)){ // 拿出那个不存在的产品编码 List<String> diff = productCodes.stream() .filter(e -> !collect.contains(e)).collect(Collectors.toList()); String productStr = "产品编码：" + diff + "不存在,"; //commonError.append(productStr); failMsgList.add(new StringBuffer(productStr)); } } for (WorkOrderVoImportDto orderVoImport : orderVoImports) { WorkOrder workOrder = new WorkOrder(); WorkOrderAudit workOrderAudit = new WorkOrderAudit(); BeanUtil.copyProperties(orderVoImport,workOrder); index++; StringBuffer failMsg = new StringBuffer(); StringBuffer failMsgDetails = new StringBuffer(); String workOrderCode = orderVoImport.getWorkOrderCode(); // 工单编码校验 if (StringUtils.isEmpty(workOrderCode)){ failMsg.append("工单编码不可为空,"); }else if (workOrderCode.length() > 40){ failMsg.append("工单编码长度不可超过40,"); } // 产品编码校验 String productCode = orderVoImport.getProductCode(); if (StringUtils.isEmpty(productCode)){ failMsg.append("产品编码不可为空,"); }else if (productCode.length() > 20){ failMsg.append("产品编码长度不可超过20,"); } // 产品名称校验 String productName = orderVoImport.getProductName(); if (StringUtils.isEmpty(productName)){ failMsg.append("产品名称不可为空,"); }else if (productCode.length() > 20){ failMsg.append("产品名称长度不可超过20,"); } // 计划生产量校验 String productionQuantity = orderVoImport.getProductionQuantity(); if (ObjUtil.isEmpty(productionQuantity)){ failMsg.append("计划生产量不可为空,"); }else { try { int i = Integer.parseInt(productionQuantity); if (i < 0 || i > 99999){ failMsg.append("计划生产量数据有误,"); } workOrder.setProductionQuantity(i); }catch (Exception e){ failMsg.append("计划生产量数据有误,"); } } // 单位校验 String productionUnit = orderVoImport.getProductionUnit(); if (StringUtils.isEmpty(productionUnit)){ failMsg.append("单位不可为空,"); }else if (productCode.length() > 20){ failMsg.append("单位长度不可超过20,"); } // 计划开始时间校验 String startTime = orderVoImport.getStartTime(); boolean startTimeFlag = true; LocalDateTime parseStart = null; if (ObjUtil.isEmpty(startTime)){ startTimeFlag = false; failMsg.append("计划开始时间不可为空,"); }else { try { parseStart = LocalDateTime.parse(startTime, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")); workOrder.setStartTime(parseStart); }catch (Exception e){ startTimeFlag = false; failMsg.append("计划开始时间数据有误,"); } } // 计划完成时间校验 String endTime = orderVoImport.getEndTime(); boolean endTimeFlag = true; LocalDateTime parseEnd = null; if (ObjUtil.isEmpty(endTime)){ endTimeFlag = false; failMsg.append("计划完成时间不可为空,"); }else { try { parseEnd = LocalDateTime.parse(endTime, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")); workOrder.setEndTime(parseEnd); }catch (Exception e){ endTimeFlag = false; failMsg.append("计划完成时间数据有误,"); } } // 开始时间和完成时间对比 if (startTimeFlag && endTimeFlag){ boolean after = parseStart.isAfter(parseEnd); if (after){ failMsg.append("计划开始时间不能在计划完成时间之后,"); } } // 初始化一些数据，方便后面的插入数据 workOrder.setOrderStatus(OrderStatusEnum.WAIT_PRODUCT.getCode()); workOrder.setAuditStatus(OrderAuditStatusEnum.WAIT_AUDIT.getCode()); workOrder.setDataSources(0); // 0 手动创建 orders.add(workOrder); workOrderAudit.setAuditStatus(OrderAuditStatusEnum.WAIT_AUDIT.getCode()); workOrderAudit.setWorkOrderCode(orderVoImport.getWorkOrderCode()); orderAudits.add(workOrderAudit); if (StringUtils.isNotEmpty(failMsg)) { int start = failMsg.lastIndexOf(","); failMsg.replace(start, start + 1, ";"); String str = "第【" + index + "】行:"; failMsgDetails.append(str).append(failMsg); failMsgList.add(failMsgDetails); } } return failMsgList; }
下面是workOrder对象
@Data @Schema(description = "工单表") @Accessors(chain = true) @TableName("work_order") public class WorkOrder extends BaseEntity implements Serializable { private static final long serialVersionUID = 1L; /** * 主键id */ @TableId(value = "id", type = IdType.ASSIGN_ID) @Schema(description = "主键id") private Long id; /** * 工单编码 */ @Schema(description = "工单编码") private String workOrderCode; /** * 产品编码 */ @Schema(description = "产品编码") private String productCode; /** * 生产数量 */ @Schema(description = "生产数量") private Integer productionQuantity; /** * 生产单位 */ @Schema(description = "生产单位") private String productionUnit; /** * 开始时间 */ @Schema(description = "开始时间") private LocalDateTime startTime; /** * 结束时间 */ @Schema(description = "结束时间") private LocalDateTime endTime; /** * 工单状态:0 待生产, 1 生产中, 2 已完成 */ @Schema(description = "工单状态") private Integer orderStatus; /** * 数据来源：0 手动创建, 1 同步创建 */ @Schema(description = "数据来源") private Integer dataSources; /** * 审核状态:0 待审核, 1 审核同意, 2 审核拒绝;方便查询，去维护这个字段 */ private Integer auditStatus; }
下面是workAudit对象
@Data @Schema(description = "工单审批表") @Accessors(chain = true) @TableName("work_order_audit") public class WorkOrderAudit extends BaseEntity implements Serializable { private static final long serialVersionUID = 1L; /** * 主键id */ @TableId(value = "id", type = IdType.ASSIGN_ID) private Long id; /** * 工单编码 */ private String workOrderCode; /** * 审核状态:0 待审核, 1 审核同意, 2 审核拒绝 */ private Integer auditStatus; /** * 审核意见 */ private String auditOpinion; }
Excel测试数据
如果数据没问题，就可以导入到数据库，也检查下数据是否有问题。测试的时候先造没问题的主流程数据，然后再测试有问题的数据，看下你写的代码是否可以正常校验到对应数据。
以上笔记为导入过程
