EasyExcel使用

一、简介

EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。EasyExcel官方文档地址（https://easyexcel.opensource.alibaba.com/）

二、使用

1、引入依赖

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

2、创建实体类

package com.genersoft.iot.vmp.info.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.genersoft.iot.vmp.info.utils.ExcelSelected;
import com.genersoft.iot.vmp.info.utils.HouseStatusConverter;
import com.genersoft.iot.vmp.info.utils.TypeConverter;
import io.swagger.v3.oas.annotations.media.Schema;
import org.springframework.data.annotation.Transient;

import java.util.List;

/**
 * @author xxx
 * @date 2023年04月03日 9:57
 *
 * 房屋基本信息表
 */

@ColumnWidth(12)
public class HouseBaseinfo {

    @Schema(description = "主键")
    @ExcelIgnore
    private Integer id;

    @Schema(description = "市级智慧小区ID")
    @ExcelIgnore
    private String communityId;

    @Transient
    @Schema(description = "市级平台内小区id")
    @ExcelProperty(index = 0,value = "小区名称")
    private String communityName;

    @Schema(description = "唯一标识（hash值）")
    @ExcelIgnore
    private String evtId;

    @Schema(description = "楼栋号")
    @ExcelProperty(index = 1,value = "楼栋号")
    private Integer number;

    @Schema(description = "单元号")
    @ExcelProperty(index = 2,value = "单元号")
    private Integer unit;

    @Schema(description = "楼层")
    @ExcelProperty(index = 3,value = "楼层")
    private Integer floor;

    @Schema(description = "门牌号")
    @ExcelProperty(index = 4,value = "门牌号")
    private Integer room;

    @Schema(description = "居住现状（1、自用 2、租用 3、自住 4、借用 5、空置 6、家庭租住 7、群租）")
    @ExcelSelected(source = {"自用","租用","自住","借用","空置","家庭租住","群租"})
    @ExcelProperty(index = 5,value = "居住现状",converter = HouseStatusConverter.class)
    private Integer status;

    @ExcelSelected(source = {"单元(公寓)楼","简子楼","别墅","平房","自建楼","四合院","其他"})
    @Schema(description = "房屋类别（1、单元(公寓)楼 2、简子楼 3、别墅 4、平房 5、自建楼 6、四合院 7、其他）")
    @ExcelProperty(index = 6,value = "房屋类别",converter = TypeConverter.class)
    private Integer type;

    @Schema(description = "房屋间数")
    @ExcelProperty(index = 7,value = "房屋间数")
    private Integer bedRoom;

    @Schema(description = "房屋面积")
    @ExcelProperty(index = 8,value = "房屋面积")
    private Integer area;

    @Schema(description = "实有人口")
    @ExcelProperty(index = 9,value = "实有人口")
    private Integer people;

    @Schema(description = "标准地址")
    @ExcelProperty(index = 10,value = "标准地址")
    @ColumnWidth(50)
    private String address;

    @Schema(description = "创建时间")
    @ExcelIgnore
    private Long createTime;

    @Schema(description = "更新时间")
    @ExcelIgnore
    private Long updateTime;

}

实体类对应的Excel表格

小区名称	楼栋号	单元号	楼层	门牌号	居住现状	房屋类别	房屋间数	房屋面积	实有人口	标准地址

注解解释

@ExcelProperty 用于设置实体类字段在excel中展示

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelProperty {
    //用于设置Excel列名
    String[] value() default {""};
    //用于设置Excel列序号
    int index() default -1;
    //用于设置Excel列序号，优先级高于index
    int order() default Integer.MAX_VALUE;
    //枚举类型字段转换器
    Class<? extends Converter<?>> converter() default AutoConverter.class;

    /** @deprecated */
    @Deprecated
    String format() default "";
}

@ExcelSelected 用于设置下拉列表

@Documented
@Retention(RetentionPolicy.RUNTIME)  //注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
@Target(ElementType.FIELD)  //用此注解用在属性上。
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

@ExcelIgnore 默认所有字段都会和excel去匹配，加了这个注解会忽略该字段

@ColumnWidth(12) 设置列宽

@ContentRowHeight(15) 设置内容的行高

@HeadRowHeight(25) 设置表头的行高

3、创建枚举类

package com.genersoft.iot.vmp.info.entity;

public enum HouseType {

    ONE(1,"单元(公寓)楼"),
    TWO(2,"简子楼"),
    THREE(3,"别墅"),
    FOUR(4,"平房"),
    FIVE(5,"自建楼"),
    SIX(6,"四合院"),
    SEVEN(7,"其他")
    ;
    // 成员变量
    private Integer code;
    private String houseType;

    HouseType(Integer code, String houseType) {
        this.houseType = houseType;
        this.code = code;
    }

    public String getHouseType() {
        return houseType;
    }

    public Integer getCode() {
        return code;
    }
}

4、创建转换器

package com.genersoft.iot.vmp.info.utils;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ReadConverterContext;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.genersoft.iot.vmp.info.entity.HouseType;

/**
 * 房屋类型转换器
 * @author xxx
 * @date 2023年04月17日 9:08
 */
public class TypeConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        //对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        //CellData属性类型
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
        //CellData转对象属性
        String cellStr = context.getReadCellData().getStringValue();
        if (StrUtil.isEmpty(cellStr)) return null;
        if (HouseType.ONE.getHouseType().equals(cellStr)) {
            return HouseType.ONE.getCode();
        } else if (HouseType.TWO.getHouseType().equals(cellStr)) {
            return HouseType.TWO.getCode();
        } else if (HouseType.THREE.getHouseType().equals(cellStr)) {
            return HouseType.THREE.getCode();
        } else if (HouseType.FOUR.getHouseType().equals(cellStr)) {
            return HouseType.FOUR.getCode();
        } else if (HouseType.FIVE.getHouseType().equals(cellStr)) {
            return HouseType.FIVE.getCode();
        } else if (HouseType.SIX.getHouseType().equals(cellStr)) {
            return HouseType.SIX.getCode();
        } else if (HouseType.SEVEN.getHouseType().equals(cellStr)) {
            return HouseType.SEVEN.getCode();
        } else {
            return null;
        }
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
        //对象属性转CellData
        Integer cellValue = context.getValue();
        if (cellValue == null) {
            return new WriteCellData<>("");
        }
        if (cellValue.equals(HouseType.ONE.getCode())) {
            return new WriteCellData<>(HouseType.ONE.getHouseType());
        } else if (cellValue.equals(HouseType.TWO.getCode())) {
            return new WriteCellData<>(HouseType.TWO.getHouseType());
        } else if (cellValue.equals(HouseType.THREE.getCode())) {
            return new WriteCellData<>(HouseType.THREE.getHouseType());
        } else if (cellValue.equals(HouseType.FOUR.getCode())) {
            return new WriteCellData<>(HouseType.FOUR.getHouseType());
        } else if (cellValue.equals(HouseType.FIVE.getCode())) {
            return new WriteCellData<>(HouseType.FIVE.getHouseType());
        } else if (cellValue.equals(HouseType.SIX.getCode())) {
            return new WriteCellData<>(HouseType.SIX.getHouseType());
        } else if (cellValue.equals(HouseType.SEVEN.getCode())) {
            return new WriteCellData<>(HouseType.SEVEN.getHouseType());
        } else {
            return new WriteCellData<>("");
        }
    }
}

5、下载导入模版

@Operation(summary = "下载房屋信息导入模板")
@GetMapping("downloadTemplate")
public void downloadTemplate(HttpServletResponse response) throws IOException {
    houseBaseinfoService.downloadTemplate(response);
}

public void downloadTemplate(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        List<HouseBaseinfo> houseBaseinfos = new ArrayList<>();
        HouseBaseinfo houseBaseinfo = new HouseBaseinfo();
        houseBaseinfo.setCommunityName("智慧小区");
        houseBaseinfo.setNumber(1);
        houseBaseinfo.setUnit(2);
        houseBaseinfo.setFloor(3);
        houseBaseinfo.setRoom(1);
        houseBaseinfo.setStatus(1);
        houseBaseinfo.setType(1);
        houseBaseinfo.setBedRoom(3);
        houseBaseinfo.setArea(89);
        houseBaseinfo.setPeople(3);
        houseBaseinfo.setAddress("不用填，后台会自动生成！");
        houseBaseinfos.add(houseBaseinfo);
        try {
            String fileName = URLEncoder.encode("房屋信息导入模板", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(HouseBaseinfo.class, 0, "房屋信息");
            excelWriter.write(houseBaseinfos, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }

    }

6、批量导入

@Operation(summary = "批量导入房屋信息")
@PostMapping("importExcel")
public Object importExcel(HttpServletRequest request, HttpServletResponse response, @RequestBody MultipartFile file) throws IOException {
    return houseBaseinfoService.importExcel(file);
}

public Object importExcel(MultipartFile file) throws IOException{
        List<HouseBaseinfo> excelList = null;
        String validate = "";
        //校验文件大小 100M
        boolean size = FileUtil.checkFileSize(file.getSize(), 100, "M");
        if(size == false){
            //文件过大啦，只能100M
            return WVPResult.fail(ErrorCode.ERROR100.getCode(), "文件过大啦，不能超过100M");
        }
        //excel读取数据
        excelList = EasyExcel.read(new BufferedInputStream(file.getInputStream())).head(HouseBaseinfo.class).sheet().doReadSync();
//        if (ExcelList.size() > 1000) {
//            //最多导入1000条哦
//        }
//        // excel数据校验
//        validate = ValidatorUtils.beanValidate(ExcelList);
//        if(!validate.equals("success")){
//            //参数异常提示  如：姓名不能为空哦~
//        }
        //总记录数
        Integer number = 0;
        for (HouseBaseinfo houseBaseinfo : excelList) {
            //根据小区名称查询小区id
            CommunityBaseinfo communityBaseinfo = communityBaseinfoMapper.getCommunityBaseinfoByCommunityName(houseBaseinfo.getCommunityName());
            if(communityBaseinfo == null){
                return WVPResult.fail(ErrorCode.ERROR100.getCode(), "请检查小区名字是否正确");
            }else{
                houseBaseinfo.setCommunityId(communityBaseinfo.getCommunityId());
            }
            Integer result = Integer.valueOf(addHouseBaseinfo(houseBaseinfo).toString());
            number = number+result;
        }
        return WVPResult.success(String.format("成功导入%s条房屋信息",number));
    }