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.formulacompiler.spreadsheet.internal.excel.xls.loader.ExcelXLSLoader.java

License:Open Source License

private void loadRows(Sheet _xlsSheet, SheetBuilder _sheetBuilder) {
    int currentRowIndex = 0;
    for (final Row row : _xlsSheet) {
        final int rowIndex = row.getRowNum();
        while (rowIndex > currentRowIndex) {
            _sheetBuilder.beginRow();/*from w  w w. j  a  va  2  s.c  om*/
            _sheetBuilder.endRow();
            currentRowIndex++;
        }
        final RowBuilder rowBuilder = _sheetBuilder.beginRow();
        int currentColIndex = 0;
        for (Cell cell : row) {
            final int columnIndex = cell.getColumnIndex();
            while (columnIndex > currentColIndex) {
                rowBuilder.addEmptyCell();
                currentColIndex++;
            }
            loadCell(cell, rowBuilder);
            currentColIndex++;
        }
        rowBuilder.endRow();
        currentRowIndex++;
    }
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static Object _readCell(Cell cell) {
    if (cell == null) {
        return null;
    }/*www  .j  a  va  2  s  .  c  om*/
    int cellType = cell.getCellType();
    Object value = null;
    switch (cellType) {
    case Cell.CELL_TYPE_BLANK:
        value = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        boolean bool = cell.getBooleanCellValue();
        value = bool;
        break;
    case Cell.CELL_TYPE_ERROR:
        // cell.getErrorCellValue();
        ExcelReadException e = new ExcelReadException("Cell type error");
        e.setRowIndex(cell.getRowIndex());
        e.setColIndex(cell.getColumnIndex());
        e.setCode(ExcelReadException.CODE_OF_CELL_ERROR);
        throw e;
    case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        Object inputValue = null;//
        double doubleVal = cell.getNumericCellValue();
        if (DateUtil.isCellDateFormatted(cell)) {
            inputValue = DateUtil.getJavaDate(doubleVal);
        } else {
            long longVal = Math.round(cell.getNumericCellValue());
            if (Double.parseDouble(longVal + ".0") == doubleVal) {
                inputValue = longVal;
            } else {
                inputValue = doubleVal;
            }
        }
        value = inputValue;
        break;
    case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
    default:
        throw new RuntimeException("unsupport cell type " + cellType);
    }
    return value;
}

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

private static Object procValueConvert(ExcelReadContext<?> context, Row row, Cell cell,
        ExcelReadFieldMappingAttribute entry, String fieldName, Object value) {
    Object convertedValue = value;
    if (entry.getValueMapping() != null) {
        ExcelReadCellValueMapping valueMapping = entry.getValueMapping();
        String strValue = TypeUtils.castToString(value);
        convertedValue = valueMapping.get(strValue);
        if (convertedValue == null) {
            if (!valueMapping.containsKey(strValue)) {
                if (valueMapping.isSettedDefaultValue()) {
                    if (valueMapping.isSettedDefaultValueWithDefaultInput()) {
                        convertedValue = value;
                    } else {
                        convertedValue = valueMapping.getDefaultValue();
                    }//  w w  w  .  j av a  2  s  .c  o m
                } else if (valueMapping.getDefaultProcessor() != null) {
                    try {
                        convertedValue = valueMapping.getDefaultProcessor().process(context, cell,
                                new ExcelCellValue(value));
                    } catch (RuntimeException re) {
                        if (re instanceof ExcelReadException) {
                            ExcelReadException ere = (ExcelReadException) re;
                            ere.setRowIndex(row.getRowNum());
                            ere.setColIndex(cell.getColumnIndex());
                            throw ere;
                        } else {
                            ExcelReadException e = new ExcelReadException(re);
                            e.setRowIndex(row.getRowNum());
                            e.setColIndex(cell.getColumnIndex());
                            e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION);
                            throw e;
                        }
                    }
                    if (convertedValue != null && convertedValue instanceof ExcelCellValue) {
                        convertedValue = value;
                    }
                } else {
                    ExcelReadException e = new ExcelReadException("Cell value is value " + strValue);
                    e.setRowIndex(row.getRowNum());
                    e.setColIndex(cell.getColumnIndex());
                    e.setCode(ExcelReadException.CODE_OF_CELL_VALUE_NOT_MATCHED);
                    throw e;
                }
            }
        }
    } else if (entry.getCellProcessor() != null) {
        try {
            convertedValue = entry.getCellProcessor().process(context, cell, new ExcelCellValue(value));
        } catch (RuntimeException re) {
            if (re instanceof ExcelReadException) {
                ExcelReadException ere = (ExcelReadException) re;
                ere.setRowIndex(row.getRowNum());
                ere.setColIndex(cell.getColumnIndex());
                throw ere;
            } else {
                ExcelReadException e = new ExcelReadException(re);
                e.setRowIndex(row.getRowNum());
                e.setColIndex(cell.getColumnIndex());
                e.setCode(ExcelReadException.CODE_OF_PROCESS_EXCEPTION);
                throw e;
            }
        }
        if (convertedValue != null && convertedValue instanceof ExcelCellValue) {
            convertedValue = value;
        }
    }
    if (convertedValue == null && entry.isRequired()) {
        ExcelReadException e = new ExcelReadException("Cell value is null");
        e.setRowIndex(row.getRowNum());
        e.setColIndex(cell.getColumnIndex());
        e.setCode(ExcelReadException.CODE_OF_CELL_VALUE_REQUIRED);
        throw e;
    } else {
        return convertedValue;
    }
}

From source file:org.isisaddons.module.excel.dom.CellMarshaller.java

License:Apache License

private static void setCellComment(final Cell cell, final String commentText) {
    Sheet sheet = cell.getSheet();//from   w w  w  .j a  v a  2 s  . c  om
    Row row = cell.getRow();
    Workbook workbook = sheet.getWorkbook();
    CreationHelper creationHelper = workbook.getCreationHelper();
    ClientAnchor anchor = creationHelper.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(row.getRowNum());
    anchor.setRow2(row.getRowNum() + 3);

    Drawing drawing = sheet.createDrawingPatriarch();
    Comment comment1 = drawing.createCellComment(anchor);

    RichTextString commentRtf = creationHelper.createRichTextString(commentText);

    comment1.setString(commentRtf);
    Comment comment = comment1;
    cell.setCellComment(comment);
}

From source file:org.isisaddons.module.excel.dom.ExcelConverter.java

License:Apache License

<T> List<T> fromBytes(final Class<T> cls, final byte[] bs, final DomainObjectContainer container)
        throws IOException, InvalidFormatException {

    final List<T> importedItems = Lists.newArrayList();

    final ObjectSpecification objectSpec = specificationLoader.loadSpecification(cls);
    final ViewModelFacet viewModelFacet = objectSpec.getFacet(ViewModelFacet.class);

    try (ByteArrayInputStream bais = new ByteArrayInputStream(bs)) {
        final Workbook wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(bais);
        final CellMarshaller cellMarshaller = this.newCellMarshaller(wb);

        final Sheet sheet = wb.getSheetAt(0);

        boolean header = true;
        final Map<Integer, Property> propertyByColumn = Maps.newHashMap();

        for (final Row row : sheet) {
            if (header) {
                for (final Cell cell : row) {
                    if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        final int columnIndex = cell.getColumnIndex();
                        final String propertyName = cellMarshaller.getStringCellValue(cell);
                        final OneToOneAssociation property = getAssociation(objectSpec, propertyName);
                        if (property != null) {
                            final Class<?> propertyType = property.getSpecification().getCorrespondingClass();
                            propertyByColumn.put(columnIndex,
                                    new Property(propertyName, property, propertyType));
                        }/*  ww w .  j  ava  2 s . c o m*/
                    }
                }
                header = false;
            } else {
                // detail
                try {

                    // Let's require at least one column to be not null for detecting a blank row.
                    // Excel can have physical rows with cells empty that it seem do not existent for the user.
                    ObjectAdapter templateAdapter = null;
                    T imported = null;
                    for (final Cell cell : row) {
                        final int columnIndex = cell.getColumnIndex();
                        final Property property = propertyByColumn.get(columnIndex);
                        if (property != null) {
                            final OneToOneAssociation otoa = property.getOneToOneAssociation();
                            final Object value = cellMarshaller.getCellValue(cell, otoa);
                            if (value != null) {
                                if (imported == null) {
                                    // copy the row into a new object
                                    imported = container.newTransientInstance(cls);
                                    templateAdapter = this.adapterManager.adapterFor(imported);
                                }
                                final ObjectAdapter valueAdapter = this.adapterManager.adapterFor(value);
                                otoa.set(templateAdapter, valueAdapter);
                            }
                        } else {
                            // not expected; just ignore.
                        }
                    }

                    if (imported != null) {
                        if (viewModelFacet != null) {
                            // if there is a view model, then use the imported object as a template
                            // in order to create a regular view model.
                            final String memento = viewModelFacet.memento(imported);
                            final T viewModel = container.newViewModelInstance(cls, memento);
                            importedItems.add(viewModel);
                        } else {
                            // else, just return the imported items as simple transient instances.
                            importedItems.add(imported);
                        }
                    }
                } catch (final Exception e) {
                    bais.close();
                    throw new ExcelService.Exception(
                            String.format("Error processing Excel row nr. %d. Message: %s", row.getRowNum(),
                                    e.getMessage()),
                            e);
                }
            }
        }
    }

    return importedItems;
}

From source file:org.isisaddons.module.excel.dom.util.ExcelConverter.java

License:Apache License

<T> List<T> fromBytes(final Class<T> cls, final byte[] bs, final DomainObjectContainer container,
        final ExcelServiceImpl.SheetLookupPolicy sheetLookupPolicy) throws IOException, InvalidFormatException {

    final List<T> importedItems = Lists.newArrayList();

    final ObjectSpecification objectSpec = specificationLoader.loadSpecification(cls);
    final ViewModelFacet viewModelFacet = objectSpec.getFacet(ViewModelFacet.class);

    try (ByteArrayInputStream bais = new ByteArrayInputStream(bs)) {
        final Workbook wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(bais);
        final CellMarshaller cellMarshaller = this.newCellMarshaller(wb);

        final List<String> sheetNames = determineSheetNames(cls);
        final Sheet sheet = lookupSheet(wb, sheetNames, sheetLookupPolicy);

        boolean header = true;
        final Map<Integer, Property> propertyByColumn = Maps.newHashMap();

        for (final Row row : sheet) {
            if (header) {
                for (final Cell cell : row) {
                    if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        final int columnIndex = cell.getColumnIndex();
                        final String propertyName = cellMarshaller.getStringCellValue(cell);
                        final OneToOneAssociation property = getAssociation(objectSpec, propertyName);
                        if (property != null) {
                            final Class<?> propertyType = property.getSpecification().getCorrespondingClass();
                            propertyByColumn.put(columnIndex,
                                    new Property(propertyName, property, propertyType));
                        }//from  www  . j a  v a2s .  co m
                    }
                }
                header = false;
            } else {
                // detail
                try {

                    // Let's require at least one column to be not null for detecting a blank row.
                    // Excel can have physical rows with cells empty that it seem do not existent for the user.
                    ObjectAdapter templateAdapter = null;
                    T imported = null;
                    for (final Cell cell : row) {
                        final int columnIndex = cell.getColumnIndex();
                        final Property property = propertyByColumn.get(columnIndex);
                        if (property != null) {
                            final OneToOneAssociation otoa = property.getOneToOneAssociation();
                            final Object value = cellMarshaller.getCellValue(cell, otoa);
                            if (value != null) {
                                if (imported == null) {
                                    // copy the row into a new object
                                    imported = container.newTransientInstance(cls);
                                    templateAdapter = this.adapterManager.adapterFor(imported);
                                }
                                final ObjectAdapter valueAdapter = this.adapterManager.adapterFor(value);
                                otoa.set(templateAdapter, valueAdapter, InteractionInitiatedBy.USER);
                            }
                        } else {
                            // not expected; just ignore.
                        }
                    }

                    if (imported != null) {
                        if (viewModelFacet != null) {
                            // if there is a view model, then use the imported object as a template
                            // in order to create a regular view model.
                            final String memento = viewModelFacet.memento(imported);
                            final T viewModel = container.newViewModelInstance(cls, memento);
                            importedItems.add(viewModel);
                        } else {
                            // else, just return the imported items as simple transient instances.
                            importedItems.add(imported);
                        }
                    }
                } catch (final Exception e) {
                    bais.close();
                    throw new ExcelService.Exception(
                            String.format("Error processing Excel row nr. %d. Message: %s", row.getRowNum(),
                                    e.getMessage()),
                            e);
                }
            }
        }
    }

    return importedItems;
}

From source file:org.isource.util.CSVUtils.java

private static HSSFWorkbook evaluateFormulas(HSSFWorkbook wb) {

    FormulaEvaluator evaluator = null;//from w  ww.ja v  a2 s  . co  m
    evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
        Sheet sheet = wb.getSheetAt(sheetNum);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    evaluator.evaluateFormulaCell(c);
                    if (sheetNum == 0 && c.getColumnIndex() == r.getPhysicalNumberOfCells() - 1) {
                        switch (c.getCachedFormulaResultType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            break;
                        case Cell.CELL_TYPE_STRING:
                            break;
                        }
                    }
                }
            }
        }
    }
    return wb;
}

From source file:org.jeecgframework.poi.excel.imports.ExcelImportServer.java

License:Apache License

/**
 * ????//www  .  j a v a  2 s .  c o m
 * 
 * @param rows
 * @param params
 * @param excelCollection
 * @return
 */
private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
        List<ExcelCollectionParams> excelCollection) {
    Map<Integer, String> titlemap = new HashMap<Integer, String>();
    Iterator<Cell> cellTitle;
    String collectionName = null;
    ExcelCollectionParams collectionParams = null;
    Row row = null;
    for (int j = 0; j < params.getHeadRows(); j++) {
        row = rows.next();
        if (row == null) {
            continue;
        }
        cellTitle = row.cellIterator();
        while (cellTitle.hasNext()) {
            Cell cell = cellTitle.next();
            String value = getKeyValue(cell);
            int i = cell.getColumnIndex();
            // ????
            if (StringUtils.isNotEmpty(value)) {
                if (titlemap.containsKey(i)) {
                    collectionName = titlemap.get(i);
                    collectionParams = getCollectionParams(excelCollection, collectionName);
                    titlemap.put(i, collectionName + "_" + value);
                } else if (StringUtils.isNotEmpty(collectionName)
                        && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) {
                    titlemap.put(i, collectionName + "_" + value);
                } else {
                    collectionName = null;
                    collectionParams = null;
                }
                if (StringUtils.isEmpty(collectionName)) {
                    titlemap.put(i, value);
                }
            }
        }
    }
    return titlemap;
}

From source file:org.joeffice.spreadsheet.actions.DeleteCellsAction.java

License:Apache License

@Override
public void actionPerformed(ActionEvent ae) {
    SpreadsheetTopComponent currentTopComponent = OfficeTopComponent
            .getSelectedComponent(SpreadsheetTopComponent.class);
    if (currentTopComponent != null) {
        JTable currentTable = currentTopComponent.getSelectedTable();
        SheetTableModel tableModel = (SheetTableModel) currentTable.getModel();
        List<Cell> selectedCells = CellUtils.getSelectedCells(currentTable);
        for (Cell cell : selectedCells) {
            cell.setCellValue("");
            tableModel.fireTableCellUpdated(cell.getRowIndex(), cell.getColumnIndex());
        }//from www .  j  a va 2  s. c o m
    }
}

From source file:org.joeffice.spreadsheet.actions.FormatAction.java

License:Apache License

@Override
public void actionPerformed(ActionEvent ae) {
    SpreadsheetTopComponent currentTopComponent = OfficeTopComponent
            .getSelectedComponent(SpreadsheetTopComponent.class);
    if (currentTopComponent != null) {
        JTable currentTable = currentTopComponent.getSelectedTable();
        SheetTableModel tableModel = (SheetTableModel) currentTable.getModel();
        List<Cell> selectedCells = CellUtils.getSelectedCells(currentTable);
        if (selectedCells.isEmpty()) {
            return;
        }//from  www  .  j  a  v a  2 s.c o m
        if (choosePattern) {
            pattern = askFromList();
        } else if (definePattern) {
            pattern = askFromInputField();
        }
        if (pattern == null) {
            return;
        }
        Workbook workbook = selectedCells.get(0).getSheet().getWorkbook();
        DataFormat format = workbook.createDataFormat();
        short formatIndex = format.getFormat(pattern);
        for (Cell cell : selectedCells) {
            cell.getCellStyle().setDataFormat(formatIndex);
            tableModel.fireTableCellUpdated(cell.getRowIndex(), cell.getColumnIndex());
        }
    }
}