Examples with HSSFComment org.apache.poi.hssf.usermodel.HSSFComment used on opensource projects

Example 1 with HSSFComment

use of org.apache.poi.hssf.usermodel.HSSFComment in project poi by apache.

the class CellComments method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF");
        // Create the drawing patriarch. This is the top level container for all shapes including cell comments.
        HSSFPatriarch patr = sheet.createDrawingPatriarch();
        //create a cell in row 3
        HSSFCell cell1 = sheet.createRow(3).createCell(1);
        cell1.setCellValue(new HSSFRichTextString("Hello, World"));
        //anchor defines size and position of the comment in worksheet
        HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // set text in the comment
        comment1.setString(new HSSFRichTextString("We can set comments in POI"));
        //set comment author.
        //you can see it in the status bar when moving mouse over the commented cell
        comment1.setAuthor("Apache Software Foundation");
        // The first way to assign comment to a cell is via HSSFCell.setCellComment method
        cell1.setCellComment(comment1);
        //create another cell in row 6
        HSSFCell cell2 = sheet.createRow(6).createCell(1);
        cell2.setCellValue(36.6);
        HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 8, (short) 6, 11));
        //modify background color of the comment
        comment2.setFillColor(204, 236, 255);
        HSSFRichTextString string = new HSSFRichTextString("Normal body temperature");
        //apply custom font to the text in the comment
        HSSFFont font = wb.createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        font.setColor(HSSFColorPredefined.RED.getIndex());
        string.applyFont(font);
        comment2.setString(string);
        //by default comments are hidden. This one is always visible.
        comment2.setVisible(true);
        comment2.setAuthor("Bill Gates");
        /**
             * The second way to assign comment to a cell is to implicitly specify its row and column.
             * Note, it is possible to set row and column of a non-existing cell.
             * It works, the comment is visible.
             */
        comment2.setRow(6);
        comment2.setColumn(1);
        FileOutputStream out = new FileOutputStream("poi_comment.xls");
        wb.write(out);
        out.close();
    } finally {
        wb.close();
    }
}

Example 2 with HSSFComment

use of org.apache.poi.hssf.usermodel.HSSFComment in project poi by apache.

the class ExcelExtractor method getText.

@Override
public String getText() {
    StringBuffer text = new StringBuffer();
    // We don't care about the difference between
    //  null (missing) and blank cells
    _wb.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
    // Process each sheet in turn
    for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = _wb.getSheetAt(i);
        if (sheet == null) {
            continue;
        }
        if (_includeSheetNames) {
            String name = _wb.getSheetName(i);
            if (name != null) {
                text.append(name);
                text.append("\n");
            }
        }
        // Header text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getHeader()));
        }
        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();
        for (int j = firstRow; j <= lastRow; j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                continue;
            }
            // Check each cell in turn
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();
            if (_includeBlankCells) {
                firstCell = 0;
            }
            for (int k = firstCell; k < lastCell; k++) {
                HSSFCell cell = row.getCell(k);
                boolean outputContents = true;
                if (cell == null) {
                    // Only output if requested
                    outputContents = _includeBlankCells;
                } else {
                    switch(cell.getCellTypeEnum()) {
                        case STRING:
                            text.append(cell.getRichStringCellValue().getString());
                            break;
                        case NUMERIC:
                            text.append(_formatter.formatCellValue(cell));
                            break;
                        case BOOLEAN:
                            text.append(cell.getBooleanCellValue());
                            break;
                        case ERROR:
                            text.append(ErrorEval.getText(cell.getErrorCellValue()));
                            break;
                        case FORMULA:
                            if (!_shouldEvaluateFormulas) {
                                text.append(cell.getCellFormula());
                            } else {
                                switch(cell.getCachedFormulaResultTypeEnum()) {
                                    case STRING:
                                        HSSFRichTextString str = cell.getRichStringCellValue();
                                        if (str != null && str.length() > 0) {
                                            text.append(str);
                                        }
                                        break;
                                    case NUMERIC:
                                        HSSFCellStyle style = cell.getCellStyle();
                                        double nVal = cell.getNumericCellValue();
                                        short df = style.getDataFormat();
                                        String dfs = style.getDataFormatString();
                                        text.append(_formatter.formatRawCellContents(nVal, df, dfs));
                                        break;
                                    case BOOLEAN:
                                        text.append(cell.getBooleanCellValue());
                                        break;
                                    case ERROR:
                                        text.append(ErrorEval.getText(cell.getErrorCellValue()));
                                        break;
                                    default:
                                        throw new IllegalStateException("Unexpected cell cached formula result type: " + cell.getCachedFormulaResultTypeEnum());
                                }
                            }
                            break;
                        default:
                            throw new RuntimeException("Unexpected cell type (" + cell.getCellTypeEnum() + ")");
                    }
                    // Output the comment, if requested and exists
                    HSSFComment comment = cell.getCellComment();
                    if (_includeCellComments && comment != null) {
                        // Replace any newlines with spaces, otherwise it
                        //  breaks the output
                        String commentText = comment.getString().getString().replace('\n', ' ');
                        text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
                    }
                }
                // Output a tab if we're not on the last cell
                if (outputContents && k < (lastCell - 1)) {
                    text.append("\t");
                }
            }
            // Finish off the row
            text.append("\n");
        }
        // Finally Footer text, if there is any
        if (_includeHeadersFooters) {
            text.append(_extractHeaderFooter(sheet.getFooter()));
        }
    }
    return text.toString();
}

Example 3 with HSSFComment

use of org.apache.poi.hssf.usermodel.HSSFComment in project poi by apache.

the class TestDrawingShapes method testRemoveShapes.

@Test
public void testRemoveShapes() throws IOException {
    HSSFWorkbook wb1 = new HSSFWorkbook();
    HSSFSheet sheet = wb1.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
    int idx = wb1.addPicture(new byte[] { 1, 2, 3 }, Workbook.PICTURE_TYPE_JPEG);
    patriarch.createPicture(new HSSFClientAnchor(), idx);
    patriarch.createCellComment(new HSSFClientAnchor());
    HSSFPolygon polygon = patriarch.createPolygon(new HSSFClientAnchor());
    polygon.setPoints(new int[] { 1, 2 }, new int[] { 2, 3 });
    patriarch.createTextbox(new HSSFClientAnchor());
    HSSFShapeGroup group = patriarch.createGroup(new HSSFClientAnchor());
    group.createTextbox(new HSSFChildAnchor());
    group.createPicture(new HSSFChildAnchor(), idx);
    assertEquals(patriarch.getChildren().size(), 6);
    assertEquals(group.getChildren().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
    wb1.close();
    sheet = wb2.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 6);
    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    group.removeShape(group.getChildren().get(0));
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb3 = HSSFTestDataSamples.writeOutAndReadBack(wb2);
    wb2.close();
    sheet = wb3.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    group = (HSSFShapeGroup) patriarch.getChildren().get(5);
    patriarch.removeShape(group);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    HSSFWorkbook wb4 = HSSFTestDataSamples.writeOutAndReadBack(wb3);
    wb3.close();
    sheet = wb4.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 5);
    HSSFShape shape = patriarch.getChildren().get(0);
    patriarch.removeShape(shape);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);
    HSSFWorkbook wb5 = HSSFTestDataSamples.writeOutAndReadBack(wb4);
    wb4.close();
    sheet = wb5.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 4);
    HSSFPicture picture = (HSSFPicture) patriarch.getChildren().get(0);
    patriarch.removeShape(picture);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);
    HSSFWorkbook wb6 = HSSFTestDataSamples.writeOutAndReadBack(wb5);
    wb5.close();
    sheet = wb6.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);
    HSSFComment comment = (HSSFComment) patriarch.getChildren().get(0);
    patriarch.removeShape(comment);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);
    HSSFWorkbook wb7 = HSSFTestDataSamples.writeOutAndReadBack(wb6);
    wb6.close();
    sheet = wb7.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 2);
    polygon = (HSSFPolygon) patriarch.getChildren().get(0);
    patriarch.removeShape(polygon);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);
    HSSFWorkbook wb8 = HSSFTestDataSamples.writeOutAndReadBack(wb7);
    wb7.close();
    sheet = wb8.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 1);
    HSSFTextbox textbox = (HSSFTextbox) patriarch.getChildren().get(0);
    patriarch.removeShape(textbox);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
    HSSFWorkbook wb9 = HSSFTestDataSamples.writeOutAndReadBack(wb8);
    wb8.close();
    sheet = wb9.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
    assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
    wb9.close();
}