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:org.codelabor.example.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);//  ww  w.j av  a2  s  . c om
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.codelabor.example.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);/*from   www .  j  a  v  a 2 s .c  o m*/
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java

License:Apache License

/**
 * Return the last cell of merged cells// w w  w  . j a  va 2 s.  c  o  m
 * 
 * @param primCell
 *            , current cell of the primary text
 * @return
 */
private int getLastCell(Cell cell, Table<Integer, Integer, CellRangeAddress> mergedCellsIdx) {
    int lastCell = cell.getRowIndex();
    CellRangeAddress mergedCell = null;
    if (mergedCellsIdx != null) {
        mergedCell = mergedCellsIdx.get(cell.getRowIndex(), cell.getColumnIndex());
    }
    if (mergedCell != null) {
        lastCell = mergedCell.getLastRow();
    }
    return lastCell;
}

From source file:org.drools.decisiontable.parser.xls.ExcelParser.java

License:Apache License

private String tryToReadCachedValue(Cell cell) {
    DataFormatter formatter = new DataFormatter(Locale.ENGLISH);
    String cachedValue;//  w  w w.  ja va2  s  .  c o  m
    switch (cell.getCachedFormulaResultType()) {
    case Cell.CELL_TYPE_NUMERIC:
        double num = cell.getNumericCellValue();
        if (num - Math.round(num) != 0) {
            cachedValue = String.valueOf(num);
        } else {
            cachedValue = formatter.formatCellValue(cell);
        }
        break;

    case Cell.CELL_TYPE_STRING:
        cachedValue = cell.getStringCellValue();
        break;

    case Cell.CELL_TYPE_BOOLEAN:
        cachedValue = String.valueOf(cell.getBooleanCellValue());
        break;

    case Cell.CELL_TYPE_ERROR:
        cachedValue = String.valueOf(cell.getErrorCellValue());
        break;

    default:
        throw new DecisionTableParseException(
                format("Can't read cached value for cell[row=%d, col=%d, value=%s]!", cell.getRowIndex(),
                        cell.getColumnIndex(), cell));
    }
    return cachedValue;
}

From source file:org.drools.decisiontable.parser.xls.ExcelParser.java

License:Apache License

CellRangeAddress getRangeIfMerged(Cell cell, CellRangeAddress[] mergedRanges) {
    for (int i = 0; i < mergedRanges.length; i++) {
        CellRangeAddress r = mergedRanges[i];
        if (r.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
            return r;
        }/*ww w  . j a  va 2s  .  co m*/
    }
    return null;
}

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static String getCellId(Cell cell) throws Exception {
    if (cellIsEmpty(cell))
        return null;

    if (!cellIsStringOrBlank(cell))
        throw new Exception("Cannot operate on a non-string cell.");

    String strippedCell = stripFootnoteReference(cell);
    if (strippedCell != null)
        strippedCell = strippedCell.trim();

    String id = String.format("R%dC%d", cell.getRowIndex(), cell.getColumnIndex());
    if (cellIsEmpty(cell))
        return id;

    String[] tokens = strippedCell.split(CELL_ID_DIVIDER_REGEX);
    if (tokens.length == 2)
        return tokens[1].trim();

    return id;//from  www  . jav  a  2  s  .co  m
}

From source file:org.drugepi.table.TableCreator.java

License:Mozilla Public License

private void fillTableInSheet(Sheet sheet, Table t) throws Exception {
    Row firstRow = sheet.getRow(0);/*w w w  . ja  va  2 s  .co m*/

    if (firstRow == null) {
        System.out.println("Sheet is empty.");
        return;
    }

    // Find four cells:
    Cell columnDefBottomLeft = null;
    Cell columnDefBottomRight = null;
    Cell rowDefTopRight = null;
    Cell rowDefBottomRight = null;

    for (Row row : sheet) {
        if (row != null) {
            for (Cell cell : row) {
                // We are iterating top to bottom, left to right
                if (ExcelUtils.cellIsColumnDefinition(cell)) {
                    // get the last cell that is on the left side
                    if ((columnDefBottomLeft == null)
                            || (cell.getColumnIndex() <= columnDefBottomLeft.getColumnIndex()))
                        columnDefBottomLeft = cell;

                    // get the last cell that is on the right side
                    if ((columnDefBottomRight == null)
                            || (cell.getColumnIndex() >= columnDefBottomRight.getColumnIndex()))
                        columnDefBottomRight = cell;

                }

                if (ExcelUtils.cellIsRowDefinition(cell)) {
                    // get the first cell that is on the right side
                    if ((rowDefTopRight == null) || (cell.getColumnIndex() > rowDefTopRight.getColumnIndex()))
                        rowDefTopRight = cell;

                    // get the last cell that is on the right side
                    if ((rowDefBottomRight == null)
                            || (cell.getColumnIndex() >= rowDefBottomRight.getColumnIndex()))
                        rowDefBottomRight = cell;
                }
            }
        }
    }

    if ((columnDefBottomLeft == null) || (columnDefBottomRight == null) || (rowDefTopRight == null)
            || (rowDefBottomRight == null))
        return;

    int rowFillStart = rowDefTopRight.getRowIndex();
    int rowFillEnd = rowDefBottomRight.getRowIndex();
    int colFillStart = columnDefBottomLeft.getColumnIndex();
    int colFillEnd = columnDefBottomRight.getColumnIndex();

    for (int rowIndex = rowFillStart; rowIndex <= rowFillEnd; rowIndex++) {
        Row row = sheet.getRow(rowIndex);
        for (int colIndex = colFillStart; colIndex <= colFillEnd; colIndex++) {
            Cell columnParent = ExcelUtils.getContainerColumnCell(sheet, rowIndex, colIndex);
            Cell rowParent = ExcelUtils.getContainerRowCell(sheet, rowIndex, colIndex);

            if ((columnParent != null) && (rowParent != null)) {
                Cell cell = row.getCell(colIndex);
                if (cell == null)
                    cell = row.createCell(colIndex);

                String colId = TableElement.makeId(ExcelUtils.getCellId(columnParent));
                String rowId = TableElement.makeId(ExcelUtils.getCellId(rowParent));
                String cellId = TableCell.getCellId(rowId, colId);

                TableCell c = t.cells.get(cellId);
                if ((c != null) && (c.description.length() > 0))
                    ExcelUtils.setCellValue(cell, c.description);
            }
        }
    }

    CellStyleLookup csl = new CellStyleLookup();
    for (Row row : sheet) {
        if (row != null) {
            for (Cell cell : row) {
                if (ExcelUtils.cellIsStringOrBlank(cell)) {
                    String id = ExcelUtils.getCellId(cell);
                    if (id != null)
                        cell.setCellValue(ExcelUtils.getCellContents(cell));
                }

                ExcelUtils.restyleCell(csl, cell);
            }
        }
    }
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiAttributeConverter.java

License:Open Source License

/**
 * {@inheritDoc}/*  w  w w .j  a v  a2  s .c  o  m*/
 *
 * @see org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
 *      org.eclipse.emf.ecore.EStructuralFeature)
 */
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature) throws EMFFormsConverterException {
    String string;
    try {
        string = cell.getStringCellValue();
    } catch (final IllegalStateException e) {
        throw new EMFFormsConverterException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                e);
    }

    if (string == null || string.length() == 0) {
        return Collections.emptyList();
    }
    final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
    final EDataType eDataType = eAttribute.getEAttributeType();
    if (isDecimalNumber(eDataType.getInstanceClass())) {
        string = string.replace(
                DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator(), '.');
    }

    final List<Object> result = new ArrayList<Object>();
    final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
    for (final String element : string.split(SEPARATOR)) {
        try {
            result.add(eFactory.createFromString(eDataType, element));
        } // BEGIN SUPRESS CATCH EXCEPTION
        catch (final RuntimeException ex) {// END SUPRESS CATCH EXCEPTION
            throw new EMFFormsConverterException(
                    MessageFormat.format("The cell value {0} could not converted to a model value.", string)); //$NON-NLS-1$
        }
    }

    return result;
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleAttributeConverter.java

License:Open Source License

/**
 * {@inheritDoc}//from  ww  w.j  a  v a2 s  .  c  om
 *
 * @see org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#getCellValue(org.apache.poi.ss.usermodel.Cell,
 *      org.eclipse.emf.ecore.EStructuralFeature)
 */
@Override
public Object getCellValue(Cell cell, EStructuralFeature eStructuralFeature) throws EMFFormsConverterException {
    final EAttribute eAttribute = EAttribute.class.cast(eStructuralFeature);
    if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return null;
    }
    if (eAttribute == null) {
        return null;
    }
    final EDataType attributeType = eAttribute.getEAttributeType();
    if (attributeType == null) {
        return null;
    }
    try {
        return readCellValue(cell, attributeType);
    } catch (final IllegalStateException e) {
        throw new EMFFormsConverterException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                e);
    } catch (final NumberFormatException e) {
        throw new EMFFormsConverterException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s is not a valid number.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                e);
    }
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetImporterImpl.java

License:Open Source License

private String getStringCellValue(Cell cell) {
    try {//from   w w w . j a va  2  s.c  o  m
        return cell.getStringCellValue();
    } catch (final IllegalArgumentException ex) {
        throw new IllegalStateException(
                String.format("Cell value of column %1$s in row %2$s on sheet %3$s must be a string.", //$NON-NLS-1$
                        cell.getColumnIndex() + 1, cell.getRowIndex() + 1, cell.getSheet().getSheetName()),
                ex);
    }
}