参考

使用Easyexcel导入大批量数据

Excel 模版

POM.xml

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>
</dependencies>

Model-ExcelCostPay

package com.luoma.finance.dto.dataImport;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
/**
 * 历史数据导入 - Excel 模版类 - 费用支付
 * luoma - 2023年03月02日10:18:28
 */
@Data
public class ExcelCostPay {
    /**
     * 融资操作信息表（financing_operate_info）主键 ID
     */
    private Long operateInfoId;
    /**
     * 融资产品编号
     */
    @ExcelProperty(index = 0)
    private String financingProductNo;
    /**
     * 费用类型名称
     */
    @ExcelProperty(index = 1)
    private String costTypeName;
    /**
     * 费用类型代码
     */
    @ExcelProperty(index = 2)
    private String costTypeCode;
    /**
     * 费用类型是否分摊，Y：是，N：否
     */
    @ExcelProperty(index = 3)
    private String costTypeIsShare;
    /**
     * 上期还款日，默认上期付款支付日期，如果属于第一期则手工输入或放空
     */
    @ExcelProperty(index = 4)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date lastTimeRepaymentDate;
    /**
     * 还款周期
     */
    @ExcelProperty(index = 5)
    private String repaymentCycle;
    /**
     * 支付日期，根据上面“还款周期”字段和“上期还款日”计算得出，按“还款周期”的列表值，分别：“上期还款”+1个月、3个月、6个月、12个月、0个月
     */
    @ExcelProperty(index = 6)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date payDate;
    /**
     * 费用覆盖开始日期
     */
    @ExcelProperty(index = 7)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date costCoverBeginDate;
    /**
     * 费用覆盖结束日期
     */
    @ExcelProperty(index = 8)
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date costCoverEndDate;
    /**
     * 费用利率，保留七位小数
     */
    @ExcelProperty(index = 9)
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    private BigDecimal costRate;
    /**
     * 付款实际天数=支付日期-上期还款日
     */
    @ExcelProperty(index = 10)
    private Integer payActualDay;
    /**
     * 本金，来源提款表剩余本金字段，保留两位小数
     */
    @ExcelProperty(index = 11)
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    private BigDecimal principalAmount;
    /**
     * 实际费用=本金*费用利率*付款实际天数/（提款表）的计息用天数，保留两位小数
     */
    @ExcelProperty(index = 12)
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    private BigDecimal actualCostAmount;
    /**
     * 支付状态：未申请支付/付款申请中/已支付，默认“未申请支付”
     */
    @ExcelProperty(index = 13)
    private String payStatus;
}

Excel 读取类-CostPayListener

package com.luoma.finance.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.luoma.finance.dto.dataImport.ExcelCostPay;
import java.util.ArrayList;
import java.util.List;
/**
 * 类别 - 描述
 * luoma - 2023年03月02日11:03:33
 */
public class CostPayListener extends AnalysisEventListener<ExcelCostPay> {
    /**
     * 存储读取 Excel 数据
     */
    private List<ExcelCostPay> listData = new ArrayList<>();
    public CostPayListener() {
    }
    /**
     * 读取 Excel 内容
     *
     * @param excelCostPay    历史数据导入 - Excel 模版类 - 费用支付
     * @param analysisContext 读取操作上下文
     */
    @Override
    public void invoke(ExcelCostPay excelCostPay, AnalysisContext analysisContext) {
        listData.add(excelCostPay);
    }
    /**
     * 读取后完成的操作
     *
     * @param analysisContext 读取操作上下文
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
    /**
     * 获取读取的 Excel 数据
     *
     * @return 费用支付数据
     */
    public List<ExcelCostPay> getListData() {
        return listData;
    }
}

控制器

/**
 * 控制器 - 历史数据导入
 * luoma - 2023年3月1日17:11:55
 */
@Slf4j
@RestController
@RequestMapping("/api/dataImport")
public class DataImportController {
    //------------------------------------------------region 公共方法
    /**
     * 写日志
     *
     * @param logInfo 日志前缀
     * @param msg     日志信息
     * @param listLog 需要添加日志信息的 list
     */
    private void writeLog(String logInfo, String msg, List<String> listLog) {
        listLog.add(msg);
        log.info(logInfo, msg);
    }
    //------------------------------------------------endregion
    //------------------------------------------------region 费用支付，利息支付，本金支付
    /**
     * 历史数据导入-费用支付
     *
     * @param passWord 操作密码
     * @param file     导入 Excel 文件
     * @return 操作信息
     */
    @ApiOperation("历史数据导入-费用支付")
    @PostMapping("/costPay")
    public ResponseVO<String> costPay(String passWord, MultipartFile file) {
        String msg;
        String logInfo = "costPay >> {}";
        List<String> listLog = new ArrayList<>();
        writeLog(logInfo, "开始进行数据导入", listLog);
        try {
            //------------------------必填校验
            if (!passWord.equals(passWordStr)) {
                throw new Exception("密码不正确");
            }
            if (CommonUtil.isNull(file)) {
                throw new Exception("没有上传 Excel 文件");
            }
            //------------------------读取 Excel
            msg = "开始读取 Excel：" + file.getOriginalFilename();
            writeLog(logInfo, msg, listLog);
            CostPayListener costPayListener = new CostPayListener();
            EasyExcel.read(file.getInputStream(), ExcelCostPay.class, costPayListener).sheet().doRead();
            List<ExcelCostPay> listExcelCostPay = costPayListener.getListData();
            if (CommonUtil.isNull(listExcelCostPay)) {
                throw new Exception("Excel 文件数据为空");
            }
            msg = "读取到数据：" + listExcelCostPay.size();
            writeLog(logInfo, msg, listLog);
            for (ExcelCostPay item : listExcelCostPay) {
                System.out.println(CommUtil.convertToJson(item));
            }
        } catch (Exception e) {
            msg = "数据导入异常，异常信息：" + e.getMessage();
            listLog.add(msg);
            log.error(msg, e);
        } finally {
            writeLog(logInfo, "结束进行数据导入", listLog);
            msg = String.join("<br />", listLog);
        }
        return new ResponseVO<>(ResponseCode.OK, msg);
    }
    //------------------------------------------------endregion
}

测试

控制台输出

{"operateInfoId":null,"financingProductNo":"S2023021002","costTypeName":"承诺费用（Commitment Fee）","costTypeCode":"COMMITMENT","costTypeIsShare":"N","lastTimeRepaymentDate":null,"repaymentCycle":"1M","payDate":"2023-02-10","costCoverBeginDate":"2023-01-10","costCoverEndDate":"2023-02-10","costRate":"0.23563","payActualDay":30,"principalAmount":"2000","actualCostAmount":"1.23456","payStatus":"未申请支付"}
{"operateInfoId":null,"financingProductNo":"S2023021002","costTypeName":"年费（Annual Fee）","costTypeCode":"ANNUAL","costTypeIsShare":"Y","lastTimeRepaymentDate":"2023-01-10","repaymentCycle":"3M","payDate":"2023-04-10","costCoverBeginDate":"2023-01-10","costCoverEndDate":"2023-04-10","costRate":"0.33563","payActualDay":90,"principalAmount":"2000","actualCostAmount":"2.23456","payStatus":"未申请支付"}
{"operateInfoId":null,"financingProductNo":"S2023021305","costTypeName":"代理费（Agency Fee）","costTypeCode":"AGENCY","costTypeIsShare":"Y","lastTimeRepaymentDate":"2023-01-12","repaymentCycle":"6M","payDate":"2023-07-12","costCoverBeginDate":"2023-01-12","costCoverEndDate":"2023-07-12","costRate":"1.1111","payActualDay":180,"principalAmount":"9000","actualCostAmount":"11.1111","payStatus":"未申请支付"}
{"operateInfoId":null,"financingProductNo":"S2023021305","costTypeName":"佣金费（Upfront Fee)","costTypeCode":"UPFRONT","costTypeIsShare":"Y","lastTimeRepaymentDate":"2023-01-12","repaymentCycle":"1M","payDate":"2023-02-12","costCoverBeginDate":"2023-01-12","costCoverEndDate":"2023-02-12","costRate":"2.2222","payActualDay":30,"principalAmount":"9000","actualCostAmount":"22.2222","payStatus":"未申请支付"}