使用EasyExcel自定义格式导出数据时指定单元格样式
2023年5月3日
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
EasyExcel.write(response.getOutputStream()) .head(header) .registerWriteHandler(new CellWriteHandler() { @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) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 非头行进行单元格样式处理 if (relativeRowIndex >= master.getHeadRowNumber()) { setCellStyle(writeSheetHolder, cell, relativeRowIndex, cellList1, cellIndex1); setCellStyle(writeSheetHolder, cell, relativeRowIndex, cellList2, cellIndex2); } } }) .inMemory(true) .sheet("Sheet1") .doWrite(values); // 对单元格设置样式 private void setCellStyle(WriteSheetHolder writeSheetHolder, Cell cell, Integer relativeRowIndex, List<ExcelCellDto> cellList, Integer cellIndex) { int columnIndex = cell.getColumnIndex(); Drawing<?> drawing = writeSheetHolder.getSheet().createDrawingPatriarch(); // 找到同一行，同一列做过比较的数据，根据比较结果设置单元格样式 if (relativeRowIndex < cellList.size() && cellIndex == columnIndex) { ExcelCellDto newCell = cellList.get(relativeRowIndex); if (newCell.isMatched()) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, columnIndex, relativeRowIndex, columnIndex + 1, relativeRowIndex + 1)); XSSFFont font = new XSSFFont( CTFont.Factory.newInstance()); font.setBold(true); font.setFontHeight(14); font.setItalic(true); font.setColor(IndexedColors.WHITE.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.fromInt(newCell.getBgColorIndex()).getIndex()); cell.setCellStyle(cellStyle); XSSFRichTextString richTextString = new XSSFRichTextString(); richTextString.append(newCell.getValue()); richTextString.applyFont(font); cell.setCellValue(richTextString); comment.setString(new XSSFRichTextString("匹配到:" + newCell.getUnionCellName())); cell.setCellComment(comment); } } } } |
在线比较Excel当中两列数据，对于重复的数据使用背景色标识
示例：
About The Author
