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

需求

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

  2、扩展支持cellStyle标记单元格超过64000

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;//保存单元格样式，否则cellStyle被创建超过64000就会报错
Map<String,CellStyle> cellStyleMap = new HashMap<>();

public ExcelBackgroudHandler(Short colorIndex, HashMap<Integer, String> rowColMap) {this.colorIndex = colorIndex;this.rowColMap = rowColMap;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic 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**/@Overridepublic 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();if (null != rowColMap && rowColMap.get(rowIndex)!=null && rowColMap.get(rowIndex).contains(columIndex+"")) {Sheet sheet = writeSheetHolder.getSheet();Workbook workbook = sheet.getWorkbook();CellStyle cellStyle;String key = colorIndex+"";if(cellStyleMap.get(key )!=null){cellStyle = cellStyleMap.get(key );}else{cellStyle = workbook.createCellStyle();cellStyle.setFillForegroundColor(colorIndex);// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUNDcellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyleMap.put(key ,cellStyle);}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;}

测试类

/*** 测试导出模板* 1. 标题指定某列标红色字段* 2. 标题指定某列加批注*/
public static void main(String[] args) throws FileNotFoundException {String filePahth = "D:\\1.xlsx";// 输出流OutputStream outputStream = new FileOutputStream(new File(filePahth));// 导出的数据List<TestVO> dataList = new ArrayList<>();for(int i=0;i<35000;i++){TestVO testVO = new TestVO();testVO.setAge(11);testVO.setName("测试dd"+i);testVO.setSchool("学校"+i);TestVO testVO1 = new TestVO();testVO1.setAge(111);testVO1.setName("测试1"+i);testVO1.setSchool("学校1"+i);dataList.add(testVO);dataList.add(testVO1);}// 指定批注HashMap<Integer, String> annotationsMap = new HashMap<>();for(int i=0;i<70000;i++){annotationsMap.put(i,"1,2");}ExcelBackgroudHandler excelBackgroudHandler = new ExcelBackgroudHandler(IndexedColors.RED.index,annotationsMap);WriteSheet writeSheet = EasyExcel.writerSheet(1, "测试").registerWriteHandler(excelBackgroudHandler).head(TestVO.class).build();WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "测试2").registerWriteHandler(excelBackgroudHandler).head(TestVO.class).build();ExcelWriter excelWriter = EasyExcel.write(outputStream).build();excelWriter.write(dataList,writeSheet);excelWriter.write(dataList,writeSheet2);excelWriter.finish();//excel追加导出String newFilePath = "D:\\1temp.xlsx";ExcelWriter excelWriter1 = EasyExcel.write(newFilePath).withTemplate(filePahth).build();WriteSheet writeSheet1 = EasyExcel.writerSheet(2, "测试1") .head(TestVO.class).build();excelWriter1.write(dataList,writeSheet1);excelWriter1.finish();File tempFile = new File(newFilePath);if (tempFile.exists()) {File file = new File(filePahth);file.delete();tempFile.renameTo(file);}}