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: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);/*w  w w. j a v a2s .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.corpus_tools.peppermodules.spreadsheet.Spreadsheet2SaltMapper.java

License:Apache License

/**
 * Return the last cell of merged cells// www. j a  v a 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.dash.valid.freq.HLAFrequenciesLoader.java

License:Open Source License

private List<String> readHeaderElementsByRace(Row row) {
    List<String> raceHeaders = new ArrayList<String>();

    Iterator<Cell> cellIterator = row.cellIterator();

    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        String[] race = cell.getStringCellValue().split(UNDERSCORE);
        raceHeaders.add(cell.getColumnIndex(), race[0]);
    }//from   ww w  .  java2 s  .c o m

    return raceHeaders;
}

From source file:org.dash.valid.freq.HLAFrequenciesLoader.java

License:Open Source License

/**
 * @param row//from  ww w.  jav  a  2 s. c  o m
 */
private DisequilibriumElement readDiseqilibriumElementsByRace(Row row, List<String> raceHeaders,
        Locus[] locusPositions) {
    // For each row, iterate through each columns
    Iterator<Cell> cellIterator = row.cellIterator();

    List<FrequencyByRace> frequenciesByRace = new ArrayList<FrequencyByRace>();
    DisequilibriumElementByRace disElement = new DisequilibriumElementByRace();

    int columnIndex;
    String cellValue = null;

    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        columnIndex = cell.getColumnIndex();

        if (columnIndex < locusPositions.length) {
            cellValue = cell.getStringCellValue();
            if (!cellValue.contains(GLStringConstants.ASTERISK)) {
                cellValue = locusPositions[columnIndex].getShortName() + GLStringConstants.ASTERISK
                        + cellValue.substring(0, 2) + GLStringUtilities.COLON + cellValue.substring(2);
            }
            disElement.setHlaElement(locusPositions[columnIndex], GLStringConstants.HLA_DASH + cellValue);
        } else {
            if ((locusPositions.length % 2 == 0 && columnIndex % 2 == 0)
                    || (locusPositions.length % 2 != 0 && columnIndex % 2 != 0)) {
                disElement
                        .setFrequenciesByRace(loadFrequencyAndRank(row, cell, frequenciesByRace, raceHeaders));
            }
        }
    }

    return disElement;
}

From source file:org.dash.valid.freq.HLAFrequenciesLoader.java

License:Open Source License

/**
 * @param row//from   w w  w  . ja v  a  2 s  .  c o m
 * @param frequenciesByRace
 * @param cell
 * @param idx
 */
private List<FrequencyByRace> loadFrequencyAndRank(Row row, Cell cell, List<FrequencyByRace> frequenciesByRace,
        List<String> raceHeaders) {
    Double freq = cell.getNumericCellValue();

    if (freq != 0) {
        FrequencyByRace frequencyByRace = new FrequencyByRace(freq,
                ((Double) row.getCell(cell.getColumnIndex() + 1).getNumericCellValue()).toString(),
                raceHeaders.get(cell.getColumnIndex()));
        frequenciesByRace.add(frequencyByRace);
    }

    Collections.sort(frequenciesByRace, new FrequencyByRaceComparator());

    return frequenciesByRace;
}

From source file:org.dbflute.helper.io.xls.DfTableXlsReader.java

License:Apache License

public boolean isNotTrimTarget(DfDataTable table, Cell cell) {
    final String tableName = table.getTableDbName();
    if (!_notTrimTableColumnMap.containsKey(tableName)) {
        return false;
    }//from  w ww. ja v  a  2  s .c  om
    final List<String> notTrimTargetColumnList = _notTrimTableColumnMap.get(tableName);
    final DfDataColumn column = table.getColumn(cell.getColumnIndex());
    final String target = column.getColumnDbName();
    for (String specified : notTrimTargetColumnList) {
        if (target.equalsIgnoreCase(specified)) {
            return true;
        }
    }
    return false;
}

From source file:org.diffkit.diff.sns.DKPoiSheet.java

License:Apache License

@SuppressWarnings("unchecked")
private List<Type> discoverColumnTypes(List<Row> rows_) {
    if (CollectionUtils.isEmpty(rows_))
        return null;
    List<Type> columnTypes = GrowthList
            .decorate(LazyList.decorate(new ArrayList<Type>(), FactoryUtils.nullFactory()));
    int start = this.hasHeader() ? 1 : 0;
    for (int i = start; i < rows_.size(); i++) {
        Row aRow = rows_.get(i);/*from   w  w  w  .  j  a  v  a  2  s.com*/
        int width = aRow.getLastCellNum();
        for (int j = 0; j < width; j++) {
            Cell cell = aRow.getCell(j);
            if (cell == null)
                continue;
            if (_isDebugEnabled) {
                _log.debug(String.format("cell->%s formatString->%s format->%s", cell.getColumnIndex(),
                        cell.getCellStyle().getDataFormatString(), cell.getCellStyle().getDataFormat()));
            }
            Type cellType = mapColumnType(cell);
            Type columnType = columnTypes.get(j);
            if (_isDebugEnabled)
                _log.debug("cellType->{} columnType->{}", cellType, columnType);
            if (columnType == null)
                columnTypes.set(j, cellType);
            else if (columnType != cellType)
                columnTypes.set(j, Type.MIXED);
        }
    }
    return columnTypes;
}

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

License:Apache License

private void processSheet(Sheet sheet, List<? extends DataListener> listeners) {
    int maxRows = sheet.getLastRowNum();

    CellRangeAddress[] mergedRanges = getMergedCells(sheet);
    DataFormatter formatter = new DataFormatter(Locale.ENGLISH);
    FormulaEvaluator formulaEvaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();

    for (int i = 0; i <= maxRows; i++) {
        Row row = sheet.getRow(i);/*w w w  .  ja v  a2 s  . com*/
        int lastCellNum = row != null ? row.getLastCellNum() : 0;
        newRow(listeners, i, lastCellNum);

        for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell == null) {
                continue;
            }
            double num = 0;

            CellRangeAddress merged = getRangeIfMerged(cell, mergedRanges);

            if (merged != null) {
                Cell topLeft = sheet.getRow(merged.getFirstRow()).getCell(merged.getFirstColumn());
                newCell(listeners, i, cellNum, formatter.formatCellValue(topLeft), topLeft.getColumnIndex());

            } else {
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_FORMULA:
                    String cellValue = null;
                    try {
                        CellValue cv = formulaEvaluator.evaluate(cell);
                        cellValue = getCellValue(cv);
                        newCell(listeners, i, cellNum, cellValue, DataListener.NON_MERGED);
                    } catch (RuntimeException e) {
                        // This is thrown if an external link cannot be resolved, so try the cached value
                        log.warn("Cannot resolve externally linked value: " + formatter.formatCellValue(cell));
                        String cachedValue = tryToReadCachedValue(cell);
                        newCell(listeners, i, cellNum, cachedValue, DataListener.NON_MERGED);
                    }
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    num = cell.getNumericCellValue();
                default:
                    if (num - Math.round(num) != 0) {
                        newCell(listeners, i, cellNum, String.valueOf(num), DataListener.NON_MERGED);
                    } else {
                        newCell(listeners, i, cellNum, formatter.formatCellValue(cell),
                                DataListener.NON_MERGED);
                    }
                }
            }
        }
    }
    finishSheet(listeners);
}

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  .  j  av  a 2s  .  com*/
    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;
        }/*  w ww .  j a  v  a2 s .c  o  m*/
    }
    return null;
}