easypoi导入导出完整代码

easypoi 导入/导出

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.2.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
        </dependency>
        <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
    </dependency>

1.导出
导出的实体类

/**
 * 加油费用管理
 * Author:  shx
 * Date: 2022/11/29 10:54
 */
@Data
@TableName("cost_oil_card_refuel")
@EqualsAndHashCode(callSuper = true)
public class CostOilCardRefuel  extends BaseEntity<Long> {
    private static final long serialVersionUID = 1L;

    /**
     * 主键id
     */
    @JsonSerialize(using = ToStringSerializer.class)
    @Schema(description = "主键")
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 车辆使用机构部门
     */
    @Schema(description = "车辆使用机构部门")
    @Column(comment = "车辆使用机构部门")
    private String hostOrgId;

    /**
     * 车辆编号
     */
    @Schema(description = "车辆编号")
    @Column(comment = "车辆编号")
    private String hostId;

    /**
     * 车牌号码
     */
    @Schema(description = "车牌号码")
    @Column(comment = "车牌号码")
    @Excel(name = "车牌号码", width = 10)
    private String hostNo;

    /**
     * 机构编号
     */
    @Schema(description = "机构编号")
    @Column(comment = "机构编号")
    private String orgId;

    /**
     * 驾驶员编号
     */
    @Schema(description = "驾驶员编号")
    @Column(comment = "驾驶员编号")
    private String driverId;

    /**
     * 驾驶员姓名
     */
    @Schema(description = "驾驶员姓名")
    @Column(comment = "驾驶员姓名")
    private String driverName;

    /**
     * 供应商编号
     */
    @Schema(description = "供应商编号")
    @Column(comment = "供应商编号")
    private String providerId;

    /**
     * 供应商名称
     */
    @Schema(description = "供应商名称")
    @Column(comment = "供应商名称")
    @Excel(name = "供应商名称", width = 10)
    private String providerName;

    /**
     * 加油时间
     */
    @DateTimeFormat(pattern = SigConstant.PATTERN_DATETIME)
    @JsonFormat(pattern = SigConstant.PATTERN_DATETIME)
    @Schema(description = "加油时间")
    @TableField("refuel_date")
    @ColumnComment("加油时间")
    @Excel(name = "加油时间", format = "yyyy-MM-dd HH:mm:ss", width = 10)
    private LocalDateTime refuelDate;

    /**
     * 付费方式：1现金、2油卡，默认1现金
     */
    @Schema(description = "付费方式：1现金、2油卡，默认1现金")
    @Column(comment = "付费方式：1现金、2油卡，默认1现金")
    private Integer payType;

    /**
     * 油料类型。取值范围：1：98#  2：97#  3：95#   4：93#  5：92#  6：90#  7：89#  8：5＃柴油  9：0＃柴油
     */
    @Schema(description = "油料类型。取值范围：1：98#  2：97#  3：95#   4：93#  5：92#  6：90#  7：89#  8：5＃柴油  9：0＃柴油")
    @Column(comment = "油料类型。取值范围：1：98#  2：97#  3：95#   4：93#  5：92#  6：90#  7：89#  8：5＃柴油  9：0＃柴油")
    private Integer oilType;

    /**
     * 加油卡号
     */
    @Schema(description = "加油卡号")
    @Column(comment = "加油卡号")
    @Excel(name = "加油卡号", width = 10)
    private String oilCardNo;

    /**
     * 加油量(单位升)
     */
    @Schema(description = "加油量(单位升)")
    @Column(comment = "加油量(单位升)")
    @Excel(name = "加油量(升)", width = 10)
    private Double oilAmount;

    /**
     * 单价(单位：元)
     */
    @Schema(description = "单价(单位：元)")
    @Column(comment = "单价(单位：元)")
    @Excel(name = "单价(元/升)", width = 10)
    private Double oilPrice;

    /**
     * 费用(单位：元)
     */
    @Schema(description = "费用(单位：元)")
    @Column(comment = "费用(单位：元)")
    @Excel(name = "费用", width = 10)
    private Double oilCost;

    /**
     * 备注
     */
    @Schema(description = "备注")
    @Column(comment = "备注")
    private String remark;

    /**
     * 创建人名字
     */
    @Schema(description = "创建人名字")
    @Column(comment = "创建人名字")
    private String createUserName;

    /**
     * 创建人部门编号
     */
    @Schema(description = "创建人部门编号")
    @Column(comment = "创建人部门编号")
    private String createOrgId;

    /**
     * 修改人名字
     */
    @Schema(description = "修改人名字")
    @Column(comment = "修改人名字")
    private String updateUserName;

    /**
     * 修改人部门编号
     */
    @Schema(description = "修改人部门编号")
    @Column(comment = "修改人部门编号")
    private String updateOrgId;

    /**
     * 油料类型描述
     */
    @Schema(description = "油料类型描述")
    @Column(comment = "油料类型描述")
    private String oilTypeDesc;

    /**
     * 出车单编号
     */
    @Schema(description = "出车单编号")
    @Column(comment = "出车单编号")
    private String vehOutRunId;

    /**
     * 是否审核
     */
    @Schema(description = "是否审核")
    @Column(comment = "是否审核")
    private Integer feeVerify;

    /**
     * 消费地点
     */
    @Schema(description = "消费地点")
    @Column(comment = "消费地点")
    private String consumeAddress;

    /**
     * 油料类型
     */
    @TableField(exist = false)
    @Excel(name = "油标号", width = 10)
    private String oilTypeName;

    @TableField(exist = false)
    @Excel(name = "付款方式", width = 10)
    private String payTypeName;
}

Controller代码

/**
     * 导出加油费用列表
     * @param costOilCardRefuelVo
     * @param response
     * @throws Exception
     */
    @Operation(description = "加油费用导出", summary = "加油费用导出")
    @PostMapping(value = "/exportCostOilCardRefuel")
    public void exportCostOilCardRefuel(@RequestBody CostOilCardRefuelVo costOilCardRefuelVo , HttpServletResponse response
    ) throws Exception {
        iCostOilCardRefuelService.exportCostOilCardRefuel(costOilCardRefuelVo, response);
    }

接口类

void  exportCostOilCardRefuel(CostOilCardRefuelVo costOilCardRefuelVo, HttpServletResponse response) throws Exception;

接口实现类代码

@Override
    public void exportCostOilCardRefuel(CostOilCardRefuelVo costOilCardRefuelVo, HttpServletResponse response) throws Exception {
        PageHelper.startPage(costOilCardRefuelVo.getPageNo(),costOilCardRefuelVo.getPageSize());
        PageInfo pageInfo = new PageInfo();
        List<CostOilCardRefuel> costOilCardRefuelList = baseMapper.getCostOilCardRefuelList(costOilCardRefuelVo);
        costOilCardRefuelList.forEach(c->{
            //
            c.setOilTypeName(dictMapper.getValueName("OIL_TYPE",String.valueOf(c.getOilType())));
            if (c.getPayType()==1){
                c.setPayTypeName("现金");
            }else {
                c.setPayTypeName("油卡");
            }

        });
        pageInfo.setTotal(costOilCardRefuelList.size());
        pageInfo.setList(costOilCardRefuelList);
        ExportParams params = new ExportParams();
        params.setType(ExcelType.HSSF);
        PoiUtil.defaultExport(pageInfo.getList(), CostOilCardRefuel.class, "加油费用列表.xls", response, params);
    }

2.导入Excel
错误返回实体

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;

import java.io.Serializable;
@Data
@AllArgsConstructor
public class ImportErrorInfo implements Serializable {
    @Excel(name = "行数", width = 10)
    private Integer lineNum;
    @Excel(name = "错误描述", width = 10)
    private String errMsg;
}

接受EXCEL数据实体

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


/**
 * Author:  shx
 * Date: 2022/12/2 13:51
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class InputCostOilCardRefuelVo implements IExcelDataModel, IExcelModel {
    /**
     * 车牌号码
     */
    @Excel(name = "车牌号码", width = 10)
    private String hostNo;
    @Excel(name = "驾驶员", width = 10)
    private String driverName;
    @Excel(name = "供应商名称", width = 10)
    private String providerName;
    @Excel(name = "加油时间", format = "yyyy-MM-dd HH:mm:ss", width = 10)
    private String refuelDate;
    @Excel(name = "油标号", width = 10)
    private String oilTypeName;
    @Excel(name = "付款方式", width = 10)
    private String payTypeName;
    @Excel(name = "付费卡号", width = 10)
    private String oilCardNo;
    @Excel(name = "加油量(升)", width = 10)
    private Double oilAmount;
    @Excel(name = "单价(元/升)", width = 10)
    private Double oilPrice;
    @Excel(name = "费用", width = 10)
    private Double oilCost;
    @Excel(name = "消费地点", width = 10)
    private String consumeAddress;
    @Excel(name = "备注", width = 10)
    private String remark;
    /**
     * 行号
     */
    private Integer rowNum;

    /**
     * 错误消息
     */
    private String errorMsg;
}

controller层代码

/**
     * 导入加油费用
     * @param file
     * @throws Exception
     */
    @Operation(description = "导入加油费用", summary = "导入加油费用")
    @PostMapping(value = "/inputCostOilCardRefuel")
    public R<List<ImportErrorInfo>> inputCostOilCardRefuel(@RequestParam("file") MultipartFile file) throws Exception {
        return iCostOilCardRefuelService.inputCostOilCardRefuel(file);
    }

接口类代码

R<List<ImportErrorInfo>> inputCostOilCardRefuel(MultipartFile file)throws Exception;

接口实现类代码

@Override
    public R<List<ImportErrorInfo>> inputCostOilCardRefuel(MultipartFile file) throws Exception {
        if (!Objects.requireNonNull(file.getOriginalFilename()).endsWith("xls")) {
            throw new ValidationException("请导入正确的文件");
        }
        ImportParams importParams = new ImportParams();
        importParams.setTitleRows(1);//去掉标题行
        importParams.setTitleRows(0);
        importParams.setNeedVerify(true);
        //数据集合
        List<CostOilCardRefuel> costOilCardRefuelList = new ArrayList<>();
        //卡集合
        List<FuelCard> fuelCardList = new ArrayList<>();
        //错误集合
        List<ImportErrorInfo> importErrorInfoList = new ArrayList<>();
        //返回集合
        R<List<ImportErrorInfo>> res = new R<>();
        //用流的方式导入数据，获取到当前导入的内容  校验通过的集合
        List<InputCostOilCardRefuelVo> inputCostOilCardRefuelVoList = ExcelImportUtil.importExcel(file.getInputStream(), InputCostOilCardRefuelVo.class, importParams);

        //校验失败的数据结果
        ExcelImportResult<InputCostOilCardRefuelVo> result = ExcelImportUtil.importExcelMore(file.getInputStream(),
                InputCostOilCardRefuelVo.class, importParams);

        for (InputCostOilCardRefuelVo entity : result.getFailList()) {
            ImportErrorInfo err = new ImportErrorInfo(entity.getRowNum() + 1, entity.getErrorMsg());
            importErrorInfoList.add(err);
        }
        for (InputCostOilCardRefuelVo inputCostOilCardRefuelVo : inputCostOilCardRefuelVoList){

            //时间校验
            Date refuelDate = DateUtil.parseStrDate(inputCostOilCardRefuelVo.getRefuelDate(), "yyyy-MM-dd HH:mm:ss");
            Date oracleDate = DateUtil.parseStrDate(DateUtil.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss") ,"yyyy-MM-dd HH:mm:ss");
            if (refuelDate.after(oracleDate)){
                ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1,"加油时间不能在当前时间之后");
                importErrorInfoList.add(err);
                continue;
            }
            //TODO 根据供应商providerId 查询信息进行校验

            //TODO 校验车辆信息
            CostOilCardRefuel costOilCardRefuel = new CostOilCardRefuel();
            BeanUtils.copyProperties(inputCostOilCardRefuelVo,costOilCardRefuel);
            //付款方式
            if (inputCostOilCardRefuelVo.getPayTypeName().equals("现金")){
                costOilCardRefuel.setPayType(1);
            }else {
                costOilCardRefuel.setPayType(2);
            }
            //油料类型
            costOilCardRefuel.setOilType(Integer.parseInt(dictMapper.getValue("OIL_TYPE",inputCostOilCardRefuelVo.getOilTypeName())));

            //校验付款方式
            if(costOilCardRefuel.getPayType()==1){
                costOilCardRefuel.setOilCardNo("");
            }else if(costOilCardRefuel.getPayType()==2){
                if(Util.isEmpty(costOilCardRefuel.getOilCardNo())){
                    ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1,"付费方式为油卡时，请输入加油卡号");
                    importErrorInfoList.add(err);
                    continue;
                }
            }else{
                ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1,"付费方式不正确");
                importErrorInfoList.add(err);
                continue;
            }

            String oilCardNo = costOilCardRefuel.getOilCardNo();
            Long oilCardRecordId = null;
            if(!Util.isEmpty(oilCardNo)){
                List<FuelCard> oilCardInfos = fuelCardMapper.selectList(new QueryWrapper<FuelCard>().eq("is_deleted","0").eq("card_no",oilCardNo));
                if(oilCardInfos.size() != 1){
                    ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1,"加油卡信息不正确");
                    importErrorInfoList.add(err);
                    continue;
                }

                if(oilCardInfos.get(0).getCardType() == 1){
                    ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1,"加油时只能输入副卡");
                    importErrorInfoList.add(err);
                    continue;
                }

                oilCardRecordId = oilCardInfos.get(0).getCardId();
            }
            //获取当前登录人的信息
            User user = userService.getById(SecureUtil.getUser().getUserId());
            costOilCardRefuel.setUpdateOrgId(user.getOrgId());
            costOilCardRefuel.setUpdateTime(LocalDateTime.now());
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            costOilCardRefuel.setRefuelDate(LocalDateTime.parse(inputCostOilCardRefuelVo.getRefuelDate(),formatter));
            costOilCardRefuel.setOrgId(user.getOrgId());
            costOilCardRefuel.setUpdateUserName(user.getRealName());
            if(!StringUtils.isEmpty(oilCardRecordId)) { // 油卡加油 减去当前油卡余额
                FuelCard cardInfo = fuelCardMapper.selectById(oilCardRecordId);
                Double balanceAmount = cardInfo.getBalanceAmount();
                cardInfo.setBalanceAmount(balanceAmount - inputCostOilCardRefuelVo.getOilCost());
                if(cardInfo.getBalanceAmount() < 0) {
                    ImportErrorInfo err = new ImportErrorInfo(inputCostOilCardRefuelVo.getRowNum() + 1, "当前油卡余额(" + balanceAmount + ")不足，请先充值");
                    importErrorInfoList.add(err);
                    continue;
                }
                fuelCardList.add(cardInfo);
            }
            costOilCardRefuelList.add(costOilCardRefuel);
        }
        if (Func.isNotEmpty(importErrorInfoList)) {
            res.setData(importErrorInfoList);
            res.setMessage("校验不通过");
            res.setStatus(ResultCode.FAILURE.getStatus());
            return res;
        }
        //导入数据库
        boolean add = super.saveBatch(costOilCardRefuelList);
        fuelCardList.forEach(f->{
            fuelCardMapper.updateById(f);
        });
        if (add){
            res.setMessage("操作成功");
        }
        return res;
    }