org.apache.poi.ss.usermodel.Comment Java Examples

private Comment createCellComment( String author, String comment ) {
  // comments only supported for XLSX
  if ( data.sheet instanceof XSSFSheet ) {
    CreationHelper factory = data.wb.getCreationHelper();
    Drawing drawing = data.sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    Comment cmt = drawing.createCellComment( anchor );
    RichTextString str = factory.createRichTextString( comment );
    cmt.setString( str );
    cmt.setAuthor( author );
    return cmt;
  }
  return null;
}
 

private Comment createCellComment( String author, String comment ) {
  // comments only supported for XLSX
  if ( data.sheet instanceof XSSFSheet ) {
    CreationHelper factory = data.wb.getCreationHelper();
    Drawing drawing = data.sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    Comment cmt = drawing.createCellComment( anchor );
    RichTextString str = factory.createRichTextString( comment );
    cmt.setString( str );
    cmt.setAuthor( author );
    return cmt;
  }
  return null;
}
 

/**
 * 結合を考慮したセルのコメントを取得する。
 * @param cell 元となるセル。
 * @return コメント。コメントが設定されていなければ、nullを返す。
 */
private Comment getMergedCellComment(final Cell cell) {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        return comment;
    }
    
    final Sheet sheet = cell.getSheet();
    final int size = sheet.getNumMergedRegions();
    
    for(int i=0; i < size; i++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(!range.isInRange(cell)) {
            continue;
        }
        
        // nullでないセルを取得する。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            final Row row = sheet.getRow(rowIdx);
            if(row == null) {
                continue;
            }

            for(int colIdx=range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {
                final Cell valueCell = row.getCell(colIdx);
                if(valueCell == null) {
                    continue;
                }

                comment = valueCell.getCellComment();
                if(comment != null) {
                    return comment;
                }
            }
        }
    }
    
    return null;
    
}
 

@Override
public Optional<String> handleLoad(final Cell cell, Optional<XlsCommentOption> commentOption) {
    
    Comment comment = getMergedCellComment(cell);
    if(comment == null) {
        return Optional.empty();
    }
    
    String commentText = comment.getString().getString();
    return Optional.of(commentText);
}
 

public Topic getCommentTopic(Cell cell, TopicMap tm) throws TopicMapException {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        RichTextString rts = comment.getString();
        String str = rts.getString();
        String basename = str.replace('\n', ' ');
        basename = basename.replace('\r', ' ');
        basename = basename.replace('\t', ' ');
        Topic topic=getOrCreateTopic(tm, EXCEL_COMMENT_SI_PREFIX+"/"+urlEncode(basename), basename);
        topic.setData(getCommentTypeTopic(tm), tm.getTopic(XTMPSI.getLang(DEFAULT_LANG)), str);
        topic.addType(getCommentTypeTopic(tm));
        return topic;
    }
    return null;
}
 

/**
 * 构建excel的表头
 * 
 * @param filename
 * @param headerList
 */
private void buildExcelHead(String filename, HSSFWorkbook workbook) {
    // Initialize
    List<String> headerList = Lists.newArrayList();
    for (Object[] os : annotationList) {
        String t = ((ExcelField) os[0]).title();
        headerList.add(t);
    }

    sheet = workbook.createSheet("导出数据");

    // Create header
    Row headerRow = sheet.createRow(rownum++);
    headerRow.setHeightInPoints(16);
    for (int i = 0; i < headerList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        String[] ss = StringUtils.split(headerList.get(i), "**", 2);
        if (ss.length == 2) {
            cell.setCellValue(ss[0]);
            Comment comment = sheet.createDrawingPatriarch().createCellComment(
                    new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
            comment.setString(new XSSFRichTextString(ss[1]));
            cell.setCellComment(comment);
        } else {
            cell.setCellValue(headerList.get(i));
        }
        sheet.autoSizeColumn(i);
    }
    for (int i = 0; i < headerList.size(); i++) {
        int colWidth = sheet.getColumnWidth(i) * 2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
    }
}
 

/**
 * Assign a comment to this cell. If the supplied
 *  comment is null, the comment for this cell
 *  will be removed.
 *
 * @param comment comment associated with this cell
 */
public void setCellComment(Comment comment){
    if(comment == null) {
        removeCellComment();
        return;
    }

    comment.setRow(_record.getRow());
    comment.setColumn(_record.getColumn());
    _comment = (HSSFComment)comment;
}
 

@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
    Integer relativeRowIndex, Boolean isHead) {
    if (isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
        // 在第一行 第二列创建一个批注
        Comment comment =
            drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short)1, 0, (short)2, 1));
        // 输入批注信息
        comment.setString(new XSSFRichTextString("创建批注!"));
        // 将批注添加到单元格对象中
        sheet.getRow(0).getCell(1).setCellComment(comment);
    }
}
 

public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	if ( parameters.get(2).getDataType() != cfData.CFSTRUCTDATA )
		throwException(_session, "parameter must be of type structure");

	cfSpreadSheetData	spreadsheet = null;
	cfStructData commentS = null;
	int rowNo, columnNo;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(3);
commentS		= (cfStructData)parameters.get(2);
rowNo				= parameters.get(1).getInt() - 1;
columnNo		= parameters.get(0).getInt() - 1;
		
if ( rowNo < 0 )
	throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
	throwException(_session, "column must be 1 or greater (" + columnNo + ")");


/*
 * Perform the insertion
 */
Sheet	sheet = spreadsheet.getActiveSheet();
Row row	= sheet.getRow( rowNo );
if ( row == null )
	row	= sheet.createRow( rowNo );

Cell cell	= row.getCell( columnNo );
if ( cell == null )
	cell = row.createCell( columnNo );


// Create the anchor
HSSFClientAnchor clientAnchor = new HSSFClientAnchor();
if ( commentS.containsKey("anchor") ){
	String[] anchor	= commentS.getData("anchor").getString().split(",");
	if ( anchor.length != 4 )
		throwException(_session,"Invalid 'anchor' attribute, should be 4 numbers");
		
		clientAnchor.setRow1( Integer.valueOf( anchor[0] ) - 1 );
		clientAnchor.setCol1( Integer.valueOf( anchor[1] ) - 1 );
		clientAnchor.setRow2( Integer.valueOf( anchor[2] ) - 1 );
		clientAnchor.setCol2( Integer.valueOf( anchor[3] ) - 1 );
}else{
		clientAnchor.setRow1( rowNo );
		clientAnchor.setCol1( columnNo );
		clientAnchor.setRow2( rowNo + 2 );
		clientAnchor.setCol2( columnNo + 2 );
}

// Create the comment
Comment comment = spreadsheet.getActiveSheet().createDrawingPatriarch().createCellComment(clientAnchor);

if ( commentS.containsKey("author") ){
	comment.setAuthor( commentS.getData("author").getString() );
}

if ( commentS.containsKey("visible") ){
	comment.setVisible( commentS.getData("visible").getBoolean() );
}

if ( commentS.containsKey("comment") ){
	HSSFRichTextString richText = new HSSFRichTextString( commentS.getData("comment").getString() );
	try {
		richText.applyFont( SpreadSheetFormatOptions.createCommentFont(spreadsheet.getWorkBook(), commentS) );
	} catch (Exception e) {
		throwException( _session, e.getMessage() );
	}
	
	comment.setString( richText );
}

cell.setCellComment( comment );
	return cfBooleanData.TRUE;
}
 

private void extractContent(final StringBuilder buffy, final XSSFWorkbook document) {
    for (int i = 0; i < document.getNumberOfSheets(); i++) {
        final XSSFSheet sheet = document.getSheetAt(i);
        buffy.append(document.getSheetName(i)).append(' ');

        // Header(s), if present
        extractHeaderFooter(buffy, sheet.getFirstHeader());
        extractHeaderFooter(buffy, sheet.getOddHeader());
        extractHeaderFooter(buffy, sheet.getEvenHeader());

        // Rows and cells
        for (final Object rawR : sheet) {
            final Row row = (Row) rawR;
            for (final Iterator<Cell> ri = row.cellIterator(); ri.hasNext();) {
                final Cell cell = ri.next();

                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    buffy.append(cell.getRichStringCellValue().getString()).append(' ');
                } else {
                    final XSSFCell xc = (XSSFCell) cell;
                    final String rawValue = xc.getRawValue();
                    if (rawValue != null) {
                        buffy.append(rawValue).append(' ');
                    }

                }

                // Output the comment in the same cell as the content
                final Comment comment = cell.getCellComment();
                if (comment != null) {
                    buffy.append(comment.getString().getString()).append(' ');
                }
            }
        }

        // Finally footer(s), if present
        extractHeaderFooter(buffy, sheet.getFirstFooter());
        extractHeaderFooter(buffy, sheet.getOddFooter());
        extractHeaderFooter(buffy, sheet.getEvenFooter());
    }
}
 

@Override
public Object[] getNext() {

	SpreadSheetCellDAO[] result=null;
	// all sheets?
	if (this.sheets==null) { //  go on with all sheets
		if (!nextAllSheets()) {
			return result;
		}
	} else { // go on with specified sheets
		if (!nextSpecificSheets()) {
			return result;
		}
	}
	// read row from the sheet currently to be processed
	Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet);
	Row rRow = rSheet.getRow(this.currentRow);
	if ((rRow==null) || (rRow.getLastCellNum()<0)) {
		this.currentRow++;
		return new SpreadSheetCellDAO[0]; // emtpy row
	}
	result = new SpreadSheetCellDAO[rRow.getLastCellNum()];
	for (int i=0;i<rRow.getLastCellNum();i++) {
		Cell currentCell=rRow.getCell(i);
		if (currentCell==null) {
			result[i]=null;
		} else {	
			String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator);
			String formula = "";
			if (currentCell.getCellType()==CellType.FORMULA)  {
				formula = currentCell.getCellFormula();
			}
			Comment currentCellComment = currentCell.getCellComment();
			String comment = "";
			if (currentCellComment!=null) {
				comment = currentCellComment.getString().getString();
			}
			String address = currentCell.getAddress().toString();
			String sheetName = currentCell.getSheet().getSheetName();
			SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue,comment,formula,address,sheetName);
			
			result[i]=mySpreadSheetCellDAO;
		}
	}
	
	// increase rows
	this.currentRow++;
	return result;
}
 

@Override
public Comment getCellComment(CellAddress ref) {
    throw new UnsupportedOperationException();
}
 

@Override
public Map<CellAddress, ? extends Comment> getCellComments() {
    throw new UnsupportedOperationException();
}
 

public Comment getCellComment(int row, int column) {
	return sheet.getCellComment(row, column);
}
 

/**
 * Not supported
 */
@Override
public Comment getCellComment(CellAddress cellAddress) {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public Map<CellAddress, ? extends Comment> getCellComments() {
  throw new UnsupportedOperationException();
}
 

/**
 * Not supported
 */
@Override
public void setCellComment(Comment comment) {
  throw new NotSupportedException();
}
 

/**
 * Not supported
 */
@Override
public Comment getCellComment() {
  throw new NotSupportedException();
}
 

/**
 * 初始化函数
 * @param title 表格标题，传“空值”，表示无标题
 * @param headerList 表头列表
 */
private void initialize(String title, List<String> headerList) {
	this.wb = new SXSSFWorkbook(500);
	this.sheet = wb.createSheet("Export");
	this.styles = createStyles(wb);
	// Create title
	if (StringUtils.isNotBlank(title)){
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints(30);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellStyle(styles.get("title"));
		titleCell.setCellValue(title);
		sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
				titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
	}
	// Create header
	if (headerList == null){
		throw new RuntimeException("headerList not null!");
	}
	Row headerRow = sheet.createRow(rownum++);
	headerRow.setHeightInPoints(16);
	for (int i = 0; i < headerList.size(); i++) {
		Cell cell = headerRow.createCell(i);
		cell.setCellStyle(styles.get("header"));
		String[] ss = StringUtils.split(headerList.get(i), "**", 2);
		if (ss.length==2){
			cell.setCellValue(ss[0]);
			Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
					new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
			comment.setString(new XSSFRichTextString(ss[1]));
			cell.setCellComment(comment);
		}else{
			cell.setCellValue(headerList.get(i));
		}
		sheet.autoSizeColumn(i);
	}
	for (int i = 0; i < headerList.size(); i++) {  
		int colWidth = sheet.getColumnWidth(i)*2;
        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  
	}
	log.debug("Initialize success.");
}
 

@Override
public void handleSave(final Cell cell, final Optional<String> text, final Optional<XlsCommentOption> commentOption) {
    
    if(!text.isPresent()) {
        // コメントが空のとき
        commentOption.ifPresent(option -> {
            if(option.removeIfEmpty()) {
                // コメントが空のとき既存のコメントを削除する
                cell.removeCellComment();
            }
        });
        return;
    }
    
    final Sheet sheet = cell.getSheet();
    final CreationHelper helper = sheet.getWorkbook().getCreationHelper();
    final Drawing<?> drawing = sheet.createDrawingPatriarch();
    
    final Comment comment;
    RichTextString richText = helper.createRichTextString(text.get());
    if(cell.getCellComment() == null) {
        ClientAnchor anchor = createAnchor(drawing, text.get(), cell, commentOption);
        comment = drawing.createCellComment(anchor);
        applyCommentFormat(richText, cell);
    } else {
        // 既存のコメントが存在する場合は、書式やサイズをコピーして使用する。
        comment = cell.getCellComment();
        RichTextString orgText = comment.getString();
        if(orgText.numFormattingRuns() > 0) {
            copyCommentFormat(richText, orgText);
        } else {
            applyCommentFormat(richText, cell);
        }
    }
    
    comment.setString(richText);
    
    // コメントの表示状態の更新
    commentOption.ifPresent(option -> comment.setVisible(option.visible()));
    
    cell.setCellComment(comment);
    
}
 

/**
	 * 创建工作表
	 * @param sheetName 指定Sheet名称
	 * @param title 表格标题，传“空值”，表示无标题
	 * @param headerList 表头字段设置
	 * @param headerWidthList 表头字段宽度设置
	 */
	public void createSheet(String sheetName, String title, List<String> headerList, List<Integer> headerWidthList) {
		this.sheet = wb.createSheet(StringUtils.defaultString(sheetName, StringUtils.defaultString(title, "Sheet1")));
		this.styles = createStyles(wb);
		this.rownum = 0;
		// Create title
		if (StringUtils.isNotBlank(title)){
			Row titleRow = sheet.createRow(rownum++);
			titleRow.setHeightInPoints(30);
			Cell titleCell = titleRow.createCell(0);
			titleCell.setCellStyle(styles.get("title"));
			titleCell.setCellValue(title);
			sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
					titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
		}
		// Create header
		if (headerList == null){
			throw new ExcelException("headerList not null!");
		}
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints(16);
		for (int i = 0; i < headerList.size(); i++) {
			Cell cell = headerRow.createCell(i);
			cell.setCellStyle(styles.get("header"));
			String[] ss = StringUtils.split(headerList.get(i), "**", 2);
			if (ss.length==2){
				cell.setCellValue(ss[0]);
				Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
						new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
				comment.setRow(cell.getRowIndex());
				comment.setColumn(cell.getColumnIndex());
				comment.setString(new XSSFRichTextString(ss[1]));
				cell.setCellComment(comment);
			}else{
				cell.setCellValue(headerList.get(i));
			}
//			sheet.autoSizeColumn(i);
		}
		boolean isDefWidth = (headerWidthList != null && headerWidthList.size() == headerList.size());
		for (int i = 0; i < headerList.size(); i++) {
			int colWidth = -1;
			if (isDefWidth){
				colWidth = headerWidthList.get(i);
			}
			if (colWidth == -1){
				colWidth = sheet.getColumnWidth(i)*2;
				colWidth = colWidth < 3000 ? 3000 : colWidth;
			}
			if (colWidth == 0){
				sheet.setColumnHidden(i, true);
			}else{
				sheet.setColumnWidth(i, colWidth);  
			}
		}
		log.debug("Create sheet {} success.", sheetName);
	}