EasyExcel实现Excel的导入导出

前言

在真实的开发者场景中，经常会使用excel作为数据的载体，进行数据导入和导出的操作，使用excel的导入和导出有很多种解决方案，用到了导出多sheet到一个excel文件，锁定、解锁、设置字体、自动换行、设置指定单元格背景色、字典转换值、导出下拉框等功能，本篇记录一下EasyExcel的使用。

一、EasyExcel是什么？

EasyExcel是一个开源的项目，是阿里开发的。EasyExcel可以简化Excel表格的导入和导出操作，使用起来简单快捷，易上手。

二、使用步骤

1.导入依赖

在pom.xml中导入我们需要使用的依赖

<!-- easy excel依赖 -->
<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.3.2</version>
</dependency>

2.控制层

控制层负责接收前端传过来的文件流，然后使用EasyExcel来对导入或者导出的数据进行进一步的处理，并把处理结果返回，需要注意的是，对于导出操作，该方法并没有返回值，因此我们不需要返回值，而对于导入方法，此处我使用的是layui的前端，所以返回的数据格式是layui可以接收的工具类。
 

    /**
     * 导入Excel
     */
    @RequestMapping("/importExcel.do")
    @ResponseBody
    public Message importExcel(@RequestParam(value = "file") MultipartFile file) throws IOException{
        EasyExcel.read(file.getInputStream(), SjhyCwrzbVo.class, new ExcelListener(sjhyCwrzService,sjhyZgjlService,xzqhService,cjsjhyRedisUtil)).sheet("核验错误结果表").doRead();
        return new Message<>(true, "导入成功");
    }

    /**
     * description: 前置机核验错误日志列表导出
     *
     * @return
     */
    @ApiOperation(value = "核验错误日志列表导出", notes = "错误日志列表导出")
    @GetMapping("/exportCwrzList.do")
    public void exportCwrzList(
            @ApiParam(name = "xzqhbm", value = "行政区划编码") @RequestParam(value = "xzqhbm", required = false) String xzqhbm,
            @ApiParam(name = "name", value = "主分类名称") @RequestParam(value = "name", required = false) String name,
            @ApiParam(name = "ywlx", value = "业务类型") @RequestParam(value = "ywlx", required = false) String ywlx,
            @ApiParam(name = "jybm", value = "校验表名") @RequestParam(value = "jybm", required = false) String jybm,
            @ApiParam(name = "jyyw", value = "校验业务") @RequestParam(value = "jyyw", required = false) String jyyw,
            @ApiParam(name = "cwms", value = "错误描述") @RequestParam(value = "cwms", required = false) String cwms,
            @ApiParam(name = "wttsMin", value = "问题条数MIN") @RequestParam(value = "wttsMin", required = false) String wttsMin,
            @ApiParam(name = "wttsMax", value = "问题条数Max") @RequestParam(value = "wttsMax", required = false) String wttsMax,
            @ApiParam(name = "jysjq", value = "校验时间起") @RequestParam(value = "jysjq", required = false) String jysjq,
            @ApiParam(name = "jysjz", value = "校验时间止") @RequestParam(value = "jysjz", required = false) String jysjz,
            @ApiParam(name = "zxwttsMin", value = "最新问题条数-min") @RequestParam(value = "zxwttsMin", required = false) String zxwttsMin,
            @ApiParam(name = "zxwttsMax", value = "最新问题条数-max") @RequestParam(value = "zxwttsMax", required = false) String zxwttsMax,
            @ApiParam(name = "zxjdMin", value = "最新进度-max") @RequestParam(value = "zxjdMin", required = false) String zxjdMin,
            @ApiParam(name = "zxjdMax", value = "最新进度-max") @RequestParam(value = "zxjdMax", required = false) String zxjdMax,
            @ApiParam(name = "sfzg", value = "是否整改：1 是， 0否") @RequestParam(value = "sfzg", required = false) String sfzg,
            @ApiParam(name = "sytsMin", value = "预计完成剩余天数-min") @RequestParam(value = "sytsMin", required = false) String sytsMin,
            @ApiParam(name = "sytsMax", value = "预计完成剩余天数-max") @RequestParam(value = "sytsMax", required = false) String sytsMax,
            @ApiParam(name = "zxjdOb", value = "最新进度-排序： asc 顺序， desc 倒序") @RequestParam(value = "zxjdOb", required = false) String zxjdOb,
            @ApiParam(name = "sytsOb", value = "剩余天数-排序： asc 顺序， desc 倒序") @RequestParam(value = "sytsOb", required = false) String sytsOb,
            @ApiParam(name = "jdbh", value = "进度变化") @RequestParam(value = "jdbh", required = false) String jdbh,
            HttpServletResponse response) {
        try {
            Pagination<SjhyCwrzbVo> result = sjhyCwrzService.getCwrzListByPage(xzqhbm, name, ywlx, jybm, jyyw, cwms, wttsMin, wttsMax, jysjq, jysjz, 1, Integer.MAX_VALUE, zxwttsMin, zxwttsMax, zxjdMin, zxjdMax, sfzg, sytsMin, sytsMax, zxjdOb, sytsOb, jdbh);
            List<SjhyCwrzbVo> list = result.getList();
            if (CollectionUtils.isEmpty(list)) {
                throw new BusinessException("未查询到相关数据");
            }

//            获取本周一和周日时间
            LocalDate mondayDate = LocalDate.now().with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));
            LocalDate sundayDate = mondayDate.plusDays(6);
//            查询整改情况汇总
            CwzgZbQueryDto cwzgZbQueryDto = new CwzgZbQueryDto();
            cwzgZbQueryDto.setXzqhbm(xzqhbm);
            cwzgZbQueryDto.setYwlx(ywlx);

            //            插入本周时间检索
            cwzgZbQueryDto.setSjfwq(mondayDate.toString());
            cwzgZbQueryDto.setSjfwz(sundayDate.toString());

            RequestPageDto<CwzgZbQueryDto> cwzgZbQueryDtoRequestPageDto = new RequestPageDto<>();
            cwzgZbQueryDtoRequestPageDto.setDto(cwzgZbQueryDto);
            cwzgZbQueryDtoRequestPageDto.setPageNo(1);
            cwzgZbQueryDtoRequestPageDto.setPageSize(Integer.MAX_VALUE);
            Pagination<CwzgZbVo> cwzgZbPage = sjhyCwrzService.getCwzgZbPage(cwzgZbQueryDtoRequestPageDto);
            List<CwzgZbVo> list1 = cwzgZbPage.getList();

//            查询整改情况分析
            CwzgQksmQueryDto cwzgQksmQueryDto = new CwzgQksmQueryDto();
            cwzgQksmQueryDto.setXzqhbm(xzqhbm);
            cwzgQksmQueryDto.setYwlx(ywlx);
//            插入本周时间检索
            cwzgQksmQueryDto.setSjfwq(mondayDate.toString());
            cwzgQksmQueryDto.setSjfwz(sundayDate.toString());

            RequestPageDto<CwzgQksmQueryDto> cwzgQksmQueryDtoRequestPageDto = new RequestPageDto<>();
            cwzgQksmQueryDtoRequestPageDto.setDto(cwzgQksmQueryDto);
            cwzgQksmQueryDtoRequestPageDto.setPageNo(1);
            cwzgQksmQueryDtoRequestPageDto.setPageSize(Integer.MAX_VALUE);
            Pagination<CwzgQksmVo> cwzgQksmPage = sjhyCwrzService.getCwzgQksmPage(cwzgQksmQueryDtoRequestPageDto);
            List<CwzgQksmVo> list2 = cwzgQksmPage.getList();
            String xzqh="";
            if (StringUtils.isNotBlank(xzqhbm)) {
                xzqh = list.get(0).getXzqh();

            }
            ExcelUtil.exportExcel(response, list, list1, list2, xzqh + "核验结果错误表" + DateUtils.dateFormat(new Date(), "yyyyMMdd"));
            System.out.println(response);
        } catch (Exception e) {
            if (e instanceof BusinessException) {
                throw (BusinessException) e;
            }
            log.error("核验错误日志列表导出"+e);
            throw new BusinessException("【导出失败】" + e.getMessage());
        }

    }

3.配置表头对应实体类

dto1:

package com.dhcc.cjsjhy.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.converters.date.DateDateConverter;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.dhcc.cjsjhy.util.UnLockCell;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.util.Date;

/**
 * @Created by Mr.Zhang
 * @Date 2023/7/19
 * @Description XXXXX表 前端展示vo
 */
@Data
@HeadRowHeight(value = 40)
//@ContentRowHeight(value = 30)
@ColumnWidth(value = 18)
@ApiModel(description = "XXXXX表 前端展示vo")
@ExcelIgnoreUnannotated
public class SjhyCwrzbVo {


    @ExcelIgnore
    @ApiModelProperty(value = "行政区划", name = "xzqhbm", required = false)
    String xzqhbm;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "行政区划", index = -1, order = 1)
    @ApiModelProperty(value = "区划名称", name = "xzqh", required = false)
    String xzqh;

    @ApiModelProperty(value = "批次", name = "pc", required = false)
    String pc;

    @ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
    String ywlx;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "主分类", index = -1, order = 2)
    @ApiModelProperty(value = "业务类型名称", name = "name", required = false)
    String name;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ColumnWidth(value = 30)
    @ExcelProperty(value = "校验业务", index = -1, order = 3)
    @ApiModelProperty(value = "校验业务", name = "jyyw", required = false)
    String jyyw;

    @ColumnWidth(value = 30)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "错误描述", index = -1, order = 4)
    @ApiModelProperty(value = "错误描述", name = "cwms", required = false)
    String cwms;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ColumnWidth(value = 35)
    @ExcelProperty(value = "校验表名", index = -1, order = 5)
    @ApiModelProperty(value = "校验表名", name = "jybm", required = false)
    String jybm;

    @ApiModelProperty(value = "问题条数", name = "wtts", required = false)
    Integer wtts;

    @ColumnWidth(value = 25)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "校验时间", index = -1, order = 7)
    @ApiModelProperty(value = "校验时间", name = "jysj", required = false)
    @DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
    Date jysj;

    @ColumnWidth(value = 25)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "预计完成时间", index = -1, order = 8)
    @ApiModelProperty(value = "预计完成时间", name = "yjwcsj", required = false)
    @DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
    Date yjwcsj;

    @ColumnWidth(value = 10)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "最新问题条数", index = -1, order = 6)
    @ApiModelProperty(value = "最新问题条数", name = "zxwtts", required = false)
    Integer zxwtts;

    @ApiModelProperty(value = "进度变化", name = "jdbh", required = false)
    String jdbh;

    /**
     * 上次进度
     */
    @ApiModelProperty(value = "上次进度", name = "scjd", required = false)
    Integer scjd;

    /**
     * 最新进度
     */
    @ApiModelProperty(value = "最新进度", name = "zxjd", required = false)
    Integer zxjd;
    /**
     * 最新进度
     */
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "截至校验时间完成进度", index = -1, order = 12)
    @ApiModelProperty(value = "最新进度百分比", name = "zxjdbfb", required = false)
    String zxjdbfb;

    /**
     * 是否整改(地市提供)
     */
    @UnLockCell
    @ColumnWidth(value = 17)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "是否整改(地市提供)", index = -1, order = 13)
    @ApiModelProperty(value = "是否整改(地市提供)", name = "sfzgdstg", required = false)
    String sfzgdstg;
    /**
     * 最新预计完成时间(地市提供)
     */
    @ColumnWidth(value = 23)
    @UnLockCell
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN, dataFormat = 14)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "最新预计完成时间(地市提供)", index = -1, order = 14)
    @ApiModelProperty(value = "最新预计完成时间(地市提供)", name = "zxyjwcsjdstg", required = false)
    @DateTimeFormat(value = "yyyy-MM-dd")
    String zxyjwcsjdstg;

    Date zxyjwcsjdstgDate;
    /**
     * 备注(地市提供：不能进行整改的注明原因)
     */
    @ColumnWidth(value = 30)
    @UnLockCell
    @ContentStyle(fillBackgroundColor = 1,
            verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "备注(地市提供：不能进行整改的注明原因)", index = -1, order = 15)
    @ApiModelProperty(value = "备注(地市提供：不能进行整改的注明原因)", name = "bz", required = false)
    String bz;

    /**
     * 是否整改
     */
    @ColumnWidth(value = 14)
    @ExcelProperty(value = "是否整改", index = -1, order = 9)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "是否整改", name = "sfzg", required = false)
    String sfzg;

    /**
     * 是否整改
     */
    @ColumnWidth(value = 25)
    @ExcelProperty(value = "整改情况说明", index = -1, order = 10)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "整改情况说明", name = "zgqksm", required = false)
    String zgqksm;

    /**
     * 预计完成剩余天数
     */
    @ApiModelProperty(value = "预计完成剩余天数", name = "yjwcsyts", required = false)
    String yjwcsyts;

    /**
     * 周一进度
     */
    @ColumnWidth(value = 14)
    @ExcelProperty(value = "本周一完成进度", index = -1, order = 11)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "周一进度", name = "zyjd", required = false)
    String zyjd;


}

dto2:

package com.dhcc.cjsjhy.dto;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @Created by liuguodong
 * @Date 2023/6/25
 * @Description 错误整改情况说明
 */
@Data
@ExcelIgnoreUnannotated
@HeadRowHeight(value = 25)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 18)
@ApiModel(description = "整改情况分析，前端展示vo")
public class CwzgQksmVo {

    @ApiModelProperty(value = "id", name = "id", required = false)
    String id;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "行政区划", index = -1, order = 1)
    @ApiModelProperty(value = "行政区划名称", name = "xzqhName", required = false)
    String xzqhName;

    @ApiModelProperty(value = "行政区划编码", name = "xzqhbm", required = false)
    String xzqhbm;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "业务类型", index = -1, order = 2)
    @ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
    String ywlx;

    @ColumnWidth(value = 30)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "校验业务", index = -1, order = 3)
    @ApiModelProperty(value = "校验业务", name = "bmName", required = false)
    String bmName;

    @ColumnWidth(value = 30)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "错误描述", index = -1, order = 4)
    @ApiModelProperty(value = "错误描述", name = "cwms", required = false)
    String cwms;

    @ColumnWidth(value = 35)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "校验表名", index = -1, order = 5)
    @ApiModelProperty(value = "校验表名", name = "bm", required = false)
    String bm;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "日期", index = -1, order = 6)
    @ApiModelProperty(value = "日期", name = "rq", required = false)
    String rq;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "整改天数", index = -1, order = 7)
    @ApiModelProperty(value = "整改天数", name = "zgts", required = false)
    String zgts;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "最新问题条数", index = -1, order = 9)
    @ApiModelProperty(value = "最新问题条数", name = "zxts", required = false)
    String zxts;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "问题最大条数", index = -1, order = 8)
    @ApiModelProperty(value = "问题最大条数", name = "zdwtts", required = false)
    String zdts;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "上周日"}, index = -1, order = 10)
//    @ExcelProperty(value = "上周日进展", index = -1, order = 10)
    @ApiModelProperty(value = "上周日进展", name = "szrjz", required = false)
    String szrjz;

    @ApiModelProperty(value = "趋势变化", name = "qsbh", required = false)
    String qsbh;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周一"}, index = -1, order = 11)
//    @ExcelProperty(value = "周一进展", index = -1, order = 11)
    @ApiModelProperty(value = "周一进展", name = "w1", required = false)
    String w1;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周二"}, index = -1, order = 12)
//    @ExcelProperty(value = "周二进展", index = -1, order = 12)
    @ApiModelProperty(value = "周二进展", name = "w2", required = false)
    String w2;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周三"}, index = -1, order = 13)
//    @ExcelProperty(value = "周三进展", index = -1, order = 13)
    @ApiModelProperty(value = "周三进展", name = "w3", required = false)
    String w3;

    @ColumnWidth(value = 12)
    @ExcelProperty(value = {"周进度", "周四"}, index = -1, order = 14)
//    @ExcelProperty(value = "周四进展", index = -1, order = 14)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "周四进展", name = "w4", required = false)
    String w4;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周五"}, index = -1, order = 15)
//    @ExcelProperty(value = "周五进展", index = -1, order = 15)
    @ApiModelProperty(value = "周五进展", name = "w5", required = false)
    String w5;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周六"}, index = -1, order = 16)
//    @ExcelProperty(value = "周六进展", index = -1, order = 16)
    @ApiModelProperty(value = "周六进展", name = "w6", required = false)
    String w6;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"周进度", "周日"}, index = -1, order = 17)
//    @ExcelProperty(value = "周日进展", index = -1, order = 17)
    @ApiModelProperty(value = "周日进展", name = "w7", required = false)
    String w7;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"进展趋势", "本周进展"}, index = -1, order = 18)
//    @ExcelProperty(value = "本周进展", index = -1, order = 18)
    @ApiModelProperty(value = "本周进展", name = "bzjz", required = false)
    String bzjz;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = {"进展趋势", "较上周进展"}, index = -1, order = 19)
//    @ExcelProperty(value = "较上周进展", index = -1, order = 19)
    @ApiModelProperty(value = "较上周进展", name = "jszjz", required = false)
    String jszjz;

//    @ApiModelProperty(value = "本周进展", name = "bzzj", required = false)
//    String bzzj;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "是否能整改", index = -1, order = 20)
    @ApiModelProperty(value = "是否能整改", name = "sfnzg", required = false)
    String sfnzg;

    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "预计完成天数", index = -1, order = 21)
    @ApiModelProperty(value = "预计完成天数", name = "yjwcts", required = false)
    String yjwcts;

    @ExcelProperty(value = "整改完成", index = -1, order = 22)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "整改完成：1 是，0 否", name = "zgwc", required = false)
    String zgwc;

    @ColumnWidth(value = 35)
    @ExcelProperty(value = "整改情况说明", index = -1, order = 23)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "整改情况说明", name = "zgqksm", required = false)
    String zgqksm;


}

dto3:

package com.dhcc.cjsjhy.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @Created by liuguodong
 * @Date 2023/6/25
 * @Description 错误整改周报vo
 */
@Data
@HeadRowHeight(value = 25)
@ContentRowHeight(value = 25)
@ColumnWidth(value = 18)
@ApiModel(description = "整改情况汇总vo")
@ExcelIgnoreUnannotated
public class CwzgZbVo {

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "行政区划", index = -1, order = 1)
    @ApiModelProperty(value = "行政区划名称", name = "xzqhName", required = false)
    String xzqhName;

    @ApiModelProperty(value = "行政区划编码", name = "xzqhbm", required = false)
    String xzqhbm;

    @ColumnWidth(value = 15)
    @ExcelProperty(value = "业务类型", index = -1, order = 2)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "业务类型", name = "ywlx", required = false)
    String ywlx;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "日期", index = -1, order = 3)
    @ApiModelProperty(value = "日期", name = "rq", required = false)
    String rq;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "存在问题表个数", index = -1, order = 4)
    @ApiModelProperty(value = "存在问题表个数", name = "wtbgs", required = false)
    String wtbgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "问题最大条数", index = -1, order = 5)
    @ApiModelProperty(value = "问题最大条数总量", name = "zdts", required = false)
    String zdts;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "最新问题条数", index = -1, order = 6)
    @ApiModelProperty(value = "最新问题条数总量", name = "zxts", required = false)
    String zxts;

    @ColumnWidth(value = 20)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "整改中最新问题条数", index = -1, order = 7)
    @ApiModelProperty(value = "整改中最新问题条数总量", name = "zgzzxts", required = false)
    String zgzzxts;

    @ExcelProperty(value = "整体进度", index = -1, order = 8)
    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ApiModelProperty(value = "整体进度-字符", name = "jd", required = false)
    String jd;

    @ApiModelProperty(value = "整体进度- 数值", name = "jdnum", required = false)
    Integer jdnum;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
    @ExcelProperty(value = "较上周趋势变化", index = -1, order = 9)
    @ApiModelProperty(value = "较上周趋势变化", name = "qsbh", required = false)
    String qsbh;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "预计完成两周以上表数", index = -1, order = 22)
    @ExcelProperty(value = {"预计时间完成情况（表个数）", "两周以上"}, index = -1, order = 22)
    @ApiModelProperty(value = "预计两周以上完成的表的个数", name = "yjlzysgs")
    String yjlzysgs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "预计一周到两周内完成表个数", index = -1, order = 21)
    @ExcelProperty(value = {"预计时间完成情况（表个数）", "一到两周"}, index = -1, order = 21)
    @ApiModelProperty(value = "预计一周到两周内完成的表的个数", name = "yjlzwcgs")
    String yjlzwcgs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "预计一周内完成表个数", index = -1, order = 20)
    @ExcelProperty(value = {"预计时间完成情况（表个数）", "一周内"}, index = -1, order = 20)
    @ApiModelProperty(value = "预计一周内完成的表的个数", name = "yjyzwcgs")
    String yjyzwcgs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "预计完成超时表数", index = -1, order = 19)
    @ExcelProperty(value = {"预计时间完成情况（表个数）", "超时表数"}, index = -1, order = 19)
    @ApiModelProperty(value = "预计完成超时表的个数", name = "yjcsgs")
    String yjcsgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "本周进度上升表数", index = -1, order = 13)
    @ExcelProperty(value = {"本周进度情况（表个数）", "上升"}, index = -1, order = 13)
    @ApiModelProperty(value = "本周进度上升表个数", name = "bzjdssgs")
    String bzjdssgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "本周进度无变化表数", index = -1, order = 15)
    @ExcelProperty(value = {"本周进度情况（表个数）", "无变化"}, index = -1, order = 15)
    @ApiModelProperty(value = "本周进度(较周一)无变化表个数", name = "bzwbhgs")
    String bzwbhgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "本周进度下降表数", index = -1, order = 14)
    @ExcelProperty(value = {"本周进度情况（表个数）", "下降"}, index = -1, order = 14)
    @ApiModelProperty(value = "本周进度(较周一)下降表个数", name = "bzjdxjgs")
    String bzjdxjgs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "整改中的表个数", index = -1, order = 12)
    @ExcelProperty(value = {"整改情况（表个数）", "整改中"}, index = -1, order = 12)
    @ApiModelProperty(value = "当前整改中的表个数", name = "zgzgs")
    String zgzgs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "不能整改表个数", index = -1, order = 11)
    @ExcelProperty(value = {"整改情况（表个数）", "不能整改"}, index = -1, order = 11)
    @ApiModelProperty(value = "不能整改表个数 (当前所有不能整改表个数）", name = "bnzggs")
    String bnzggs;

    @ColumnWidth(value = 15)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "整改完成表个数", index = -1, order = 10)
    @ExcelProperty(value = {"整改情况（表个数）", "整改完成"}, index = -1, order = 10)
    @ApiModelProperty(value = "整改完成表个数 (本周）", name = "bzzgwcgs")
    String bzzgwcgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "较上周进度下降表个数", index = -1, order = 17)
    @ExcelProperty(value = {"较上周进度变化（表个数）", "下降"}, index = -1, order = 17)
    @ApiModelProperty(value = "较上周进度下降表个数", name = "jszjdxjgs")
    String jszjdxjgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "较上周进度上升表个数", index = -1, order = 16)
    @ExcelProperty(value = {"较上周进度变化（表个数）", "上升"}, index = -1, order = 16)
    @ApiModelProperty(value = "本周进度(较周一)上升表个数 ", name = "bzjdjzyssgs")
    String bzjdjzyssgs;

    @ColumnWidth(value = 12)
    @ContentStyle(fillBackgroundColor = 1, verticalAlignment = VerticalAlignmentEnum.CENTER, horizontalAlignment = HorizontalAlignmentEnum.LEFT, wrapped = BooleanEnum.TRUE, borderLeft = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
    @ContentFontStyle(fontName = "宋体")
//    @ExcelProperty(value = "较上周进度无变化表个数", index = -1, order = 18)
    @ExcelProperty(value = {"较上周进度变化（表个数）", "无变化"}, index = -1, order = 18)
    @ApiModelProperty(value = "较上周进度无变化表个数", name = "jszjdwbhgs")
    String jszjdwbhgs;


    @ApiModelProperty(value = "是否空数据、补全数据：1 是， 0 否", name = "sfksj", required = false)
    String sfksj;


}

4.导出工具类编写

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.dhcc.basic.util.DateUtils;
import com.dhcc.cjsjhy.dto.CwzgQksmVo;
import com.dhcc.cjsjhy.dto.CwzgZbVo;
import com.dhcc.cjsjhy.dto.SjhyCwrzbVo;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * excel工具类
 */
public class ExcelUtil {
    
    public static void exportExcel(HttpServletResponse response, List<SjhyCwrzbVo> dtos, List<CwzgZbVo> list1, List<CwzgQksmVo> list2, String fileName) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            //使用URLEncoder来防止文件名乱码或者读取错误
            //Access-Control-Expose-Headers 将fileName暴漏在请求头里，不然只能看到前端获取不到
            response.setHeader("Access-Control-Expose-Headers", "filename");
            response.setHeader("Access-Control-Expose-Headers", "Content-disposition");
            response.setHeader("filename",  fileName + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
/*            EasyExcel.write(response.getOutputStream(), SjhyCwrzbVo.class)
//                    .head(header)
                    .registerWriteHandler(new CellHandler())
                    .registerWriteHandler(new StyleWriteHandler())
                    .sheet("核验错误结果表")
                    .doWrite(dtos);*/

            ServletOutputStream outputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
            //写入核验错误日志 sheet
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "核验错误结果表")
                    .head(SjhyCwrzbVo.class)
                    .registerWriteHandler(new LockSheetWriteHandler())
                    .registerWriteHandler(new UnLockCellHandler())
                    .registerWriteHandler(new CompleteDateHandler())
                    .registerWriteHandler(new CellRangeHandler()).build();
            excelWriter.write(dtos, writeSheet);

            if (CollectionUtils.isNotEmpty(list2)) {
                //写入整改情况分析 sheet
                WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "整改情况分析")
                        .head(CwzgQksmVo.class)
                        .registerWriteHandler(new LockSheetWriteHandler())
                        .registerWriteHandler(new UnLockCellHandler())
                        .registerWriteHandler(new ZgqkUpDownHandler()).build();
                excelWriter.write(list2, writeSheet2);
            }

            if (CollectionUtils.isNotEmpty(list1)) {
                //写入整改情况汇总 sheet
                WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "整改情况汇总")
                        .head(CwzgZbVo.class)
                        .registerWriteHandler(new LockSheetWriteHandler())
                        .registerWriteHandler(new UnLockCellHandler())
                        .registerWriteHandler(new WtclqkUpDownHandler())
                        .build();
                excelWriter.write(list1, writeSheet1);
            }

            //关闭流
            excelWriter.finish();
            outputStream.flush();
        } catch (Exception e) {
            throw e;
        }


    }

//    @Transactional
//    public static void importExcel(MultipartFile file) throws Exception{
//
//    }

    public static void main(String[] args) {
        String pathname = "D:/2.xlsx";
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"1", "表头2", "表头3", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "表头4", "  "})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"2", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"3", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"4", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"5", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"6", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"7", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"8", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"9", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"10", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"11", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"12", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"13", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new String[]{"14", "表头2", "表头3", "表头4"})));

        List<List<String>> header = new ArrayList<>(10);
        header.add(new ArrayList<>(Arrays.asList(new String[]{"区划"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"批次"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"数据集名称"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"表名称"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"表名"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"问题描述"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"问题数量"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"校验时间"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"是否整改"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"本周一完成进度"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"截至校验时间完成进度"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"是否整改（地市提供）"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"最新预计完成时间（地市提供）"})));
        header.add(new ArrayList<>(Arrays.asList(new String[]{"备注（地市提供：不能进行整改的注明原因）"})));


        EasyExcel.write(new File(pathname))
                .head(header)
                .registerWriteHandler(new CellHandler())
                .registerWriteHandler(new StyleWriteHandler())
                .sheet("Sheet1")
                .doWrite(dataList);
    }


}

5.导出Handler编写

使用EasyExcel导出，需求是对导出的excel进行锁定，并实现对指定列解锁。
新建一个handler包，并在该包下新建一个LockSheetWriteHandler类，继承SheetWriteHandler类，泛型设置为当前需要导出的类：

1、编写UnLockCell注解

作用于需要解锁的实体类字段上

package com.dhcc.cjsjhy.util;

/**
 * @ClassName UnLockCell
 * @Description TODO
 * @Author Mr.Zhang
 * @Date 2023/7/20 23:03
 * @Version 1.0
 */

import java.lang.annotation.*;

/**
 * 用于标记锁定哪些列不需要锁定
 */
@Target(value = {ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UnLockCell {


}

2、LockSheetWriteHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;

/**
 * @ClassName LockSheetWriteHandler
 * @Description 锁定文档handler
 * @Author Mr.Zhang
 * @Date 2023/7/20 23:11
 * @Version 1.0
 */
public class LockSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //锁定工作簿，设置保护密码
        sheet.protectSheet("1qaz!QAZ");
        // 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)
        ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);
    }

}

3、UnLockCellHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellUtil;
import org.elasticsearch.common.collect.HppcMaps;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName UnLockCellHandler
 * @Description 解锁用了@UnLockCell注解的字段，handler
 * @Author Mr.Zhang
 * @Date 2023/7/20 23:00
 * @Version 1.0
 */
public class UnLockCellHandler implements CellWriteHandler {

    private static final String PASSWORD = "1qaz!QAZ";

    /**
     * 在创建单元格之前调用
     * The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param row
     * @param head
     * @param columnIndex
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }


    /**
     * 在创建单元格后调用
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cell             * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }




    /**
     * 在完成对单元格的所有操作后调用
     *
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellDataList
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        String fieldName = head.getFieldName();
        Class clazz = writeSheetHolder.getClazz();
        Field[] fields = clazz.getDeclaredFields();
        Lists.newArrayList(fields).stream().filter(field -> field.isAnnotationPresent(UnLockCell.class)).forEach(field -> {
            if (field.getName().equals(fieldName)) {
                if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {

        /*            //可以写的模式
                    CellStyle unLockCellStyle = cell.getSheet().getWorkbook().createCellStyle();
                    unLockCellStyle.setLocked(false);
                    cell.setCellStyle(unLockCellStyle);*/

                    Map<String, Object> properties = new HashMap<>(1);
                    properties.put(CellUtil.LOCKED, false);
                    CellUtil.setCellStyleProperties(cell, properties);

                }
            }
        });
        /*for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelIgnore.class)){
                continue;
            }
            if (field.getName().equals(fieldName)) {
                if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {
                    Map<String, Object> properties = new HashMap<>(1);
                    properties.put(CellUtil.LOCKED, false);
                    CellUtil.setCellStyleProperties(cell, properties);
                }
            }
        }*/
    }

    @Override
    public int order() {
        return Integer.MAX_VALUE;
    }
}

4、CompleteDateHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.commons.lang.time.DateUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * @ClassName CompleteDateHandler
 * @Description 对导出的指定列进行处理，设置颜色，填充值等
 * @Author Mr.Zhang
 * @Date 2023/7/22 22:56
 * @Version 1.0
 */
public class CompleteDateHandler implements CellWriteHandler {

    private static final Logger log = LoggerFactory.getLogger(CompleteDateHandler.class);
    private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static boolean isYjwcsjHeadAppeared = false;
/*
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if ("yjwcsj".equals(head.getFieldName())) {
            LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
            if (null != yjwcsjLocalDateTime) {
                boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
                if (after) {
                    CellStyle cellStyle = cell.getCellStyle();
                    XSSFFont xssfFont = new XSSFFont();
                    xssfFont.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(xssfFont);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }*/

    @Override
    public void afterCellDataConverted(CellWriteHandlerContext context) {
        Head head = context.getHeadData();
        List<WriteCellData<?>> cellDataList = context.getCellDataList();
        Cell cell = context.getCell();
        if ("yjwcsj".equals(head.getFieldName())) {
            int rowIndex = cell.getRowIndex();
            if (rowIndex > 0) {
                WriteCellData<?> cellData = cellDataList.get(0);
                LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
                if (null != yjwcsjLocalDateTime) {
                    boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
                    if (after) {
                        // 设置字体颜色
                        WriteFont writeFont = new WriteFont();
                        writeFont.setColor(IndexedColors.WHITE.getIndex());

                        WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
                        writeCellStyle.setWriteFont(writeFont);
//                        设置背景颜色
                        writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                        //必须设置 否则背景色不生效
                        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                        cellData.setWriteCellStyle(writeCellStyle);
                    }
                }
            }
        }
        else if ("sfzg".equals(head.getFieldName())) {
            int rowIndex = cell.getRowIndex();
            if (rowIndex > 0) {
                WriteCellData<?> cellData = cellDataList.get(0);
                String stringValue = cellData.getStringValue();
                if (StringUtils.isNotBlank(stringValue)  ) {
                    if ("否".equals(stringValue)){
                        // 设置字体颜色
                        WriteFont writeFont = new WriteFont();
                        writeFont.setColor(IndexedColors.WHITE.getIndex());
                        WriteCellStyle writeCellStyle = new WriteCellStyle();
                        writeCellStyle.setWriteFont(writeFont);
//                        设置背景颜色
                        writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                        //必须设置 否则背景色不生效
                        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                        cellData.setWriteCellStyle(writeCellStyle);

                    }
                }
            }
        }
    }
}

5、CellRangeHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.Arrays;
import java.util.Optional;

/**
 * @ClassName CellRangeHandler
 * @Description 设置指定列导出值为选择下拉
 * @Author Mr.Zhang
 * @Date 2023/7/23 1:12
 * @Version 1.0
 */
public class CellRangeHandler implements CellWriteHandler {
    @Override
    public void afterCellDataConverted(CellWriteHandlerContext context) {
        Integer rowIndex = Optional.ofNullable(context.getCell()).map(Cell::getRowIndex).orElse(null);
        if (null != rowIndex) {
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowIndex, rowIndex, 12, 12);
            DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"是", "否"});
            context.getWriteSheetHolder().getSheet().addValidationData(helper.createValidation(constraint, cellRangeAddressList));
        }
    }
}

6、ZgqkUpDownHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
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 org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @ClassName CompleteDateHandler
 * @Description TODO 整改情况 本周进展和较上周进展根据值设置 上升、下降、无变化
 * @Author Mr.Zhang
 * @Date 2023/7/22 22:56
 * @Version 1.0
 */
public class ZgqkUpDownHandler implements CellWriteHandler {

    private static final Logger log = LoggerFactory.getLogger(ZgqkUpDownHandler.class);
    private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static boolean isYjwcsjHeadAppeared = false;
/*
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if ("yjwcsj".equals(head.getFieldName())) {
            LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
            if (null != yjwcsjLocalDateTime) {
                boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
                if (after) {
                    CellStyle cellStyle = cell.getCellStyle();
                    XSSFFont xssfFont = new XSSFFont();
                    xssfFont.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(xssfFont);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }*/

    @Override
    public void afterCellDataConverted(CellWriteHandlerContext context) {
        Head head = context.getHeadData();
        List<WriteCellData<?>> cellDataList = context.getCellDataList();
        Cell cell = context.getCell();
        if ("bzjz".equals(head.getFieldName()) || "jszjz".equals(head.getFieldName())) {
            int rowIndex = cell.getRowIndex();
            if (rowIndex > 0) {
                WriteCellData<?> cellData = cellDataList.get(0);
                String stringValue = cellData.getStringValue();
                if (StringUtils.isNotBlank(stringValue)) {
                    switch (stringValue) {
                        case "1":
                            cellData.setStringValue("无变化");
                            break;
                        case "2":
                            cellData.setStringValue("上升");
                            break;
                        case "3":
                            cellData.setStringValue("下降");
                            break;
                    }
                }
            }
        }

    }
}

7、WtclqkUpDownHandler

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @ClassName CompleteDateHandler
 * @Description TODO 整改情况 本周进展和较上周进展根据值设置 上升、下降、无变化
 * @Author Mr.Zhang
 * @Date 2023/7/22 22:56
 * @Version 1.0
 */
public class WtclqkUpDownHandler implements CellWriteHandler {

    private static final Logger log = LoggerFactory.getLogger(WtclqkUpDownHandler.class);
    private static final SimpleDateFormat SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static boolean isYjwcsjHeadAppeared = false;
/*
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if ("yjwcsj".equals(head.getFieldName())) {
            LocalDateTime yjwcsjLocalDateTime = cellData.getDateValue();
            if (null != yjwcsjLocalDateTime) {
                boolean after = LocalDateTime.now().plusDays(5).isAfter(yjwcsjLocalDateTime);
                if (after) {
                    CellStyle cellStyle = cell.getCellStyle();
                    XSSFFont xssfFont = new XSSFFont();
                    xssfFont.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(xssfFont);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }*/

    @Override
    public void afterCellDataConverted(CellWriteHandlerContext context) {
        Head head = context.getHeadData();
        List<WriteCellData<?>> cellDataList = context.getCellDataList();
        Cell cell = context.getCell();
        if ("qsbh".equals(head.getFieldName())) {
            int rowIndex = cell.getRowIndex();
            if (rowIndex > 0) {
                WriteCellData<?> cellData = cellDataList.get(0);
                String stringValue = cellData.getStringValue();
                if (StringUtils.isNotBlank(stringValue)) {
                    switch (stringValue) {
                        case "1":
                            cellData.setStringValue("无变化");
                            break;
                        case "2":
                            cellData.setStringValue("上升");
                            break;
                        case "3":
                            cellData.setStringValue("下降");
                            break;
                    }
                }
            }
        }

    }
}

6、导入监听器编写

使用EasyExcel需要进行全局监听配置
新建一个listener包，并在该包下新建一个ExcelListener类，继承AnalysisEventListener类，泛型设置为当前需要导出的类，我这里需要导出的是XX数据，因此我将泛型设置为XX类，整体代码如下：

package com.dhcc.cjsjhy.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.dhcc.basic.dao.query.Operator;
import com.dhcc.basic.dao.query.SimpleCondition;
import com.dhcc.basic.exception.BusinessException;
import com.dhcc.basic.util.DateUtils;
import com.dhcc.basic.util.Uuid;
import com.dhcc.cjsjhy.constants.SysConstants;
import com.dhcc.cjsjhy.dto.SjhyCwrzbVo;
import com.dhcc.cjsjhy.enums.SjhyZgjlLsyyEnum;
import com.dhcc.cjsjhy.model.SjhyZgjl;
import com.dhcc.cjsjhy.model.Xzqh;
import com.dhcc.cjsjhy.service.SjhyCwrzService;
import com.dhcc.cjsjhy.service.SjhyZgjlService;
import com.dhcc.cjsjhy.service.XzqhService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName ExcelListener
 * @Description 导入对数据进行处理
 * @Author Mr.Zhang
 * @Date 2023/7/28 9:28
 * @Version 1.0
 */
// 有个很重要的点 ExcelListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExcelListener extends AnalysisEventListener<SjhyCwrzbVo> {


    private List<SjhyCwrzbVo> list = new ArrayList<>();
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private SjhyCwrzService sjhyCwrzService;
    private SjhyZgjlService sjhyZgjlService;
    private XzqhService xzqhService;
    private CJsjhyRedisUtil cjsjhyRedisUtil;

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public ExcelListener(SjhyCwrzService sjhyCwrzService, SjhyZgjlService sjhyZgjlService, XzqhService xzqhService, CJsjhyRedisUtil cjsjhyRedisUtil) {
        this.sjhyCwrzService = sjhyCwrzService;
        this.sjhyZgjlService = sjhyZgjlService;
        this.xzqhService = xzqhService;
        this.cjsjhyRedisUtil = cjsjhyRedisUtil;
    }

    int i = 2;

    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    @Transactional
    public void invoke(SjhyCwrzbVo sjhyCwrzbVo, AnalysisContext analysisContext) {
        System.out.println("解析到第" + i + "条数据:========================" + sjhyCwrzbVo.toString());
        // 数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        if (null != sjhyCwrzbVo.getSfzgdstg() || null != sjhyCwrzbVo.getZxyjwcsjdstg() || null != sjhyCwrzbVo.getBz()) {
            if (null == sjhyCwrzbVo.getSfzgdstg() || !("否".equals(sjhyCwrzbVo.getSfzgdstg()) || "是".equals(sjhyCwrzbVo.getSfzgdstg()))) {
                throw new BusinessException("第" + i + "行数据，是否整改(地市提供)填写有问题，不能为空,且只能填写 “是” 或 “否”，请检查。");
            }
        }
        if (null != sjhyCwrzbVo.getSfzgdstg()) {
            if ("是".equals(sjhyCwrzbVo.getSfzgdstg())) {
                if (null == sjhyCwrzbVo.getZxyjwcsjdstg() || !sjhyCwrzbVo.getZxyjwcsjdstg().contains("-")) {
                    throw new BusinessException("第" + i + "行数据，最新预计完成时间(地市提供)不能为空或者格式错误，格式为：yyyy/MM/dd，请检查。");
                }
                Date parseDate = TimeUtils.stringToDate(sjhyCwrzbVo.getZxyjwcsjdstg(), TimeUtils.DateFormat.ONLY_DAY);
                sjhyCwrzbVo.setZxyjwcsjdstgDate(parseDate);
                if (sjhyCwrzbVo.getZxyjwcsjdstgDate().before(new Date())) {
                    throw new BusinessException("第" + i + "行数据，最新预计完成时间(地市提供)不能小于当前时间，格式为：yyyy/MM/dd，请检查。");
                }
                list.add(sjhyCwrzbVo);
            } else if ("否".equals(sjhyCwrzbVo.getSfzgdstg())) {
                if (StringUtils.isBlank(sjhyCwrzbVo.getBz())) {
                    throw new BusinessException("第" + i + "行数据，备注(地市提供)不能为空，请检查。");
                }
                list.add(sjhyCwrzbVo);
            }

//            list.add(sjhyCwrzbVo);
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理datas
            list.clear();
        }
        i++;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    @Transactional
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }



    /**
     * 加上存储数据库
     */
    @Transactional
    public void saveData() {
        System.out.println("==============================" + list.size() + "条数据，开始存储到数据库");
        for (SjhyCwrzbVo sjhyCwrzbVo : list) {
            // 数据存储到datas，供批量处理，或后续自己业务逻辑处理。
            if (null != sjhyCwrzbVo.getSfzgdstg()) {
                if ("是".equals(sjhyCwrzbVo.getSfzgdstg())) {
                    Xzqh xzqh = xzqhService.findCodeByName(sjhyCwrzbVo.getXzqh());

                    SjhyZgjl one = sjhyZgjlService.findOne(new SimpleCondition()
                            .addParm("bm", sjhyCwrzbVo.getJybm())
                            .addParm("XZQHBM", xzqh.getCode())
                            .begin()
                            .addParm("lSYY", Operator.notEqual, SjhyZgjlLsyyEnum.SC.getValue())
                            .or()
                            .addParm("lSYY", Operator.isNull, null)
                            .end().setOrderBy("CJSJ desc"));
                    if (null != one && null == one.getSjwcsj()) {
                        if (SysConstants.LSBZ_F.equals(one.getLsbz())) {
//                    修改以前的整改记录
                            one.setLsbz(SysConstants.LSBZ_S);
                            one.setLsyy(SjhyZgjlLsyyEnum.GX.getValue());
                            one.setXgsj(new Date());
                            sjhyZgjlService.update(one);
                        }
//                    新增一条整改记录
//                    新建一个对象
                        SjhyZgjl sjhyZgjl = new SjhyZgjl();
                        BeanUtils.copyProperties(one, sjhyZgjl);
                        sjhyZgjl.setId(Uuid.GenUuid());
                        sjhyZgjl.setSfnzg(SysConstants.SFYX_S);
                        sjhyZgjl.setLsbz(SysConstants.LSBZ_F);
                        sjhyZgjl.setLsyy(null);
                        sjhyZgjl.setYjkssj(null == one.getYjkssj() ? new Date() : one.getYjkssj());
                        sjhyZgjl.setYjwcsj(sjhyCwrzbVo.getZxyjwcsjdstgDate());
                        sjhyZgjl.setCjsj(new Date());
                        sjhyZgjl.setXgsj(new Date());
                        if (null != sjhyCwrzbVo.getBz()) {
                            sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
                        }
                        sjhyZgjlService.save(sjhyZgjl);

                    } else {
//                    如果没有以前的整改记录则直接新增一条整改记录
                        SjhyZgjl sjhyZgjl = new SjhyZgjl();
                        sjhyZgjl.setId(Uuid.GenUuid());
                        sjhyZgjl.setBm(sjhyCwrzbVo.getJybm());
                        // 周期code
                        sjhyZgjl.setCode(cjsjhyRedisUtil.getCurrentDayNo(SysConstants.SJHY_ZGJL_CODE_KEY, DateUtils.dateFormat(new Date(), "yyyyMMdd")));
                        sjhyZgjl.setXzqhbm(xzqh.getCode());
                        sjhyZgjl.setSfnzg(SysConstants.SFYX_S);
                        sjhyZgjl.setYjkssj(new Date());
                        sjhyZgjl.setYjwcsj(sjhyCwrzbVo.getZxyjwcsjdstgDate());
                        sjhyZgjl.setCjsj(new Date());
                        sjhyZgjl.setXgsj(new Date());
                        if (null != sjhyCwrzbVo.getBz()) {
                            sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
                        }
                        sjhyZgjl.setLsbz(SysConstants.LSBZ_F);
                        sjhyZgjlService.save(sjhyZgjl);
                    }

                } else if ("否".equals(sjhyCwrzbVo.getSfzgdstg())) {
                    Xzqh xzqh = xzqhService.findCodeByName(sjhyCwrzbVo.getXzqh());
                    SjhyZgjl one = sjhyZgjlService.findOne(new SimpleCondition()
                            .addParm("bm", sjhyCwrzbVo.getJybm())
                            .addParm("XZQHBM", xzqh.getCode())
                            .begin()
                            .addParm("lSYY", Operator.notEqual, SjhyZgjlLsyyEnum.SC.getValue())
                            .or()
                            .addParm("lSYY", Operator.isNull, null)
                            .end()
                            .setOrderBy("CJSJ desc"));
                    if (null != one && null == one.getSjwcsj()) {
                        if (SysConstants.LSBZ_F.equals(one.getLsbz())) {
//                    修改以前的整改记录
                            one.setLsbz(SysConstants.LSBZ_S);
                            one.setLsyy(SjhyZgjlLsyyEnum.GX.getValue());
                            one.setXgsj(new Date());
                            sjhyZgjlService.update(one);
                        }
//                    新增一条整改记录
//                    新建一个对象
                        SjhyZgjl sjhyZgjl = new SjhyZgjl();
                        BeanUtils.copyProperties(one, sjhyZgjl);
                        sjhyZgjl.setId(Uuid.GenUuid());
                        sjhyZgjl.setSfnzg(SysConstants.SFYX_F);
                        sjhyZgjl.setLsbz(SysConstants.LSBZ_S);
                        sjhyZgjl.setLsyy(SjhyZgjlLsyyEnum.BZG.getValue());
                        sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
                        sjhyZgjl.setCjsj(new Date());
                        sjhyZgjl.setXgsj(new Date());
                        sjhyZgjlService.save(sjhyZgjl);
                    } else {
                        //                    如果没有以前的整改记录则直接新增一条整改记录
                        SjhyZgjl sjhyZgjl = new SjhyZgjl();
                        sjhyZgjl.setId(Uuid.GenUuid());
                        sjhyZgjl.setBm(sjhyCwrzbVo.getJybm());
                        // 周期code
                        sjhyZgjl.setCode(cjsjhyRedisUtil.getCurrentDayNo(SysConstants.SJHY_ZGJL_CODE_KEY, DateUtils.dateFormat(new Date(), "yyyyMMdd")));
                        sjhyZgjl.setXzqhbm(xzqh.getCode());
                        sjhyZgjl.setSfnzg(SysConstants.SFYX_F);
                        sjhyZgjl.setCjsj(new Date());
                        sjhyZgjl.setXgsj(new Date());
                        sjhyZgjl.setQksm(sjhyCwrzbVo.getBz());
                        sjhyZgjl.setLsbz(SysConstants.LSBZ_S);
                        sjhyZgjl.setLsyy(SjhyZgjlLsyyEnum.BZG.getValue());
                        sjhyZgjlService.save(sjhyZgjl);
                    }
                }
            }
        }
    }
}

总结

EasyExcel是一个excel导入导出的解决方案，非常好用，其它的功能查看官方API文档。