POI 给单元格添加批注

 图中红框框是处理单元格内容和批注的地方。

参考:https://blog.csdn.net/qq_38974638/article/details/114837631

//SXSSFWorkbook
    public static void createExcelMergeSetCellColor1(SXSSFWorkbook workbook, SXSSFSheet sheet, String title,
        final String[] headers, List<List<String>> dataset, boolean isSortDataSet, final Integer[] mergeBasis,
        final Integer[] mergeCells, final Integer[] sumCells, final Integer[] timeCells)
    {
        sheet.setDefaultColumnWidth(15); // 设置表格默认列宽度为15个字节
        sheet.setDefaultRowHeight((short)20);
        sheet.setDefaultRowHeightInPoints(20);
        CellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
        CellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式
        commonDataStyle.setWrapText(true);
        CellStyle commonDataStyleRed = createCommonDataStyleRed(workbook);//生成红色文本样式
        commonDataStyleRed.setWrapText(true);
        CellStyle numStyle = createNumStyle(workbook); //生成数字类型保留两位小数样式
        CellStyle sumRowStyle = createSumRowStyle(workbook); //生成合计行样式

        if (headers == null || headers.length <= 0)
        {
            return;
        }

        SXSSFDrawing drawing = sheet.createDrawingPatriarch();
        SXSSFRow row = sheet.createRow(0); // 产生表格标题行
        row.setHeightInPoints(30);  //标题行的行高为25

        for (int index = 0; index < headers.length; index++)
        {
            SXSSFCell cell = row.createCell(index);
            cell.setCellStyle(headStyle);
            XSSFRichTextString text = new XSSFRichTextString(headers[index]);
            cell.setCellValue(text);
            String str = text.toString();
            if(str.contains("雇员唯一号") || str.contains("雇员姓名") || str.contains("证件号")
                || str.contains("缴金地") || str.contains("社保额"))
            {
                Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(),
                    cell.getColumnIndex() + 5, cell.getRowIndex() + 6));
                comment.setString(new XSSFRichTextString("必填项"));
                cell.setCellComment(comment);
            }
            else if(str.contains("证件类型"))
            {
                Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(),
                    cell.getColumnIndex() + 5, cell.getRowIndex() + 6));
                comment.setString(new XSSFRichTextString("必填项,证件类型只支持 身份证"));
                cell.setCellComment(comment);
            }
            else if(str.contains("账单年月"))
            {
                Comment comment = drawing.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(),
                    cell.getColumnIndex() + 5, cell.getRowIndex() + 6));
                comment.setString(new XSSFRichTextString("必填项,日期格式:\n" + "202109\n"));
                cell.setCellComment(comment);
            }
        }

        // 遍历集合数据,产生数据行
        Iterator<List<String>> it = dataset.iterator();
        int index = 0;
        while (it.hasNext())
        {
            index++;
            row = sheet.createRow(index);
            row.setHeightInPoints(20);  //一般数据的行高为20
            List<String> dataSources = it.next();
            for (int i = 0; i < dataSources.size(); i++)
            {
                SXSSFCell cell = row.createCell(i);
                cell.setCellStyle(commonDataStyle);
                cell.setCellValue(dataSources.get(i));
            }
        }

    }

 

posted @ 2021-11-30 08:54  云村的王子  阅读(1258)  评论(0编辑  收藏  举报