jnpf-common-v6x/jnpf-boot-common/jnpf-common-office/target/classes/jnpf/excel/handle/ExcelCommentHandle.java

package jnpf.excel.handle;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import jnpf.excel.ExcelHelper;
import jnpf.excel.ExcelPreHandle;
import jnpf.util.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * excel添加批注
 */
public class ExcelCommentHandle implements ExcelPreHandle {
    String sheetName;
    Workbook workbook;
    int headerRowLen;
    int lastRowNum;

    /**
     * workbook增加标题批注
     *
     * @param workbook    工作簿
     * @param sheetName   工作表名称
     * @param columnIndex 列索引,从0开始
     * @param comment     批注内容
     */
    public void addComment(Workbook workbook, String sheetName, int rowIndex, int columnIndex, String comment) {
        Sheet sheet = workbook.getSheet(sheetName);
        Row headerRow = sheet.getRow(rowIndex);
        Cell headerCell = headerRow.getCell(columnIndex);
        // 判断是否存在批注
        Comment cellComment = headerCell.getCellComment();
        if (!Objects.isNull(cellComment)) {
            headerCell.setCellComment(null);
        }
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        CreationHelper factory = workbook.getCreationHelper();
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setRow1(rowIndex);
        anchor.setCol1(columnIndex);
        Comment headerComment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(comment);
        headerComment.setString(str);
        headerCell.setCellComment(headerComment);
    }

    @Override
    public void execute(ExcelHelper data, Map<String, Object> params) {
        List<ExcelExportEntity> list = data.getEntities();
        ExportParams exportParams = data.getExportParams();
        workbook = data.getWorkbook();
        params.put("sheetName", sheetName);
        this.sheetName = exportParams.getSheetName();
        Sheet sheet = workbook.getSheet(exportParams.getSheetName());
        headerRowLen = data.isComplexTable() ? 2 : 1;
        sheet.createFreezePane(exportParams.getFreezeCol(),headerRowLen);
        lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < headerRowLen; i++) {
            Row headerRow = sheet.getRow(i);
            int lastCellNum = headerRow.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = headerRow.getCell(j);
                this.addComment(cell, i, j);
            }
        }
    }


    private void addComment(Cell cell, int rowIndex, int columnIndex) {
        if (cell == null) return;
        String name = ((XSSFCell) cell).getRichStringCellValue().getString();
        if (StringUtils.isBlank(name)) return;
        String id = ExcelUtil.getIdFromCellValue(name);
        if (StringUtils.isNotBlank(id)) {
            this.addComment(workbook, sheetName, rowIndex, columnIndex, id);
        }
    }

}