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

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

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

License:Open Source License

/**
 * Returns the content of the row into an array using start and end values defined in the row
 *
 * @param sheet//from  w ww . j ava2  s  .  c o m
 * @param rowIndex
 * @return The String array representation of the row
 */

public static String[] rowAsStringArray(final Sheet sheet, final int rowIndex) {
    final Row row = sheet.getRow(rowIndex);

    final int start = row.getFirstCellNum();
    final int end = row.getLastCellNum() - 1;

    return PoiUtil.rowAsStringArray(sheet, rowIndex, start, end);
}

---

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

License:Open Source License

public static String rowAsString(final Sheet sheet, final int rowIndex, final String delimiter) {
    final Row row = sheet.getRow(rowIndex);
    if (row == null) {
        return "";
    }//from  w ww  .  j av a  2  s . c  o  m
    final int startCell = row.getFirstCellNum();
    final int endCell = row.getLastCellNum() - 1;

    return PoiUtil.rowAsString(sheet, rowIndex, startCell, endCell, delimiter);
}

---

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 ww  w  .  j a v  a  2s . c  o  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.generationcp.middleware.util.PoiUtil.java

License:Open Source License

/**
 * Given a sheet, this method deletes a column from a sheet and moves
 * all the columns to the right of it to the left one cell.
 *
 * Note, this method will not update any formula references.
 *
 * @param sheet//from  w ww .  jav a2s  .  co  m
 * @param column
 */
public static void deleteColumn(final Sheet sheet, final int columnToDelete) {
    int maxColumn = 0;
    for (int r = 0; r < sheet.getLastRowNum() + 1; r++) {
        final Row row = sheet.getRow(r);

        // if no row exists here; then nothing to do; next!
        if (row == null)
            continue;

        // if the row doesn't have this many columns then we are good; next!
        final int lastColumn = row.getLastCellNum();
        if (lastColumn > maxColumn)
            maxColumn = lastColumn;

        if (lastColumn < columnToDelete)
            continue;

        for (int x = columnToDelete + 1; x < lastColumn + 1; x++) {
            final Cell oldCell = row.getCell(x - 1);
            if (oldCell != null)
                row.removeCell(oldCell);

            final Cell nextCell = row.getCell(x);
            if (nextCell != null) {
                final Cell newCell = row.createCell(x - 1, nextCell.getCellType());
                cloneCell(newCell, nextCell);
            }
        }
    }

}

---

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 /*from  w  w  w  . j  a va2s .co  m*/
 * @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 w  w. j av 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   www .j a  va  2  s.co  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;//from  w  w  w. ja  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.gitools.ui.app.fileimport.wizard.excel.ExcelReader.java

License:Open Source License

private List<ExcelHeader> rowToHeader(Row header, Row firstRow) {

    Cell cell;//w  w  w  .j a v a 2 s.  c  o m
    Cell firstCell;
    List<ExcelHeader> headers = new ArrayList<>();

    if (header != null) {

        for (int i = 0; i < header.getLastCellNum(); i++) {
            cell = header.getCell(i);
            firstCell = firstRow.getCell(i);
            if (cell != null) {
                int cellType = (firstCell == null ? Cell.CELL_TYPE_BLANK : firstCell.getCellType());
                headers.add(new ExcelHeader(cell.toString(), i, cellType));
            }
        }
    }
    return headers;

}

---

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 . j av  a2  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());
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• »