1. 批注信息
package com.xxx.demo;import lombok.Getter;/*** This class represents the comment information associated with a specific cell in an Excel sheet.* The columnIndex field specifies the column number of the cell, and the comment field stores the text of the comment.* The commentWidth and commentHeight fields specify the dimensions of the comment box.* If the width or height is not specified, default values are used.** @author xm.z*/
@Getter
public class CommentInfo {/*** Default width of the comment box (number of columns spanned).*/private static final int DEFAULT_COMMENT_WIDTH = 0;/*** Default height of the comment box (number of rows spanned).*/private static final int DEFAULT_COMMENT_HEIGHT = 0;/*** The index of the column where the comment should be added.*/private final int columnIndex;/*** The content of the comment.*/private final String comment;/*** The width of the comment box (number of columns spanned).*/private final int commentWidth;/*** The height of the comment box (number of rows spanned).*/private final int commentHeight;/*** Constructor to create a CommentInfo with default width and height.** @param columnIndex The index of the column where the comment should be added.* @param comment The content of the comment.*/public CommentInfo(int columnIndex, String comment) {this(columnIndex, comment, DEFAULT_COMMENT_WIDTH, DEFAULT_COMMENT_HEIGHT);}/*** Constructor to create a CommentInfo with specified width and height.** @param columnIndex The index of the column where the comment should be added.* @param comment The content of the comment.* @param commentWidth The width of the comment box (number of columns spanned).* @param commentHeight The height of the comment box (number of rows spanned).*/public CommentInfo(int columnIndex, String comment, int commentWidth, int commentHeight) {this.columnIndex = columnIndex;this.comment = comment;this.commentWidth = commentWidth;this.commentHeight = commentHeight;}}
2. 批注处理器
package com.xxx.demo;import com.alibaba.excel.write.handler.RowWriteHandler;
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.*;import java.util.List;
import java.util.Map;/*** This class handles adding comments to specific cells in an Excel sheet written by EasyExcel.* It takes a map of row number to a list of CommentInfo objects, which define the comment content,* target cell index, width, and height.** @author xm.z*/
@Slf4j
public class CommentWriteHandler implements RowWriteHandler {private final Map<Integer, List<CommentInfo>> rowColumnCommentMap;/*** Constructor that takes a map of row number to a list of CommentInfo objects.** @param rowColumnCommentMap A map where the key is the row number (1-based) and the value is a list of CommentInfo objects for that row.*/public CommentWriteHandler(Map<Integer, List<CommentInfo>> rowColumnCommentMap) {this.rowColumnCommentMap = rowColumnCommentMap;}/*** This method is called after a row is written to the Excel sheet.* It iterates through the comments associated with the current row and adds them to the corresponding cells.** @param writeSheetHolder Holds information about the current sheet being written.* @param writeTableHolder Holds information about the current table being written.* @param row The row that was just written.* @param relativeRowIndex The 0-based index of the row within the current sheet.* @param isHead True if the row is the header row.*/@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {Sheet sheet = row.getSheet();CreationHelper creationHelper = sheet.getWorkbook().getCreationHelper();Drawing<?> drawing = sheet.createDrawingPatriarch();// Get comments associated with the current row (1-based)List<CommentInfo> commentInfos = rowColumnCommentMap.get(row.getRowNum() + 1);if (commentInfos != null && !commentInfos.isEmpty()) {for (CommentInfo commentInfo : commentInfos) {addCommentToCell(row, creationHelper, drawing, commentInfo);}}}/*** This method adds a comment to a specific cell in the current row.** @param row The row containing the cell to add the comment to.* @param creationHelper Used to create comment anchors and rich text strings.* @param drawing The drawing patriarch used to create comments in the sheet.* @param commentInfo An object containing information about the comment (content, target cell index, width, height).*/private void addCommentToCell(Row row, CreationHelper creationHelper, Drawing<?> drawing, CommentInfo commentInfo) {Cell cell = row.getCell(commentInfo.getColumnIndex());if (cell != null) {String value = cell.toString();ClientAnchor anchor = creationHelper.createClientAnchor();// Set anchor position and sizeanchor.setCol1(cell.getColumnIndex());anchor.setCol2(commentInfo.getCommentWidth());anchor.setRow1(row.getRowNum());anchor.setRow2(commentInfo.getCommentHeight());Comment comment = drawing.createCellComment(anchor);String commentContent = String.format(commentInfo.getComment(), value);comment.setString(creationHelper.createRichTextString(commentContent));cell.setCellComment(comment);}}
}
3. 测试导出
package com.xxx.demo;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;import java.util.*;/*** This class demonstrates how to export data to an Excel file with comments using EasyExcel.*/
public class ExcelExportDemo {public static void main(String[] args) {// Prepare demo dataList<DemoData> data = new ArrayList<>();data.add(new DemoData("张三", 25));data.add(new DemoData("李四", 30));// Define comment information for each row and columnMap<Integer, List<CommentInfo>> rowColumnCommentMap = new HashMap<>();// Add comments for row 2 (Name and Age columns)rowColumnCommentMap.put(2, Arrays.asList(new CommentInfo(0, "姓名：%s"),new CommentInfo(1, "年龄是：%s")));// Add comments for row 3 (Age column)rowColumnCommentMap.put(3, Collections.singletonList(new CommentInfo(1, "年龄：%s")));// Export data to Excel file with commentsEasyExcel.write("demo.xlsx", DemoData.class).inMemory(Boolean.TRUE).registerWriteHandler(new CommentWriteHandler(rowColumnCommentMap)).sheet("Sheet1").doWrite(data);}@Dataprivate static class DemoData {@ExcelProperty("姓名")private String name;@ExcelProperty("年龄")private Integer age;public DemoData(String name, Integer age) {this.name = name;this.age = age;}}
}
