EasyExcel 批量添加批注

太过爱你忘了你带给我的痛 2022-11-09 12:59 392阅读 0赞

### 1 Maven配置 ###

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.1</version>
            </dependency>
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>

### 2 CommentWriteHandler ###

自定义批注处理器。

package com.easyexcel;
    
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
    import com.alibaba.excel.write.metadata.holder.*;
    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;
    
    /**
     * 自定义批注处理器
     */
    public class CommentWriteHandler extends AbstractRowWriteHandler {
    
        /**
         * sheet名称KEY
         */
        public static final String SHEETNAME_NAME = "sheetName";
        /**
         * 文档后缀名
         */
        private String extension;
        /**
         * 列索引key
         */
        public static final String COLINDEX_NAME = "colIndex";
        /**
         * 行索引key
         */
        public static final String ROWINDEX_NAME = "rowIndex";
        /**
         * 批注内容key
         */
        public static final String COMMENTCONTENT_NAME = "commentContent";
        /**
         * sheet页名称列表
         */
        private List<String> sheetNameList;
        List<Map<String, String>> commentList = new ArrayList<>();
    
        public CommentWriteHandler(List<Map<String, String>> commentList, String extension) {
            this.commentList = commentList != null && commentList.size() > 0
                    ? commentList.stream().filter(x ->
                    x.keySet().contains(SHEETNAME_NAME) == true && x.get(SHEETNAME_NAME) != null && StrUtil.isNotBlank(x.get(SHEETNAME_NAME).toString())
                            && x.keySet().contains(COLINDEX_NAME) == true && x.get(COLINDEX_NAME) != null && StrUtil.isNotBlank(x.get(COLINDEX_NAME).toString())
                            && x.keySet().contains(ROWINDEX_NAME) == true && x.get(ROWINDEX_NAME) != null && StrUtil.isNotBlank(x.get(ROWINDEX_NAME).toString())
                            && x.keySet().contains(COMMENTCONTENT_NAME) == true && x.get(COMMENTCONTENT_NAME) != null && StrUtil.isNotBlank(x.get(COMMENTCONTENT_NAME).toString())
            ).collect(Collectors.toList()) : new ArrayList<>();
            sheetNameList = this.commentList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).collect(Collectors.toList());
            this.extension = extension;
        }
    
        /**
         * 生成批注信息
         *
         * @param sheetName      sheet页名称
         * @param rowIndex       行号
         * @param columnIndex    列号
         * @param commentContent 批注内容
         * @return
         */
        public static Map<String, String> createCommentMap(String sheetName, int rowIndex, int columnIndex, String commentContent) {
            Map<String, String> map = new HashMap<>();
            //sheet页名称
            map.put(SHEETNAME_NAME, sheetName);
            //行号
            map.put(ROWINDEX_NAME, rowIndex + "");
            //列号
            map.put(COLINDEX_NAME, columnIndex + "");
            //批注内容
            map.put(COMMENTCONTENT_NAME, commentContent);
            return map;
        }
    
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                    Integer relativeRowIndex, Boolean isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            //不需要添加批注，或者当前sheet页不需要添加批注
            if (commentList == null || commentList.size() <= 0 || sheetNameList.contains(sheet.getSheetName()) == false) {
                return;
            }
            //获取当前行的批注信息
            List<Map<String, String>> rowCommentList = commentList.stream().filter(x ->
                    StrUtil.equals(x.get(SHEETNAME_NAME).toString(), sheet.getSheetName())
                            && relativeRowIndex == Integer.parseInt(x.get(ROWINDEX_NAME))).collect(Collectors.toList());
            //当前行没有批注信息
            if (rowCommentList == null || rowCommentList.size() <= 0) {
                return;
            }
            List<String> colIndexList = rowCommentList.stream().map(x -> x.get(COLINDEX_NAME)).distinct().collect(Collectors.toList());
            for (String colIndex : colIndexList) {
                //同一单元格的批注信息
                List<Map<String, String>> cellCommentList = rowCommentList.stream().filter(x ->
                        StrUtil.equals(colIndex, x.get(COLINDEX_NAME))).collect(Collectors.toList());
                if (CollectionUtil.isEmpty(cellCommentList)) {
                    continue;
                }
                //批注内容拼成一条
                String commentContent = cellCommentList.stream().map(x -> x.get(COMMENTCONTENT_NAME)).collect(Collectors.joining());
                Cell cell = row.getCell(Integer.parseInt(colIndex));
                addComment(cell, commentContent, extension);
            }
            //删除批注信息
            commentList.remove(rowCommentList);
            //重新获取要添加的sheet页姓名
            sheetNameList = commentList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).collect(Collectors.toList());
        }
    
    }

注：addComment()源码请参考以下博客。

[POI 给单元格添加批注][POI]

### 3 调用代码 ###

/**
         * 导出（批注）
         *
         * @param response
         */
        @GetMapping("/exportComment")
        public void exportComment(HttpServletResponse response) {
            try {
                //生成表格数据
                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 fileName = new String("文件名称.xlsx".getBytes(), "UTF-8");
                String sheetName = "模板";
                List<Map<String, String>> commentList = new ArrayList<>();
                commentList.add(CommentWriteHandler.createCommentMap(sheetName, 0, 1, "第一条批注。"));
                commentList.add(CommentWriteHandler.createCommentMap(sheetName, 0, 1, "第二条批注。"));
    
                response.addHeader("Content-Disposition", "filename=" + fileName);
                //设置类型，扩展名为.xls
                response.setContentType("application/vnd.ms-excel");
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                        .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 调试结果 ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_12_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16][]

[POI]: https://blog.csdn.net/qq_38974638/article/details/114837631
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_12_color_FFFFFF_t_70_g_se_x_16]: /images/20221023/75014f0414874e5e8a5d43f7d83599a5.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5pet5Lic5oCq_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20221023/60315be1a55b4ec0b1941cbf369807ee.png