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

2022-07-19 13:35:43

1.引入依赖

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

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;

/**
 * @Description StartUserVO
 * @Author lizhq
 * @Date 2022/5/6 13:57
 **/

@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<StartUserVO> StartUsers1 = new ArrayList<>();

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

            StartFile startFile = fileMapper.selectOne(new LambdaQueryWrapper<StartFile>().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<StartFile>().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;

/**
 * @Description StateCodeConverter
 * @Author lizhq
 * @Date 2022/5/12 10:48
 **/
public class StateCodeConverter implements Converter<Integer> {
    @Override
    public Class<Integer> supportJavaTypeKey() {
        return Integer.class;
    }

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

    @Override
    public WriteCellData<String> convertToExcelData(Integer status, ExcelContentProperty contentProperty,
                                                    GlobalConfiguration globalConfiguration) {
        return new WriteCellData<String>(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<Object> {
        ALLNOVERIFY(0,"全都未验证",(short)2),
        EMAILNOVERIFY(1,"邮箱验证",(short)5),
        PHONENOVERIFY(2,"手机验证",(short)53),
        EMAILANDPHONENOVERIFY(3,"邮箱和手机都验证",(short)3);
    private Integer code;
    private String name;
    private Short backColor;


    private static final Map<Object, StateCodeEnum> map = EnumUtils.createValuedEnumMap2(StateCodeEnum.class, item-> item.getCode());
    private static final Map<Object, StateCodeEnum> Stringmap = EnumUtils.createValuedEnumMap2(StateCodeEnum.class, item-> item.getName());

    public static void main(String[] args) {
        StateCodeEnum anEnum = StateCodeEnum.findByName("全都未验证");
        System.out.println(anEnum.backColor);

        StateCodeEnum anEnum1 = StateCodeEnum.findByCode(0);
        System.out.println(anEnum1.backColor);
    }
    public static StateCodeEnum findByCode(@JsonProperty("code") Integer code) {
        return map.get(code);
    }
    public static StateCodeEnum findByName(@JsonProperty("name") String name) {
        return Stringmap.get(name);
    }

}

                6.2.4设置自定义拦截器如果不设置背景颜色将不会显示

package com.qiang.config.easyexcel;


import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.qiang.enumerate.StateCodeEnum;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.springframework.stereotype.Component;

@Component
public class CustomCellWriteHandler extends AbstractCellStyleStrategy {

    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
        Cell cell = context.getCell();


        if (cell.getColumnIndex() == 5){
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
            contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
            // 背景绿色
            contentWriteCellStyle.setFillForegroundColor(StateCodeEnum.findByName(cell.getStringCellValue())==null?0:StateCodeEnum.findByName(cell.getStringCellValue()).getBackColor());
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
           // contentWriteFont.setFontHeightInPoints((short)20);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            WriteCellData<?> cellData = context.getFirstCellData();
            WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
        }

    }





}

                6.2.5service注入自定义拦截器，不清楚看3.有完整代码

EasyExcel.write(response.getOutputStream(), StartUserVO.class)
                    .withTemplate(templateInputStream)
                    .sheet().registerWriteHandler(new CustomCellWriteHandler()).registerWriteHandler(new CustomRowWriteHandler())
                    .doFill(StartUsers1);

        6.3设置序号

                6.3.1实体类

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

                6.3.2service 不清楚看3.有完整代码

   AtomicInteger i= new AtomicInteger();
   StartUserVO.setNo(i.incrementAndGet());
  • 作者：weixin_48776118
  • 原文链接：https://blog.csdn.net/weixin_48776118/article/details/124769648
    更新时间：2022-07-19 13:35:43