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.hauldata.dbpa.file.book.XlsxTargetSheet.java

License:Apache License

private void adjustAdjacentCellStyle(Cell cell, Styles styles, RowPosition rowPosition,
        ColumnPosition columnPosition, Styles leftStyles, Styles aboveStyles) {

    if (styles == null) {
        return;// w  ww  .j av a 2s.  c om
    }

    if ((leftStyles != null) && (columnPosition != ColumnPosition.LEFT)
            && (columnPosition != ColumnPosition.SINGLE) && !leftStyles.rightBorder.equals(styles.leftBorder)) {

        leftStyles.rightBorder = styles.leftBorder;

        Cell leftCell = cell.getRow().getCell(cell.getColumnIndex() - 1);

        leftCell.setCellStyle(composeCellStyle(leftCell, leftStyles));
    }

    if ((aboveStyles != null) && (rowPosition != RowPosition.HEADER) && (rowPosition != RowPosition.TOP)
            && !aboveStyles.bottomBorder.equals(styles.topBorder)) {

        aboveStyles.bottomBorder = styles.topBorder;

        Cell aboveCell = sheet.getRow(cell.getRowIndex() - 1).getCell(cell.getColumnIndex());

        aboveCell.setCellStyle(composeCellStyle(aboveCell, aboveStyles));
    }
}

From source file:com.hurence.logisland.processor.excel.ExcelExtract.java

License:Apache License

/**
 * Handle row content and transform it into a {@link Record}
 *
 * @param row the {@link Row}/*from w w w .  j  a  v  a 2s  . c o m*/
 * @return the transformed {@link Record}
 */
private Record handleRow(Row row, List<String> header) {
    Record ret = new StandardRecord().setTime(new Date());
    int index = 0;
    for (Cell cell : row) {
        if (configuration.getFieldNames() != null && index >= configuration.getFieldNames().size()) {
            //we've reached the end of mapping. Go to next row.
            break;
        }
        if (configuration.getColumnsToSkip().contains(cell.getColumnIndex())) {
            //skip this cell.
            continue;
        }
        String fieldName = header != null ? header.get(cell.getColumnIndex())
                : configuration.getFieldNames().get(index++);
        Field field;
        // Alternatively, get the value and format it yourself
        switch (cell.getCellTypeEnum()) {
        case STRING:
            field = new Field(fieldName, FieldType.STRING, cell.getStringCellValue());
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                field = new Field(fieldName, FieldType.LONG, cell.getDateCellValue().getTime());
            } else {
                field = new Field(fieldName, FieldType.DOUBLE, cell.getNumericCellValue());
            }
            break;
        case BOOLEAN:
            field = new Field(fieldName, FieldType.BOOLEAN, cell.getBooleanCellValue());
            break;
        case FORMULA:
            field = new Field(fieldName, FieldType.STRING, cell.getCellFormula());
            break;
        default:
            //blank or unknown
            field = new Field(fieldName, FieldType.NULL, null);
            break;
        }
        ret.setField(field);
    }
    return ret;
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * Put the next row in the dvd row given in parameter.
 * Return SCAN_COMPLETED if there is no more row in the spreadsheet, or GOT_ROW if a row was successfully put in the dvd row.
 * /*from   www . ja va 2  s  .  c o m*/
 * Uses the attribute currentRow to save the previous row fetched.
 * 
 * @param sheet
 * @param dvdr
 * @param numberOfLogicalColumnsInvolved
 * @param columnIndexes
 * @return SCAN_COMPLETED or GOT_ROW
 * @throws SQLException
 */
private int createNextRow(Sheet sheet, DataValueDescriptor[] dvdr) {
    boolean gotData = false;

    /*
     * Find the next row to return.
     * 
     * currentRow should currently point to the last row returned.
     * If that's null, then start from first row.
     * Else, search for the next non-empty row (until we hit the end of the prescribed range).
     */
    if (currentRow == null)
        currentRow = sheet.getRow(firstRowIndex + (firstRowIsMetaData ? 1 : 0));
    else {
        int nextRowIndex = currentRow.getRowNum() + 1;
        currentRow = null;

        if (stopScanOnFirstEmptyRow) {
            currentRow = sheet.getRow(nextRowIndex);
        } else {
            while (currentRow == null && nextRowIndex <= lastRowIndex) {
                currentRow = sheet.getRow(nextRowIndex);
                nextRowIndex++;
            }
        }
    }

    /*
     * If we've run out of spreadsheet (currentRow == null) or gone out of the prescribed range,
     * then scan complete - return that.
     */
    if (currentRow == null || currentRow.getRowNum() > lastRowIndex) {
        return SCAN_COMPLETED;
    }

    /*
     * Get the offset of the first column in the spreadsheet.
     * Note: this is used when iterating below, so that we can correctly relate 
     * the actual column in the spreadsheet to the correct 'column' in the 
     * DataValueDescriptor [] representing the row.
     */
    int columnOffset = firstColumnIndex;

    //Figure out how many columns there are
    int numberOfColumns = lastColumnIndex - firstColumnIndex + 1;

    for (int i = 0; i < numberOfColumns; i++) {
        /*
         * Note: i is used to refer to the index of the DataValueDescriptor which represents
         * the actual spreadsheet column (at i + columnOffset) in the DataValueDescriptor[] 
         * representing this row. 
         */

        Cell cell = currentRow.getCell(i + columnOffset);

        if (cell == null) {
            dvdr[i].setToNull();
        } else {
            try {
                int cellValueType = cell.getCellType();

                if (cellValueType == Cell.CELL_TYPE_FORMULA)
                    cellValueType = cell.getCachedFormulaResultType();

                switch (cellValueType) {

                case Cell.CELL_TYPE_STRING:
                    dvdr[i].setValue(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        dvdr[i].setValue(new java.sql.Date(cell.getDateCellValue().getTime()));
                    else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        dvdr[i].setValue(cell.getStringCellValue());
                    }
                    break;

                case Cell.CELL_TYPE_BOOLEAN:
                    dvdr[i].setValue(cell.getBooleanCellValue());
                    break;

                default:
                    dvdr[i].setToNull();
                    break;
                }

                //If a cell has data that is not null - then flag that we actually have data to return
                if (!dvdr[i].isNull())
                    gotData = true;

            } catch (Exception e) {
                dvdr[i].setToNull();
                logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_MAP_LT_ERROR, "Excel cell [spreadsheet "
                        + sheet.getSheetName() + "; row " + cell.getRow().getRowNum() + "; column "
                        + cell.getColumnIndex() + "; value " + cell
                        + "] could not be mapped into the logical table because of the column logical type: "
                        + e);
            }
        }
    }

    if (!gotData && stopScanOnFirstEmptyRow) {
        logger.logInfo(
                "Ending GExcel table scan on first empty row (as no row limit was specified in the ending cell config constraint)");
        return SCAN_COMPLETED;
    }

    return GOT_ROW;
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * looks for the column definition and initializes the following attributes :
 * /*from   w  ww .j a  v  a  2  s.co m*/
 * - numberOfColumns
 * - columnIndexes
 * - columnNames
 *
 * If a column which contains no values is ignored.
 * 
 * If firstRowIsMetaData is true, the column names will be extract from the first row of the spreadsheet.
 * Else, they will be automatically generated : COLUMN1, COLUMN2...
 * 
 * @param sheet
 */
private void findColumns(Sheet sheet) {
    numberOfColumns = 0;

    columnIndexes = new ArrayList<Integer>();
    columnNames = new ArrayList<String>();

    Row firstRow = sheet.getRow(firstRowIndex);

    int columnLabelIndex = 1;

    if (firstRowIsMetaData) {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());

            int cellType = cell.getCellType();
            if (Cell.CELL_TYPE_FORMULA == cellType) {
                cellType = cell.getCachedFormulaResultType();
                //                System.out.println("cell type is now getCachedFormulaResultType() = " + cellType );
            }

            //Build the column names depending on it's type
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                //                case Cell.CELL_TYPE_FORMULA: // DO NOT USE: getCellFormula() !!!

                //                   System.out.println("cell type string" );

                // Note: Javadoc on method getStringCellValue() states:
                // "get the value of the cell as a string - for numeric cells we throw an exception. For blank cells we return an empty string. 
                // For formulaCells that are not string Formulas, we throw an exception"

                ++numberOfColumns;
                columnNames.add(cell.getStringCellValue().replaceAll("[\\ ]", "_")); // Note we should not have to do this in future... once defect is fixed
                break;

            case Cell.CELL_TYPE_NUMERIC:

                //                   System.out.println("cell type numeric " + 
                //                         ( DateUtil.isCellDateFormatted( cell ) ? "date: " + cell.getDateCellValue().toString() : "num: " + cell.getNumericCellValue() ) );

                ++numberOfColumns;
                columnNames.add(DateUtil.isCellDateFormatted(cell) ? cell.getDateCellValue().toString()
                        : "" + cell.getNumericCellValue());
                break;

            case Cell.CELL_TYPE_BOOLEAN:

                //                   System.out.println("cell type boolean" );

                ++numberOfColumns;
                columnNames.add("" + cell.getBooleanCellValue());
                break;

            default:

                //                   System.out.println("cell type default" );

                ++numberOfColumns;
                columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex);
                break;
            }

            columnLabelIndex++;
        }
    } else {
        //For each column
        for (int i = firstColumnIndex; i <= lastColumnIndex; ++i) {
            //Get the first cell in the column
            Cell cell = firstRow.getCell(i, Row.CREATE_NULL_AS_BLANK);

            columnIndexes.add(cell.getColumnIndex());
            columnNames.add(DEFAULT_COLUMN_LABEL + "" + columnLabelIndex++);
        }
    }
}

From source file:com.ibm.db2j.GExcel.java

License:Open Source License

/**
 * This method checks if the spreadsheet is well typed.
 * This means that all values in each column have the same excel type.
 * //from www . j  a  v  a2s.  com
 * Returns true if the spreadsheet is well typed, else returns false.
 *
 * In addition, this method tries to deduce the excel types from each columns and initializes the attribute : columnTypes.
 * If a column is empty, then its type is CELL_WITH_NO_TYPE.
 * The attribute columnTypes must only be used if the spreadsheet is well typed. In the other cases, columnTypes is not significant.
 * 
 * @param columnIndexes
 * @return returns true if the spreadsheet is well typed, else returns false
 */
private boolean checkSheetTypeConsistency(List<Integer> columnIndexes) {
    boolean isConsistent = true;

    int firstRow = firstRowIndex;
    if (firstRowIsMetaData) {
        ++firstRow;
    }

    Row currentRow;
    Cell cell;
    int index;
    for (int i = firstRow; i <= lastRowIndex; ++i) {
        currentRow = sheet.getRow(i);
        if (currentRow != null) {
            index = 0;
            for (int j = firstColumnIndex; j <= lastColumnIndex; ++j) {
                cell = currentRow.getCell(j, Row.CREATE_NULL_AS_BLANK);
                if (cell != null) {
                    //                  logger.logInfo("Checking non-null cell: " + cell);
                    int cellType = -1;
                    try {
                        cellType = evaluator.evaluateInCell(cell).getCellType();
                    } catch (Exception e) {
                        logger.logWarning(GDBMessages.DSWRAPPER_GEXCEL_CELL_TYPE_EVALUATION_FAILURE,
                                "Unable to evaluate type for cell at row " + i + " col " + j + ": " + cell);
                        isConsistent = false;
                        continue;
                    }

                    switch (cellType) {
                    case Cell.CELL_TYPE_STRING:

                        isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent);
                        ++index;
                        break;

                    case Cell.CELL_TYPE_NUMERIC:

                        isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent);
                        ++index;
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:

                        isConsistent = checkSheetConsistencySubMethod(cell, index, isConsistent);
                        ++index;
                        break;

                    default:

                        if (index < columnIndexes.size() && columnIndexes.get(index) == cell.getColumnIndex()) {
                            // The cell is null for this column
                            ++index;
                        }
                        break;
                    }
                }
            }
        }
    }

    return isConsistent;
}

From source file:com.impetus.kvapps.runner.UserBroker.java

License:Apache License

private String extractCellData(Row row, int iCurrent) throws Exception {
    Cell cell = (Cell) row.getCell(iCurrent);
    if (cell == null) {
        return "";
    } else {//  w  w  w  .  java2 s  .c  om
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:
            double value = cell.getNumericCellValue();
            if (HSSFDateUtil.isCellDateFormatted(cell))

            {
                if (HSSFDateUtil.isValidExcelDate(value)) {
                    Date date = HSSFDateUtil.getJavaDate(value);
                    SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
                    return dateFormat.format(date);
                } else {
                    throw new Exception("Invalid Date value found at row number " + row.getRowNum()
                            + " and column number " + cell.getColumnIndex());
                }
            } else {
                return value + "";
            }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case HSSFCell.CELL_TYPE_BLANK:
            return null;
        default:
            return null;
        }
    }
}

From source file:com.jeefuse.system.code.web.imports.excel.GsysCodeExcelImport.java

License:GNU General Public License

/**
 * populate model./*from w  w  w.  j  a  v  a 2  s.c o  m*/
 * 
 * @generated
 */
@Override
protected GsysCode readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysCode model = new GsysCode();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysCodeField gsysCodeField = GsysCodeField.valueOfFieldLabel(columnName);
        if (null == gsysCodeField) {
            gsysCodeField = GsysCodeField.valueOfFieldName(columnName);
        }
        if (null == gsysCodeField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysCodeField);
        InvalidValue[] invalidValues = GsysCodeValidate.validateProperty(model, gsysCodeField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysCodeField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.code.web.imports.excel.GsysCodevalueExcelImport.java

License:GNU General Public License

/**
 * populate model./*from  w w w  . java 2 s.co m*/
 * 
 * @generated
 */
@Override
protected GsysCodevalue readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysCodevalue model = new GsysCodevalue();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysCodevalueField gsysCodevalueField = GsysCodevalueField.valueOfFieldLabel(columnName);
        if (null == gsysCodevalueField) {
            gsysCodevalueField = GsysCodevalueField.valueOfFieldName(columnName);
        }
        if (null == gsysCodevalueField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysCodevalueField);
        InvalidValue[] invalidValues = GsysCodevalueValidate.validateProperty(model, gsysCodevalueField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysCodevalueField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.log.web.imports.excel.GsysLoginlogExcelImport.java

License:GNU General Public License

/**
 * populate model.//from   w ww  . j a  va2  s.  com
 * 
 * @generated
 */
@Override
protected GsysLoginlog readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysLoginlog model = new GsysLoginlog();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysLoginlogField gsysLoginlogField = GsysLoginlogField.valueOfFieldLabel(columnName);
        if (null == gsysLoginlogField) {
            gsysLoginlogField = GsysLoginlogField.valueOfFieldName(columnName);
        }
        if (null == gsysLoginlogField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysLoginlogField);
        InvalidValue[] invalidValues = GsysLoginlogValidate.validateProperty(model, gsysLoginlogField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysLoginlogField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}

From source file:com.jeefuse.system.log.web.imports.excel.GsysOperatelogExcelImport.java

License:GNU General Public License

/**
 * populate model.//from  ww w .  j  av  a  2s . c o m
 * 
 * @generated
 */
@Override
protected GsysOperatelog readExcelToModel(Row row, String[] columnNames) {
    if (row == null)
        return null;
    GsysOperatelog model = new GsysOperatelog();
    int cellLenght = columnNames.length;
    Cell cell = null;
    for (int i = 0; i < cellLenght; i++) {
        cell = row.getCell(i);
        String columnName = columnNames[i];
        GsysOperatelogField gsysOperatelogField = GsysOperatelogField.valueOfFieldLabel(columnName);
        if (null == gsysOperatelogField) {
            gsysOperatelogField = GsysOperatelogField.valueOfFieldName(columnName);
        }
        if (null == gsysOperatelogField)
            throw new DataNoExistException("??:" + columnName);
        setModelData(model, cell, gsysOperatelogField);
        InvalidValue[] invalidValues = GsysOperatelogValidate.validateProperty(model, gsysOperatelogField);
        if (invalidValues.length > 0) {
            List<String> errors = new ArrayList<String>();
            for (InvalidValue invalidValue : invalidValues) {
                errors.add(gsysOperatelogField.getFieldLabel() + ": " + invalidValue.getMessage());
            }
            throw new ValidateViolationException("" + (cell.getRowIndex() + 1) + ","
                    + (cell.getColumnIndex() + 1) + "!", errors);
        }
    }
    return model;
}