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

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. 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:misuExcel.excelSplit.java

License:Open Source License

private void splitTarget() {
    if (target != null) {
        ArrayList<Cell> other = new ArrayList<Cell>();
        Sheet sheet = target.getSheetAt(sheetNum_target);
        names = new ArrayList<String>();
        for (int i = splitJpanel.ignore_Rowtar; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);/*from   w  ww. j  a  v  a2 s  .  com*/
            if (row != null) {
                Cell cell = row.getCell(cellNum_target);
                if (cell != null) {
                    String str = getCellString(cell);
                    if (!names.contains(str))
                        if (!other.contains(cell)) {
                            names.add(str);
                            other.add(cell);
                            Log.info(" " + str);
                        }
                } else {
                    other.add(cell);
                }
            }
        } //end for
    }
}

From source file:misuExcel.excelSplit.java

License:Open Source License

private void examExcel() {
    if (excel != null) {
        if (names != null && names.size() > 0) {
            Sheet sheet = excel.getSheetAt(sheetNum);
            initList(names.size());//from   ww  w .  j ava  2 s.co  m
            nones = new ArrayList<Integer>();
            Boolean isAdd = false;
            for (int j = splitJpanel.ignore_Row; j <= sheet.getLastRowNum(); j++) {
                Row row = sheet.getRow(j);
                if (row != null) {
                    Cell cell = row.getCell(cellNum);
                    String str = getCellString(cell);
                    Log.info(" " + str);
                    for (int i = 0; i < names.size(); i++) {
                        if (str != null && str.equals(names.get(i))) {
                            isAdd = true;
                            splitList.get(i).add(row.getRowNum());
                        }
                    } //end names for   
                    if (!isAdd) {
                        nones.add(row.getRowNum());
                    }
                    isAdd = false;
                }
            } //end for
            Log.info("examExcel is already");
        } else {
            Log.warm("target is none");
        }
    } else {
        Log.warm("excel is not exit");
    }
}

From source file:misuExcel.excelWrite.java

License:Open Source License

private void outType04() {
    if (wbSheet != null && addWb != null && names != null && list != null) {
        Sheet sheet = addWb.getSheetAt(sheetNum_target);
        int numRow = sheet.getLastRowNum() + 1;
        ArrayList<Integer> integers = list.get(0);
        for (int j = addJpanel.ignore_Rowtar; j <= wbSheet.getLastRowNum(); j++) {
            Row row = null;/*w w w .ja  v a  2s . c om*/
            Row copy = null;
            if (j != cellNum_target) {
                if ((cellNum_target + 1) > addJpanel.ignore_Rowtar)
                    row = sheet.createRow(j < cellNum_target ? (j + numRow - addJpanel.ignore_Rowtar)
                            : (j + numRow - 1 - addJpanel.ignore_Rowtar));
                else
                    row = sheet.createRow(j + numRow - addJpanel.ignore_Rowtar);
                copy = wbSheet.getRow(j);
            }
            if (copy != null) {
                for (int k = 0; k < copy.getLastCellNum(); k++) {
                    Cell cell = null;
                    if (k >= addJpanel.ignore_Celltar)
                        cell = row.createCell(integers.get((k - addJpanel.ignore_Celltar)));
                    else
                        cell = row.createCell(k);
                    Cell copyCell = copy.getCell(k);
                    if (copyCell != null) {
                        switch (copyCell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            cell.setCellValue(copyCell.getRichStringCellValue().getString().trim());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(copyCell)) {
                                cell.setCellValue(copyCell.getDateCellValue());
                            } else {
                                cell.setCellValue(copyCell.getNumericCellValue());
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            cell.setCellValue(copyCell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellValue(copyCell.getCellFormula());
                            break;
                        default:
                            cell.setCellValue(copyCell.getStringCellValue().trim());
                        }
                    }
                }
            }
        } //end for
        createWB(addWb, fileReal + "(add)");
        JOptionPane.showMessageDialog(null, fileReal + "(add)." + _index + "?");
    }
}

From source file:mnb.io.tabular.preparse.PreparsedSheet.java

License:Open Source License

public PreparsedSheet(Sheet sheet, ExcelModelProperties properties, TableDescription bounds) {

    this.properties = properties;
    this.columns = this.properties.getDefinedColumns(bounds.getClass());

    if (properties.contains(bounds.getKey())) {
        this.bounds = properties.getDataBounds(bounds.getKey());
        this.minIndex = this.bounds.y - 1;
        this.maxIndex = this.bounds.y + this.bounds.height;
    } else {//from   w  w  w.  j  av a  2 s  .co m
        // unbounded (e.g. model-SEED)
        this.minIndex = 0;
        this.maxIndex = sheet.getLastRowNum();
    }
    this.rowIndex = minIndex;

}

From source file:model.ReadExcel.java

public void readExcelFileforImportingIssuedChecks(String excelFilePath, int colsExcel[], int startingRow) {

    try {/*w w  w .jav a  2s  .  c o  m*/

        int validData = 0;
        Check check = new Check();
        queryData cd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            for (int i = 0; i < colsExcel.length; i++) {

                check.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

            }

            validData++;
            cd.insertData(check);

        }

        if (validData > 1)
            JOptionPane.showMessageDialog(null,
                    "There are " + rowEnd + " new issued checks for " + Global.getBranchName());
        else
            JOptionPane.showMessageDialog(null,
                    "There is " + rowEnd + " new issued check for " + Global.getBranchName());

    }

    catch (Exception ex) {

        JOptionPane.showMessageDialog(null, ex.toString() + rowStart);

    }

}

From source file:model.ReadExcel.java

public void readExcelFileforImportingClaimedChecks(String excelFilePath, int colsExcel[], int startingRow) {

    try {//from www .j av  a2 s  . c o m

        validation vd = new validation();
        ImportClaim importclaim = new ImportClaim();
        queryData cd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            if (r1.getCell(colsExcel[3], Row.RETURN_BLANK_AS_NULL).getStringCellValue() != null) {

                for (int i = 0; i < colsExcel.length; i++) {

                    importclaim.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

                }

                if (vd.validateClaim(importclaim.getCheckNum(), importclaim.getCheckAmount())) {

                    validImport++;
                    cd.updateClaimedfromImport(importclaim);

                }

            } else {

                return;
            }

        }

    }

    catch (Exception ex) {

        // JOptionPane.showMessageDialog(null,ex.toString() + rowStart);

    } finally {

        if (validImport > 0) {
            JOptionPane.showMessageDialog(null,
                    "Successfully tagged " + validImport + " check numbers for claimed status..");
        } else {
            JOptionPane.showMessageDialog(null,
                    "No data has been processed, please check your excel template..");
        }

    }

}

From source file:model.ReadExcel.java

public void readExcelFileforImportingReceivedByUCC(String excelFilePath, int colsExcel[], int startingRow) {

    try {/*from w w w .j  a  v  a2 s  .c  o  m*/

        int validData = 0;
        ReceivedByUCC rb = new ReceivedByUCC();
        queryData qd = new queryData();

        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
        Workbook workbook = getWorkbook(inputStream, excelFilePath);

        Sheet firstSheet = workbook.getSheetAt(0);

        rowStart = startingRow;
        int rowEnd = Math.max(rowStart, firstSheet.getLastRowNum());

        for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {

            Row r1 = firstSheet.getRow(rowNum);

            for (int i = 0; i < colsExcel.length; i++) {

                rb.setValues(colsExcel[i], r1.getCell(colsExcel[i], Row.RETURN_BLANK_AS_NULL));

            }

            qd.updateStatusToReceivedByUCC(rb);
            validData++;

        }

        if (validData > 1)
            JOptionPane.showMessageDialog(null, "UCC received " + rowEnd + " checks..");
        else
            JOptionPane.showMessageDialog(null, "UCC received " + rowEnd + " check..");

    }

    catch (Exception ex) {

        JOptionPane.showMessageDialog(null, ex.toString() + rowStart);

    }

}

From source file:mvp.presenter.OpenDataDialogPresenter.java

private void parseFile(String excelFilePath, boolean colHeader) throws IOException {
    if (view.shapeFilePathField.getText().isEmpty()
            || view.shapeFilePathField.getText().equalsIgnoreCase("Enter the shapefile path here")) {
        data.setShpPath("empty");
    }/*from w ww.  j  a v a 2  s . c o m*/
    try (FileInputStream inputStream = new FileInputStream(new File(excelFilePath))) {
        GridBase grid = new GridBase(1000, 100);
        ObservableList<String> listHeader = FXCollections.observableArrayList();

        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet firstSheet = workbook.getSheetAt(0);
            data.setRowNumber(firstSheet.getLastRowNum());
            data.setColumnNumber(firstSheet.getRow(0).getLastCellNum());
            Iterator<Row> iterator = firstSheet.iterator();

            ObservableList<ObservableList<SpreadsheetCell>> rows = FXCollections.observableArrayList();
            for (int row = 0; row < grid.getRowCount(); row++) {
                final ObservableList<SpreadsheetCell> list = FXCollections.observableArrayList();
                for (int column = 0; column < grid.getColumnCount(); column++) {
                    list.add(SpreadsheetCellType.STRING.createCell(row, column, 1, 1, ""));
                }
                rows.add(list);
            }

            if (colHeader) {
                if (iterator.hasNext()) {
                    Row headerRow = iterator.next();
                    Iterator<Cell> cellIterator = headerRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        listHeader.add(cell.getStringCellValue());
                    }
                }

                ObservableList<String> variableType = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size(); i++) {
                    variableType.add(null);
                }

                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            variableType.set(cell.getColumnIndex(), "String");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            variableType.set(cell.getColumnIndex(), "Boolean");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            variableType.set(cell.getColumnIndex(), "Double");
                            rows.get(cell.getRowIndex() - 1).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex() - 1,
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }

                ObservableList<Variable> variables = FXCollections.observableArrayList();
                for (int i = 0; i < listHeader.size() && i < variableType.size(); i++) {
                    Variable variable = new Variable(listHeader.get(i), variableType.get(i));
                    variables.add(variable);
                }
                data.setVariables(variables);
            } else if (!colHeader) {
                while (iterator.hasNext()) {
                    Row nextRow = iterator.next();
                    Iterator<Cell> cellIterator = nextRow.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getStringCellValue()));
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.STRING.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1,
                                            String.valueOf(cell.getBooleanCellValue())));
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            rows.get(cell.getRowIndex()).set(cell.getColumnIndex(),
                                    SpreadsheetCellType.DOUBLE.createCell(cell.getRowIndex(),
                                            cell.getColumnIndex(), 1, 1, cell.getNumericCellValue()));
                            break;
                        }
                    }
                }
            }
            grid.setRows(rows);
            mwview.drawTable(listHeader, grid);
        }
    }
    view.closeStage();
}

From source file:mw.sqlitetool.MainFrame.java

private void innerExportToDb(Sheet sheet) {
    int rowNum = sheet.getLastRowNum();
    if (rowNum < 2) {
        throw new RuntimeException("Empty excel.");
    }//from  w  w w  .  ja v a 2s .  c  o  m
    // the first row is attribute names.
    Row firstRow = sheet.getRow(1);
    int colNum = firstRow.getLastCellNum();
    Cell cell = null;

    StringBuilder sb = new StringBuilder();
    sb.append("create table [");
    sb.append(_currentFile);
    sb.append("](");
    for (int i = 0; i < colNum; i++) {
        cell = firstRow.getCell(i);
        String attribute = ExcelHelper.getInstance().getCellValue(cell).toString();
        sb.append("[").append(attribute).append("] varchar(100)");
        if (i != colNum - 1) {
            sb.append(", ");
        }
    }
    sb.append(");");
    String sql = sb.toString();
    this.log("Executing sql: " + sql);
    try {
        SqliteHelper.getInstance().executeSql(sql);
    } catch (SQLException ex) {
        this.log("Error: " + ex.getMessage());
    }

    // import the data
    Row row = null;
    sql = "insert into [" + _currentFile + "] values(";
    for (int i = 2; i < rowNum; i++) {
        row = sheet.getRow(i);
        String tmpSql = sql;
        for (int j = 0; j < colNum; j++) {
            cell = row.getCell(j);
            String val = "\"" + ExcelHelper.getInstance().getCellValue(cell).toString().replace("\'", "\'\'")
                    + "\"";
            tmpSql += val;
            if (j != colNum - 1) {
                tmpSql += ", ";
            }
        }
        tmpSql += ");";
        this.log("Executing sql: " + tmpSql);
        try {
            SqliteHelper.getInstance().executeSql(tmpSql);
        } catch (SQLException ex) {
            this.log("Error: " + ex.getMessage());
        }
    }
}

From source file:net.bafeimao.umbrella.support.data.entity.ExcelEntityParser.java

License:Apache License

private <E extends DataEntity> LinkedList<E> parse0(Class<E> entityClass, Sheet sheet)
        throws EntityParseException {
    LinkedList<E> retList = new LinkedList<E>();

    try {/*  www.  ja va2s. c  o  m*/
        // ???(??)
        for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
            // LOGGER.info("Parsing {} with data row #{} ...", entityClass.getSimpleName(), rowNum + 1);

            E instance = entityClass.newInstance();
            for (Field field : getEntityFields(entityClass)) {
                if (field.isAnnotationPresent(IgnoreParsing.class)) {
                    continue;
                }

                field.setAccessible(true);

                int colNum = getColumnIndex(sheet, field.getName());
                if (colNum != -1) {
                    Cell cell = sheet.getRow(rowNum).getCell(colNum);
                    if (cell != null) {
                        try {
                            String cellValue = this.getCellValue(cell);

                            //                                LOGGER.debug("Assigning {}.{} = {} ({}))", entityClass.getSimpleName()
                            //                                        , field.getName(), cellValue, field.getType().getSimpleName());

                            this.assignFieldValue(instance, field, cellValue);
                        } catch (IllegalAccessException e) {
                            LOGGER.error("{}", e);
                        } catch (IllegalArgumentException e) {
                            LOGGER.error("{}", e);
                        } catch (NullPointerException e) {
                            LOGGER.error("{}", e);
                        } catch (ExceptionInInitializerError e) {
                            LOGGER.error("{}", e);
                        } catch (Exception e) {
                            throw new EntityParseException("(" + "name:" + field.getName()
                            //                                        + ", type:" + fieldType.getSimpleName()
                            //                                        + ", value:" + cellValue
                                    + ", entity:" + instance.getClass().getSimpleName()
                                    + ")?:" + e.getMessage(), e);
                        }
                    }
                }
            }
            retList.add(instance);
        }
    } catch (Exception e) {
        LOGGER.error("{}", e);
    }

    return retList;
}