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测试数据

如果数据没问题，就可以导入到数据库，也检查下数据是否有问题。测试的时候先造没问题的主流程数据，然后再测试有问题的数据，看下你写的代码是否可以正常校验到对应数据。

以上笔记为导入过程