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:apm.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/* w w w.  j a v a 2  s.  c  o m*/
 * @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.");
}

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 w  ww.  java 2s . 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.cms.utils.ExportExcell.java

/**
 * Tao cac sheet. Chua export ra file excell
 *
 * @param sheetName/*from   w w w  . j a v  a 2  s.  c  o m*/
 * @param lstData
 * @param formatExcell
 * @param key
 */
public void buildSheet(String sheetName, List<?> lstData, List<FormatExcell> formatExcell, String key) {
    Sheet sheetTmp = wb.createSheet(sheetName);
    FormatExcell item;
    Row rowheader = sheetTmp.createRow(0);
    ExportExcellCelltype customCellType = new ExportExcellCelltype(wb);

    Cell cellStt = rowheader.createCell(0);
    cellStt.setCellValue(BundleUtils.getString("STT"));
    cellStt.setCellStyle(customCellType.cellTypeColumnStt());
    for (int i = 0; i < formatExcell.size(); i++) {
        item = formatExcell.get(i);
        Cell cell = rowheader.createCell(i + 1);
        cell.setCellValue(getColumnTitle(item.getCellColumn(), key));
        cell.setCellStyle(customCellType.cellTypeHeader(false));
        sheetTmp.setColumnWidth(i + 1, item.getColumnWidth());
        sheetTmp.setColumnHidden(i + 1, item.getColumnHiden());
        if (item.getHeaderCommand() != null) {
            cell.setCellComment(getcellComment(sheetTmp, item, cell));
        }
        setFormatCell(customCellType, item);
    }
    if (DataUtil.isListNullOrEmpty(formatExcell) || DataUtil.isListNullOrEmpty(lstData)) {
        return;
    }
    buildContentSheet(sheetTmp, customCellType, lstData, formatExcell);
}

From source file:com.cms.utils.ExportExcell.java

public void buildSheet(List<?> lstData, List<FormatExcell> formatExcell, String key) {
    FormatExcell item;// w w w  .j av  a2  s.c o m

    Row rowheader = sh.createRow(0);
    customCellType = new ExportExcellCelltype(wb);

    Cell cellStt = rowheader.createCell(0);
    cellStt.setCellValue(BundleUtils.getString("STT"));
    cellStt.setCellStyle(customCellType.cellTypeColumnStt());
    for (int i = 0; i < formatExcell.size(); i++) {
        item = formatExcell.get(i);
        Cell cell = rowheader.createCell(i + 1);
        cell.setCellValue(getColumnTitle(item.getCellColumn(), key));
        cell.setCellStyle(customCellType.cellTypeHeader(false));
        sh.setColumnWidth(i + 1, item.getColumnWidth());
        sh.setColumnHidden(i + 1, item.getColumnHiden());
        if (item.getHeaderCommand() != null) {
            cell.setCellComment(getcellComment(item, cell));
        }
        setFormatCell(item);
    }
    if (DataUtil.isListNullOrEmpty(formatExcell) || DataUtil.isListNullOrEmpty(lstData)) {
        return;
    }
    buildContentSheet(lstData, formatExcell);

}

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  ww  .  j  a  va  2s.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);//from  ww  w  . j av a2 s . c  om

    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.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

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);/*w  w  w. ja v a 2  s. com*/
        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.");
}

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

License:Open Source License

/**
 * Insert a row at a given index.//from ww w . j  a v a 2 s. c om
 *
 * @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;/* ww w .  j  a  v a 2 s. 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;
    }
    }

}