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

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

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

Introduction

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

Prototype

int getRowIndex();

Source Link

Document

Returns row index of a row in the sheet that contains this cell

Usage

From source file:com.r573.enfili.common.doc.spreadsheet.SpreadsheetHelper.java

License:Apache License

public static String getSpreadsheetCellReference(Cell cell) {
    return getSpreadsheetCellReference(cell.getColumnIndex(), cell.getRowIndex());
}

From source file:com.runwaysdk.dataaccess.io.excel.FieldColumn.java

License:Open Source License

public Object getValue(Cell cell) throws Exception {
    int fieldType = this.getExpectedFieldType();

    if (cell.getCellType() == fieldType) {
        return this.getCellValue(cell);
    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        String value = cell.getRichStringCellValue().getString();

        if (value == null || value.length() == 0) {
            return null;
        }//  w ww .java 2 s.  c om
    }

    int row = cell.getRowIndex();
    String attributeName = this.getAttributeName();
    String msg = "Conversion exception on row (" + row + ", " + attributeName + ") expected type [" + fieldType
            + "] actual type [" + cell.getCellType() + "]";

    throw new FieldConversionException(msg, this);
}

From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java

private void addListProducts() {

    Cell referenceCell = this.findCell(LIST_PRODUCT_KEY).get(0);
    XSSFSheet sheetMoves = this.getSheet(0);
    int i = 0;//from w ww  .  ja  va2  s.co  m

    XSSFRow row = null;
    for (InvoiceInventoryMapEntity inventory : listInventory) {

        row = sheetMoves.createRow(referenceCell.getRowIndex() + i++);
        this.createCellProduct(sheetMoves, row, 0, inventory.getProduct().getName());
        this.createCell(row, 4, inventory.getQtdInventoryPreviusTrimester().toString());
        this.createCell(row, 5, inventory.getQtdBuyProduct().toString());
        this.createCell(row, 6,
                inventory.getQtdInventoryPreviusTrimester().add(inventory.getQtdBuyProduct()).toString());
        this.createCellUtilization(sheetMoves, row, inventory.getQtdUtilization().toString());
        this.createCellSell(sheetMoves, row, inventory.getQtdSellProduct().toString());
        this.createCellNextTrimester(sheetMoves, row,
                inventory.getQtdBuyProduct().subtract(inventory.getQtdSellProduct()).toString());
        this.createCell(row, 14, inventory.getTypeQtdProduct());

    }

    this.addBotton(row, sheetMoves);

}

From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java

private void addListInvoices(List<InvoiceProductEntity> listInvoice, String keyInvoice, Integer numSheet) {

    if (listInvoice == null || listInvoice.isEmpty()) {
        return;//from w  w  w.  jav a 2s  .  co m
    }

    Cell referenceCell = this.findCell(keyInvoice).get(0);
    XSSFSheet sheet = this.getSheet(numSheet);
    int i = 0;

    XSSFRow rowProviderPhoneCity = null;
    for (InvoiceProductEntity productInvoice : listInvoice) {

        XSSFRow row = sheet.createRow(referenceCell.getRowIndex() + i++);
        this.createCell(row, 0, DateUtils.format(productInvoice.getInvoice().getDateCreate()));
        this.createCell(row, 1, productInvoice.getInvoice().getNumInvoice());

        String qtdtypeQtdInvoice = productInvoice.getQtdProduct().toString() + " "
                + productInvoice.getTypeQtdProduct();

        this.createCell(row, 2, qtdtypeQtdInvoice);
        this.createCell(row, 3, productInvoice.getProduct().getProductOfficial().getName());

        // Fornecedor
        ProviderEntity provider = productInvoice.getInvoice().getProvider();
        String providerName = provider != null ? productInvoice.getInvoice().getProvider().getName() : "";
        String providerStreet = provider != null ? productInvoice.getInvoice().getProvider().getStreet() : "";
        String providerCep = provider != null ? productInvoice.getInvoice().getProvider().getCep() : "";
        String providerCity = provider != null ? productInvoice.getInvoice().getProvider().getCity() : "";

        ProviderEntity providerCarrier = productInvoice.getInvoice().getProviderCarrier();
        String providerCarrierName = providerCarrier != null
                ? productInvoice.getInvoice().getProviderCarrier().getName()
                : "";
        String providerCarrierStreet = providerCarrier != null
                ? productInvoice.getInvoice().getProviderCarrier().getStreet()
                : "";
        String providerCarrierCep = providerCarrier != null
                ? productInvoice.getInvoice().getProviderCarrier().getCep()
                : "";
        String providerCarrierCity = providerCarrier != null
                ? productInvoice.getInvoice().getProviderCarrier().getCity()
                : "";

        this.createCell(row, 4, "Razo Social");
        this.createCell(row, 5, providerName);
        this.createCell(row, 7, "Razo Social");
        this.createCell(row, 8, providerCarrierName);

        XSSFRow rowProviderStreet = sheet.createRow(referenceCell.getRowIndex() + i++);
        this.createCell(rowProviderStreet, 4, "Endereo/Cep");
        this.createCell(rowProviderStreet, 5, providerStreet + " " + providerCep);
        this.createCell(rowProviderStreet, 7, "Endereo/Cep");
        this.createCell(rowProviderStreet, 8, providerCarrierStreet + " " + providerCarrierCep);

        rowProviderPhoneCity = sheet.createRow(referenceCell.getRowIndex() + i++);
        this.createCell(rowProviderPhoneCity, 4, "Cidade/UF/Fone");
        this.createCell(rowProviderPhoneCity, 5,
                providerCity + "/" + productInvoice.getInvoice().getCompany().getPhoneNumber());
        this.createCell(rowProviderPhoneCity, 7, "Cidade/UF/Fone");
        this.createCell(rowProviderPhoneCity, 8,
                providerCarrierCity + "/" + " " + productInvoice.getInvoice().getCompany().getPhoneNumber());
    }

    this.addBotton(rowProviderPhoneCity, sheet);

}

From source file:com.tutorial.excelreadwrite.excelFunctions.java

    public void markHorizontal(int spacesApart){
        //Set-up rowIterator and get Row
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()){
            Row row = rowIterator.next();
            /*from   w ww . j  av a  2  s . c  o m*/
            //Set-up cellIterator and get Cell
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()){
                Cell cell = cellIterator.next();
                
                //Obtains the Cell Style
                XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();
                //Checks to see if the Cell Style is null; if null, go to next cell
                if(cellStyle != null){
                    //Checks to see what color is the cell's color
                    XSSFColor cellColor = cellStyle.getFillForegroundXSSFColor();
                    //Checks to see if color is null; if not compare to accept only editted or userDefined cells
                    if(cellColor != null){
                        //Checks if current cell is userDefined or editted
                        //If it is not, then go to the next cell
                        if(cellColor.equals(mark.getFillForegroundXSSFColor()) || cellColor.equals(userDefinedColor)){

                            //Set boolean isCellMarked to false before proceeding
                            isCellMarked = false;

                            //Define Cell to be (spacesApart+1) away
                            //So if x = current cell then the cell that is 5 spacesApart =
                            // [x][][][][][][x]
                            Cell cellMark = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex() + spacesApart + 1);

                            //Checks to see if cell is null; if present, get its Cell Style
                            if(cellMark != null){
                                XSSFCellStyle cellMarkStyle = (XSSFCellStyle)cellMark.getCellStyle();

                                //Checks to see if the style is null; if present, get its color
                                if(cellMarkStyle != null){
                                    XSSFColor cellMarkColor = cellMarkStyle.getFillForegroundXSSFColor();

                                    //Checks to see if the color is null; if present, compare colors
                                    if(cellMarkColor != null){
                                        if(cellMarkColor.equals(userDefinedColor)){
                                            isCellMarked = true;
                                        }
                                    }
                                }
                            }

                            /*
                            ** CHECK#1: 'isCellMarked'
                            ** If isCellMarked is marked true, start iterating through the
                            ** cells in between and check if null or not userDefinedStyle
                            */
                            if(isCellMarked == true){
                                for(int i = 1; i <= spacesApart; ++i){
                                    Cell isNull = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex()+i);

                                    //Checks to see if the cell is null; if color is present, set isCellMarked to false
                                    if(isNull != null){
                                        XSSFCellStyle cellCheckIfNullCellStyle = (XSSFCellStyle)isNull.getCellStyle();
                                        if(cellCheckIfNullCellStyle != null){
                                            XSSFColor cellCheckIfNullColor = cellCheckIfNullCellStyle.getFillForegroundXSSFColor();
                                            if(cellCheckIfNullColor != null){
                                                if(cellCheckIfNullColor.equals(userDefinedColor)){
                                                    isCellMarked = false;
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            /*
                            ** CHECK#2: 'isCellMarked2'
                            ** If isCellMarked remains as true, set the two cell's style
                            */
                            if(isCellMarked == true){
                                cell.setCellStyle(mark);
                                cellMark.setCellStyle(mark);
                            }
                        }
                }
            }
        }
    }
}

From source file:com.vaadin.addon.spreadsheet.action.InsertDeleteCellCommentAction.java

License:Open Source License

private void createCellComment(Spreadsheet spreadsheet, Sheet sheet, Cell cell, CellReference cellRef) {
    CreationHelper factory = sheet.getWorkbook().getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 3);

    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString("");
    comment.setString(str);// w  w w  .  j a  v  a 2s .  c om

    // Fetch author from provider or fall back to default
    String author = null;
    if (spreadsheet.getCommentAuthorProvider() != null) {
        author = spreadsheet.getCommentAuthorProvider().getAuthorForComment(cellRef);
    }
    if (author == null || author.trim().isEmpty()) {
        author = "Spreadsheet User";
    }
    comment.setAuthor(author);

    // Assign the comment to the cell
    cell.setCellComment(comment);
}

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//from   w  w w.  ja va  2s  .  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 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
 * //w  ww .  j  av a 2  s. c om
 * @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./* www. j  a  v  a2  s . c o  m*/
 * 
 * @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  ww. j a v  a  2 s  .  c o m
        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;
}