目录
1 Maven配置
<!--hutool工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.1</version>
</dependency>
<!-- EasyExcel文档处理工具 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
2 CommentModel
批注信息类。
package com.xudongbase.common.easyexcel.model;
import lombok.Getter;
/**
* 批注信息类
*
* @author xudongmaster
*/
@Getter
public class CommentModel {
/**
* sheet页名称
*/
private String sheetName;
/**
* 列索引
*/
private int colIndex;
/**
* 行索引
*/
private int rowIndex;
/**
* 行索引
*/
private String commentContent;
private void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
private void setColIndex(int colIndex) {
this.colIndex = colIndex;
}
private void setRowIndex(int rowIndex) {
this.rowIndex = rowIndex;
}
private void setCommentContent(String commentContent) {
this.commentContent = commentContent;
}
private CommentModel() {
}
/**
* 生成批注信息
*
* @param sheetName sheet页名称
* @param rowIndex 行号
* @param columnIndex 列号
* @param commentContent 批注内容
* @return
*/
public static CommentModel createCommentModel(String sheetName, int rowIndex, int columnIndex, String commentContent) {
CommentModel commentModel = new CommentModel();
//sheet页名称
commentModel.setSheetName(sheetName);
//行号
commentModel.setRowIndex(rowIndex);
//列号
commentModel.setColIndex(columnIndex);
//批注内容
commentModel.setCommentContent(commentContent);
return commentModel;
}
}
3 CommentWriteHandler
自定义批注处理器。
package com.xudongbase.common.easyexcel.handler;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
import com.alibaba.excel.write.metadata.holder.*;
import com.xudongbase.common.easyexcel.model.CommentModel;
import com.xudongbase.common.util.EqualsUtil;
import com.xudongbase.common.util.POIUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* 自定义批注处理器
*
* @author xudongmaster
*/
public class CommentWriteHandler extends AbstractRowWriteHandler {
/**
* sheet页名称列表
*/
private List<String> sheetNameList;
List<CommentModel> commentList = new ArrayList<>();
/**
* 自定义批注适配器构造方法
*
* @param commentList 批注信息
* @param extension 文件后缀(xlsx、xls)
*/
public CommentWriteHandler(List<CommentModel> commentList, String extension) {
if (CollUtil.isEmpty(commentList)) {
return;
}
//文件不为指定的格式时,默认为Xlsx
if (StrUtil.equals(extension, "xlsx") == false && StrUtil.equals(extension, "xls") == false) {
extension = "xlsx";
}
this.commentList = commentList.stream().filter(x ->
StrUtil.isNotBlank(x.getSheetName()) && x.getColIndex() >=0 && x.getRowIndex() >= 0 && StrUtil.isNotBlank(x.getCommentContent())
).collect(Collectors.toList());
sheetNameList = this.commentList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
this.extension = extension;
}
/**
* 文档后缀名
*/
private String extension;
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
//不需要添加批注,或者当前sheet页不需要添加批注
if (CollUtil.isEmpty(commentList) || sheetNameList.contains(sheet.getSheetName()) == false) {
return;
}
//获取当前行的批注信息
List<CommentModel> rowCommentList = commentList.stream().filter(x ->
StrUtil.equals(x.getSheetName(), sheet.getSheetName())
&& EqualsUtil.equals(relativeRowIndex, x.getRowIndex())).collect(Collectors.toList());
//当前行没有批注信息
if (CollUtil.isEmpty(rowCommentList)) {
return;
}
List<Integer> colIndexList = rowCommentList.stream().map(x -> x.getColIndex()).distinct().collect(Collectors.toList());
for (Integer colIndex : colIndexList) {
//同一单元格的批注信息
List<CommentModel> cellCommentList = rowCommentList.stream().filter(x ->
EqualsUtil.equals(colIndex, x.getColIndex())).collect(Collectors.toList());
if (CollUtil.isEmpty(cellCommentList)) {
continue;
}
//批注内容拼成一条
String commentContent = cellCommentList.stream().map(x -> x.getCommentContent()).collect(Collectors.joining());
Cell cell = row.getCell(colIndex);
POIUtil.addComment(cell, commentContent, extension);
}
//删除批注信息
commentList.remove(rowCommentList);
//重新获取要添加的sheet页姓名
sheetNameList = commentList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());
}
}
注:addComment()源码请参考以下博客。
3 调试代码
/**
* 测试添加批注
*/
@Test
public void testComment() {
try {
File file = new File("D:/easyexcel/testComment.xlsx");
FileUtil.createNewFile(file);
//生成表格数据
List<List<Object>> dataList = new ArrayList<>();
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
//导出文件
String sheetName = "模板";
List<CommentModel> commentList = new ArrayList<>();
commentList.add(CommentModel.createCommentModel(sheetName, 0, 1, "第一条批注。"));
commentList.add(CommentModel.createCommentModel(sheetName, 0, 1, "第二条批注。"));
FileOutputStream fileOutputStream = new FileOutputStream(file);
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream)
.inMemory(Boolean.TRUE).registerWriteHandler(new CommentWriteHandler(commentList, "xlsx")).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
excelWriter.write(dataList, writeSheet);
//千万别忘记finish 会帮忙关闭流
excelWriter.finish();
} catch (Exception e) {
e.printStackTrace();
}
}
4 调试结果
注:
如果需要源码请前往Gitee查看。