easyexcel指定sheet页动态给行列加背景色

本文介绍: easyexc el，有多个sheet页，某些sheet页的行、列动态需要加背景色

easyexcel，有多个sheet页，某些sheet页的行、列动态需要加背景色

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.util.HashMap;
import java.util.List;

@Slf4j
public class ExcelBackgroudHandler implements CellWriteHandler {


    //颜色
    private Short colorIndex;
    //行，以及对应的列，多个列逗号拼接
    private HashMap<Integer,String> rowColMap;

    public ExcelBackgroudHandler(Short colorIndex, HashMap<Integer, String> rowColMap) {
        this.colorIndex = colorIndex;
        this.rowColMap = rowColMap;
    }


    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

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

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

    }

    /***
     * 指定行列加颜色
     * @param writeSheetHolder
     * @param writeTableHolder
     * @param cellDataList
     * @param cell
     * @param head
     * @param relativeRowIndex
     * @param isHead

     * @Date: 2023/11/22 17:02
    **/
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head,
                                 Integer relativeRowIndex, Boolean isHead) {
        int columIndex = cell.getColumnIndex();
        int rowIndex = cell.getRowIndex();
        Sheet sheet = writeSheetHolder.getSheet();
        Workbook workbook = sheet.getWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        if (null != rowColMap && rowColMap.get(rowIndex)!=null && rowColMap.get(rowIndex).contains(columIndex+"")) {
            cellStyle.setFillForegroundColor(colorIndex);
            // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle );
        }
    }



}

@Data
public class TestVO {


    @ExcelProperty(value = "姓名", index = 0)
    private String name;


    @ExcelProperty(value = "年龄", index = 1)
    private int age;

    @ExcelProperty(value = "学校", index = 2)
    private String school;


}

测试类

public class TestEasyExcel {


    /**
     * 测试导出模板
     * 1. 标题指定某列标红色字段
     * 2. 标题指定某列加批注
     */
    public static void main(String[] args) throws FileNotFoundException {
        // 输出流
        OutputStream outputStream = new FileOutputStream(new File("D:\12.xlsx"));
        // 导出的数据
        List<TestVO> dataList = new ArrayList<>();
        TestVO testVO = new TestVO();
        testVO.setAge(11);
        testVO.setName("测试dd");
        testVO.setSchool("学校");
        TestVO testVO1 = new TestVO();
        testVO1.setAge(111);
        testVO1.setName("测试1");
        testVO1.setSchool("学校1");
        dataList.add(testVO);
        dataList.add(testVO1);
        // 指定行列
        HashMap<Integer, String> rowColMap = new HashMap<>();
        rowColMap.put(1,"1,2");
        rowColMap.put(2,"2");
        ExcelBackgroudHandler excelBackgroudHandler = new ExcelBackgroudHandler(IndexedColors.RED.index,rowColMap);
        WriteSheet writeSheet = EasyExcel.writerSheet(1, "测试")
                .registerWriteHandler(excelBackgroudHandler).head(TestVO.class).build();
        WriteSheet writeSheet1 = EasyExcel.writerSheet(2, "测试1") .head(TestVO.class).build();
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        excelWriter.write(dataList,writeSheet);
        excelWriter.write(dataList,writeSheet1);
        excelWriter.finish();
    }

}