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 org.apache.poi.ss.usermodel.*;import java.util.List;public class ExcelCellWriteHandler implements CellWriteHandler {@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) {// 2.0 设置单元格为文本Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();DataFormat dataFormat = workbook.createDataFormat();cellStyle.setDataFormat(dataFormat.getFormat("@"));cell.setCellStyle(cellStyle);}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}
}
/*** 自定义拦截器.新增注释,第一行头加批注** @author Gsy*/
@Slf4j
public class CommentWriteHandler implements RowWriteHandler {@Overridepublic void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead){if (!isHead){return;}Sheet sheet = writeSheetHolder.getSheet();Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();// 将批注添加到单元格对象中Cell cell1 = sheet.getRow(0).getCell(0);Comment comment1 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 1, 0, (short) 2, 1, "长度限制20位");cell1.setCellComment(comment1);Cell cell2 = sheet.getRow(0).getCell(3);Comment comment2 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 4, 0, (short) 5, 1, "格式:男/女");cell2.setCellComment(comment2);Cell cell3 = sheet.getRow(0).getCell(4);Comment comment3 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 5, 0, (short) 6, 1, "示例:汉族");cell3.setCellComment(comment3);Cell cell4 = sheet.getRow(0).getCell(5);Comment comment4 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 6, 0, (short) 8, 1, "单元格格式:文本 格式:yyyy-MM-dd 示例:2023-01-02");cell4.setCellComment(comment4);Cell cell10 = sheet.getRow(0).getCell(7);Comment comment10 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 8, 0, (short) 9, 1, "长度限制11位");cell10.setCellComment(comment10);Cell cell5 = sheet.getRow(0).getCell(12);Comment comment5 = ExcelUtil.addComments(drawingPatriarch, 0, 0, 0, 0, (short) 13, 0, (short) 14, 1, "示例:一班");cell5.setCellComment(comment5);Cell cell6 = sheet.getRow(0).getCell(13);Comment comment6 = ExcelUtil.addComments(drawingPatriarch,0, 0, 0, 0, (short) 14, 0, (short) 15, 1, "示例:2012级 要求:入学年份+级");cell6.setCellComment(comment6);Cell cell7 = sheet.getRow(0).getCell(14);Comment comment7 = ExcelUtil.addComments(drawingPatriarch,0, 0, 0, 0, (short) 15, 0, (short) 16, 1, "要求：必须与系统中名称一致");cell7.setCellComment(comment7);Cell cell11 = sheet.getRow(0).getCell(16);Comment comment11 = ExcelUtil.addComments(drawingPatriarch,0, 0, 0, 0, (short) 17, 0, (short) 18, 1, "单位:cm");cell11.setCellComment(comment11);Cell cell12 = sheet.getRow(0).getCell(17);Comment comment12 = ExcelUtil.addComments(drawingPatriarch,0, 0, 0, 0, (short) 18, 0, (short) 19, 1, "单位:kg");cell12.setCellComment(comment12);Cell cell8 = sheet.getRow(0).getCell(42);Comment comment8 = ExcelUtil.addComments(drawingPatriarch,0, 0, 0, 0, (short) 43, 0, (short) 44, 1, "类型分为:不戴镜/框架眼镜/隐形眼镜/夜戴角膜塑形镜");cell8.setCellComment(comment8);}
}
用的时候在这里：
前面代码省略
try {response.setContentType("application/vnd.ms-excel");String fileName = URLEncoder.encode("学生数据模板", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");EasyExcel.write(response.getOutputStream(), StudentInfoExcel.class).inMemory(Boolean.TRUE).registerWriteHandler(new ExcelCellWriteHandler()).registerWriteHandler(new CommentWriteHandler()).sheet("学生数据表").doWrite(list);}catch (IOException e){e.printStackTrace();}
