Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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

License:Open Source License

/**
 * Returns an array with Double values in column with columnIndex from row
 * r1 to row r2 in activeSheet until first non numeric cell or null value
 * Used by/*ww w . jav a2  s. c o  m*/
 * {@link CellSelectionShifter#getColumnSequenceIncrement(int, int, int)}
 * 
 * @param activeSheet
 *            Sheet where the cells are goint to be taken from
 * @param columnIndex
 *            Defines the origin of the cell values to be returned, 1-based
 * @param r1
 *            First row of the column to be returned, 1-based
 * @param r2
 *            Last row of the column to be returned, 1-based
 * @return Double array with values
 */
private Double[] getColumnNumericValues(Sheet activeSheet, int columnIndex, int r1, int r2) {
    Double[] result = new Double[r2 - r1 + 1];
    Cell cell;
    Row row;
    for (int i = r1; i <= r2; i++) {
        row = activeSheet.getRow(i - 1);
        if (row != null) {
            cell = row.getCell(columnIndex - 1);
            if (cell != null && cell.getCellType() == CELL_TYPE_NUMERIC) {
                result[i - r1] = cell.getNumericCellValue();
            } else {
                break;
            }
        } else {
            break;
        }
    }
    return result;
}

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

License:Open Source License

/**
 * Returns the increment between all consecutive cells in column with cIndex
 * from row r1 to row r2//from ww w  .ja  v a2  s .com
 * 
 * @param cIndex
 *            Column index for the sequence recognition, 1-based
 * 
 * @param r1
 *            First row of the column to be considered, 1-based
 * @param r2
 *            Last row of the column to be considered, 1-based
 * @return common difference or null
 */
private Double getColumnSequenceIncrement(int cIndex, int r1, int r2) {
    Double result = null;
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    final Row row = activeSheet.getRow(r1 - 1);
    if (row != null) {
        Cell firstCell = row.getCell(cIndex - 1);
        if (firstCell != null) {
            if (firstCell.getCellType() == CELL_TYPE_STRING) {
                return getSequenceIncrement(getColumnStringValues(activeSheet, cIndex, r1, r2));
            } else if (firstCell.getCellType() == CELL_TYPE_NUMERIC) {
                return getSequenceIncrement(getColumnNumericValues(activeSheet, cIndex, r1, r2));
            }
        }
    }
    return result;
}

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

License:Open Source License

/**
 * Updates the cell value and type, causes a recalculation of all the values
 * in the cell.//ww w.  j  ava2s  .  c o  m
 *
 * If there is a {@link CellValueHandler} defined, then it is used.
 *
 * Cells starting with "=" or "+" will be created/changed into FORMULA type.
 *
 * Cells that are existing and are NUMERIC type will be parsed according to
 * their existing format, or if that fails, as Double.
 *
 * Cells not containing any letters and containing at least one number will
 * be created/changed into NUMERIC type (formatting is not changed).
 *
 * Existing Boolean cells will be parsed as Boolean.
 *
 * For everything else and if any of the above fail, the cell will get the
 * STRING type and the value will just be a string, except empty values will
 * cause the cell type to be BLANK.
 *
 * @param col
 *            Column index of target cell, 1-based
 * @param row
 *            Row index of target cell, 1-based
 * @param value
 *            The new value to set to the target cell, formulas will start
 *            with an extra "=" or "+"
 */
public void onCellValueChange(int col, int row, String value) {
    Workbook workbook = spreadsheet.getWorkbook();
    // update cell value
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row - 1);
    if (r == null) {
        r = activeSheet.createRow(row - 1);
    }
    Cell cell = r.getCell(col - 1);
    String formattedCellValue = null;
    int oldCellType = -1;
    // capture cell value to history
    CellValueCommand command = new CellValueCommand(spreadsheet);
    command.captureCellValues(new CellReference(row - 1, col - 1));
    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    boolean updateHyperlinks = false;

    if (getCustomCellValueHandler() == null || getCustomCellValueHandler().cellValueUpdated(cell, activeSheet,
            col - 1, row - 1, value, getFormulaEvaluator(), formatter)) {
        Exception exception = null;
        try {
            // handle new cell creation
            SpreadsheetStyleFactory styler = spreadsheet.getSpreadsheetStyleFactory();
            final Locale spreadsheetLocale = spreadsheet.getLocale();
            if (cell == null) {
                cell = r.createCell(col - 1);
            } else {
                // modify existing cell, possibly switch type
                formattedCellValue = getFormattedCellValue(cell);
                final String key = SpreadsheetUtil.toKey(col, row);
                oldCellType = cell.getCellType();
                if (!sentCells.remove(key)) {
                    sentFormulaCells.remove(key);
                }

                // Old value was hyperlink => needs refresh
                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                        && cell.getCellFormula().startsWith("HYPERLINK")) {
                    updateHyperlinks = true;
                }
            }
            if (formulaFormatter.isFormulaFormat(value)) {
                if (formulaFormatter.isValidFormulaFormat(value, spreadsheetLocale)) {
                    spreadsheet.removeInvalidFormulaMark(col, row);
                    getFormulaEvaluator().notifyUpdateCell(cell);
                    cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    cell.setCellFormula(
                            formulaFormatter.unFormatFormulaValue(value.substring(1), spreadsheetLocale));
                    getFormulaEvaluator().notifySetFormula(cell);
                    if (value.startsWith("=HYPERLINK(")
                            && cell.getCellStyle().getIndex() != hyperlinkStyleIndex) {
                        // set the cell style to link cell
                        CellStyle hyperlinkCellStyle;
                        if (hyperlinkStyleIndex == -1) {
                            hyperlinkCellStyle = styler.createHyperlinkCellStyle();
                            hyperlinkStyleIndex = -1;
                        } else {
                            hyperlinkCellStyle = workbook.getCellStyleAt(hyperlinkStyleIndex);
                        }
                        cell.setCellStyle(hyperlinkCellStyle);
                        styler.cellStyleUpdated(cell, true);
                        updateHyperlinks = true;
                    }
                } else {
                    // it's formula but invalid
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                    spreadsheet.markInvalidFormula(col, row);
                }
            } else {
                spreadsheet.removeInvalidFormulaMark(col, row);
                Double percentage = SpreadsheetUtil.parsePercentage(value, spreadsheetLocale);
                Double numVal = SpreadsheetUtil.parseNumber(cell, value, spreadsheetLocale);
                if (value.isEmpty()) {
                    cell.setCellType(Cell.CELL_TYPE_BLANK);
                } else if (percentage != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    CellStyle cs = cell.getCellStyle();
                    if (cs == null) {
                        cs = workbook.createCellStyle();
                        cell.setCellStyle(cs);
                    }

                    if (cs.getDataFormatString() != null && !cs.getDataFormatString().contains("%")) {
                        cs.setDataFormat(workbook.createDataFormat()
                                .getFormat(spreadsheet.getDefaultPercentageFormat()));
                        styler.cellStyleUpdated(cell, true);
                    }
                    cell.setCellValue(percentage);
                } else if (numVal != null) {
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(numVal);
                } else if (oldCellType == Cell.CELL_TYPE_BOOLEAN) {
                    cell.setCellValue(Boolean.parseBoolean(value));
                } else {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(value);
                }
                getFormulaEvaluator().notifyUpdateCell(cell);
            }

        } catch (FormulaParseException fpe) {
            try {
                exception = fpe;

                // parses formula
                cell.setCellFormula(value.substring(1).replace(" ", ""));
            } catch (FormulaParseException fpe2) {
                exception = fpe2;
                /*
                 * We could force storing the formula even if it is invalid.
                 * Instead, just store it as the value. Clearing the formula
                 * makes sure the value is displayed as-is.
                 */
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cell.setCellValue(value);
                spreadsheet.markInvalidFormula(col, row);
            }
        } catch (NumberFormatException nfe) {
            exception = nfe;
            cell.setCellValue(value);
        } catch (Exception e) {
            exception = e;
            cell.setCellValue(value);
        }
        if (cell != null) {
            markCellForUpdate(cell);
            if (formattedCellValue == null || !formattedCellValue.equals(getFormattedCellValue(cell))
                    || oldCellType != cell.getCellType()) {
                fireCellValueChangeEvent(cell);
            }
        }
        if (exception != null) {
            LOGGER.log(Level.FINE, "Failed to parse cell value for cell at col " + col + " row " + row + " ("
                    + exception.getMessage() + ")", exception);
        }
    }

    spreadsheet.updateMarkedCells();

    if (updateHyperlinks) {
        spreadsheet.loadHyperLinks();
    }
}

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

License:Open Source License

/**
 * Deletes the currently selected cells' values. Does not affect styles.
 *//* w w w . j a  v a  2  s.  c o m*/
public void onDeleteSelectedCells() {
    final Sheet activeSheet = spreadsheet.getActiveSheet();
    CellReference selectedCellReference = getCellSelectionManager().getSelectedCellReference();
    // TODO show error on locked cells instead
    if (selectedCellReference != null) {
        Row row = activeSheet.getRow(selectedCellReference.getRow());
        if (row != null && spreadsheet.isCellLocked(row.getCell(selectedCellReference.getCol()))) {
            return;
        }
    }
    List<CellReference> individualSelectedCells = getCellSelectionManager().getIndividualSelectedCells();
    for (CellReference cr : individualSelectedCells) {
        final Row row = activeSheet.getRow(cr.getRow());
        if (row != null && spreadsheet.isCellLocked(row.getCell(cr.getCol()))) {
            return;
        }
    }
    List<CellRangeAddress> cellRangeAddresses = getCellSelectionManager().getCellRangeAddresses();
    for (CellRangeAddress range : cellRangeAddresses) {
        if (!spreadsheet.isRangeEditable(range)) {
            return;
        }
    }

    boolean selectedIsInRange = selectedIsInRange(selectedCellReference, cellRangeAddresses);
    boolean cellDeletionCheckPassed = !selectedIsInRange && individualSelectedCells.isEmpty()
            && passesDeletionCheck(selectedCellReference);
    boolean individualCellsDeletionCheckPassed;
    if (selectedCellReference == null) {
        individualCellsDeletionCheckPassed = passesDeletionCheck(individualSelectedCells);
    } else if (!selectedIsInRange && !individualSelectedCells.isEmpty()) {
        List<CellReference> individualSelectedCellsIncludingCurrentSelection = new ArrayList<CellReference>(
                individualSelectedCells);
        individualSelectedCellsIncludingCurrentSelection.add(selectedCellReference);
        individualCellsDeletionCheckPassed = passesDeletionCheck(
                individualSelectedCellsIncludingCurrentSelection);
        cellDeletionCheckPassed = individualCellsDeletionCheckPassed;
    } else {
        individualCellsDeletionCheckPassed = passesDeletionCheck(individualSelectedCells);
    }
    boolean cellRangeDeletionCheckPassed = passesRangeDeletionCheck(cellRangeAddresses);
    // at least one of the selection types must pass the check and have
    // contents
    if ((selectedCellReference == null || !cellDeletionCheckPassed)
            && (individualSelectedCells.isEmpty() || !individualCellsDeletionCheckPassed)
            && (cellRangeAddresses.isEmpty() || !cellRangeDeletionCheckPassed)) {
        return;
    }
    if (!cellDeletionCheckPassed) {
        selectedCellReference = null;
    }
    if (!individualCellsDeletionCheckPassed) {
        individualSelectedCells.clear();
    }
    if (!cellRangeDeletionCheckPassed) {
        cellRangeAddresses.clear();
    }

    CellValueCommand command = new CellValueCommand(spreadsheet);
    if (selectedCellReference != null && !selectedIsInRange) {
        command.captureCellValues(selectedCellReference);
    }
    for (CellReference cr : individualSelectedCells) {
        command.captureCellValues(cr);
    }
    for (CellRangeAddress range : cellRangeAddresses) {
        command.captureCellRangeValues(range);
    }
    if (selectedCellReference != null && !selectedIsInRange) {
        removeCell(selectedCellReference.getRow() + 1, selectedCellReference.getCol() + 1, false);
    }
    for (CellReference cr : individualSelectedCells) {
        removeCell(cr.getRow() + 1, cr.getCol() + 1, false);
    }
    for (CellRangeAddress range : cellRangeAddresses) {
        removeCells(range.getFirstRow() + 1, range.getFirstColumn() + 1, range.getLastRow() + 1,
                range.getLastColumn() + 1, false);
    }
    // removeCell and removeCells makes sure that cells are removed and
    // cleared from client side cache.
    updateMarkedCellValues();
    spreadsheet.getSpreadsheetHistoryManager().addCommand(command);
    fireCellValueChangeEvent(spreadsheet.getSelectedCellReferences());
    spreadsheet.loadHyperLinks();
}

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

License:Open Source License

/**
 * Checks whether the default deletion handling should be performed for the
 * selected cell or whether a custom deletion handler takes care of
 * everything.// w  w w. j  av  a 2 s  . c  om
 * 
 * @param selectedCellReference
 * @return {@code true} if the default handling should be performed,
 *         {@code false} otherwise
 */
private boolean passesDeletionCheck(CellReference selectedCellReference) {
    if (selectedCellReference == null || customCellDeletionHandler == null) {
        return true;
    }
    final Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    int rowIndex = selectedCellReference.getRow();
    final Row row = activeSheet.getRow(rowIndex);
    if (row != null) {
        short colIndex = selectedCellReference.getCol();
        final Cell cell = row.getCell(colIndex);
        if (cell != null) {
            return customCellDeletionHandler.cellDeleted(cell, activeSheet, colIndex, rowIndex,
                    getFormulaEvaluator(), formatter);
        }
    }
    return true;
}

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

License:Open Source License

/**
 * Gets cell data for cells within the given bounds.
 *
 * @param firstRow//from   w  w w .j  av  a 2 s  .c  o  m
 *            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
 * @return A list of CellData for the cells in the given area.
 */
protected ArrayList<CellData> loadCellDataForRowAndColumnRange(int firstRow, int firstColumn, int lastRow,
        int lastColumn) {
    ArrayList<CellData> cellData = new ArrayList<CellData>();
    Workbook workbook = spreadsheet.getWorkbook();
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Map<String, String> componentIDtoCellKeysMap = spreadsheet.getState(false).componentIDtoCellKeysMap;
    @SuppressWarnings("unchecked")
    final Collection<String> customComponentCells = (Collection<String>) (componentIDtoCellKeysMap == null
            ? Collections.emptyList()
            : componentIDtoCellKeysMap.values());
    for (int r = firstRow - 1; r < lastRow; r++) {
        Row row = activeSheet.getRow(r);
        if (row != null && row.getLastCellNum() != -1 && row.getLastCellNum() >= firstColumn) {
            for (int c = firstColumn - 1; c < lastColumn; c++) {
                final String key = SpreadsheetUtil.toKey(c + 1, r + 1);
                if (!customComponentCells.contains(key) && !sentCells.contains(key)
                        && !sentFormulaCells.contains(key)) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        final CellData cd = createCellDataForCell(cell);
                        if (cd != null) {
                            int cellType = cell.getCellType();
                            if (cellType == Cell.CELL_TYPE_FORMULA) {
                                sentFormulaCells.add(key);
                            } else {
                                sentCells.add(key);
                            }
                            cellData.add(cd);
                        }
                    }
                }
            }
        }
    }
    return cellData;
}

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.//from w  ww.j av a2  s .  co  m
 *
 * @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  . ja va  2 s  .c om
 *
 * @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.Spreadsheet.java

License:Open Source License

/**
 * Returns the Cell at the given coordinates. If the cell is updated in
 * outside code, call {@link #refreshCells(Cell...)} AFTER ALL UPDATES
 * (value, type, formatting or style) to mark the cell as "dirty".
 *
 * @param row//www . j a v  a2 s. c  o  m
 *            Row index of the cell to return, 0-based
 * @param col
 *            Column index of the cell to return, 0-based
 * @param sheet
 *            Sheet of the cell
 * @return The cell at the given coordinates, or null if not defined
 */
public Cell getCell(int row, int col, Sheet sheet) {
    Row r = sheet.getRow(row);
    if (r != null) {
        return r.getCell(col);
    } else {
        return null;
    }
}

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

License:Open Source License

/**
 * Deletes the cell from the sheet and the underlying POI model as well.
 * This really deletes the cell, instead of just making it's value blank.
 * //from w w w  . jav a2s  .c  o m
 * @param row
 *            Row index of the cell to delete, 0-based
 * @param col
 *            Column index of the cell to delete, 0-based
 */
public void deleteCell(int row, int col) {
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    final Cell cell = activeSheet.getRow(row).getCell(col);
    if (cell != null) {
        // cell.setCellStyle(null); // TODO NPE on HSSF
        styler.cellStyleUpdated(cell, true);
        activeSheet.getRow(row).removeCell(cell);
        valueManager.cellDeleted(cell);
        refreshCells(cell);
    }
}