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

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

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

Introduction

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

Prototype

Comment getCellComment();

Source Link

Document

Returns comment associated with this cell

Usage

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void copyCellComment(Cell oldCell, Cell newCell) {
    if (newCell.getCellComment() != null)
        newCell.setCellComment(oldCell.getCellComment());
}

From source file:cn.afterturn.easypoi.util.PoiSheetUtil.java

License:Apache License

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

    switch (cNew.getCellType()) {
    case BOOLEAN: {
        cNew.setCellValue(cOld.getBooleanCellValue());
        break;/*from www  .ja va  2  s . c  o  m*/
    }
    case NUMERIC: {
        cNew.setCellValue(cOld.getNumericCellValue());
        break;
    }
    case STRING: {
        cNew.setCellValue(cOld.getStringCellValue());
        break;
    }
    case ERROR: {
        cNew.setCellValue(cOld.getErrorCellValue());
        break;
    }
    case FORMULA: {
        cNew.setCellFormula(cOld.getCellFormula());
        break;
    }
    default:
        cNew.setCellValue(cOld.getStringCellValue());
    }

}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyCell(Cell srcCell, Cell distCell) {
    distCell.setCellStyle(srcCell.getCellStyle());
    if (srcCell.getCellComment() != null) {
        distCell.setCellComment(srcCell.getCellComment());
    }/*from w  w  w  . j  a v a 2  s.  c o  m*/
    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);

    if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
        if (DateUtil.isCellDateFormatted(srcCell)) {
            distCell.setCellValue(srcCell.getDateCellValue());
        } else {
            distCell.setCellValue(srcCell.getNumericCellValue());
        }
    } else if (srcCellType == Cell.CELL_TYPE_STRING) {
        distCell.setCellValue(srcCell.getRichStringCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_BLANK) {
        //nothing
    } else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
        distCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_ERROR) {
        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    } else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
        distCell.setCellFormula(srcCell.getCellFormula());
    } else {
        //nothing
    }
}

From source file:com.docdoku.server.export.ExcelGenerator.java

License:Open Source License

public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);// ww w  .  ja  va  2  s .c o m

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
        i++;
        data.put(i, createXLSRow(selects, row, baseURL));
        commentsData.put(i, createXLSRowComments(selects, row));
    }

    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

        Row row = sheet.createRow(rownum++);
        String[] objArr = data.get(key);
        int cellnum = 0;
        for (String obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            cell.setCellValue(obj);
        }

        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        String[] commentsObjArr = commentsData.get(key);
        cellnum = 0;
        for (String commentsObj : commentsObjArr) {
            if (commentsObj.length() > 0) {
                Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

                // When the comment box is visible, have it show in a 1x3 space
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 1);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = factory.createRichTextString(commentsObj);
                comment.setString(str);

                // Assign the comment to the cell
                cell.setCellComment(comment);
            }
            cellnum++;
        }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
        Cell cell = sheet.getRow(0).getCell(j);
        cell.setCellStyle(headerStyle);

        if (cell.getCellComment() != null) {
            String comment = cell.getCellComment().getString().toString();

            if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER)
                    || comment.equals(QueryField.PART_MASTER_NUMBER)) {
                for (int k = 0; k < queryResult.getRows().size(); k++) {
                    Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j)
                            : sheet.getRow(k + 1).createCell(j);
                    grayCell.setCellStyle(headerStyle);
                }
            }
        }
    }

    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(excelFile);
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;

}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Insert a row at a given index.//from   w w  w .j  ava2 s  . c o m
 *
 * @param createAtIndex row-number of the cell at which to create a new row
 * @param sourceRow     the row to insert
 */
public void insertRowAt(int createAtIndex, Row sourceRow) {
    Row newRow = getRow(createAtIndex);
    if (newRow != null) {
        // shift all rows >= createAtIndex up by one
        getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1);
    } else {
        newRow = getSheet().createRow(createAtIndex);
    }

    // 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 = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = workbook.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:
            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;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < getSheet().getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            getSheet().addMergedRegion(newCellRangeAddress);
        }
    }
}

From source file:com.qihang.winter.poi.util.PoiSheetUtility.java

License:Apache License

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;//from   w  w w . java2s. c o  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:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java

License:Open Source License

@Override
public void executeActionOnSelection(Spreadsheet spreadsheet, SelectionChangeEvent event) {
    Sheet sheet = spreadsheet.getActiveSheet();
    CellReference cr = event.getSelectedCellReference();
    boolean cellCreated = false, rowCreated = false, commentEdited = false;
    Row row = sheet.getRow(cr.getRow());
    if (row == null) {
        row = sheet.createRow(cr.getRow());
        rowCreated = true;/*  www .j  a  v  a2s . c  o  m*/
    }
    Cell cell = spreadsheet.getCell(cr);
    if (cell == null) {
        cell = row.createCell(cr.getCol());
        cellCreated = true;
    }
    if (cell.getCellComment() == null) {
        createCellComment(spreadsheet, sheet, cell, cr);
        commentEdited = true;
    } else {
        cell.removeCellComment();
        if (cellCreated) {
            sheet.getRow(cr.getRow()).removeCell(cell);
        }
        if (rowCreated) {
            sheet.removeRow(sheet.getRow(cr.getRow()));
        }
    }
    if (cell != null) {
        spreadsheet.refreshCells(cell);
    }
    if (commentEdited) {
        spreadsheet.editCellComment(cr);
    }
}

From source file:de.enerko.reports2.engine.CellDefinition.java

License:Apache License

public CellDefinition(final String sheetname, final Cell cell) {
    final int ct = cell.getCellType();

    Method m = null;/* w  ww. j  av a 2s.c  om*/
    try {
        m = this.getClass().getDeclaredMethod("parse_" + Report.IMPORTABLE_CELL_TYPES.get(new Integer(ct)),
                new Class[] { Cell.class });
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (m == null)
            throw new RuntimeException("Invalid type " + ct);
    }

    try {
        final CellValue cellValue = (CellValue) m.invoke(this, new Object[] { cell });
        this.sheetname = sheetname;
        this.column = cell.getColumnIndex();
        this.row = cell.getRowIndex();
        this.name = CellReferenceHelper.getCellReference(cell.getColumnIndex(), cell.getRowIndex());
        this.type = cellValue.type;
        this.value = cellValue.representation;
        if (cell.getCellComment() == null || cell.getCellComment().getString() == null)
            this.comment = null;
        else
            this.comment = new CommentDefinition(cell.getCellComment());
    } catch (Exception e) {
        throw new RuntimeException(e);
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public String getCommentCellValue(int columnIndex, boolean nullable, boolean trim, boolean useLast)
        throws Exception {
    String value = null;/*ww  w.  j ava2 s.  c o m*/
    Cell cell = getCell(columnIndex);
    if (cell == null) {
        if (nullable == false) {
            throw new Exception("Cell in column " + columnIndex + " has no value!");
        }
    } else {
        Comment comment = cell.getCellComment();
        if (comment == null) {
            if (nullable == false) {
                throw new Exception("Cell in column " + columnIndex + " has no value!");
            }
        } else {
            RichTextString rt = comment.getString();
            if (rt == null) {
                if (nullable == false) {
                    throw new Exception("Cell in column " + columnIndex + " has no value!");
                }
            } else {
                value = rt.getString();
                if (value != null) {
                    value = value.trim();
                }
            }
        }
    }
    if (useLast && (value == null || value.isEmpty())) {
        value = (String) lastValueMap.get(columnIndex);
    } else {
        lastValueMap.put(columnIndex, value);
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public boolean isCellCommentEmpty(int columnIndex) {
    Cell cell = getCell(columnIndex);
    if (cell == null) {
        return true;
    }/*from ww w. ja v  a2 s. com*/
    Comment comment = cell.getCellComment();
    if (comment == null) {
        return true;
    } else {
        RichTextString rt = comment.getString();
        if (rt == null) {
            return true;
        } else {
            return rt.getString() != null ? rt.getString().trim().isEmpty() : true;
        }
    }
}