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

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

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

Introduction

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

Prototype

Hyperlink getHyperlink();

Source Link

Usage

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

License:Apache License

private static void copyCellHyperlink(Cell oldCell, Cell newCell) {
    if (oldCell.getHyperlink() != null)
        newCell.setHyperlink(oldCell.getHyperlink());
}

From source file:com.helger.poi.excel.ExcelReadUtils.java

License:Apache License

@Nullable
public static Hyperlink getHyperlink(@Nullable final Cell aCell) {
    return aCell == null ? null : aCell.getHyperlink();
}

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

License:Open Source License

/**
 * Insert a row at a given index.//from ww  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.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Removes all the cells within the given bounds from the Spreadsheet and
 * the underlying POI model.// w  ww  .j  a v a 2 s .  c om
 *
 * @param firstRow
 *            Starting row index, 1-based
 * @param firstColumn
 *            Starting column index, 1-based
 * @param lastRow
 *            Ending row index, 1-based
 * @param lastColumn
 *            Ending column index, 1-based
 * @param clearRemovedCellStyle
 *            true to also clear styles from the removed cells
 */
protected void removeCells(int firstRow, int firstColumn, int lastRow, int lastColumn,
        boolean clearRemovedCellStyle) {
    final Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    for (int i = firstRow - 1; i < lastRow; i++) {
        Row row = activeSheet.getRow(i);
        if (row != null) {
            for (int j = firstColumn - 1; j < lastColumn; j++) {
                Cell cell = row.getCell(j);
                if (cell != null) {
                    final String key = SpreadsheetUtil.toKey(j + 1, i + 1);
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        sentFormulaCells.remove(key);
                    } else {
                        sentCells.remove(key);
                    }
                    if (cell.getHyperlink() != null) {
                        removeHyperlink(cell, activeSheet);
                    }
                    if (clearRemovedCellStyle) {
                        // update style to 0
                        cell.setCellStyle(null);
                        spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(cell, true);
                    }
                    // need to make protection etc. settings for the cell
                    // won't get effected. deleting the cell would make it
                    // locked
                    if (clearRemovedCellStyle || cell.getCellStyle().getIndex() == 0) {
                        CellData cd = new CellData();
                        cd.col = j + 1;
                        cd.row = i + 1;
                        removedCells.add(cd);
                    } else {
                        markedCells.add(key);
                    }
                    cell.setCellValue((String) null);
                    getFormulaEvaluator().notifyUpdateCell(cell);
                }
            }
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Removes an individual cell from the Spreadsheet and the underlying POI
 * model.//from w  w w  .j av  a2s .  com
 *
 * @param rowIndex
 *            Row index of target cell, 1-based
 * @param colIndex
 *            Column index of target cell, 1-based
 * @param clearRemovedCellStyle
 *            true to also clear styles from the removed cell
 */
protected void removeCell(int rowIndex, int colIndex, boolean clearRemovedCellStyle) {
    final Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    final Row row = activeSheet.getRow(rowIndex - 1);
    if (row != null) {
        final Cell cell = row.getCell(colIndex - 1);
        if (cell != null) {
            CellData cd = new CellData();
            cd.col = colIndex;
            cd.row = rowIndex;
            final String key = SpreadsheetUtil.toKey(colIndex, rowIndex);
            if (clearRemovedCellStyle || cell.getCellStyle().getIndex() == 0) {
                removedCells.add(cd);
            } else {
                markedCells.add(key);
            }
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                sentFormulaCells.remove(key);
            } else {
                sentCells.remove(key);
            }
            // POI (3.9) doesn't have a method for removing a hyperlink !!!
            if (cell.getHyperlink() != null) {
                removeHyperlink(cell, activeSheet);
            }
            if (clearRemovedCellStyle) {
                // update style to 0
                cell.setCellStyle(null);
                spreadsheet.getSpreadsheetStyleFactory().cellStyleUpdated(cell, true);
            }
            cell.setCellValue((String) null);
            getFormulaEvaluator().notifyUpdateCell(cell);
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.CellValueManager.java

License:Open Source License

/**
 * Removes hyperlink from the given cell
 *
 * @param cell/*from   w  w  w .  ja  v  a 2s. c  om*/
 *            Target cell
 * @param sheet
 *            Sheet the target cell belongs to
 */
protected void removeHyperlink(Cell cell, Sheet sheet) {
    try {
        if (sheet instanceof XSSFSheet) {
            Field f;
            f = XSSFSheet.class.getDeclaredField("hyperlinks");
            f.setAccessible(true);
            @SuppressWarnings("unchecked")
            List<XSSFHyperlink> hyperlinks = (List<XSSFHyperlink>) f.get(sheet);
            hyperlinks.remove(cell.getHyperlink());
            f.setAccessible(false);
        } else if (sheet instanceof HSSFSheet && cell instanceof HSSFCell) {
            HSSFHyperlink link = (HSSFHyperlink) cell.getHyperlink();
            Field sheetField = HSSFSheet.class.getDeclaredField("_sheet");
            sheetField.setAccessible(true);
            InternalSheet internalsheet = (InternalSheet) sheetField.get(sheet);
            List<RecordBase> records = internalsheet.getRecords();
            Field recordField = HSSFHyperlink.class.getDeclaredField("record");
            recordField.setAccessible(true);
            records.remove(recordField.get(link));
            sheetField.setAccessible(false);
            recordField.setAccessible(false);
        }
    } catch (SecurityException e) {
        LOGGER.log(Level.FINEST, e.getMessage(), e);
    } catch (NoSuchFieldException e) {
        LOGGER.log(Level.FINEST, e.getMessage(), e);
    } catch (IllegalArgumentException e) {
        LOGGER.log(Level.FINEST, e.getMessage(), e);
    } catch (IllegalAccessException e) {
        LOGGER.log(Level.FINEST, e.getMessage(), e);
    }
}

From source file:com.vaadin.addon.spreadsheet.DefaultHyperlinkCellClickHandler.java

License:Open Source License

@Override
public void onHyperLinkCellClick(Cell cell, Hyperlink hyperlink, Spreadsheet spreadsheet) {
    if (hyperlink != null) {
        if (hyperlink.getType() == LINK_DOCUMENT) { // internal
            navigateTo(cell, spreadsheet, hyperlink.getAddress());
        } else {//  ww w .j  a  v a  2 s .c o  m
            spreadsheet.getUI().getPage().open(cell.getHyperlink().getAddress(), "_new");
        }
    } else if (isHyperlinkFormulaCell(cell)) {
        String address = getHyperlinkFunctionCellAddress(cell);
        if (address.startsWith("#")) { // inter-sheet address
            navigateTo(cell, spreadsheet, address.substring(1));
        } else if (address.startsWith("[") && address.contains("]")) {
            // FIXME: for now we assume that the hyperlink points to the
            // current file. Should check file name against
            // address.substring(1, address.indexOf("]"));
            navigateTo(cell, spreadsheet, address.substring(address.indexOf("]") + 1));
        } else {
            spreadsheet.getUI().getPage().open(address, "_new");
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

void onLinkCellClick(int row, int column) {
    Cell cell = getActiveSheet().getRow(row - 1).getCell(column - 1);
    if (hyperlinkCellClickHandler != null) {
        hyperlinkCellClickHandler.onHyperLinkCellClick(cell, cell.getHyperlink(), Spreadsheet.this);
    } else {/*  ww  w.j  a  v a 2  s .com*/
        DefaultHyperlinkCellClickHandler.get().onHyperLinkCellClick(cell, cell.getHyperlink(),
                Spreadsheet.this);
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private void loadHyperLinks(int r1, int c1, int r2, int c2) {
    for (int r = r1 - 1; r < r2; r++) {
        final Row row = getActiveSheet().getRow(r);
        if (row != null) {
            for (int c = c1 - 1; c < c2; c++) {
                Cell cell = row.getCell(c);
                if (cell != null) {
                    try {
                        Hyperlink link = cell.getHyperlink();
                        if (link != null) {
                            if (link instanceof XSSFHyperlink) {
                                String tooltip = ((XSSFHyperlink) link).getTooltip();
                                // Show address if no defined tooltip (like
                                // in
                                // excel)
                                if (tooltip == null) {
                                    tooltip = link.getAddress();
                                }//from w w  w  .j  a va2s.  c  o  m
                                getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1), tooltip);
                            } else {
                                getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1),
                                        link.getAddress());
                            }
                        } else {
                            // Check if the cell has HYPERLINK function
                            if (DefaultHyperlinkCellClickHandler.isHyperlinkFormulaCell(cell)) {
                                getState().hyperlinksTooltips.put(SpreadsheetUtil.toKey(c + 1, r + 1),
                                        DefaultHyperlinkCellClickHandler.getHyperlinkFunctionCellAddress(cell));
                            }
                        }
                    } catch (XmlValueDisconnectedException exc) {
                        LOGGER.log(Level.FINEST, exc.getMessage(), exc);
                    }
                }
            }
        }
    }
}

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

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;/*ww w  .  ja va 2 s  .co m*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        if (returnURLInsteadOfName) {
                            Hyperlink link = cell.getHyperlink();
                            if (link != null) {
                                if (concatenateLabelUrl) {
                                    String url = link.getAddress();
                                    if (url == null) {
                                        url = "";
                                    }
                                    String label = link.getLabel();
                                    if (label == null) {
                                        label = "";
                                    }
                                    value = label + "|" + url;
                                } else {
                                    value = link.getAddress();
                                }
                            } else {
                                value = cell.getStringCellValue();
                            }
                        } else {
                            value = cell.getStringCellValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            if (defaultDateFormat != null) {
                                Date d = cell.getDateCellValue();
                                if (d != null) {
                                    value = defaultDateFormat.format(d);
                                }
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        } else {
                            if (overrideExcelNumberFormat) {
                                value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        }
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue() ? "true" : "false";
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                value = getDataFormatter().formatCellValue(cell);
            } else {
                if (overrideExcelNumberFormat) {
                    value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                } else {
                    value = getDataFormatter().formatCellValue(cell);
                }
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}