org.apache.poi.ss.usermodel.Comment#setString

Source File: ExcelWriterTransform.java From hop with Apache License 2.0

5 votes

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

Source File: CommentWriteHandler.java From easyexcel with Apache License 2.0

5 votes

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

Source File: DefaultExcelView.java From Mario with Apache License 2.0

5 votes

/**
 * 构建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);
    }
}

Source File: ExcelWriterStep.java From pentaho-kettle with Apache License 2.0

5 votes

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

Source File: ExcelExport.java From frpMgr with MIT License

4 votes

/**
	 * 创建工作表
	 * @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);
	}

Source File: ExportExcel.java From Shop-for-JavaWeb with MIT License

4 votes

/**
 * 初始化函数
 * @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.");
}

Source File: SpreadsheetSetCellComment.java From openbd-core with GNU General Public License v3.0

4 votes

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

Source File: DefaultCellCommentHandler.java From xlsmapper with Apache License 2.0

4 votes

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

Java Code Examples for org.apache.poi.ss.usermodel.Comment#setString()