需求
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; } @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(); if (null != rowColMap && rowColMap.get(rowIndex)!=null && Arrays.asList(rowColMap.get(rowIndex).split(",")).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_FOREGROUND cellStyle.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; @ExcelProperty(value = "姓名1", index = 3) private String nameAx; @ExcelProperty(value = "年龄2", index = 4) private int ageAx; @ExcelProperty(value = "学校3", index = 5) private String schoolAx; @ExcelProperty(value = "姓名4", index = 6) private String nameBx; @ExcelProperty(value = "年龄5", index = 7) private int ageBx; @ExcelProperty(value = "学校6", index = 8) private String schoolBx; @ExcelProperty(value = "姓名7", index = 9) private String nameCx; @ExcelProperty(value = "年龄8", index = 10) private int ageCx; @ExcelProperty(value = "学校9", index = 11) private String schoolCx; @ExcelProperty(value = "姓名10", index = 12) private String nameDx; @ExcelProperty(value = "年龄11", index = 13) private int ageDx; @ExcelProperty(value = "学校12", index = 14) private String schoolDx; @ExcelProperty(value = "姓名13", index = 15) private String nameEx; @ExcelProperty(value = "年龄14", index = 16) private int ageEx; @ExcelProperty(value = "学校15", index = 17) private String schoolEx; @ExcelProperty(value = "姓名16", index = 18) private String nameHx; @ExcelProperty(value = "年龄17", index = 19) private int ageHx; @ExcelProperty(value = "学校18", index = 20) private String schoolHx; @ExcelProperty(value = "姓名19", index = 21) private String nameFx; @ExcelProperty(value = "年龄20", index = 22) private int ageFx; @ExcelProperty(value = "学校21", index = 23) private String schoolFx;
}
测试类
/** * 测试导出模板 * 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.setNameAx("测试dd"+i); testVO.setAgeAx(12); testVO.setSchoolAx("测试学校1dd"+i); testVO.setNameBx("测试dd"+i); testVO.setAgeBx(13); testVO.setSchoolBx("测试学校2dd"+i); testVO.setNameCx("测试dd"+i); testVO.setAgeCx(14); testVO.setSchoolCx("测试学校3dd"+i); testVO.setNameDx("测试dd"+i); testVO.setAgeDx(15); testVO.setSchoolDx("测试学校4dd"+i); testVO.setNameEx("测试dd"+i); testVO.setAgeEx(16); testVO.setSchoolEx("测试学校5dd"+i); testVO.setNameHx("测试dd"+i); testVO.setAgeHx(17); testVO.setSchoolHx("测试学校6dd"+i); testVO.setNameFx("测试dd"+i); testVO.setAgeFx(18); testVO.setSchoolFx("测试学校7dd"+i); TestVO testVO1 = new TestVO(); testVO.setAge(22); testVO.setName("22测试dd"+i); testVO.setSchool("22学校"+i); testVO1.setNameAx("22测试dd"+i); testVO1.setAgeAx(0); testVO1.setSchoolAx("22测试学校dd"+i); testVO1.setNameBx("22测试dd"+i); testVO1.setAgeBx(0); testVO1.setSchoolBx("22测试学校dd"+i); testVO1.setNameCx("22测试dd"+i); testVO1.setAgeCx(0); testVO1.setSchoolCx("22测试学校dd"+i); testVO1.setNameDx("22测试dd"+i); testVO1.setAgeDx(0); testVO1.setSchoolDx("22测试学校dd"+i); testVO1.setNameEx("22测试dd"+i); testVO1.setAgeEx(0); testVO1.setSchoolEx("22测试学校dd"+i); testVO1.setNameHx("22测试dd"+i); testVO1.setAgeHx(0); testVO1.setSchoolHx("22测试d学校d"+i); testVO1.setNameFx("22测试dd"+i); testVO1.setAgeFx(0); testVO1.setSchoolFx("22测试学校dd"+i);
} // 指定批注 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); } }