1. 批注信息

package com.xxx.demo;

import lombok.Getter;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * 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;

    /**
     * The background color of the comment box.
     */
    private final IndexedColors commentColor;

    /**
     * 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.
     * @param commentColor The background color for the comment box.
     */
    public CommentInfo(int columnIndex, String comment, IndexedColors commentColor) {
        this(columnIndex, comment, DEFAULT_COMMENT_WIDTH, DEFAULT_COMMENT_HEIGHT, commentColor);
    }

    /**
     * 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).
     * @param commentColor The background color for the comment box.
     */
    public CommentInfo(int columnIndex, String comment, int commentWidth, int commentHeight, IndexedColors commentColor) {
        this.columnIndex = columnIndex;
        this.comment = comment;
        this.commentWidth = commentWidth;
        this.commentHeight = commentHeight;
        this.commentColor = commentColor;
    }

}

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.
     */
    @Override
    public 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 size
            anchor.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);

            // Set cell style with background
            IndexedColors commentColor = commentInfo.getCommentColor();
            if (commentColor != null) {
                Workbook workbook = row.getSheet().getWorkbook();
                CellStyle commentStyle = workbook.createCellStyle();
                commentStyle.setFillForegroundColor(commentInfo.getCommentColor().getIndex());
                commentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(commentStyle);
            }
        }
    }
}

3. 测试导出

package com.xxx.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.IndexedColors;

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 data
        List<DemoData> data = new ArrayList<>();
        data.add(new DemoData("张三", 25));
        data.add(new DemoData("李四", 30));

        // Define comment information for each row and column
        Map<Integer, List<CommentInfo>> rowColumnCommentMap = new HashMap<>();

        // Add comments for row 2 (Name and Age columns)
        rowColumnCommentMap.put(2, Arrays.asList(
                new CommentInfo(0, "姓名：%s", IndexedColors.RED),
                new CommentInfo(1, "年龄是：%s", IndexedColors.YELLOW)
        ));

        // Add comments for row 3 (Age column)
        rowColumnCommentMap.put(3, Collections.singletonList(new CommentInfo(1, "年龄：%s", IndexedColors.RED)));

        // Export data to Excel file with comments
        EasyExcel.write("demo.xlsx", DemoData.class)
                .inMemory(Boolean.TRUE)
                .registerWriteHandler(new CommentWriteHandler(rowColumnCommentMap))
                .sheet("Sheet1")
                .doWrite(data);
    }

    @Data
    private static class DemoData {
        @ExcelProperty("姓名")
        private String name;

        @ExcelProperty("年龄")
        private Integer age;

        public DemoData(String name, Integer age) {
            this.name = name;
            this.age = age;
        }
    }
}