EasyExcel导入数据

最新推荐文章于 2024-10-05 14:41:26 发布

原创最新推荐文章于 2024-10-05 14:41:26 发布 · 5.3k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#java

本文介绍如何利用Java库EasyExcel进行Excel文件的数据批量导入。通过定义实体类和监听器，实现了对公司数据和项目数据的有效读取及处理，并提供了一个具体的实现示例。

前几天业务需求,需要导入公司数据和项目数据(excel格式),运用easyexcel导入实现如下:

excel文件映射实体

@Data
public class ExcelItem {

    @ExcelProperty(value = "序号")
    private Integer index;

    /**
     * 公司名称
     */
    @ExcelProperty(value = "企业名称")
    private String companyName;

    /**
     * 公司地址
     */
    @ExcelProperty(value = "镇街")
    private String address;

    /**
     * 项目名称
     */
    @ExcelProperty(value = "项目名称")
    private String itemName;

    /**
     * 固定资产总投资
     */
    @ExcelProperty(value = "固定资产总投资")
    private String fixedTotalInvest;

    /**
     * 设备投资
     */
    @ExcelProperty(value = "其中设备投资")
    private String deviceInvest;

    /**
     * 已完工数量
     */
    @ExcelProperty(value = "已完成投资")
    private String completedInvest;

    @ExcelProperty("2022年拟投资")
    private String nowYearInvest;

    /**
     * 项目审批情况
     */
    @ExcelProperty(value = "项目审批情况")
    private String authState;

    /**
     * 拟开完工时间
     */
    @ExcelProperty(value = "拟开完工时间")
    private String date;

    /**
     * 项目标签
     */
    @ExcelProperty(value = "项目标签1")
    private String label;

    public Integer getIndex() {
        return index;
    }

    public void setIndex(Integer index) {
        this.index = index;
    }

    public String getItemName() {
        return itemName;
    }

    public void setItemName(String itemName) {
        this.itemName = itemName;
    }

    public String getFixedTotalInvest() {
        return fixedTotalInvest;
    }

    public void setFixedTotalInvest(String fixedTotalInvest) {
        this.fixedTotalInvest = fixedTotalInvest;
    }

    public String getDeviceInvest() {
        return deviceInvest;
    }

    public void setDeviceInvest(String deviceInvest) {
        this.deviceInvest = deviceInvest;
    }

    public String getCompletedInvest() {
        return completedInvest;
    }

    public void setCompletedInvest(String completedInvest) {
        this.completedInvest = completedInvest;
    }

    public String getAuthState() {
        return authState;
    }

    public void setAuthState(String authState) {
        this.authState = authState;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public String getLabel() {
        return label;
    }

    public void setLabel(String label) {
        this.label = label;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getNowYearInvest() {
        return nowYearInvest;
    }

    public void setNowYearInvest(String nowYearInvest) {
        this.nowYearInvest = nowYearInvest;
    }

监听器

@Slf4j
@Service
public class ExcelItemListener extends AnalysisEventListener<ExcelItem> {

    /**
     * 批处理阈值
     */
    private static final int BATCH_COUNT = 5;
    List<ExcelItem> list = new ArrayList<>(BATCH_COUNT);

    @Override
    public void invoke(ExcelItem excelItem, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(excelItem));
        list.add(excelItem);
        if (list.size() >= BATCH_COUNT) {
            importItemInfo(list);
            list.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        importItemInfo(list);
        log.info("所有数据解析完成！");
    }


    public void importItemInfo(List<ExcelItem> infoList) {
           //导入操作---新增公司和项目数据
    }
}

测试类调用

//文件路径
String fileName = "D:\\导入文件.xlsx";
//调用方法实现读取
EasyExcel.read(fileName,ExcelItem.class,new ExcelItemListener()).sheet().doRead();