Example usage for org.apache.poi.ss.usermodel Sheet 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.Spreadsheet.java

License:Open Source License

/**
 * Creates a new Formula type cell with the given formula.
 * /*from   w  w  w.java 2 s . c  o  m*/
 * After all editing is done, call {@link #refreshCells(Cell...)()} or
 * {@link #refreshAllCellValues()} to make sure client side is updated.
 * 
 * @param row
 *            Row index of the new cell, 0-based
 * @param col
 *            Column index of the new cell, 0-based
 * @param formula
 *            The formula to set to the new cell (should NOT start with "="
 *            nor "+")
 * @return The newly created cell
 * @throws IllegalArgumentException
 *             If columnIndex < 0 or greater than the maximum number of
 *             supported columns (255 for *.xls, 1048576 for *.xlsx)
 */
public Cell createFormulaCell(int row, int col, String formula) throws IllegalArgumentException {
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row);
    if (r == null) {
        r = activeSheet.createRow(row);
    }
    Cell cell = r.getCell(col);
    if (cell == null) {
        cell = r.createCell(col, Cell.CELL_TYPE_FORMULA);
    } else {
        final String key = SpreadsheetUtil.toKey(col + 1, row + 1);
        valueManager.clearCellCache(key);
        cell.setCellType(Cell.CELL_TYPE_FORMULA);
    }
    cell.setCellFormula(formula);
    valueManager.cellUpdated(cell);
    return cell;
}

---

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

License:Open Source License

/**
 * Create a new cell (or replace existing) with the given value, the type of
 * the value parameter will define the type of the cell. The value may be of
 * the following types: Boolean, Calendar, Date, Double or String. The
 * default type will be String, value of ({@link #toString()} will be given
 * as the cell value./*  w  w  w.j  a va  2s . co m*/
 * 
 * For formula cells, use {@link #createFormulaCell(int, int, String)}.
 * 
 * After all editing is done, call {@link #refreshCells(Cell...)} or
 * {@link #refreshAllCellValues()} to make sure the client side is updated.
 * 
 * @param row
 *            Row index of the new cell, 0-based
 * @param col
 *            Column index of the new cell, 0-based
 * @param value
 *            Object representing the type and value of the Cell
 * @return The newly created cell
 * @throws IllegalArgumentException
 *             If columnIndex < 0 or greater than the maximum number of
 *             supported columns (255 for *.xls, 1048576 for *.xlsx)
 */
public Cell createCell(int row, int col, Object value) throws IllegalArgumentException {
    final Sheet activeSheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    Row r = activeSheet.getRow(row);
    if (r == null) {
        r = activeSheet.createRow(row);
    }
    Cell cell = r.getCell(col);
    if (cell == null) {
        cell = r.createCell(col);
    } else {
        final String key = SpreadsheetUtil.toKey(col + 1, row + 1);
        valueManager.clearCellCache(key);
    }
    if (value instanceof Double) {
        cell.setCellValue((Double) value);
    } else if (value instanceof Boolean) {
        cell.setCellValue((Boolean) value);
    } else if (value instanceof Date) {
        cell.setCellValue((Date) value);
    } else if (value instanceof Calendar) {
        cell.setCellValue((Calendar) value);
    } else {
        cell.setCellValue(value.toString());
    }
    valueManager.cellUpdated(cell);
    return cell;
}

---

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

License:Open Source License

/**
 * Shifts rows between startRow and endRow n number of rows. If you use a
 * negative number for n, the rows will be shifted upwards. This method
 * ensures that rows can't wrap around.//  ww  w. ja va 2s .c  o m
 * <p>
 * If you are adding / deleting rows, you might want to change the number of
 * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}.
 * <p>
 * See {@link Sheet#shiftRows(int, int, int, boolean, boolean)}.
 * 
 * @param startRow
 *            The first row to shift, 0-based
 * @param endRow
 *            The last row to shift, 0-based
 * @param n
 *            Number of rows to shift, positive numbers shift down, negative
 *            numbers shift up.
 * @param copyRowHeight
 *            True to copy the row height during the shift
 * @param resetOriginalRowHeight
 *            True to set the original row's height to the default
 */
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
    Sheet sheet = getActiveSheet();
    int lastNonBlankRow = getLastNonBlankRow(sheet);
    sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight);
    // need to re-send the cell values to client
    // remove all cached cell data that is now empty
    getFormulaEvaluator().clearAllCachedResultValues();
    int start = n < 0 ? Math.max(lastNonBlankRow, startRow) : startRow;
    int end = n < 0 ? endRow : startRow + n - 1;
    valueManager.updateDeletedRowsInClientCache(start + 1, end + 1);
    int firstAffectedRow = n < 0 ? startRow + n : startRow;
    int lastAffectedRow = n < 0 ? endRow : endRow + n;
    if (copyRowHeight || resetOriginalRowHeight) {
        // might need to increase the size of the row heights array
        int oldLength = getState(false).rowH.length;
        int neededLength = endRow + n + 1;
        if (n > 0 && oldLength < neededLength) {
            getState().rowH = Arrays.copyOf(getState().rowH, neededLength);
        }
        for (int i = firstAffectedRow; i <= lastAffectedRow; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                if (row.getZeroHeight()) {
                    getState().rowH[i] = 0f;
                } else {
                    getState().rowH[i] = row.getHeightInPoints();
                }
            } else {
                getState().rowH[i] = sheet.getDefaultRowHeightInPoints();
            }
        }
    }

    if (hasSheetOverlays()) {
        reloadImageSizesFromPOI = true;
    }
    // need to shift the cell styles, clear and update
    // need to go -1 and +1 because of shifted borders..
    final ArrayList<Cell> cellsToUpdate = new ArrayList<Cell>();
    for (int r = (firstAffectedRow - 1); r <= (lastAffectedRow + 1); r++) {
        if (r < 0) {
            r = 0;
        }
        Row row = sheet.getRow(r);
        final Integer rowIndex = new Integer(r + 1);
        if (row == null) {
            valueManager.updateDeletedRowsInClientCache(rowIndex, rowIndex);
            if (getState(false).hiddenRowIndexes.contains(rowIndex)) {
                getState().hiddenRowIndexes.remove(rowIndex);
            }
            for (int c = 0; c < getState().cols; c++) {
                styler.clearCellStyle(r, c);
            }
        } else {
            if (row.getZeroHeight()) {
                getState().hiddenRowIndexes.add(rowIndex);
            } else if (getState(false).hiddenRowIndexes.contains(rowIndex)) {
                getState().hiddenRowIndexes.remove(rowIndex);
            }
            for (int c = 0; c < getState().cols; c++) {
                Cell cell = row.getCell(c);
                if (cell == null) {
                    styler.clearCellStyle(r, c);
                    if (r <= lastNonBlankRow + n) {
                        // There might be a pre-shift value for this cell in
                        // client-side and should be overwritten
                        cell = row.createCell(c);
                        cellsToUpdate.add(cell);
                    }
                } else {
                    cellsToUpdate.add(cell);
                }
            }
        }
    }
    rowsMoved(firstAffectedRow, lastAffectedRow, n);

    for (Cell cell : cellsToUpdate) {
        styler.cellStyleUpdated(cell, false);
        markCellAsUpdated(cell, false);
    }
    styler.loadCustomBorderStylesToState();

    updateMarkedCells(); // deleted and formula cells and style selectors
    updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); // shifted area values
    updateMergedRegions();

    CellReference selectedCellReference = selectionManager.getSelectedCellReference();
    if (selectedCellReference != null) {
        if (selectedCellReference.getRow() >= firstAffectedRow
                && selectedCellReference.getRow() <= lastAffectedRow) {
            selectionManager.onSheetAddressChanged(selectedCellReference.formatAsString(), false);
        }
    }
}

---

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

License:Open Source License

private int getLastNonBlankRow(Sheet sheet) {
    for (int r = sheet.getLastRowNum(); r >= 0; r--) {
        Row row = sheet.getRow(r);
        if (row != null) {
            for (short c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    return r;
                }/*from   ww w  .j a va  2s.c  o  m*/
            }
        }
    }
    return 0;
}

---

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

License:Open Source License

/**
 * Deletes rows. See {@link Sheet#removeRow(Row)}. Removes all row content,
 * deletes cells and resets the sheet size.
 * /*from   w  w  w.  j  a va2s  . c  o  m*/
 * Does not shift rows up (!) - use
 * {@link #shiftRows(int, int, int, boolean, boolean)} for that.
 * 
 * @param startRow
 *            Index of the starting row, 0-based
 * @param endRow
 *            Index of the ending row, 0-based
 */
public void deleteRows(int startRow, int endRow) {
    Sheet sheet = getActiveSheet();
    for (int i = startRow; i <= endRow; i++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            getActiveSheet().removeRow(row);
        }
    }
    for (int i = startRow; i <= endRow; i++) {
        getState(false).rowH[i] = sheet.getDefaultRowHeightInPoints();
    }
    updateMergedRegions();
    valueManager.updateDeletedRowsInClientCache(startRow + 1, endRow + 1);

    if (hasSheetOverlays()) {
        reloadImageSizesFromPOI = true;
    }
    updateMarkedCells();
    CellReference selectedCellReference = getSelectedCellReference();
    if (selectedCellReference.getRow() >= startRow && selectedCellReference.getRow() <= endRow) {
        selectionManager.reSelectSelectedCell();
    }

}

---

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

License:Open Source License

private void createMergedRegionIntoSheet(CellRangeAddress region) {
    Sheet sheet = getActiveSheet();
    int addMergedRegionIndex = sheet.addMergedRegion(region);
    MergedRegion mergedRegion = new MergedRegion();
    mergedRegion.col1 = region.getFirstColumn() + 1;
    mergedRegion.col2 = region.getLastColumn() + 1;
    mergedRegion.row1 = region.getFirstRow() + 1;
    mergedRegion.row2 = region.getLastRow() + 1;
    mergedRegion.id = mergedRegionCounter++;
    if (getState().mergedRegions == null) {
        getState().mergedRegions = new ArrayList<MergedRegion>();
    }/* w w w .j  a v  a  2s.c om*/
    getState().mergedRegions.add(addMergedRegionIndex - 1, mergedRegion);
    // update the style & data for the region cells, effects region + 1
    // FIXME POI doesn't seem to care that the other cells inside the merged
    // region should be removed; the values those cells have are still used
    // in formulas..
    for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) {
        Row row = sheet.getRow(r - 1);
        for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) {
            if (row != null) {
                Cell cell = row.getCell(c - 1);
                if (cell != null) {
                    styler.cellStyleUpdated(cell, false);
                    if ((c != mergedRegion.col1 || r != mergedRegion.row1) && c <= mergedRegion.col2
                            && r <= mergedRegion.row2) {
                        getCellValueManager().markCellForRemove(cell);
                    }
                }
            }
        }
    }
    styler.loadCustomBorderStylesToState();
    updateMarkedCells();
}

---

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

License:Open Source License

private void deleteMergedRegion(int index) {
    final Sheet sheet = getActiveSheet();
    sheet.removeMergedRegion(index);/*ww w .ja  va  2s .  c  om*/
    MergedRegion mergedRegion = getState().mergedRegions.remove(index);
    // update the style for the region cells, effects region + 1 row&col
    for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) {
        Row row = sheet.getRow(r - 1);
        if (row != null) {
            for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) {
                Cell cell = row.getCell(c - 1);
                if (cell != null) {
                    styler.cellStyleUpdated(cell, false);
                    valueManager.markCellForUpdate(cell);
                } else {
                    styler.clearCellStyle(r, c);
                }
            }
        }
    }
    styler.loadCustomBorderStylesToState();
}

---

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

License:Open Source License

/**
 * Hides or shows the given row, see {@link Row#setZeroHeight(boolean)}.
 * /*from   www  .  ja va  2 s  .  c o m*/
 * @param rowIndex
 *            Index of the target row, 0-based
 * @param hidden
 *            True to hide the target row, false to show it.
 */
public void setRowHidden(int rowIndex, boolean hidden) {
    final Sheet activeSheet = getActiveSheet();
    Row row = activeSheet.getRow(rowIndex);
    if (row == null) {
        row = activeSheet.createRow(rowIndex);
    }
    row.setZeroHeight(hidden);
    if (hidden && !getState().hiddenRowIndexes.contains(rowIndex + 1)) {
        getState().hiddenRowIndexes.add(rowIndex + 1);
        getState().rowH[rowIndex] = 0.0F;
    } else if (!hidden && getState().hiddenRowIndexes.contains(rowIndex + 1)) {
        getState().hiddenRowIndexes.remove(getState().hiddenRowIndexes.indexOf(rowIndex + 1));
        getState().rowH[rowIndex] = row.getHeightInPoints();
    }

    if (hasSheetOverlays()) {
        reloadImageSizesFromPOI = true;
        loadOrUpdateOverlays();
    }
}

---

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

License:Open Source License

private void loadCellComments(int r1, int c1, int r2, int c2) {
    Sheet sheet = getActiveSheet();
    for (int r = r1 - 1; r < r2; r++) {
        Row row = sheet.getRow(r);
        if (row != null && row.getZeroHeight()) {
            continue;
        }/*from www. ja  va2s  .c  om*/
        for (int c = c1 - 1; c < c2; c++) {
            if (sheet.isColumnHidden(c)) {
                continue;
            }

            int c_one_based = c + 1;
            int row_one_based = r + 1;

            MergedRegion region = mergedRegionContainer.getMergedRegion(c_one_based, row_one_based);
            // do not add comments that are "below" merged regions.
            // client side handles cases where comment "moves" (because
            // shifting etc.) from merged cell into basic or vice versa.
            if (region == null || region.col1 == c_one_based && region.row1 == row_one_based) {
                Comment comment = sheet.getCellComment(r, c);
                String key = SpreadsheetUtil.toKey(c_one_based, row_one_based);
                if (comment != null) {
                    // by default comments are shown when mouse is over the
                    // red
                    // triangle on the cell's top right corner. the comment
                    // position is calculated so that it is completely
                    // visible.
                    getState().cellComments.put(key, comment.getString().getString());
                    getState().cellCommentAuthors.put(key, comment.getAuthor());
                    if (comment.isVisible()) {
                        getState().visibleCellComments.add(key);
                    }
                }
                if (isMarkedAsInvalidFormula(c_one_based, row_one_based)) {
                    getState().invalidFormulaCells.add(key);
                }

            } else {
                c = region.col2 - 1;
            }
        }
    }
}

---

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

License:Open Source License

private void handleRowSizes(Set<Integer> rowsWithComponents) {
    // Set larger height for new rows with components
    for (Integer row : rowsWithComponents) {
        if (isRowHidden(row)) {
            continue;
        }/*from  w  w  w. ja v  a  2  s  .  co  m*/
        float currentHeight = getState(false).rowH[row];
        if (currentHeight < getMinimumRowHeightForComponents()) {
            getState().rowH[row] = getMinimumRowHeightForComponents();
        }
    }
    // Reset row height for rows which no longer have components
    if (this.rowsWithComponents != null) {
        Sheet activeSheet = getActiveSheet();
        for (Integer row : this.rowsWithComponents) {
            if (!rowsWithComponents.contains(row)) {
                if (isRowHidden(row)) {
                    getState().rowH[row] = 0;
                } else {
                    Row r = activeSheet.getRow(row);
                    if (r == null) {
                        getState().rowH[row] = activeSheet.getDefaultRowHeightInPoints();
                    } else {
                        getState().rowH[row] = r.getHeightInPoints();
                    }
                }
            }
        }
    }

    this.rowsWithComponents = rowsWithComponents;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »