Example usage for org.apache.poi.ss.usermodel Row getFirstCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getFirstCellNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getFirstCellNum.

Prototype

short getFirstCellNum();

Source Link

Document

Get the number of the first cell contained in this row.

Usage

From source file:org.generationcp.middleware.util.PoiUtil.java

License:Open Source License

public static Integer getLastRowNum(final Sheet sheet) {
    Integer lastRowNum = sheet.getLastRowNum() + 1;

    if (lastRowNum == 1) {
        return 0;
    }//from  w w  w .  jav  a  2 s .  co  m

    Row row = null;
    int start = 0;
    int end = 0;

    do {
        lastRowNum--;

        row = sheet.getRow(lastRowNum);
        if (row == null) {
            continue;
        }
        start = row.getFirstCellNum();
        end = row.getLastCellNum() - 1;

    } while (PoiUtil.rowIsEmpty(sheet, lastRowNum, start, end) && lastRowNum > 0);

    return lastRowNum;
}

From source file:org.geotools.data.excel.ExcelDataStore.java

License:Open Source License

/**
 * Look up a column name and return it's index
 * @param columnName //ww w .  j  a v a  2 s  .  c  om
 * @return the index of the column
 */
private int lookupColumn(String columnName) {
    Row header = sheet.getRow(getHeaderRowIndex());

    for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {
        if (header.getCell(i).getStringCellValue().trim().equalsIgnoreCase(columnName)) {
            return i;
        }
    }
    return -1;
}

From source file:org.geotools.data.excel.ExcelFeatureSource.java

License:Open Source License

/**
 * create a FeatureSource with the specified Query
 * /*from   w ww.j a v a  2 s .  co m*/
 * @param entry
 * @param query
 *            - a query containing a filter that will be applied to the data
 */
public ExcelFeatureSource(ContentEntry entry, Query query) {
    super(entry, query);
    Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0);

    dataStore = (ExcelDataStore) entry.getDataStore();

    sheet = dataStore.getSheet();
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    int rows = sheet.getPhysicalNumberOfRows();
    int start = dataStore.getHeaderRowIndex() + 1;
    latCol = dataStore.getLatColumnIndex();
    lonCol = dataStore.getLonColumnIndex();
    features = new ArrayList<SimpleFeature>();
    filteredFeatures = new ArrayList<SimpleFeature>();
    evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator();
    if (schema == null) {
        schema = getSchema();
    }
    GeometryFactory geometryFactory = dataStore.getGeometryFactory();

    SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema);
    Row header = sheet.getRow(dataStore.getHeaderRowIndex());
    for (int i = start; i < rows; i++) {
        Row data = sheet.getRow(i);
        double x = 0.0;
        double y = 0.0;
        for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) {
            final Cell cell = data.getCell(col);
            CellValue value = evaluator.evaluate(cell);
            if (col == latCol) {

                if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    y = value.getNumberValue();
                }
            } else if (col == lonCol) {
                if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    x = value.getNumberValue();
                }
            } else {
                // cast and handle
                final String name = header.getCell(col).getStringCellValue().trim();
                switch (value.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    AttributeType type = schema.getType(name);
                    Class<?> clazz = type.getBinding();
                    if (clazz == Double.class) {
                        builder.set(name, value.getNumberValue());
                    } else if (clazz == java.sql.Date.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        final Calendar cal = Calendar.getInstance();
                        cal.clear();
                        cal.setTime(javaDate);
                        java.sql.Date date = new java.sql.Date(cal.getTimeInMillis());
                        builder.set(name, date);
                    } else if (clazz == java.util.Date.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        builder.set(name, javaDate);
                    } else if (clazz == Time.class) {
                        final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue());
                        final Calendar cal = Calendar.getInstance();
                        cal.clear();
                        cal.setTime(javaDate);
                        cal.set(0, 0, 0);
                        Time time = new Time(cal.getTimeInMillis());
                        builder.set(name, time);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    builder.set(name, value.getStringValue().trim());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    builder.set(name, value.getBooleanValue());
                    break;

                default:
                    System.out.println("We don't handle " + cell.getCellType() + " type cells "
                            + cell.getStringCellValue());
                }
            }
        }
        Point p = geometryFactory.createPoint(new Coordinate(x, y));
        builder.set("the_geom", p);

        SimpleFeature feature = builder.buildFeature(null);
        features.add(feature);

    }
    filterFeatures(query);
}

From source file:org.geotools.data.excel.ExcelFeatureSource.java

License:Open Source License

@Override
protected SimpleFeatureType buildFeatureType() throws IOException {
    SimpleFeatureTypeBuilder tb = new SimpleFeatureTypeBuilder();
    tb.setName(entry.getName());/*from   w w  w .ja  va  2  s.c o  m*/
    tb.setCRS(dataStore.getProjection());
    Row header = sheet.getRow(dataStore.getHeaderRowIndex());
    Row data = sheet.getRow(dataStore.getHeaderRowIndex() + 1);
    Row nextData = sheet.getRow(dataStore.getHeaderRowIndex() + 2);
    boolean latColGood = false;
    boolean lonColGood = false;
    for (int i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {
        // go through and guess data type from cell types!
        Cell cell = data.getCell(i);
        String name = header.getCell(i).getStringCellValue().trim();
        CellValue value = evaluator.evaluate(cell);
        int type = value.getCellType();

        Class<?> clazz = null;
        if (latCol == i) {
            // check it's a number
            if (type == Cell.CELL_TYPE_NUMERIC) {
                latColGood = true;
            }
        } else if (lonCol == i) {
            // check it's a number
            if (type == Cell.CELL_TYPE_NUMERIC) {
                lonColGood = true;
            }
        } else {
            switch (type) {
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    if (value.getNumberValue() < 1.0) {
                        clazz = Time.class;
                    } else if (Math.floor(cell.getNumericCellValue()) == Math
                            .ceil(cell.getNumericCellValue())) {
                        // midnight or just a date
                        // check the next row
                        Cell cell2 = nextData.getCell(i);
                        if (Math.floor(cell2.getNumericCellValue()) == Math.ceil(cell2.getNumericCellValue())) {
                            //probably a simple date
                            clazz = java.sql.Date.class;
                        } else {
                            // actual date/time element
                            clazz = java.util.Date.class;
                        }
                    } else {
                        // actual date/time element
                        clazz = java.util.Date.class;
                    }
                } else {
                    clazz = Double.class;
                }
                break;
            case Cell.CELL_TYPE_STRING:
                clazz = String.class;
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                clazz = Boolean.class;
                break;
            }
            System.out.println(name + ":" + clazz);
            tb.add(name, clazz);
        }

    }
    if (latColGood && lonColGood) {
        tb.add("the_geom", Point.class);
    } else {
        throw new IOException("failed to find a Lat and Lon column");
    }
    // build the type (it is immutable and cannot be modified)
    final SimpleFeatureType SCHEMA = tb.buildFeatureType();
    return SCHEMA;
}

From source file:org.gephi.io.importer.plugin.file.spreadsheet.sheets.excel.ExcelSheetParser.java

License:Open Source License

private void calculateRowStartAndEndIndexes() {
    int rowsToScan;

    if (withFirstRecordAsHeader) {
        rowsToScan = 1;/*w w w.j  a  v  a  2  s  . c  om*/
    } else {
        rowsToScan = 25;
    }

    for (int i = sheet.getFirstRowNum(), j = 0; i < sheet.getLastRowNum() && j < rowsToScan; i++, j++) {
        Row row = sheet.getRow(i);
        if (row != null) {
            rowsFirstIndex = Math.min(rowsFirstIndex, row.getFirstCellNum());
            rowsLastIndex = Math.max(rowsLastIndex, row.getLastCellNum() - 1);
        }
    }

    if (rowsFirstIndex == Integer.MAX_VALUE) {
        rowsFirstIndex = 0;
        rowsLastIndex = 0;
    }
}

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

License:Apache License

private static void convertFieldMapping(Sheet sheet, ExcelReadSheetProcessor<?> sheetProcessor,
        Map<String, Map<String, ExcelReadFieldMappingAttribute>> src,
        Map<Integer, Map<String, ExcelReadFieldMappingAttribute>> tar) {
    if (src == null) {
        return;//  w  w w.java  2 s  .c  o m
    }
    Integer headRowIndex = sheetProcessor.getHeadRowIndex();
    Map<String, Integer> colCache = new HashMap<String, Integer>();
    if (headRowIndex != null) {
        Row row = sheet.getRow(headRowIndex);
        if (row != null) {
            int start = row.getFirstCellNum();
            int end = row.getLastCellNum();
            for (int i = start; i < end; i++) {
                Cell cell = row.getCell(i);
                Object cellValue = _readCell(cell);
                if (cellValue != null) {
                    String strVal = cellValue.toString().trim();
                    colCache.put(strVal, i);
                }
            }
        }
    }

    for (Map.Entry<String, Map<String, ExcelReadFieldMappingAttribute>> entry : src.entrySet()) {
        String colIndexOrColName = entry.getKey();
        Integer colIndex = null;
        if (headRowIndex == null) {
            colIndex = convertColCharIndexToIntIndex(colIndexOrColName);
        } else {
            colIndex = colCache.get(colIndexOrColName);
            if (colIndex == null) {
                throw new IllegalStateException("For sheet:" + sheet.getSheetName() + " headRowIndex:"
                        + headRowIndex + " can't find colum named:" + colIndexOrColName);
            }
        }
        tar.put(colIndex, entry.getValue());
    }
}

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

License:Apache License

private static InnerRow getTemplateRow(Map<Integer, InnerRow> cache, Sheet sheet,
        ExcelWriteSheetProcessor<?> sheetProcessor, int rowIndex) {
    InnerRow cachedRow = cache.get(rowIndex);
    if (cachedRow != null || cache.containsKey(rowIndex)) {
        return cachedRow;
    }//from  w  w  w. j av  a 2s . c  o m
    InnerRow templateRow = null;
    if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) {
        if (rowIndex <= sheetProcessor.getTemplateEndRowIndex()) {
            return null;
        }
        int tempRowIndex = (rowIndex - sheetProcessor.getTemplateEndRowIndex() - 1)
                % (sheetProcessor.getTemplateEndRowIndex() - sheetProcessor.getTemplateStartRowIndex() + 1)
                + sheetProcessor.getTemplateStartRowIndex();
        Row tempRow = sheet.getRow(tempRowIndex);
        if (tempRow != null) {
            templateRow = new InnerRow();
            templateRow.setHeight(tempRow.getHeight());
            templateRow.setHeightInPoints(tempRow.getHeightInPoints());
            templateRow.setRowStyle(tempRow.getRowStyle());
            templateRow.setZeroHeight(tempRow.getZeroHeight());
            for (int i = tempRow.getFirstCellNum(); i <= tempRow.getLastCellNum(); i++) {
                Cell cell = tempRow.getCell(i);
                if (cell != null) {
                    InnerCell innerCell = new InnerCell();
                    innerCell.setCellStyle(cell.getCellStyle());
                    innerCell.setCellType(cell.getCellType());
                    templateRow.setCell(i, innerCell);
                }
            }
        }
    }
    cache.put(rowIndex, templateRow);
    return templateRow;
}

From source file:org.jberet.support.io.ExcelItemReaderWriterBase.java

License:Open Source License

/**
 * Saves string values to a string array for all non-blank cells in the row passed in. Useful when trying to get
 * header values.//  ww w .jav  a2s. c  o m
 *
 * @param row the source row to get values from
 * @return a String array containing values from all non-blank cells in the row
 */
protected static String[] getCellStringValues(final Row row) {
    final short firstCellNum = row.getFirstCellNum();
    final short lastCellNum = row.getLastCellNum();
    final String[] values = new String[lastCellNum - firstCellNum];
    for (int i = 0; i < values.length; ++i) {
        values[i] = row.getCell(i).getStringCellValue();
    }
    return values;
}

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

License:Apache License

/***
 * ?List?//from   www  .j  a v a  2s .  co  m
 * 
 * @param exclusions
 * @param object
 * @param param
 * @param row
 * @param titlemap
 * @param targetId
 * @param pictures
 * @param params
 */
private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap,
        String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception {
    Collection collection = (Collection) PoiPublicUtil.getMethod(param.getName(), object.getClass())
            .invoke(object, new Object[] {});
    Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
    String picId;
    boolean isUsed = false;// ??
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        String titleString = (String) titlemap.get(i);
        if (param.getExcelParams().containsKey(titleString)) {
            if (param.getExcelParams().get(titleString).getType() == 2) {
                picId = row.getRowNum() + "_" + i;
                saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
            } else {
                saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
            }
            isUsed = true;
        }
    }
    if (isUsed) {
        collection.add(entity);
    }
}

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

License:Apache License

private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params,
        Map<String, PictureData> pictures) throws Exception {
    List collection = new ArrayList();
    Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
    List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
    String targetId = null;/*from w w  w  .  j  av a 2 s .  c o  m*/
    if (!Map.class.equals(pojoClass)) {
        Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
        ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
        if (etarget != null) {
            targetId = etarget.value();
        }
        getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
    }
    Iterator<Row> rows = sheet.rowIterator();
    for (int j = 0; j < params.getTitleRows(); j++) {
        rows.next();
    }
    Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
    Row row = null;
    Object object = null;
    String picId;
    // 
    int count = 4;
    while (rows.hasNext()
            && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
        count++;
        row = rows.next();
        // ???,?,?
        if ((row.getCell(params.getKeyIndex()) == null
                || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) {
            for (ExcelCollectionParams param : excelCollection) {
                try {
                    addListContinue(object, param, row, titlemap, targetId, pictures, params);
                } catch (Exception e) {
                    // TODO: handle exception
                    Exception f = new Exception("" + count + "" + e.getMessage());
                    throw f;
                }
            }
        } else {
            object = PoiPublicUtil.createObject(pojoClass, targetId);
            try {
                for (int i = row.getFirstCellNum(), le = row.getLastCellNum(); i < le; i++) {
                    Cell cell = row.getCell(i);
                    String titleString = (String) titlemap.get(i);
                    if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
                        if (excelParams.get(titleString) != null
                                && excelParams.get(titleString).getType() == 2) {
                            picId = row.getRowNum() + "_" + i;
                            saveImage(object, picId, excelParams, titleString, pictures, params);
                        } else {
                            try {
                                saveFieldValue(params, object, cell, excelParams, titleString, row);
                            } catch (Exception e) {
                                // TODO: handle exception
                                Exception f = new Exception("" + count + "" + e.getMessage());
                                throw f;
                            }
                        }
                    }
                }

                for (ExcelCollectionParams param : excelCollection) {
                    try {
                        addListContinue(object, param, row, titlemap, targetId, pictures, params);
                    } catch (Exception e) {
                        // TODO: handle exception
                        Exception f = new Exception("" + count + "" + e.getMessage());
                        throw f;
                    }
                }
                collection.add(object);
            } catch (ExcelImportException e) {
                if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                    throw new ExcelImportException(e.getType(), e);
                }
            }
        }
    }
    return collection;
}