Example usage for org.apache.poi.ss.usermodel Cell setCellComment

List of usage examples for org.apache.poi.ss.usermodel Cell setCellComment

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. setCellComment

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell setCellComment.

Prototype

void setCellComment(Comment comment);

Source Link

Document

Assign a comment to this cell

Usage

From source file:org.eclipse.emfforms.spreadsheet.integrationtest.ImportErrors_ITest.java

License:Open Source License

@Test
public void testAdditionalInformationSheetIgnored() throws IOException {
    /* setup *///from w  ww.ja v  a  2  s.  c om
    stream = bundle.getEntry("errorSheets/basexls").openStream(); //$NON-NLS-1$
    final Workbook workbook = new HSSFWorkbook(stream);
    final Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("My Sheet")); //$NON-NLS-1$
    final Row titleRow = sheet.createRow(0);
    final Cell idLabelCell = titleRow.createCell(0);
    idLabelCell.setCellValue(EMFFormsIdProvider.ID_COLUMN);
    idLabelCell.setCellComment(createComment(workbook, sheet, 0, 0));
    titleRow.createCell(1).setCellValue("MyColumn"); //$NON-NLS-1$

    for (int i = 1; i < 5; i++) {
        final Row dataRow = sheet.createRow(i);
        dataRow.createCell(0).setCellValue("_5XI6cEG2EeW04_MCsEmiSg"); //$NON-NLS-1$
        dataRow.createCell(1).setCellValue("My Value " + i); //$NON-NLS-1$
    }

    /* act */
    final SpreadsheetImportResult result = EMFFormsSpreadsheetImporter.INSTANCE.importSpreadsheet(workbook,
            eClass);
    final EList<ErrorReport> errorReports = result.getErrorReports();

    /* assert */
    assertEquals(0, errorReports.size());
}

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

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

    switch (cNew.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;//ww  w  .  j  a  v a2 s  .  co m
    }
    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;
    }
    }

}

From source file:org.isisaddons.module.excel.dom.CellMarshaller.java

License:Apache License

private static void setCellComment(final Cell cell, final String commentText) {
    Sheet sheet = cell.getSheet();//from  w w w.  j a v a 2  s.c  o  m
    Row row = cell.getRow();
    Workbook workbook = sheet.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);

    Drawing drawing = sheet.createDrawingPatriarch();
    Comment comment1 = drawing.createCellComment(anchor);

    RichTextString commentRtf = creationHelper.createRichTextString(commentText);

    comment1.setString(commentRtf);
    Comment comment = comment1;
    cell.setCellComment(comment);
}

From source file:org.joeffice.spreadsheet.cell.CellUtils.java

License:Apache License

public static void copyCell(Cell oldCell, Cell newCell) {
    newCell.setCellStyle(oldCell.getCellStyle());

    if (newCell.getCellComment() != null) {
        newCell.setCellComment(oldCell.getCellComment());
    }/*w  ww  .j a v a 2s .c  o m*/

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

    newCell.setCellType(oldCell.getCellType());

    // Set the cell data value
    switch (oldCell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        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;
    }
}

From source file:org.nuxeo.ecm.platform.groups.audit.service.acl.excel.ExcelBuilder.java

License:Open Source License

/** {@inheritDoc} */
@Override//  w  w  w  .  j a  v a 2s.  c  o m
public Comment addComment(Cell cell, String text, int row, int col, int colWidth, int rowHeight) {
    Comment comment = buildComment(text, row, col, colWidth, rowHeight);
    if (comment != null)
        cell.setCellComment(comment);
    return comment;
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(//from   ww  w .j a va  2 s  .  co m
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX,
        Object[] row, int fieldNr, boolean isTitle) throws KettleException {
    try {/*from   ww  w . ja  v a 2 s. c  o m*/
        boolean cellExisted = true;
        // get the cell
        Cell cell = xlsRow.getCell(posX);
        if (cell == null) {
            cellExisted = false;
            cell = xlsRow.createCell(posX);
        }

        // if cell existed and existing cell's styles should not be changed, don't
        if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {

            // if the style of this field is cached, reuse it
            if (!isTitle && data.getCachedStyle(fieldNr) != null) {
                cell.setCellStyle(data.getCachedStyle(fieldNr));
            } else {
                // apply style if requested
                if (excelField != null) {

                    // determine correct cell for title or data rows
                    String styleRef = null;
                    if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) {
                        styleRef = excelField.getStyleCell();
                    } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) {
                        styleRef = excelField.getTitleStyleCell();
                    }

                    if (styleRef != null) {
                        Cell styleCell = getCellFromReference(styleRef);
                        if (styleCell != null && cell != styleCell) {
                            cell.setCellStyle(styleCell.getCellStyle());
                        }
                    }
                }

                // set cell format as specified, specific format overrides cell specification
                if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat())
                        && !excelField.getFormat().startsWith("Image")) {
                    setDataFormat(excelField.getFormat(), cell);
                }
                // cache it for later runs
                if (!isTitle) {
                    data.cacheStyle(fieldNr, cell.getCellStyle());
                }
            }
        }

        // create link on cell if requested
        if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) {
            String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr])
                    .getString(row[data.linkfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(link)) {
                CreationHelper ch = data.wb.getCreationHelper();
                // set the link on the cell depending on link type
                Hyperlink hyperLink = null;
                if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.URL);
                    hyperLink.setLabel("URL Link");
                } else if (link.startsWith("mailto:")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.EMAIL);
                    hyperLink.setLabel("Email Link");
                } else if (link.startsWith("'")) {
                    hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT);
                    hyperLink.setLabel("Link within this document");
                } else {
                    hyperLink = ch.createHyperlink(HyperlinkType.FILE);
                    hyperLink.setLabel("Link to a file");
                }

                hyperLink.setAddress(link);
                cell.setHyperlink(hyperLink);

                // if cell existed and existing cell's styles should not be changed, don't
                if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) {

                    if (data.getCachedLinkStyle(fieldNr) != null) {
                        cell.setCellStyle(data.getCachedLinkStyle(fieldNr));
                    } else {
                        // CellStyle style = cell.getCellStyle();
                        Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex());
                        Font hlink_font = data.wb.createFont();
                        // reporduce original font characteristics

                        hlink_font.setBold(origFont.getBold());
                        hlink_font.setCharSet(origFont.getCharSet());
                        hlink_font.setFontHeight(origFont.getFontHeight());
                        hlink_font.setFontName(origFont.getFontName());
                        hlink_font.setItalic(origFont.getItalic());
                        hlink_font.setStrikeout(origFont.getStrikeout());
                        hlink_font.setTypeOffset(origFont.getTypeOffset());
                        // make it blue and underlined
                        hlink_font.setUnderline(Font.U_SINGLE);
                        hlink_font.setColor(IndexedColors.BLUE.getIndex());
                        CellStyle style = cell.getCellStyle();
                        style.setFont(hlink_font);
                        cell.setCellStyle(style);
                        data.cacheLinkStyle(fieldNr, cell.getCellStyle());
                    }
                }
            }
        }

        // create comment on cell if requrested
        if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0
                && data.wb instanceof XSSFWorkbook) {
            String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr])
                    .getString(row[data.commentfieldnrs[fieldNr]]);
            if (!Utils.isEmpty(comment)) {
                String author = data.commentauthorfieldnrs[fieldNr] >= 0
                        ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString(
                                row[data.commentauthorfieldnrs[fieldNr]])
                        : "Kettle PDI";
                cell.setCellComment(createCellComment(author, comment));
            }
        }
        // cell is getting a formula value or static content
        if (!isTitle && excelField != null && excelField.isFormula()) {
            // formula case
            cell.setCellFormula(vMeta.getString(v));
        } else {
            // static content case
            switch (vMeta.getType()) {
            case ValueMetaInterface.TYPE_DATE:
                if (v != null && vMeta.getDate(v) != null) {
                    cell.setCellValue(vMeta.getDate(v));
                }
                break;
            case ValueMetaInterface.TYPE_BOOLEAN:
                if (v != null) {
                    cell.setCellValue(vMeta.getBoolean(v));
                }
                break;
            case ValueMetaInterface.TYPE_STRING:
            case ValueMetaInterface.TYPE_BINARY:
                if (v != null) {
                    cell.setCellValue(vMeta.getString(v));
                }
                break;
            case ValueMetaInterface.TYPE_BIGNUMBER:
            case ValueMetaInterface.TYPE_NUMBER:
            case ValueMetaInterface.TYPE_INTEGER:
                if (v != null) {
                    cell.setCellValue(vMeta.getNumber(v));
                }
                break;
            default:
                break;
            }
        }
    } catch (Exception e) {
        logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString());
        logError(Const.getStackTracker(e));
        throw new KettleException(e);
    }
}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }//from  ww  w  . j a  v a 2 s . c  om

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            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(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    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);
        }
    }
    return newRow;
}

From source file:org.sigmah.server.endpoint.export.Export.java

License:Open Source License

private void createDataRows(ActivityDTO activity, Sheet sheet) {

    // Create the drawing patriarch. This is the top level container for all shapes including
    // cell comments.
    HSSFPatriarch patr = ((HSSFSheet) sheet).createDrawingPatriarch();

    int rowIndex = 2;
    for (SiteData site : querySites(activity)) {

        Row row = sheet.createRow(rowIndex++);
        int column = 0;

        createCell(row, column++, site.getDate1());
        createCell(row, column++, site.getDate2());
        createCell(row, column++, site.getPartnerName());

        Cell locationCell = createCell(row, column++, site.getLocationName());
        if (site.getComments() != null) {
            Comment comment = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 8, 10));
            comment.setString(creationHelper.createRichTextString(site.getComments()));

            locationCell.setCellComment(comment);
        }//from  www  . j  a  va  2  s.c  o  m

        createCell(row, column++, site.getLocationAxe());

        for (Integer indicatorId : indicators) {
            createIndicatorValueCell(row, column++, site.getIndicatorValue(indicatorId));
        }

        for (Integer attribId : attributes) {

            Boolean value = site.getAttributeValue(attribId);
            if (value != null) {
                Cell valueCell = createCell(row, column, value);
                valueCell.setCellStyle(attribValueStyle);
            }
            column++;
        }

        for (Integer levelId : levels) {
            AdminEntity entity = site.adminEntities.get(levelId);
            if (entity != null) {
                createCell(row, column, entity.getCode());
                createCell(row, column + 1, entity.getName());
            }
            column += 2;
        }

        if (site.hasLatLong()) {
            createCoordCell(row, column++, site.getLongitude());
            createCoordCell(row, column++, site.getLatitude());
        }
    }
}

From source file:org.tiefaces.components.websheet.utility.CellUtility.java

License:MIT License

/**
 * clone existing comments into new cell comment.
 * //from w ww  . j a va 2 s  . c  o  m
 * @param sourceCell
 *            source cell.
 * @param newCell
 *            target cell.
 */

public static void cloneComment(final Cell sourceCell, final Cell newCell) {

    XSSFSheet sheet = (XSSFSheet) newCell.getSheet();
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    XSSFComment sourceComment = (XSSFComment) sourceCell.getCellComment();
    // Below code are from POI busy manual.
    // When the comment box is visible, have it show in a 1x3 space
    ClientAnchor anchor = createCommentAnchor(newCell, factory);
    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(sourceComment.getString().toString());
    comment.setString(str);
    comment.setAuthor(sourceComment.getAuthor());
    // Assign the comment to the cell
    newCell.setCellComment(comment);
    comment.setColumn(newCell.getColumnIndex());
    comment.setRow(newCell.getRowIndex());
    // As POI doesn't has well support for comments,
    // So we have to use low level api to match the comments.
    matchCommentSettings(newCell, sourceCell);
}