EasyExcel写标注comment异常解决

最新推荐文章于 2025-02-25 22:16:57 发布

原创最新推荐文章于 2025-02-25 22:16:57 发布 · 2.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#java #excel #poi

java 专栏收录该内容

16 篇文章

订阅专栏

博客介绍了在使用EasyExcel时遇到的'多个单元格注释不允许'的异常情况，提供了业务代码以及解决方案，包括如何为单元格添加标识和设置背景色。

异常信息：Multiple cell comments in one cell are not allowed, cell: A1

java.lang.IllegalArgumentException: Multiple cell comments in one cell are not allowed, cell: A1
	at org.apache.poi.xssf.usermodel.XSSFDrawing.createCellComment(XSSFDrawing.java:358) ~[poi-ooxml-3.17.jar:3.17]
	at org.apache.poi.xssf.streaming.SXSSFDrawing.createCellComment(SXSSFDrawing.java:53) ~[poi-ooxml-3.17.jar:3.17]
	at com.wqwangcn.wwexcel.excel.hanlder.CommentWriteHandler.writeCellComment(CommentWriteHandler.java:60) ~[classes/:na]
	at com.wqwangcn.wwexcel.excel.hanlder.CommentWriteHandler.afterRowDispose(CommentWriteHandler.java:49) ~[classes/:na]
	at com.alibaba.excel.util.WriteHandlerUtils.afterRowDispose(WriteHandlerUtils.java:220) ~[easyexcel-2.2.6.jar:na]
	at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.addOneRowOfDataToExcel(ExcelWriteAddExecutor.java:74) ~[easyexcel-2.2.6.jar:na]
	at com.alibaba.excel.write.executor.ExcelWriteAddExecutor.add(ExcelWriteAddExecutor.java:56) ~[easyexcel-2.2.6.jar:na]
	at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:58) ~[easyexcel-2.2.6.jar:na]

业务代码：

public void download(String fileName,Class clazz){
        ExcelWriter excelWriter = null;
        try {
            ExcelWriterSheetBuilder sheet = EasyExcel.write(fileName, clazz)
            .inMemory(Boolean.TRUE) //上面的异常别问为什么，加上.inMemory(Boolean.TRUE)就对了
            .needHead(true).sheet(sheetName);
            sheet.useDefaultStyle(false);
            sheet.registerWriteHandler(cellStyle)
                    .registerWriteHandler(rowHeightStyle)
                    .registerWriteHandler(columnWidthStyle);
            if(commentWriteHandler!=null){
                sheet.registerWriteHandler(commentWriteHandler);
            }
            sheet.head(headList);
            sheet.doWrite(data);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

解决方案：

EasyExcel.write(fileName, clazz).inMemory(Boolean.TRUE).inMemory(Boolean.TRUE)

单元格加标识和设置背景色代码

@Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                Integer relativeRowIndex, Boolean isHead) {

        if(!isHead){
            int actualRowIndex = writeSheetHolder.getExcelWriteHeadProperty().getHeadRowNumber() + relativeRowIndex;
            List<VerifyFailureInfo> failureList = failures.get(actualRowIndex);
            Sheet sheet = writeSheetHolder.getSheet();
            Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();

            if(!CollectionUtils.isEmpty(failureList)){
                for (VerifyFailureInfo failureInfo : failureList){
                    writeCellComment(drawingPatriarch,row,failureInfo);
                    fillForegroundColor(writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook(),row,failureInfo.getColumnIndex());
                }
            }
        }
    }

    private void writeCellComment(Drawing<?> drawingPatriarch,Row row,VerifyFailureInfo failure){
        // 在第一行 第二列创建一个批注
        //Drawing<?> drawingPatriarch = row.getSheet().createDrawingPatriarch();
        Comment comment =
                drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 0,0, 2, 2));
        // 输入批注信息
        comment.setString(new XSSFRichTextString(failure.getMessage()));
        // 将批注添加到单元格对象中
        row.getCell(failure.getColumnIndex()).setCellComment(comment);
    }

    private void fillForegroundColor(Workbook workbook, Row row, Integer columnIndex){
        CellStyle style = workbook.createCellStyle();
        //设置前景色
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        //设置填充模式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        row.getCell(columnIndex).setCellStyle(style);
    }