Spring Boot结合easyexcel3.0.5使用模板导出，实现序号，根据值动态设置背景颜色，图片

1.引入依赖

    com.alibaba
    easyexcel
    3.0.5

2.添加模板加入resoures目录下

3.创建导出VO类添加excel注解

package com.qiang.service.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.converters.string.StringImageConverter;
import com.qiang.covert.StateCodeConverter;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.File;
import java.io.InputStream;
import java.time.LocalDateTime;



@AllArgsConstructor
@NoArgsConstructor
@Data
public   class StartUserVO {
    private static final long serialVersionUID = 1L;


    @ExcelProperty(value = "序号")
    private Integer no;
    @ExcelProperty(value = "用户名")
    private String userName;

    @ExcelProperty(value = "密码")
    private String password;

    @ExcelProperty(value = "电子邮箱")
    private String email;

    @ExcelProperty(value = "手机号")
    private String phone;
//    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
//    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty(value = "注册时间")
    private LocalDateTime registrationDate;
    
    @ExcelProperty(value = "注册IP地址")
    private String registrationIp;

    @ExcelProperty(value = "密码试错次数")
    private Integer trialErrorTimes;
    
    @ExcelProperty(value = "状态码",converter = StateCodeConverter.class)
    @ApiModelProperty(value = "状态码：0全都未验证；1邮箱验证；2手机验证；3邮箱和手机都验证")
    private Integer stateCode;
    
    @ExcelProperty(value = "问题照片1")
    private File path1;

    @ExcelProperty(value = "问题照片2")
    private InputStream path2;

    @ExcelProperty(value = "问题照片4",converter = StringImageConverter.class)
    private String path4;

    @ExcelProperty(value = "问题照片3")
    private byte[] path3;

}

3.导出代码service

    @Override
    public void exportExcel(HttpServletResponse response) {
        AtomicInteger i= new AtomicInteger();
        ArrayList StartUsers1 = new ArrayList<>();

        List StartUsers = StartUserMapper.selectList(new LambdaQueryWrapper().eq(StartUser::getIsBuiltIn,0));
        StartUsers.stream().forEach(user -> {

            StartFile startFile = fileMapper.selectOne(new LambdaQueryWrapper().eq(StartFile::getEntityId, user.getUuid()));
            StartUserVO StartUserVO = new StartUserVO();
            InputStream inputStream = null;
            try {
                inputStream = FileUtils.openInputStream(new File(startFile.getPath()));
            } catch (IOException e) {
                e.printStackTrace();
            }
            StartUserVO.setPath1(new File(startFile.getPath()));
            StartUserVO.setPath2(inputStream);
            try {
                StartUserVO.setPath3(FileUtils.readFileToByteArray(new File(startFile.getPath())));
            } catch (IOException e) {
                e.printStackTrace();
            }
            StartUserVO.setPath4(startFile.getPath());
            StartUserVO.setNo(i.incrementAndGet());
            BeanUtils.copyProperties(user, StartUserVO);
            StartUsers1.add(StartUserVO);
        });
        //生成excel
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
        String templatePath = "/template/用户.xlsx";
        InputStream templateInputStream = this.getClass().getResourceAsStream(templatePath);

        response.setHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
        try {
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("用户-导出.xlsx", "utf-8"));
            EasyExcel.write(response.getOutputStream(), StartUserVO.class)
                    .withTemplate(templateInputStream)
                    .sheet().registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(new CustomRowWriteHandler())
                    .doFill(StartUsers1);
        }
            catch (IOException e) {
            e.printStackTrace();
        }
    }

4.导出controller

    @GetMapping("/template/easyExcelPort")
    public void index5(HttpServletResponse response) throws Exception {
         userService.exportExcel(response);
    }

5.结果

6.其中用到了图片，背景颜色，序号

        6.1 图片 添加完这些代码后图片就会显示出来

                6.1.1实体类

    @ExcelProperty(value = "问题照片1")
    private File path1;

    @ExcelProperty(value = "问题照片2")
    private InputStream path2;

    @ExcelProperty(value = "问题照片4",converter = StringImageConverter.class)
    private String path4;

    @ExcelProperty(value = "问题照片3")
    private byte[] path3;

                6.1.2service

        StartUsers.stream().forEach(user -> {

            StartFile startFile = fileMapper.selectOne(new LambdaQueryWrapper().eq(StartFile::getEntityId, user.getUuid()));
            StartUserVO StartUserVO = new StartUserVO();
            InputStream inputStream = null;
            try {
                inputStream = FileUtils.openInputStream(new File(startFile.getPath()));
            } catch (IOException e) {
                e.printStackTrace();
            }
            StartUserVO.setPath1(new File(startFile.getPath()));
            StartUserVO.setPath2(inputStream);
            try {
                StartUserVO.setPath3(FileUtils.readFileToByteArray(new File(startFile.getPath())));
            } catch (IOException e) {
                e.printStackTrace();
            }
            StartUserVO.setPath4(startFile.getPath());
            StartUserVO.setNo(i.incrementAndGet());
            BeanUtils.copyProperties(user, StartUserVO);
            StartUsers1.add(StartUserVO);
        });

         6.2根据值动态设置背景颜色 添加完这些代码后背景颜色就会显示出来

                6.2.1 实体类

    @ExcelProperty(value = "状态码",converter = StateCodeConverter.class)
    @ApiModelProperty(value = "状态码：0全都未验证；1邮箱验证；2手机验证；3邮箱和手机都验证")
    private Integer stateCode;

                6.2.2 自定义转换器

package com.qiang.covert;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.qiang.enumerate.StateCodeEnum;


public class StateCodeConverter implements Converter {
    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public WriteCellData convertToExcelData(Integer status, ExcelContentProperty contentProperty,
                                                    GlobalConfiguration globalConfiguration) {
        return new WriteCellData(StateCodeEnum.findByCode(status).getName());
    }


}

                6.2.3枚举类

package com.qiang.enumerate;

import com.fasterxml.jackson.annotation.JsonProperty;
import com.qiang.util.EnumUtils;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;

import java.util.Map;
@AllArgsConstructor
@NoArgsConstructor
@Getter
public enum StateCodeEnum implements Coded