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.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected void readColumnNames() {
    // read the schema from the first row or the named row.
    _headerRange = null;/*  w ww.j a v a  2  s .  c  o m*/
    Row headerRow = null;
    if (StringUtilities.isNotNullOrEmpty(_columnNameAddress)) {
        _headerRange = new ExcelRange(_columnNameAddress);
        CellReference cr = _headerRange.getStartCell();
        headerRow = _sheet.getRow(cr.getRow());
    } else {
        Iterator<Row> iterator = _sheet.iterator();
        headerRow = iterator.next();
    }

    _columnCount = 0;
    int endColumnIndex = (_headerRange == null) ? -1 : _headerRange.getEndColumn();
    String value = "";
    _columnLabels = new ArrayList<String>();
    _columnAddress = new ArrayList<String>();
    List<String> usedLabels = new ArrayList<String>();
    for (Cell cell : headerRow) {
        String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
        if ((endColumnIndex != -1) && (cell.getColumnIndex() > endColumnIndex)) {
            break;
        }

        CellType ct = cell.getCellTypeEnum();
        if (ct == CellType.FORMULA)
            ct = cell.getCachedFormulaResultTypeEnum();
        switch (ct) {
        case STRING:
            value = cell.getStringCellValue();
            if (usedLabels.contains(value.toLowerCase()))
                value = String.format("%s_%s%d", value, columnLetter, cell.getRowIndex() + 1);
            else
                usedLabels.add(value.toLowerCase());
            _columnLabels.add(value);
            _columnAddress.add(columnLetter);
            break;
        case BOOLEAN:
        case NUMERIC:
        case FORMULA:
        case BLANK:
            _columnLabels.add(String.format("Column_%s", columnLetter));
            _columnAddress.add(columnLetter);
            break;
        default:
            break;
        }
    }

    _columnCount = _columnLabels.size();
    _dataSchema = new String[_columnCount][2];
    _dataTypes = new DataType[_columnCount];
    for (int i = 0; i < _columnCount; i++) {
        _dataSchema[i][0] = _columnLabels.get(i);
    }
}

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected void readRowSchema(Row dataRow, int endColumnIndex) {
    String cellAddress = "";
    String schemaColumnType = null;
    String currentCellDataType = null;
    try {/*from   w w  w.  j  a v  a2s . c o m*/
        for (Cell cell : dataRow) {
            cellAddress = cell.getAddress().toString();
            String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
            int columnIndex = _columnAddress.indexOf(columnLetter);
            if (columnIndex == -1) {
                continue;
            }

            currentCellDataType = "String";
            CellType ct = cell.getCellTypeEnum();
            if (ct == CellType.FORMULA)
                ct = cell.getCachedFormulaResultTypeEnum();
            switch (ct) {
            case STRING:
                currentCellDataType = "String";
                break;
            case BOOLEAN:
                currentCellDataType = "Boolean";
                break;
            case NUMERIC:
                currentCellDataType = "Double";
                break;
            case FORMULA:
                currentCellDataType = "Object";
                break;
            case BLANK:
                currentCellDataType = "String";
                break;
            default:
                break;
            }

            // Object, String, Numeric, Boolean
            schemaColumnType = _dataSchema[columnIndex][1];
            if (schemaColumnType == null) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Object".equals(schemaColumnType)) {
                // no change
            } else if ("String".equals(schemaColumnType) && "Object".equals(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Double".equals(schemaColumnType) && "Object|String".contains(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            } else if ("Boolean".equals(schemaColumnType)
                    && "Object|String|Double".contains(currentCellDataType)) {
                _dataSchema[columnIndex][1] = currentCellDataType;
            }
            _dataTypes[columnIndex] = DataUtilities.dataTypeToEnum(_dataSchema[columnIndex][1]);
            columnIndex++;
        }
    } catch (Exception ex) {
        throw new PieException(String.format("Error while reading Excel cell %s for its data type (%s). %s",
                cellAddress, currentCellDataType, ex.getMessage()), ex);
    }
}

From source file:com.fanniemae.ezpie.data.connectors.ExcelConnector.java

License:Open Source License

protected Object[] readExcelData(Row excelDataRow) {
    Object[] data = new Object[_columnCount];
    String cellAddress = "";
    int dataIndex = 0;
    try {//from   w ww . ja  v  a2s.  c  o m
        for (Cell cell : excelDataRow) {
            cellAddress = cell.getAddress().toString();
            String columnLetter = CellReference.convertNumToColString(cell.getColumnIndex());
            int columnIndex = _columnAddress.indexOf(columnLetter);
            if (columnIndex == -1) {
                continue;
            }

            CellType ct = cell.getCellTypeEnum();
            if (ct == CellType.FORMULA)
                ct = cell.getCachedFormulaResultTypeEnum();
            switch (ct) {
            case STRING:
                data[dataIndex] = cell.getStringCellValue();
                break;
            case BOOLEAN:
                data[dataIndex] = _allTypesStrings ? Boolean.toString(cell.getBooleanCellValue())
                        : cell.getBooleanCellValue();
                break;
            case NUMERIC:
                data[dataIndex] = _allTypesStrings ? Double.toString(cell.getNumericCellValue())
                        : cell.getNumericCellValue();
                break;
            default:
                data[dataIndex] = _allTypesStrings ? "" : null;
                break;
            }
            dataIndex++;
        }
        if (_addFilename)
            data[data.length - 1] = _filenameOnly;
    } catch (Exception ex) {
        throw new PieException(
                String.format("Error while reading Excel data from cell %s. %s", cellAddress, ex.getMessage()),
                ex);
    }

    return data;
}

From source file:com.github.camaral.sheeco.exceptions.SpreadsheetViolation.java

License:Apache License

public SpreadsheetViolation(final String msgKey, final Cell cell) {
    this.msgKey = msgKey;
    this.colNumber = cell.getColumnIndex();
    this.rowRange = new RowRange(cell.getRowIndex());
    this.originalValue = new OriginalCellValue(getCellType(cell), getCellValue(cell));
}

From source file:com.github.crab2died.ExcelUtils.java

License:Open Source License

private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine,
        int sheetIndex) throws Excel4JException {

    Sheet sheet = workbook.getSheetAt(sheetIndex);
    Row row = sheet.getRow(offsetLine);/*from w  w  w .j  av a2  s  . c  om*/
    List<T> list = new ArrayList<>();
    Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
    if (maps == null || maps.size() <= 0)
        throw new Excel4jReadException(
                "The Excel format to read is not correct, and check to see if the appropriate rows are set");
    long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
            : sheet.getLastRowNum();

    for (int i = offsetLine + 1; i <= maxLine; i++) {
        row = sheet.getRow(i);
        if (null == row)
            continue;
        T obj;
        try {
            obj = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new Excel4JException(e);
        }
        for (Cell cell : row) {
            int ci = cell.getColumnIndex();
            ExcelHeader header = maps.get(ci);
            if (null == header)
                continue;
            String val = Utils.getCellValue(cell);
            Object value;
            String filed = header.getFiled();
            // ??
            if (null != header.getReadConverter()
                    && header.getReadConverter().getClass() != DefaultConvertible.class) {
                value = header.getReadConverter().execRead(val);
            } else {
                // ?
                value = Utils.str2TargetClass(val, header.getFiledClazz());
            }
            Utils.copyProperty(obj, filed, value);
        }
        list.add(obj);
    }
    return list;
}

From source file:com.github.crab2died.handler.SheetTemplateHandler.java

License:Open Source License

/**
 * ???// w  w w  .j  av  a2s.c om
 */
private static void initModuleConfig(SheetTemplate template) {

    for (Row row : template.sheet) {
        for (Cell c : row) {
            if (c.getCellTypeEnum() != CellType.STRING)
                continue;
            String str = c.getStringCellValue().trim().toLowerCase();
            // ??
            if (HandlerConstant.SERIAL_NUMBER.equals(str)) {
                template.serialNumberColumnIndex = c.getColumnIndex();
            }
            // ?
            if (HandlerConstant.DATA_INIT_INDEX.equals(str)) {
                template.initColumnIndex = c.getColumnIndex();
                template.initRowIndex = row.getRowNum();
                template.rowHeight = row.getHeightInPoints();
            }
            // ???
            initStyles(template, c, str);
        }
    }
}

From source file:com.github.crab2died.utils.Utils.java

License:Open Source License

/**
 * ?excel//from   w ww  .ja v a2s .co  m
 *
 * @param titleRow excel
 * @param clz      
 * @return ExcelHeader?
 * @throws Excel4JException 
 */
public static Map<Integer, ExcelHeader> getHeaderMap(Row titleRow, Class<?> clz) throws Excel4JException {

    List<ExcelHeader> headers = getHeaderList(clz);
    Map<Integer, ExcelHeader> maps = new HashMap<>();
    for (Cell c : titleRow) {
        String title = c.getStringCellValue();
        for (ExcelHeader eh : headers) {
            if (eh.getTitle().equals(title.trim())) {
                maps.put(c.getColumnIndex(), eh);
                break;
            }
        }
    }
    return maps;
}

From source file:com.github.drbookings.excel.FileFormatBookingXLS.java

License:Open Source License

public static int getColumnIndexForIdentifier(final Row row, final String identifierBookingNumber)
        throws ExceptionFileFormat {
    final Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        final Cell nextCell = cellIterator.next();
        final String cellContent = nextCell.getStringCellValue();
        if (cellContent.equals(identifierBookingNumber)) {
            return nextCell.getColumnIndex();
        }//from w w w  . j a v  a 2 s  . com
    }
    throw new ExceptionFileFormat("Failed to parse " + identifierBookingNumber);
}

From source file:com.github.svrtm.xlreport.ACell.java

License:Apache License

List<Cell> findMergedCells(final Cell poiCell) {
    if (builder.regionsList == null)
        return null;

    List<Cell> mergedCells;
    int rowIndex = poiCell.getRowIndex();
    for (final CellRangeAddress region : builder.regionsList)
        if (region.isInRange(rowIndex, poiCell.getColumnIndex())) {
            final int firstCol = region.getFirstColumn();
            final int lastCol = region.getLastColumn();
            final int firstRow = region.getFirstRow();
            final int lastRow = region.getLastRow();
            mergedCells = new ArrayList<Cell>(region.getNumberOfCells());
            for (rowIndex = firstRow; rowIndex <= lastRow; rowIndex++) {
                final Row<HB, ?, ?, ?> row;
                row = builder.rowOrCreateIfAbsent(rowIndex);
                for (int colIdx = firstCol; colIdx <= lastCol; colIdx++) {
                    final Cell mergedCell;
                    mergedCell = row.cellOrCreateIfAbsent(colIdx).poiCell;
                    mergedCells.add(mergedCell);
                }/*from  ww  w. j av a2  s .co  m*/
            }

            return mergedCells;
        }

    return null;
}

From source file:com.github.svrtm.xlreport.ACell.java

License:Apache License

void setAutoSizeColumn(final Cell poiCell) {
    boolean enableAutoSize = false;
    final int cellType = poiCell.getCellType();
    switch (cellType) {
    case Cell.CELL_TYPE_STRING: {
        final String value = poiCell.getStringCellValue();
        if (value != null && value.length() >= AUTOSIZE_MIN_LENGTH) {
            final String tr = value.trim();
            for (int i = 0; i < tr.length(); i++)
                if (Character.isWhitespace(tr.charAt(i)) == false) {
                    enableAutoSize = true;
                    break;
                }/*from   www.j  a  va 2s.c  o  m*/
        }
        break;
    }

    case Cell.CELL_TYPE_BLANK:
        break;

    default:
        enableAutoSize = true;
    }
    if (enableAutoSize)
        builder.sheet.autoSizeColumn(poiCell.getColumnIndex());
}