Easyexcel 实现合并单元格&动态列头&批注&列头样式 Demo

2023-06-02 20:39| 来源: 网络整理| 查看: 265

前言

其实 easyexcel 官网例子十分多，也很完善，但是实际工作中，我们遇到的情况往往是这些例子的复数情况。当我们重叠一起使用，就发现有些地方没法像官网那样直接使用。我这里就遇到了，所以写下我的解决方案，记录下。因为工作中的报表具有商业价值，所以没法直接使用。我就针对我觉得比较麻烦的几种情况，自己想个复合 demo。

期望生成的报表 sheet1 image.png

还有下拉

image.png sheet2 image.png 示例代码

我这里偷懒了，有很多能抽出来复用的代码，我没抽，只是简单实现 demo，用于实际工作要自己优化下

package com.alibaba.easyexcel.test.demo.write; import com.alibaba.easyexcel.test.util.TestFileUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.util.BooleanUtils; import com.alibaba.excel.util.ListUtils; import com.alibaba.excel.write.handler.RowWriteHandler; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext; import com.alibaba.excel.write.metadata.WriteSheet; 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 com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.alibaba.fastjson2.JSON; import lombok.Data; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.assertj.core.util.Sets; import org.junit.jupiter.api.Test; import java.lang.reflect.Field; import java.util.*; /** * @author CaiZhuliang * @date 2023/5/10 */ @Slf4j public class MyWriteTest { @Test public void complexDemo() { // 合并单元格+动态列头+同个单元格内多样式+列头填充颜色+多sheet+批注 String fileName = TestFileUtil.getPath() + "complexDemo" + System.currentTimeMillis() + ".xlsx"; try (ExcelWriter excelWriter = EasyExcel.write(fileName, ComplexDemoDTO.class).inMemory(Boolean.TRUE).build()) { // 头部样式 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(); horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle); WriteSheet sheet1 = EasyExcel.writerSheet("资料") // 背景填充颜色 .registerWriteHandler(horizontalCellStyleStrategy) // 下拉 .registerWriteHandler(new SheetWriteHandler() { @Override public void afterSheetCreate(SheetWriteHandlerContext context) { // 由于性别在 (2,1)，因此数据从 (2,2) 开始输入。假设现在我给两百行都做下拉 CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 201, 2, 2); DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper(); DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"男", "女"}); DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList); context.getWriteSheetHolder().getSheet().addValidationData(dataValidation); } }) // sheet2 必填标识变成红色 .registerWriteHandler(new RowWriteHandler() { @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(2) == 0) { Iterator iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String stringCellValue = cell.getStringCellValue(); if (stringCellValue.startsWith("*")) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); Font font = workbook.createFont(); font.setColor(Font.COLOR_RED); RichTextString richStringCellValue = cell.getRichStringCellValue(); richStringCellValue.applyFont(0, 1, font); } } } } }) // 批注 .registerWriteHandler(new RowWriteHandler() { private final Set COMMENT_HEAD = Sets.set("*入职日期", "*离职日期"); @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { /* 只处理入职日期离职日期，它们分别在工作经历 -> 经历1/经历2 下面，所以批注要在以下坐标上创建 (5,2) (6,2) (8,2) (9,2) */ if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(2) == 0) { // 因为目标单元格都在第三行，所以我们处理点在第三行上 Drawing drawingPatriarch = writeSheetHolder.getSheet().createDrawingPatriarch(); Iterator iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); int columnIndex = cell.getColumnIndex(); short col1 = (short) (columnIndex + 1); short col2 = (short) (columnIndex + 3); if (COMMENT_HEAD.contains(cell.getStringCellValue())) { // 创建批注 Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, col1, 2, col2, 4)); comment.setString(new XSSFRichTextString("1、必填\n" + "2、填写格式:yyyy-mm-dd")); row.getCell(columnIndex).setCellComment(comment); } } } } }) .build(); WriteSheet sheet2 = EasyExcel.writerSheet("面试结果") .head(dynamicHead()) // 背景填充颜色 .registerWriteHandler(horizontalCellStyleStrategy) // sheet2 必填标识变成红色 .registerWriteHandler(new RowWriteHandler() { @Override public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) { if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(1) == 0) { Iterator iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String stringCellValue = cell.getStringCellValue(); if (stringCellValue.startsWith("*")) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); Font font = workbook.createFont(); font.setColor(Font.COLOR_RED); RichTextString richStringCellValue = cell.getRichStringCellValue(); richStringCellValue.applyFont(0, 1, font); } } } } }) .build(); excelWriter.write(Collections.EMPTY_LIST, sheet1); excelWriter.write(Collections.EMPTY_LIST, sheet2); } } private List dynamicHead() { // 里面的 list 是纵向维度，而最外面的 list 是横向维度，所以要看目标列头所在的位置填充 List headColList = ListUtils.newArrayList(); headColList.add(ListUtils.newArrayList("评价", "*序号")); headColList.add(ListUtils.newArrayList("评价", "*姓名")); headColList.add(ListUtils.newArrayList("评价", "*综合得分")); headColList.add(ListUtils.newArrayList("评价", "*一面评分")); headColList.add(ListUtils.newArrayList("评价", "*二面评分")); headColList.add(ListUtils.newArrayList("评价", "三面评分")); headColList.add(ListUtils.newArrayList("评价", "*HR评分")); return headColList; } @Data public static class ComplexDemoDTO { @ExcelProperty("序号") private String no; @ExcelProperty({"基础信息", "*姓名"}) private String name; @ExcelProperty({"基础信息", "性别"}) private String sex; @ExcelProperty({"基础信息", "*年龄"}) private Integer age; @ExcelProperty({"工作经历", "经历1", "*公司名称"}) private String companyOfOne; @ExcelProperty({"工作经历", "经历1", "*入职日期"}) private String startDateOfOne; @ExcelProperty({"工作经历", "经历1", "*离职日期"}) private String endDateOfOne; @ExcelProperty({"工作经历", "经历2", "*公司名称"}) private String companyOfTwo; @ExcelProperty({"工作经历", "经历2", "*入职日期"}) private String startDateOfTwo; @ExcelProperty({"工作经历", "经历2", "*离职日期"}) private String endDateOfTwo; } } 固定表头&动态表头

可能还会遇到固定+动态表头的情况，我这里是用了反射解决的

示例代码如下

【本文地址】

公司简介

联系我们

Easyexcel 实现 合并单元格&动态列头&批注&列头样式 Demo

Easyexcel 实现合并单元格&动态列头&批注&列头样式 Demo