Java Code Examples for org.apache.poi.ss.usermodel.Cell#getCellComment()

public static void copyCellStyle(Cell source, Cell target){
	CellStyle style = source.getCellStyle();
	if(style!=null){
		//TODO:会影响性能, 可缓存。。。
		CellStyle newCellStyle = source.getRow().getSheet().getWorkbook().createCellStyle();
		newCellStyle.cloneStyleFrom(style);
		target.setCellStyle(style);
	}
	if(source.getCellComment()!=null){
		target.setCellComment(source.getCellComment());
	}
	if(source.getHyperlink()!=null){
		target.setHyperlink(source.getHyperlink());
	}
	target.setCellType(source.getCellType());
}
 

public void associateToComment(Cell cell, TopicMap tm) throws TopicMapException {
    if(cell.getCellComment() != null) {
        Topic commentTypeTopic = getCommentTypeTopic(tm);
        Topic commentTopic = getCommentTopic(cell, tm);
        Topic cellTypeTopic = getCellTypeTopic(tm);
        Topic cellTopic = getCellTopic(cell, tm);

        if(commentTypeTopic != null && commentTopic != null && cellTypeTopic != null && cellTopic != null) {
            Association a = tm.createAssociation(commentTypeTopic);
            a.addPlayer(cellTopic, cellTypeTopic);
            a.addPlayer(commentTopic, commentTypeTopic);
        }
    }
}
 

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;
}
 

/**
 * 結合を考慮したセルのコメントを取得する。
 * @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 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;
}
 

public static void copyRow(Sheet worksheet, Row newRow, Row sourceRow) {
Workbook workbook = worksheet.getWorkbook();
      for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
          Cell oldCell = sourceRow.getCell(i);
          Cell newCell = newRow.createCell(i);

          if (oldCell == null) {
              newCell = null;
              continue;
          }

          CellStyle newCellStyle = workbook.createCellStyle();
          newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
          newCell.setCellStyle(newCellStyle);

          if (oldCell.getCellComment() != null) {
              newCell.setCellComment(oldCell.getCellComment());
          }

          if (oldCell.getHyperlink() != null) {
              newCell.setHyperlink(oldCell.getHyperlink());
          }

          newCell.setCellType(oldCell.getCellType());

          switch (oldCell.getCellType()) {
              case Cell.CELL_TYPE_BLANK:
                  newCell.setCellValue(oldCell.getStringCellValue());
                  break;
              case Cell.CELL_TYPE_BOOLEAN:
                  newCell.setCellValue(oldCell.getBooleanCellValue());
                  break;
              case Cell.CELL_TYPE_ERROR:
                  newCell.setCellErrorValue(oldCell.getErrorCellValue());
                  break;
              case Cell.CELL_TYPE_FORMULA:
                  newCell.setCellFormula(oldCell.getCellFormula());
                  break;
              case Cell.CELL_TYPE_NUMERIC:
                  newCell.setCellValue(oldCell.getNumericCellValue());
                  break;
              case Cell.CELL_TYPE_STRING:
                  newCell.setCellValue(oldCell.getRichStringCellValue());
                  break;
          }
      }

      for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
          CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
          if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
              CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                      (newRow.getRowNum() +
                              (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                      )),
                      cellRangeAddress.getFirstColumn(),
                      cellRangeAddress.getLastColumn());
              worksheet.addMergedRegion(newCellRangeAddress);
          }
      }
  }
 

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 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);
    
}