EasyExcel导出添加批注

直接看代码。根据个人需要做改动

注：POI也可以做批注，文章链接https://www.cnblogs.com/qq1445496485/p/15622664.html

 /**
     * 导出（批注）
     *
     * @param response
     */
    @GetMapping("/exportComment")
    public void exportComment(HttpServletResponse response)
    {
        try
        {
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            // 设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)14);
            // 字体加粗
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            // 设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            
            // 内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置内容字体
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontHeightInPoints((short)12);
            contentWriteFont.setFontName("宋体");
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            // 设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置单元格格式为 文本
            contentWriteCellStyle.setDataFormat((short)49);
            
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            
            // 生成表格数据
            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, 0, "A批注。"));
            commentList.add(CommentWriteHandler.createCommentMap(sheetName, 0, 1, "B批注。"));
            commentList.add(CommentWriteHandler.createCommentMap(sheetName, 2, 0, "B批注。"));
            
            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"))
                .registerWriteHandler(horizontalCellStyleStrategy)
                .build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
            excelWriter.write(dataList, writeSheet);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

package com.temporary.handle;

import cn.hutool.core.collection.CollectionUtil;
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;

/**
 * @author Han
 * @Description 自定义批注处理器
 * @date 2022/3/21
 */
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());
    }
    
    /**
     * 给Cell添加批注
     *
     * @param cell 单元格
     * @param value 批注内容
     * @param extension 扩展名
     */
    public static void addComment(Cell cell, String value, String extension)
    {
        Sheet sheet = cell.getSheet();
        cell.removeCellComment();
        if ("xls".equals(extension))
        {
            ClientAnchor anchor = new HSSFClientAnchor();
            // 关键修改
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            anchor.setCol1(cell.getColumnIndex());
            anchor.setRow1(cell.getRowIndex());
            anchor.setCol2(cell.getColumnIndex() + 5);
            anchor.setRow2(cell.getRowIndex() + 6);
            // 结束
            Drawing drawing = sheet.createDrawingPatriarch();
            Comment comment = drawing.createCellComment(anchor);
            // 输入批注信息
            comment.setString(new HSSFRichTextString(value));
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);
        }
        else if ("xlsx".equals(extension))
        {
            ClientAnchor anchor = new XSSFClientAnchor();
            // 关键修改
            anchor.setDx1(0);
            anchor.setDx2(0);
            anchor.setDy1(0);
            anchor.setDy2(0);
            anchor.setCol1(cell.getColumnIndex());
            anchor.setRow1(cell.getRowIndex());
            anchor.setCol2(cell.getColumnIndex() + 5);
            anchor.setRow2(cell.getRowIndex() + 6);
            // 结束
            Drawing drawing = sheet.createDrawingPatriarch();
            Comment comment = drawing.createCellComment(anchor);
            // 输入批注信息
            comment.setString(new XSSFRichTextString(value));
            // 将批注添加到单元格对象中
            cell.setCellComment(comment);
        }
    }
    
}