org.apache.poi.ss.usermodel.Cell#setCellComment

Source File: ExcelUtils.java From onetwo with Apache License 2.0

6 votes

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

Source File: PoiSheetUtility.java From autopoi with Apache License 2.0

5 votes

private static void cloneCell(Cell cNew, Cell cOld) {
	cNew.setCellComment(cOld.getCellComment());
	cNew.setCellStyle(cOld.getCellStyle());

	switch (cNew.getCellType()) {
	case Cell.CELL_TYPE_BOOLEAN: {
		cNew.setCellValue(cOld.getBooleanCellValue());
		break;
	}
	case Cell.CELL_TYPE_NUMERIC: {
		cNew.setCellValue(cOld.getNumericCellValue());
		break;
	}
	case Cell.CELL_TYPE_STRING: {
		cNew.setCellValue(cOld.getStringCellValue());
		break;
	}
	case Cell.CELL_TYPE_ERROR: {
		cNew.setCellValue(cOld.getErrorCellValue());
		break;
	}
	case Cell.CELL_TYPE_FORMULA: {
		cNew.setCellFormula(cOld.getCellFormula());
		break;
	}
	}

}

Source File: PoiSheetUtility.java From jeasypoi with Apache License 2.0

5 votes

private static void cloneCell(Cell cNew, Cell cOld) {
	cNew.setCellComment(cOld.getCellComment());
	cNew.setCellStyle(cOld.getCellStyle());

	switch (cNew.getCellType()) {
	case Cell.CELL_TYPE_BOOLEAN: {
		cNew.setCellValue(cOld.getBooleanCellValue());
		break;
	}
	case Cell.CELL_TYPE_NUMERIC: {
		cNew.setCellValue(cOld.getNumericCellValue());
		break;
	}
	case Cell.CELL_TYPE_STRING: {
		cNew.setCellValue(cOld.getStringCellValue());
		break;
	}
	case Cell.CELL_TYPE_ERROR: {
		cNew.setCellValue(cOld.getErrorCellValue());
		break;
	}
	case Cell.CELL_TYPE_FORMULA: {
		cNew.setCellFormula(cOld.getCellFormula());
		break;
	}
	}

}

Source File: PoiSheetUtility.java From easypoi with Apache License 2.0

5 votes

private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    switch (cNew.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN: {
            cNew.setCellValue(cOld.getBooleanCellValue());
            break;
        }
        case Cell.CELL_TYPE_NUMERIC: {
            cNew.setCellValue(cOld.getNumericCellValue());
            break;
        }
        case Cell.CELL_TYPE_STRING: {
            cNew.setCellValue(cOld.getStringCellValue());
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            cNew.setCellValue(cOld.getErrorCellValue());
            break;
        }
        case Cell.CELL_TYPE_FORMULA: {
            cNew.setCellFormula(cOld.getCellFormula());
            break;
        }
    }

}

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

5 votes

public static void cloneCell( Cell cNew, Cell cOld ){
	cNew.setCellComment( cOld.getCellComment() );
	cNew.setCellStyle( cOld.getCellStyle() );
	cNew.setCellType( cOld.getCellType() );
	
	switch ( cNew.getCellType() ){
		case Cell.CELL_TYPE_BOOLEAN:{
			cNew.setCellValue( cOld.getBooleanCellValue() );
			break;
		}
		case Cell.CELL_TYPE_NUMERIC:{
			cNew.setCellValue( cOld.getNumericCellValue() );
			break;
		}
		case Cell.CELL_TYPE_STRING:{
			cNew.setCellValue( cOld.getStringCellValue() );
			break;
		}
		case Cell.CELL_TYPE_ERROR:{
			cNew.setCellValue( cOld.getErrorCellValue() );
			break;
		}
		case Cell.CELL_TYPE_FORMULA:{
			cNew.setCellFormula( cOld.getCellFormula() );
			break;
		}
		case Cell.CELL_TYPE_BLANK:{
			cNew.setCellValue( cOld.getNumericCellValue() );
			break;
		}
			
	}
	
}

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: 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: ExcelUtils.java From onetwo with Apache License 2.0

4 votes

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

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.Cell#setCellComment()