Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

Introduction

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

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private int getNextBlockRowIndex(Sheet transactionSheet, int rowIndex, String nextBlockIdentifier) {
    int numRows = transactionSheet.getLastRowNum() + 1;
    for (; rowIndex < numRows; rowIndex++) {
        Row row = transactionSheet.getRow(rowIndex);
        if (row == null)
            continue;
        Cell cell = row.getCell(0);/*from  w  w  w. j  a  va  2  s  .com*/
        if (cell == null)
            continue;
        cell.setCellType(Cell.CELL_TYPE_STRING);
        String s = cell.getStringCellValue().trim();
        if (s.equals(nextBlockIdentifier))
            return rowIndex;
    }
    return -100;
}

---

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java

License:Open Source License

private Station getStation(List<Exception> exceptions, Sheet businessSheet, String lookup, Row srcrow) {
    Station result = null;/*from   w  w w . j  a v  a 2 s  . c o m*/
    int numRows = businessSheet.getLastRowNum() + 1;
    for (int i = 0; i < numRows; i++) {
        Row row = businessSheet.getRow(i);
        if (row != null) {
            Cell cell = row.getCell(0);
            if (cell.getStringCellValue().equals(lookup)) {
                result = getStation(businessSheet.getRow(0), row);
                break;
            }
        }
    }
    if (result == null)
        exceptions.add(new Exception(
                "Station '" + lookup + "' is not correctly defined in Row " + (srcrow.getRowNum() + 1)));
    return result;
}

---

From source file:de.bund.bfr.knime.pmm.common.XLSReader.java

License:Open Source License

public Set<String> getValuesInColumn(File file, String sheet, String column) throws Exception {
    Set<String> valueSet = new LinkedHashSet<>();
    Workbook wb = getWorkbook(file);/*w ww  .ja va2 s  .c o m*/
    Sheet s = wb.getSheet(sheet);

    evaluator = wb.getCreationHelper().createFormulaEvaluator();

    if (s == null) {
        throw new Exception("Sheet not found");
    }

    Map<String, Integer> columns = getColumns(s);
    int columnId = columns.get(column);

    for (int i = 1; i <= s.getLastRowNum(); i++) {
        if (s.getRow(i) != null) {
            Cell cell = s.getRow(i).getCell(columnId);

            if (hasData(cell)) {
                valueSet.add(getData(cell));
            }
        }
    }

    return valueSet;
}

---

From source file:de.bund.bfr.knime.pmm.common.XLSReader.java

License:Open Source License

public List<Integer> getMissingData(File file, String sheet, String column) throws Exception {
    List<Integer> missing = new ArrayList<>();
    Workbook wb = getWorkbook(file);/*from  www  .  ja v a 2  s  .  co  m*/
    Sheet s = wb.getSheet(sheet);

    evaluator = wb.getCreationHelper().createFormulaEvaluator();

    Map<String, Integer> columns = getColumns(s);
    int columnId = columns.get(column);

    for (int i = 1; i <= s.getLastRowNum(); i++) {
        if (s.getRow(i) != null && !hasData(s.getRow(i).getCell(columnId))) {
            for (int c : columns.values()) {
                if (hasData(s.getRow(i).getCell(c))) {
                    missing.add(i + 1);
                    break;
                }
            }
        }
    }

    return missing;
}

---

From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java

License:Open Source License

/**
 * @param sbvrXls/*w  w  w  .j a va  2  s . com*/
 * @return mapping of schema package name to SBVR rules that apply to
 *         classes in this schema *
 *         <ul>
 *         <li>key: class name</li>
 *         <li>value: mapping of schema package name to SBVR rule info
 *         <ul>
 *         <li>key: schema package name (
 *         {@value #UNSPECIFIED_SCHEMA_PACKAGE_NAME} if no schema package
 *         name has been provided)</li>
 *         <li>value: list of SBVR rules that apply to classes in that
 *         schema (the list is sorted according to lexical order on a) the
 *         class name and b) the rule text)</li>
 *         </ul>
 *         </ul>
 */
private TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> parseSBVRRuleInfos(Workbook sbvrXls) {

    TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> rules = new TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>>();

    if (sbvrXls == null)
        return null;

    Sheet rulesSheet = null;

    for (int i = 0; i < sbvrXls.getNumberOfSheets(); i++) {

        String sheetName = sbvrXls.getSheetName(i);

        if (sheetName.equalsIgnoreCase("Constraints")) {
            rulesSheet = sbvrXls.getSheetAt(i);
            break;
        }
    }

    if (rulesSheet == null) {

        result.addError(this, 3);
        return null;
    }

    // read header row to determine which columns contain relevant
    // information
    Map<String, Integer> fieldIndexes = new HashMap<String, Integer>();

    Row header = rulesSheet.getRow(rulesSheet.getFirstRowNum());

    if (header == null) {
        result.addError(this, 4);
        return null;
    }

    boolean classNameFound = false;
    boolean commentsFound = false;
    boolean ruleNameFound = false;
    boolean ruleTextFound = false;
    boolean schemaPackageFound = false;

    for (short i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) {

        Cell c = header.getCell(i, Row.RETURN_BLANK_AS_NULL);

        if (c == null) {
            // this is allowed
        } else {

            String value = c.getStringCellValue();

            if (value.equalsIgnoreCase(SbvrRuleInfo.CLASS_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.CLASS_COLUMN_NAME, (int) i);
                classNameFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.COMMENT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.COMMENT_COLUMN_NAME, (int) i);
                commentsFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME, (int) i);
                schemaPackageFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME, (int) i);
                ruleTextFound = true;

            } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_NAME_COLUMN_NAME)) {

                fieldIndexes.put(SbvrRuleInfo.RULE_NAME_COLUMN_NAME, (int) i);
                ruleNameFound = true;
            }
        }
    }

    // if (fieldIndexes.size() != 5) {
    if (!ruleNameFound && !ruleTextFound) {
        // log message that required fields were not found
        result.addError(this, 5);
        return null;
    }

    /*
     * Read rule content
     */
    for (int i = rulesSheet.getFirstRowNum() + 1; i <= rulesSheet.getLastRowNum(); i++) {

        Row r = rulesSheet.getRow(i);
        int rowNumber = i + 1;

        if (r == null) {
            // ignore empty rows
            continue;
        }

        SbvrRuleInfo sri = new SbvrRuleInfo();

        // get rule name (required)
        Cell c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_NAME_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 6, "" + rowNumber);
            continue;
        } else {
            String cellValue = c.getStringCellValue();
            if (cellValue != null) {
                if (cellValue.contains(":")) {
                    sri.setName(cellValue.substring(cellValue.lastIndexOf(":") + 1));
                } else {
                    sri.setName(cellValue);
                }
            }
        }

        // get rule text (required)
        c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
        if (c == null) {
            // log message
            result.addWarning(this, 7, "" + rowNumber);
            continue;
        } else {
            sri.setText(c.getStringCellValue());
        }

        // get comment (optional)
        if (commentsFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.COMMENT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c != null) {
                sri.setComment(c.getStringCellValue());
            }
        }

        // get schema package (optional)
        if (schemaPackageFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                sri.setSchemaPackageName(UNSPECIFIED_SCHEMA_PACKAGE_NAME);
            } else {
                sri.setSchemaPackageName(c.getStringCellValue());
            }
        }

        /*
         * get class name (optional when loading from excel because later we
         * can still try parsing it from the rule text)
         */
        if (classNameFound) {
            c = r.getCell(fieldIndexes.get(SbvrRuleInfo.CLASS_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL);
            if (c == null) {
                /*
                 * then after this we'll try to parse the class name from
                 * the rule text
                 */
            } else {
                sri.setClassName(c.getStringCellValue());
            }
        }

        if (sri.getClassName() == null) {

            /*
             * try parsing the main class name from the rule text
             */
            result.addInfo(this, 10, sri.getName());

            String mainClassName = parseClassNameFromRuleText(sri.getText());

            if (mainClassName == null) {
                result.addWarning(this, 8, sri.getName());
                continue;
            } else {
                sri.setClassName(mainClassName);
            }
        }

        List<SbvrRuleInfo> rulesList;
        TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName;

        if (rules.containsKey(sri.getClassName())) {

            rulesBySchemaPackageName = rules.get(sri.getClassName());

            if (rulesBySchemaPackageName.containsKey(sri.getSchemaPackageName())) {
                rulesList = rulesBySchemaPackageName.get(sri.getSchemaPackageName());
            } else {
                rulesList = new ArrayList<SbvrRuleInfo>();
                rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
            }

        } else {

            rulesBySchemaPackageName = new TreeMap<String, List<SbvrRuleInfo>>();
            rules.put(sri.getClassName(), rulesBySchemaPackageName);

            rulesList = new ArrayList<SbvrRuleInfo>();
            rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList);
        }

        rulesList.add(sri);
    }

    // now sort all lists contained in the map
    for (TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName : rules.values()) {
        for (List<SbvrRuleInfo> rulesList : rulesBySchemaPackageName.values()) {

            Collections.sort(rulesList, new Comparator<SbvrRuleInfo>() {

                @Override
                public int compare(SbvrRuleInfo o1, SbvrRuleInfo o2) {

                    int classNameComparison = o1.getClassName().compareTo(o2.getClassName());

                    if (classNameComparison != 0) {
                        return classNameComparison;
                    } else {
                        return o1.getText().compareTo(o2.getText());
                    }
                }
            });
        }
    }

    return rules;
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ExcelImportUtilities.java

License:Open Source License

/**
 * Returns the index of last row on the specified {@code sheet}.
 * /*from ww  w . j  a v  a 2  s .c om*/
 * @param sheet the sheet
 * @return the index of last row on the specified {@code sheet}
 */
public static int getLastRow(Sheet sheet) {
    return sheet.getLastRowNum();
}

---

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.ImportWorkbook.java

License:Open Source License

protected boolean readInConfigSheet() {
    // Nullify variables that must be read in from this config
    this.setLocale(null);

    Sheet sheetConfig = getWb().getSheet(DEFAULT_SHEET_KEY);
    if (sheetConfig == null) {
        getProcessingLog().error(DEFAULT_SHEET_KEY + " not found.");
        return false;
    }/* w  ww  .  j a  v  a  2 s.  c o  m*/

    // Read in various settings (Config page must have all its variable names in English)
    for (int curRow = 0; curRow <= sheetConfig.getLastRowNum(); curRow++) {

        Row row = sheetConfig.getRow(curRow);
        if (row != null) {
            String key = ExcelImportUtilities.contentAsString(row.getCell(0), getProcessingLog());

            if ("Locale".equals(key)) {
                String content = ExcelImportUtilities.contentAsString(row.getCell(1), getProcessingLog());
                this.setLocale(new Locale(content));
            }
        }
    }

    return this.getLocale() != null;
}

---

From source file:de.jlo.talendcomp.excel.SpreadsheetFile.java

License:Apache License

public boolean isEmpty() {
    if (workbook == null) {
        throw new IllegalStateException("workbook is not initialized");
    }/* w w  w  . j a v a2s .  co  m*/
    int countSheets = workbook.getNumberOfSheets();
    if (countSheets == 0) {
        return true;
    }
    for (int i = 0; i < countSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        if (sheet.getLastRowNum() == 0) {
            return true;
        }
    }
    return false;
}

---

From source file:de.jlo.talendcomp.excel.SpreadsheetList.java

License:Apache License

public int getCountSheetRows(int sheetIndex) throws Exception {
    if (workbook == null) {
        throw new Exception("Workbook is not initialized!");
    } else {/*from  w  ww  . ja  va 2 s  .  c o  m*/
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        if (sheet != null) {
            return sheet.getLastRowNum();
        } else {
            return 0;
        }
    }
}

---

From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java

License:Apache License

@Test
public void testExportThoughts() throws Exception {
    File tempFile = File.createTempFile("thoughtExport", ".xlsx");
    EntityExportSource<Thought> source = new EntityExportSource<>(getAllIds(), Thought.class);
    XlsxExporter exporter = new XlsxExporter();
    exporter.export(tempFile, source);/*from w ww .  j  a v  a  2 s  .com*/

    Workbook wb = WorkbookFactory.create(tempFile);
    Sheet sheet = wb.getSheetAt(0);
    assertEquals(Thought.class.getName(), sheet.getSheetName());
    int lastRowNum = sheet.getLastRowNum();
    assertEquals(COUNT, lastRowNum);
    Row firstRow = sheet.getRow(0);

    ArrayList<String> titles = new ArrayList<>();
    firstRow.cellIterator().forEachRemaining(col -> titles.add(col.getStringCellValue()));
    assertThat(titles.size(), greaterThanOrEqualTo(3));
    log.info("Found titles {}", titles);

    String creationTime = PropertyPath.property(Thought.class, t -> t.getCreationTime());
    String name = PropertyPath.property(Thought.class, t -> t.getName());
    String description = PropertyPath.property(Thought.class, t -> t.getDescription());

    assertTrue(titles.contains(creationTime));
    assertTrue(titles.contains(name));
    assertTrue(titles.contains(description));

    int nameColumn = titles.indexOf(name);
    ArrayList<String> names = new ArrayList<String>(COUNT);
    for (int i = 1; i <= COUNT; i++) {
        Row row = sheet.getRow(i);
        names.add(row.getCell(nameColumn).getStringCellValue());
    }
    Collections.sort(names);
    assertEquals("Thought000", names.get(0));
    assertEquals("Thought141", names.get(COUNT - 1));

    Date excelDate = sheet.getRow(1).getCell(titles.indexOf(creationTime)).getDateCellValue();

    Thought thought = PersistentWork.forName(Thought.class, "Thought000");

    Timestamp timestamp = java.sql.Timestamp.valueOf(thought.getCreationTime());
    Date creationDate = new Date(timestamp.getTime());
    assertEquals(creationDate, excelDate);
}

---

• «
• 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
• 49
• 50
• »