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;
}
}
}