EasyExcel 批量添加批注

旭东怪

已于 2022-03-12 09:17:44 修改

阅读量5k

点赞数 2

分类专栏：文档处理工具文章标签： java easyexcel

于 2021-03-15 16:30:15 首次发布

本文链接：https://blog.csdn.net/qq_38974638/article/details/114837669

版权

文档处理工具专栏收录该内容

103 篇文章 23 订阅

订阅专栏

1 Maven配置

2 CommentModel

3 CommentWriteHandler

3 调试代码

4 调试结果

注：

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()源码请参考以下博客。

POI 给单元格添加批注

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