EasyExcel生成的excel指定行设置背景色（2.2.3版本EasyExcel）

 网上找了些资料然后结合自己的实践，将这个功能实现了出来，直接上代码

• service层的部分代码如下

// 需要修改底色的行下标 start  ----- 
        List<Integer> backGroundIndex = new ArrayList<>(); // 存放需要修改背景色的行的下标
        backGroundIndex.add(1); // 设置excel中第二行的背景色
		
        // 自定义策略
        Set<Integer> yellowRowsSet = new HashSet<>(backGroundIndex);
        QualificationsDeclareCellWriteHandler customCellWriteHandler = new QualificationsDeclareCellWriteHandler(yellowRowsSet);

        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("pxqz");// excel中不导出 pxqz 这个字段

        // 需要修改底色的行下标 end  ----- 
        try {
            fileName = URLEncoder.encode(fileName, "utf-8");
            response.addHeader("Access-Control-Allow-Origin", "*");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            response.setContentType("application/octet-stream; charset=UTF-8");
            EasyExcel.write(response.getOutputStream(), BatchScoreVO.class)
                    // 自定义背景颜色策略
                    .registerWriteHandler(customCellWriteHandler)
                    .sheet("背景色标记").doWrite(resultList);
        } catch (IOException e) {
            e.printStackTrace();
        }

• QualificationsDeclareCellWriteHandler工具类代码如下，通过覆写afterCellDispose方法给行标记背景色

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.BooleanUtils;
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.alibaba.excel.write.metadata.style.WriteCellStyle;
import org.apache.poi.ss.usermodel.*;

import java.util.List;
import java.util.Set;

public class QualificationsDeclareCellWriteHandler implements CellWriteHandler{

    /**
     * 标背景色行下标集合
     */
    private final Set<Integer> colorRowIndex;

    public QualificationsDeclareCellWriteHandler(Set<Integer> colorRowIndex) {
        this.colorRowIndex = colorRowIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 源码context中row待优化：支持入参 获取需要字段的出现次数 从而设置背景颜色
        // to do：（row.getCell(columnIndex)失效）
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // empty
    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    /**
     * @Description
     * @author       db
     * @param        writeSheetHolder, writeTableHolder, list, cell, head,
     *               relativeRowIndex：下标，从0开始（不包括顶行，即0是从第二行开始）,
     *               isHead：是否是顶部第一行
     * @return       void
     * @exception
     * @date         2024/4/9 18:32
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//        CellStyle cellStyle = cell.getCellStyle();
        if (!isHead && cell.getColumnIndex() <= 8 && colorRowIndex.contains(relativeRowIndex)) {
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            Font cellFont = workbook.createFont();
            cellFont.setFontName("宋体");
            cellStyle.setFont(cellFont);
            cellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
            cellStyle.setFillPattern(FillPatternType.FINE_DOTS);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            cell.setCellStyle(cellStyle);
        }

    }
}

• 实现效果如下，这里我只给了第三行赋背景色（即除了顶行的数据行的第二行）