springboot集成easyexcel实现导入导出

1、添加依赖

 <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

2、controller

   /**
     * 基于Listener方式从Excel导入会员列表
     */
    @Operation(summary = "导入垃圾厢房数据")
    @PostMapping(value = "/import")
    public Result importWasteRoom(@RequestPart("file") MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), WasteRoomInfoExcelDTO.class, new WasteRoomInfoExcelListener(wasteRoomInfoService)).sheet().doRead();
        return Result.success();
    }

package com.zygh.hzhw.manage.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import lombok.experimental.Accessors;

import javax.validation.constraints.NotNull;
import java.io.Serializable;

/**
 * @ExcelIgnore：忽略掉该字段；
 * @ExcelProperty("用户名")：设置该列的名称为”用户名“；
 * @ColumnWidth(20)：设置表格列的宽度为20；
 * @DateTimeFormat("yyyy-MM-dd")：按照指定的格式对日期进行格式化；
 * @ExcelProperty(value = "性别", converter = GenderConverter.class)：自定义内容转换器，类似枚举的实现，将“男”、“女”转换成“0”、“1”的数值。
 * 垃圾厢房信息
 * @author liubh
 */
@Data
//切记不能添加次注解，否则对象映射不上
//@Accessors(chain = true)
public class WasteRoomInfoExcelDTO implements Serializable {

    /**
     * 街道办事处
     */
    @ExcelProperty("街道办事处")
    @Schema(description = "街道办事处")
    private String street;

    /**
     * 社区
     */
    @ExcelProperty("社区")
    @Schema(description = "社区")
    private String community;

    /**
     * 小区
     */
    @ExcelProperty("小区")
    @Schema(description = "小区")
    private String village;

    /**
     * 位置
     */
    @ExcelProperty("位置")
    @Schema(description = "位置")
    private String locationName;

//    /**
//     * 类型
//     */
//    @Schema(description = "类型")
//    private Integer type;

    /**
     * 垃圾桶数量
     */
    @ExcelProperty("垃圾桶数量")
    @Schema(description = "垃圾桶数量")
    private Integer wasteNumber;


    /**
     * 回收柜数量
     */

    @ExcelProperty("回收柜数量")
    @Schema(description = "回收柜数量")
    private Integer recoveryNumber;

    /**
     * 垃圾箱编号
     */
    @ExcelProperty("垃圾箱编号")
    @Schema(description = "垃圾箱编号")
    private String wasteCode;

    /**
     * 大屏终端编号
     */
    @ExcelProperty("大屏终端编号")
    @Schema(description = "大屏终端编号")
    private String largeScreen;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;
}

package com.zygh.hzhw.manage.handler;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.zygh.hzhw.manage.dto.WasteRoomInfoExcelDTO;
import com.zygh.hzhw.manage.entity.WasteRoomInfo;
import com.zygh.hzhw.manage.service.WasteRoomInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;

import java.util.List;

/**
 * // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 *
 * @author liubh
 */
@Slf4j
public class WasteRoomInfoExcelListener extends AnalysisEventListener<WasteRoomInfoExcelDTO> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */

    private static final int BATCH_COUNT = 10;
    /**
     * 缓存的数据
     */
    private List<WasteRoomInfo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    public WasteRoomInfoService wasteRoomInfoService;

    public WasteRoomInfoExcelListener(WasteRoomInfoService wasteRoomInfoService) {
        this.wasteRoomInfoService = wasteRoomInfoService;
    }

    @Override
    public void invoke(WasteRoomInfoExcelDTO wasteRoomInfoExcelDTO, AnalysisContext analysisContext) {
        WasteRoomInfo wasteRoomInfo = new WasteRoomInfo();
        BeanUtils.copyProperties(wasteRoomInfoExcelDTO, wasteRoomInfo);
        cachedDataList.add(wasteRoomInfo);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData(cachedDataList);
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 保存数据
     *
     * @param wasteRoomInfoExcelDTOList
     */
    private void saveData(List<WasteRoomInfo> wasteRoomInfoExcelDTOList) {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        wasteRoomInfoService.saveBatch(wasteRoomInfoExcelDTOList);
        log.info("存储数据库成功！");
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // do something
        System.out.println("读取Excel完毕");
        // do something
    }
}

欢迎关注公众号：

参考：https://developer.aliyun.com/article/1248377?spm=a2c6h.12873639.article-detail.28.37347e47pXAynm&scm=20140722.ID_community@@article@@1248377._.ID_community@@article@@1248377-OR_rec-V_1-RL_community@@article@@1057858

  https://developer.aliyun.com/article/1226862?spm=a2c6h.12873639.article-detail.31.37347e47pXAynm&scm=20140722.ID_community@@article@@1226862._.ID_community@@article@@1226862-OR_rec-V_1-RL_community@@article@@1057858