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
    }
}

导出：

 @Operation(summary = "导出空气传感器数据列表")
    @GetMapping("/export")
    public Result export(HttpServletResponse response, @RequestParam(value = "equipmentAddress", required = false) String equipmentAddress) {
        environmentDataService.export(response, equipmentAddress);
        return Result.success();
    }


 public void export(HttpServletResponse response,String equipmentAddress) {
        String fileName = "空气传感器数据列表" + ".xlsx";
        //获取设备是传感器的数据
        List<EquipmentInfo> equipments = equipmentInfoService.listEquipmentInfoByEquipmentType(equipmentAddress);
        // todo 从redis获取温度，湿度，pm25数据
        EasyExcel.write(fileName, EnvironmentDataExportVO.class).sheet("模板").doWrite(equipments);
    }

package com.zygh.hzhw.manage.entity.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * 环境对象
 * @author: liubh
 * @since: 2023/5/27
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class EnvironmentDataExportVO implements Serializable {
    @ExcelProperty("pm2.5")
    public String pm25;
    /**
     * 温度
     */
    @ExcelProperty("温度")
    public String temperature;
    /**
     * 湿度
     */
    @ExcelProperty("湿度")
    private String humidity;
    /**
     * 氨气
     */
    @ExcelProperty("氨气")
    private String ammonia;
    /**
     * 硫化氢
     */
    @ExcelProperty("硫化氢")
    private String hydrogen;

    /**
     * 编码
     */
    @ExcelProperty("传感器编码")
    private String code;
    /**
     * 设备地址
     */
    @ExcelProperty("位置")
    private String equipmentAddress;
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("更新时间")
    private Date createTime;
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelProperty("推送时间")
    private Date pushTime;
    @ExcelProperty("voc")
    private String voc;

}

 导出例子：

 @Operation(summary = "导出数据类型")
    @GetMapping("/export")
    public void export(HttpServletResponse response, @Valid DictTypeExportReqVO reqVO) throws IOException {
        List<DictTypeDO> list = dictTypeService.getDictTypeList(reqVO);
        List<DictTypeExcelVO> data = DictTypeConvert.INSTANCE.convertList02(list);
        // 输出
        ExcelUtils.write(response, "字典类型.xls", "类型列表", DictTypeExcelVO.class, data);
    }
  /**
     * 将列表以 Excel 响应给前端
     *
     * @param response 响应
     * @param filename 文件名
     * @param sheetName Excel sheet 名
     * @param head Excel head 头
     * @param data 数据列表哦
     * @param <T> 泛型，保证 head 和 data 类型的一致性
     * @throws IOException 写入失败的情况
     */
    public static <T> void write(HttpServletResponse response, String filename, String sheetName,
                                 Class<T> head, List<T> data) throws IOException {
        // 输出 Excel
        EasyExcel.write(response.getOutputStream(), head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
                .sheet(sheetName).doWrite(data);
        // 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    }

 

欢迎关注公众号：

 

参考：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