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

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

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

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

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

License:Open Source License

/**
 * Set's cell value for the newCell. It will be the same as shiftedCell
 * unless sequenceIncrement is not null, in that case the last digits are
 * replaced/* w  ww . j  a va 2s.  c om*/
 * 
 * @param shiftedCell
 *            Source cell
 * @param newCell
 *            Resulting new cell
 * @param sequenceIncrement
 *            not null to increase the number in source cell
 */
private void shiftString(Cell shiftedCell, Cell newCell, Double sequenceIncrement) {
    if (sequenceIncrement != null) {
        int dif;
        if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) {
            // shift column indexes
            dif = newCell.getColumnIndex() - shiftedCell.getColumnIndex();
        } else {
            dif = newCell.getRowIndex() - shiftedCell.getRowIndex();
        }

        Matcher matcher = stringSequencePattern.matcher(shiftedCell.getStringCellValue());
        if (matcher.find()) {
            String base = shiftedCell.getStringCellValue().substring(0, matcher.start());
            String currentValue = matcher.group();
            Double currVal = Double.parseDouble(currentValue);
            newCell.setCellValue(base + (int) Math.abs(currVal + sequenceIncrement * dif));
        } else {
            newCell.setCellValue(shiftedCell.getStringCellValue());
        }
    } else {
        newCell.setCellValue(shiftedCell.getStringCellValue());
    }
}

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

License:Open Source License

/**
 * Set's cell value for the newCell. It will be the same as shiftedCell
 * unless sequenceIncrement is not null, in that case the value changes
 * depending on sequenceIncrement and cell distance
 * /*from   w  ww . j a v  a2  s  . c  o m*/
 * @param shiftedCell
 *            Source cell
 * @param newCell
 *            Resulting new cell
 * @param sequenceIncrement
 *            not null to increase the number in source cell
 */
private void shiftNumeric(Cell shiftedCell, Cell newCell, Double sequenceIncrement) {
    if (sequenceIncrement != null) {
        int dif;
        if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) {
            // shift column indexes
            dif = newCell.getColumnIndex() - shiftedCell.getColumnIndex();
        } else {
            dif = newCell.getRowIndex() - shiftedCell.getRowIndex();
        }
        newCell.setCellValue(shiftedCell.getNumericCellValue() + sequenceIncrement * dif);
    } else {
        newCell.setCellValue(shiftedCell.getNumericCellValue());
    }
}

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

License:Open Source License

/**
 * Set's cell value for the newCell. It will be the same as shiftedCell with
 * updated references./* w  w  w  .j a va 2s. c om*/
 * 
 * @param shiftedCell
 *            Source cell
 * @param newCell
 *            Resulting new cell
 */
private void shiftFormula(Cell shiftedCell, Cell newCell) {
    try {
        if (shiftedCell.getColumnIndex() != newCell.getColumnIndex()) {
            // shift column indexes
            int collDiff = newCell.getColumnIndex() - shiftedCell.getColumnIndex();
            Matcher matcher = rowShiftPattern.matcher(shiftedCell.getCellFormula());
            String originalFormula = shiftedCell.getCellFormula();
            StringBuilder newFormula = new StringBuilder();
            int lastEnd = 0;
            while (matcher.find()) {
                String s = matcher.group();
                String replacement;
                if (!s.startsWith("$")) {
                    String oldIndexString = s.replaceAll("[$]{0,1}\\d+", "");

                    int columnIndex = SpreadsheetUtil.getColHeaderIndex(oldIndexString);
                    columnIndex += collDiff;
                    replacement = s.replace(oldIndexString, SpreadsheetUtil.getColHeader(columnIndex));
                } else {
                    // if column has a '$' reference shouldn't change
                    replacement = s;
                }
                newFormula.append(originalFormula.substring(lastEnd, matcher.start()));
                newFormula.append(replacement);
                lastEnd = matcher.end();
            }
            newFormula.append(originalFormula.substring(lastEnd));
            newCell.setCellFormula(newFormula.toString());
        } else { // shift row indexes
            int rowDiff = newCell.getRowIndex() - shiftedCell.getRowIndex();
            Matcher matcher = rowShiftPattern.matcher(shiftedCell.getCellFormula());
            String originalFormula = shiftedCell.getCellFormula();
            StringBuilder newFormula = new StringBuilder();
            int lastEnd = 0;
            while (matcher.find()) {
                String s = matcher.group();
                String rowString = s.replaceAll("[$]{0,1}[a-zA-Z]+", "");
                String replacement;
                if (!rowString.startsWith("$")) {
                    int row = Integer.parseInt(rowString);
                    row += rowDiff;
                    replacement = s.replace(rowString, Integer.toString(row));
                } else {
                    // if row has a '$' reference shouldn't change
                    replacement = s;
                }
                newFormula.append(originalFormula.substring(lastEnd, matcher.start()));
                newFormula.append(replacement);
                lastEnd = matcher.end();
            }
            newFormula.append(originalFormula.substring(lastEnd));
            newCell.setCellFormula(newFormula.toString());
        }
    } catch (Exception e) {
        LOGGER.log(Level.FINE, e.getMessage(), e);
        // TODO visualize shifting error
        newCell.setCellFormula(shiftedCell.getCellFormula());
    }
    spreadsheet.getCellValueManager().getFormulaEvaluator().notifySetFormula(newCell);
}

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

License:Open Source License

protected CellData createCellDataForCell(Cell cell) {
    CellData cellData = new CellData();
    cellData.row = cell.getRowIndex() + 1;
    cellData.col = cell.getColumnIndex() + 1;
    CellStyle cellStyle = cell.getCellStyle();
    cellData.cellStyle = "cs" + cellStyle.getIndex();
    cellData.locked = spreadsheet.isCellLocked(cell);
    try {//from w  w w.  j  a  va  2 s.c om
        if (!spreadsheet.isCellHidden(cell)) {
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                cellData.formulaValue = formulaFormatter.reFormatFormulaValue(cell.getCellFormula(),
                        spreadsheet.getLocale());
                try {
                    String oldValue = getCachedFormulaCellValue(cell);
                    String newValue = formatter.formatCellValue(cell, getFormulaEvaluator());
                    if (!newValue.equals(oldValue)) {
                        changedFormulaCells.add(new CellReference(cell));
                    }
                } catch (RuntimeException rte) {
                    // Apache POI throws RuntimeExceptions for an invalid
                    // formula from POI model
                    String formulaValue = cell.getCellFormula();
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cell.setCellValue(formulaValue);
                    spreadsheet.markInvalidFormula(cell.getColumnIndex() + 1, cell.getRowIndex() + 1);
                }

            }
        }

        if (cell.getCellStyle().getDataFormatString().contains("%")) {
            cellData.isPercentage = true;
        }

        String formattedCellValue = formatter.formatCellValue(cell, getFormulaEvaluator());

        if (!spreadsheet.isCellHidden(cell)) {
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA || cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                formattedCellValue = formattedCellValue.replaceAll("^-(?=0(.0*)?$)", "");
            }
        }
        if (spreadsheet.isMarkedAsInvalidFormula(cellData.col, cellData.row)) {
            // The prefix '=' or '+' should not be included in formula value
            if (cell.getStringCellValue().charAt(0) == '+' || cell.getStringCellValue().charAt(0) == '=') {
                cellData.formulaValue = cell.getStringCellValue().substring(1);
            }
            formattedCellValue = "#VALUE!";
        }

        if (formattedCellValue != null && !formattedCellValue.isEmpty() || cellStyle.getIndex() != 0) {
            // if the cell is not wrapping text, and is of type numeric or
            // formula (but not date), calculate if formatted cell value
            // fits the column width and possibly use scientific notation.
            cellData.value = formattedCellValue;
            cellData.needsMeasure = false;
            if (!cellStyle.getWrapText()
                    && (!SpreadsheetUtil.cellContainsDate(cell) && cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                            || cell.getCellType() == Cell.CELL_TYPE_STRING
                            || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                    && !cell.getCellFormula().startsWith("HYPERLINK")))) {
                if (!doesValueFit(cell, formattedCellValue)) {
                    if (valueContainsOnlyNumbers(formattedCellValue) && isGenerallCell(cell)) {
                        cellData.value = cellValueFormatter.getScientificNotationStringForNumericCell(
                                cell.getNumericCellValue(), formattedCellValue,
                                cellStyleWidthRatioMap.get((int) cell.getCellStyle().getIndex()),
                                spreadsheet.getState(false).colW[cell.getColumnIndex()] - 10);
                    } else if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        cellData.needsMeasure = true;
                    }
                }
            }

            if (cellStyle.getAlignment() == CellStyle.ALIGN_RIGHT) {
                cellData.cellStyle = cellData.cellStyle + " r";
            } else if (cellStyle.getAlignment() == CellStyle.ALIGN_GENERAL) {
                if (SpreadsheetUtil.cellContainsDate(cell) || cell.getCellType() == Cell.CELL_TYPE_NUMERIC
                        || (cell.getCellType() == Cell.CELL_TYPE_FORMULA
                                && !cell.getCellFormula().startsWith("HYPERLINK")
                                && !(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_STRING))) {
                    cellData.cellStyle = cellData.cellStyle + " r";
                }
            }

        }

        // conditional formatting might be applied even if there isn't a
        // value (such as borders for the cell to the right)
        Set<Integer> cellFormattingIndexes = spreadsheet.getConditionalFormatter().getCellFormattingIndex(cell);
        if (cellFormattingIndexes != null) {

            for (Integer i : cellFormattingIndexes) {
                cellData.cellStyle = cellData.cellStyle + " cf" + i;
            }

            markedCells.add(SpreadsheetUtil.toKey(cell));
        }

        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
            cellData.originalValue = cellData.value;
        } else {
            cellData.originalValue = getOriginalCellValue(cell);
        }

        handleIsDisplayZeroPreference(cell, cellData);
    } catch (RuntimeException rte) {
        LOGGER.log(Level.FINEST, rte.getMessage(), rte);
        cellData.value = "#VALUE!";
    }

    return cellData;
}

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

License:Open Source License

private boolean doesValueFit(Cell cell, String value) {
    Float r = cellStyleWidthRatioMap.get((int) cell.getCellStyle().getIndex());
    if (r == null) {
        return true;
    }/*w  ww  .j  ava  2 s.  c  o  m*/
    BigDecimal ratio = new BigDecimal(r);
    BigDecimal stringPixels = ratio.multiply(new BigDecimal(value.length()));
    // The -4 here is for 2px cell left/right padding
    // FIXME We should probably measure this from the actual value since it
    // might be changed in the style
    BigDecimal columnWidth = new BigDecimal(spreadsheet.getState(false).colW[cell.getColumnIndex()] - 4);
    return stringPixels.compareTo(columnWidth) <= 0;
}

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

License:Open Source License

/**
 * Marks the given cell as deleted and notifies the evaluator
 *
 * @param cell/*from  w  ww  .ja  v  a 2s . c  om*/
 *            Deleted cell
 */
protected void cellDeleted(Cell cell) {
    getFormulaEvaluator().notifyDeleteCell(cell);
    spreadsheet.removeInvalidFormulaMark(cell.getColumnIndex() + 1, cell.getRowIndex() + 1);
    markCellForRemove(cell);
}

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

License:Open Source License

/**
 * Marks the given cell for removal./*  w  ww  .j av a  2 s .  c o  m*/
 *
 * @param cell
 *            Cell to mark for removal
 */
protected void markCellForRemove(Cell cell) {
    String cellKey = SpreadsheetUtil.toKey(cell);
    CellData cd = new CellData();
    cd.col = cell.getColumnIndex() + 1;
    cd.row = cell.getRowIndex() + 1;
    removedCells.add(cd);
    clearCellCache(cellKey);
}

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

License:Open Source License

/**
 * Method for updating cells that are marked for update and formula cells.
 *
 * Iterates over the whole sheet (existing rows and columns) and updates
 * client side cache for all sent formula cells, and cells that have been
 * marked for updating.//ww w  .j a  v  a2  s  .  c o m
 *
 */
protected void updateMarkedCellValues() {
    final ArrayList<CellData> updatedCellData = new ArrayList<CellData>();
    Sheet sheet = spreadsheet.getActiveSheet();
    // it is unnecessary to worry about having custom components in the cell
    // because the client side handles it -> it will not replace a custom
    // component with a cell value

    // update all cached formula cell values on client side, because they
    // might have changed. also make sure all marked cells are updated
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        final Row r = rows.next();
        final Iterator<Cell> cells = r.cellIterator();
        while (cells.hasNext()) {
            final Cell cell = cells.next();
            int rowIndex = cell.getRowIndex();
            int columnIndex = cell.getColumnIndex();
            final String key = SpreadsheetUtil.toKey(columnIndex + 1, rowIndex + 1);
            CellData cd = createCellDataForCell(cell);
            // update formula cells
            if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                if (cd != null) {
                    if (sentFormulaCells.contains(key) || markedCells.contains(key)) {
                        sentFormulaCells.add(key);
                        updatedCellData.add(cd);
                    }
                } else if (sentFormulaCells.contains(key)) {
                    // in case the formula cell value has changed to null or
                    // empty; this case is probably quite rare, formula cell
                    // pointing to a cell that was removed or had its value
                    // cleared ???
                    sentFormulaCells.add(key);
                    cd = new CellData();
                    cd.col = columnIndex + 1;
                    cd.row = rowIndex + 1;
                    cd.cellStyle = "" + cell.getCellStyle().getIndex();
                    updatedCellData.add(cd);
                }
            } else if (markedCells.contains(key)) {
                sentCells.add(key);
                updatedCellData.add(cd);
            }
        }
    }
    if (!changedFormulaCells.isEmpty()) {
        fireFormulaValueChangeEvent(changedFormulaCells);
        changedFormulaCells = new HashSet<CellReference>();
    }
    // empty cells have cell data with just col and row
    updatedCellData.addAll(removedCells);
    if (!updatedCellData.isEmpty()) {
        spreadsheet.getRpcProxy().cellsUpdated(updatedCellData);
        spreadsheet.getRpcProxy().refreshCellStyles();
    }
    markedCells.clear();
    removedCells.clear();
}

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

License:Open Source License

/**
 * Gets the locked state of the given cell.
 * /*from  w  w w  . j  av  a  2  s .c o m*/
 * @param cell
 *            The cell to check
 * @return true if the cell is locked, false otherwise
 */
public boolean isCellLocked(Cell cell) {
    if (isActiveSheetProtected()) {
        if (cell != null) {
            if (cell.getCellStyle().getIndex() != 0) {
                return cell.getCellStyle().getLocked();
            } else {
                return getState(false).lockedColumnIndexes.contains(cell.getColumnIndex() + 1)
                        && getState(false).lockedRowIndexes.contains(cell.getRowIndex() + 1);
            }
        } else {
            return true;
        }
    } else {
        return false;
    }
}

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

License:Open Source License

/**
 * This should be called when a Cell's styling has been changed. This will
 * tell the Spreadsheet to send the change to the client side.
 * //from   w ww .  ja  v a 2  s  .com
 * @param cell
 *            Target cell
 * @param updateCustomBorders
 *            true to also update custom borders
 */
public void cellStyleUpdated(Cell cell, boolean updateCustomBorders) {
    final String cssSelector = ".col" + (cell.getColumnIndex() + 1) + ".row" + (cell.getRowIndex() + 1);
    final Integer key = (int) cell.getCellStyle().getIndex();
    // remove/modify all possible old custom styles that the cell had (can
    // be found from state)
    ArrayList<String> add = new ArrayList<String>();
    Iterator<String> iterator = spreadsheet.getState().shiftedCellBorderStyles.iterator();
    while (iterator.hasNext()) {
        String style = iterator.next();
        // only cell with this style -> remove
        if (style.startsWith(cssSelector + "{")) {
            iterator.remove();
        } else if (style.contains(cssSelector)) { // shifted borders
            iterator.remove();
            int index = style.indexOf(cssSelector);
            if (index > 0) { // doesn't start with the selector
                style = style.replace(cssSelector + ",", "");
                // in case it is the last
                style = style.replace("," + cssSelector + "{", "{");
            } else {
                style = style.replace(cssSelector + ",", "");
                // in case it is the only
                style = style.replace(cssSelector + "{", "{");
            }
            if (!style.startsWith(",") && !style.startsWith("{")) {
                add.add(style);
            }
        }
    }
    for (String s : add) {
        spreadsheet.getState().shiftedCellBorderStyles.add(s);
    }
    // remove the cell's new custom styles from state (will be added again
    // as this cell is styled)
    if (shiftedBorderLeftStyles.containsKey(key)) {
        final String style = shiftedBorderLeftStyles.get(key);
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }
    if (shiftedBorderTopStyles.containsKey(key)) {
        final String style = shiftedBorderTopStyles.get(key);
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }
    if (mergedCellBorders.containsKey(cssSelector)) {
        final String style = buildMergedCellBorderCSS(cssSelector, mergedCellBorders.remove(cssSelector));
        spreadsheet.getState().shiftedCellBorderStyles.remove(style);
    }

    // TODO May need optimizing since the client side might already have
    // this cell style
    CellStyle cellStyle = cell.getCellStyle();
    addCellStyleCSS(cellStyle);

    shiftedBorderTopStylesMap.clear();
    shiftedBorderLeftStylesMap.clear();
    // custom styles
    doCellCustomStyling(cell);
    updateStyleMap(shiftedBorderLeftStylesMap, shiftedBorderLeftStyles);
    updateStyleMap(shiftedBorderTopStylesMap, shiftedBorderTopStyles);
    if (updateCustomBorders) {
        if (shiftedBorderLeftStyles.containsKey(key)) {
            final String style = shiftedBorderLeftStyles.get(key);
            spreadsheet.getState().shiftedCellBorderStyles.add(style);
        }
        if (shiftedBorderTopStyles.containsKey(key)) {
            final String style = shiftedBorderTopStyles.get(key);
            spreadsheet.getState().shiftedCellBorderStyles.add(style);
        }
        if (mergedCellBorders.containsKey(cssSelector)) {
            spreadsheet.getState().shiftedCellBorderStyles
                    .add(buildMergedCellBorderCSS(cssSelector, mergedCellBorders.get(cssSelector)));
        }
    }
}