Example usage for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getLastRowNum

Introduction

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

Prototype

@Override
    public int getLastRowNum()

Source Link

Usage

From source file:se.nrm.dina.dyntaxa.dump.logic.ExcelReader.java

public List<TaxonVO> read() {

    logger.info("read excel");
    List<TaxonVO> list = new ArrayList();
    try (FileInputStream file = new FileInputStream(new File(EXCEL_FILE_PATH))) {
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowEndNumber = sheet.getLastRowNum();

        IntStream.range(1, rowEndNumber + 1).forEach(nbr -> {
            XSSFRow row = sheet.getRow(nbr);
            String rank = row.getCell(1).getStringCellValue();
            if (rank.equals("Genus") || rank.equals("Subgenus")) {
                currentParent = row.getCell(2).getStringCellValue();
                currentRank = rank;//  ww w  . ja  va  2 s  .co  m
            } else if (rank.equals("Species")) {

                taxonName = row.getCell(2).getStringCellValue();
                TaxonVO vo = new TaxonVO(taxonName, currentParent, currentRank);
                System.out.println(taxonName + " --- " + currentParent + " --- " + currentRank);
                list.add(vo);
            }
        });
        System.out.println("row number : " + rowEndNumber);
    } catch (IOException ex) {
        System.out.println("error: " + ex.getMessage());
    }
    return list;
}

From source file:Search.IDSSearchFunctionRunner.java

@Test
public void searchAction() throws FileNotFoundException, IOException, InterruptedException {

    FileInputStream file = new FileInputStream(new File("D:\\Book13.xlsx"));
    workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    CellStyle style = workbook.createCellStyle();
    Cell cell;/*  www  .  j ava 2 s .c o m*/
    Row row;

    navigateToLogin();
    LoginAction loginAction = new LoginAction(driver);
    loginAction.enterUserName("maxval");
    loginAction.enterPassword("Qcom2015*");
    TermsAndCondition termsAndConditions = loginAction.loginSubmit();
    Dashboard dashboard = termsAndConditions.Accept();
    System.out.println("abcd");
    System.out.println("abcde");

    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart + 1; rowNum < rowEnd; rowNum++) {
        row = sheet.getRow(rowNum);
        if (row != null) {
            int columnNumber = 0;
            cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL);
            try {
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    RichTextString fullTextQuery = cell.getRichStringCellValue();
                    String myQuery = fullTextQuery.toString();
                    System.out.println(myQuery);
                    SearchList searchList = new SearchList(driver);
                    if (rowNum == 1) {
                        dashboard.enterFullTextSearchQuery(myQuery);
                        searchList = dashboard.submitFullTextSearchQueryFromDashboard();
                    } else {
                        searchList.enterFullTextSearchQuery(myQuery);
                        searchList.submitFullTextSearchQueryFromSearchList();
                    }
                    String searchRecordList = searchList.getRecordIdInSearchList();
                    cell = row.createCell(2);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    style.setWrapText(true);
                    cell.setCellStyle(style);
                    cell.setCellValue(searchRecordList);
                    cell = row.getCell(1);
                    String expectedResult = cell.getStringCellValue();
                    if (expectedResult.equals(searchRecordList)) {
                        cell = row.createCell(3);
                        cell.setCellValue("Seach result matched with Expected");
                    } else {
                        cell = row.createCell(3);
                        cell.setCellValue("Seach result NOT matched with Expected");
                    }
                    try (FileOutputStream fileOut = new FileOutputStream("D:\\result.xlsx")) {
                        workbook.write(fileOut);
                    }
                }
            } catch (AssertionError Ae) {
            }
        }
    }
}

From source file:Servelt.ExcelReader.java

private void readSheet(XSSFSheet sheet, int Max_Col, ComponentSet comSet) throws Exception {
    String data = null;// w w w  . ja  v  a 2s.  c  om
    Component com = null;

    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        com = comSet.newComponent();
        for (int j = 0; j < Max_Col; j++) {
            Cell cell = sheet.getRow(i).getCell(j);
            if (cell != null) {
                data = cellToString(cell);
            } else {
                data = "";
            }
            System.out.printf("| %2d-%2d%30s |", i, j, data);
            com.setComponentByColumn(j, data);
        }
        System.out.println();
        comSet.addComponent(com);
    }
    return;
}

From source file:Servlets.UploadList.java

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    PrintWriter out = response.getWriter();

    String file_name = "";
    String extension = "";
    String cellData = "";
    InputStream input = null;// www  .  jav  a2s .  c om
    ArrayList<String[]> list = new ArrayList<String[]>();
    String[] arr;

    FileItemFactory factory = new DiskFileItemFactory();
    ServletFileUpload upload = new ServletFileUpload(factory);
    List items = null;
    try {
        items = upload.parseRequest(request);
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        Iterator itr = items.iterator();
        while (itr.hasNext()) {
            FileItem item = (FileItem) itr.next();
            file_name = item.getName();
            input = item.getInputStream();
        }
        if (!file_name.equals("")) {
            extension = file_name.substring(file_name.indexOf("."));
            if (extension.equalsIgnoreCase(".xlsx")) {
                XSSFWorkbook wb = new XSSFWorkbook(input);
                // Get first sheet from the workbook
                XSSFSheet sheet = wb.getSheetAt(0);
                Row row, header;
                int r = sheet.getLastRowNum();
                int headerCnt = 0;
                header = sheet.getRow(3);
                String[] headerText = new String[10];
                for (int i = 0; i < 10; i++) {
                    try {
                        headerText[i] = header.getCell(i).getStringCellValue();
                        headerCnt++;
                    } catch (Exception e) {
                    }
                }
                arr = new String[headerCnt];
                System.arraycopy(headerText, 0, arr, 0, headerCnt);
                list.add(arr);
                for (int i = 4; i <= r; i++) {
                    arr = new String[headerCnt];
                    row = sheet.getRow(i);
                    for (int j = 0; j < headerCnt; j++) {
                        try {
                            cellData = row.getCell(j).getStringCellValue();
                            arr[j] = cellData;
                        } catch (Exception e) {
                        }
                    }
                    list.add(arr);
                }
                InfosDao dao = new InfosDao();
                ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo();
                request.setAttribute("collegeList", collegeList);
                request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.')));
                request.setAttribute("itemList", list);
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp");
                dispatcher.forward(request, response);
            } else if (extension.equalsIgnoreCase(".xls")) {
                HSSFWorkbook workbook = new HSSFWorkbook(input);
                // Get first sheet from the workbook
                HSSFSheet sheet = workbook.getSheetAt(0);
                Row row, header;
                int r = sheet.getLastRowNum();
                int headerCnt = 0;
                header = sheet.getRow(3);
                String[] headerText = new String[10];
                for (int i = 0; i < 10; i++) {
                    try {
                        headerText[i] = header.getCell(i).getStringCellValue();
                        headerCnt++;
                    } catch (Exception e) {
                    }

                }
                arr = new String[headerCnt];
                System.arraycopy(headerText, 0, arr, 0, headerCnt);
                list.add(arr);
                for (int i = 4; i <= r; i++) {
                    arr = new String[headerCnt];
                    row = sheet.getRow(i);
                    for (int j = 0; j < headerCnt; j++) {
                        try {
                            cellData = row.getCell(j).getStringCellValue();
                            arr[j] = cellData;
                        } catch (Exception e) {
                        }
                    }
                    list.add(arr);
                }
                InfosDao dao = new InfosDao();
                ArrayList<CollegeBean> collegeList = dao.getAllCollegeInfo();
                request.setAttribute("collegeList", collegeList);
                request.setAttribute("fileName", file_name.substring(0, file_name.lastIndexOf('.')));
                request.setAttribute("itemList", list);
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/uploadInTable.jsp");
                dispatcher.forward(request, response);
            } else {
                request.setAttribute("temp", "File Is Not In Proper Format !!!");
                RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
                dispatcher.forward(request, response);
            }

        } else {
            request.setAttribute("temp", "Please Select File !!!");
            RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
            dispatcher.forward(request, response);
        }
    } catch (Exception e) {
        request.setAttribute("temp", "Error Loading File !!! Check contents and format of file.");
        RequestDispatcher dispatcher = request.getRequestDispatcher("/admin/upload.jsp");
        dispatcher.forward(request, response);
    }

}

From source file:steffen.haertlein.file.FileObject.java

License:Apache License

private void readExcelDocument() {
    try {/*from   w w  w .  ja v a2 s.c om*/
        FileInputStream fs = new FileInputStream(f);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sh;
        String text = "";
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            sh = wb.getSheetAt(i);
            for (int j = sh.getFirstRowNum(); j <= sh.getLastRowNum(); j++) {
                XSSFRow currRow = sh.getRow(j);
                if (currRow == null || currRow.getFirstCellNum() == -1) {
                    continue;
                } else {
                    for (int k = currRow.getFirstCellNum(); k < currRow.getLastCellNum(); k++) {
                        if (currRow.getCell(k, Row.RETURN_BLANK_AS_NULL) == null) {
                            continue;
                        } else {
                            text += currRow.getCell(k) + "; ";
                        }
                    }
                    text += System.lineSeparator();
                }
            }
        }
        fs.close();
        wb.close();
        String[] xlsxLines = text.split(System.lineSeparator());
        for (String line : xlsxLines) {
            lines.add(line);
        }
    } catch (IOException e) {
        JOptionPane.showMessageDialog(null, "Fehler in readExcelDocument", "Fehler", JOptionPane.ERROR_MESSAGE);
        e.printStackTrace();
    }
}

From source file:sv.com.mined.sieni.controller.GestionNotasController.java

public StreamedContent getFilePlantilla() {
    filePlantilla = null;/*from  w  w  w.  ja  v  a  2 s  . c  om*/
    String ruthPath = null;
    try {
        if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) {
            // Se crea el libro
            XSSFWorkbook libro = new XSSFWorkbook();
            // Se crea una hoja dentro del libro
            XSSFSheet sheetD = libro.createSheet();
            //Obtener lista de alumnos del curso
            List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote
                    .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso());
            //Leer datos y colocarlos en la hoja
            int f = 0;
            //Guardar datos en celda
            for (SieniAlumno alumno : alumnosEval) {
                // Se crea una fila dentro de la hoja
                XSSFRow fila = sheetD.createRow(f);
                f++;
                // Se crea las celdas dentro de la fila
                XSSFCell celdaCarnet = fila.createCell((short) 0);
                XSSFCell celdaAlumno = fila.createCell((short) 1);
                XSSFCell celdaNota = fila.createCell((short) 2);
                //Colocar valor en celda
                celdaCarnet.setCellValue(alumno.getAlCarnet());
                celdaAlumno.setCellValue(alumno.getNombreCompleto());
                celdaNota.setCellValue((double) 0.00);
            }
            //Encabezados desde plantilla
            InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext()
                    .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx");
            StreamedContent plantillaXLS = new DefaultStreamedContent(stream,
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx");
            XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream());
            XSSFSheet sheetP = plantilla.getSheetAt(0);

            //Filas que ocupa el encabezado de plantilla
            int encabezado = 3;
            //Quitar encabezado y desplazar Datos
            sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado);
            //Copiar contenido de plantilla a la hoja del reporte
            int inicio = 0;
            for (int row = 0; row < encabezado; row++) {
                copyRow(sheetP, sheetD, row, inicio);
                inicio++;
            }
            //Combinar las columnas al igual que la plantilla
            for (int m = 0; m < sheetP.getNumMergedRegions(); m++) {
                CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy();
                sheetD.addMergedRegion(cellRangeAddress);
            }
            //Evaluacion
            XSSFCell celdaEval = sheetD.getRow(0).getCell(1);
            celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre());
            // Se salva el libro.
            FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx");
            libro.write(elFichero);
            elFichero.close();
            //Leer libro para descarga
            FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx"));
            filePlantilla = new DefaultStreamedContent(file,
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx");

        } else {
            new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR);
        }
    } catch (Exception exc) {
        new ValidationPojo().printMsj(
                "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath,
                FacesMessage.SEVERITY_ERROR);
    }
    return filePlantilla;
}

From source file:tan.jam.jsf.Shifting.java

public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum,
        int destinationRowNum) {

    worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    XSSFRow newRow = worksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {/*from   w ww  .j  av a 2  s  . c o m*/
        newRow = worksheet.createRow(destinationRowNum);
    }

    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        if (oldCell == null) {
            newCell = null;
            continue;
        }

        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        newCell.setCellType(oldCell.getCellType());

        switch (oldCell.getCellType()) {
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum);
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
            //newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
            newCell.setCellValue("");
            break;
        }
    }

    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    int inc = destinationRowNum + 1;
    worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc);
}

From source file:tubessc.Dataset.java

public void addDataSetTrainingExcel(String InputFile, int numOfInput) throws IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i <= rowEnd; i++) {
        double price[] = new double[numOfInput];
        double target = 0;
        if ((i + numOfInput) <= rowEnd) {
            for (int j = 0; j <= numOfInput; j++) {
                Row row = sheet.getRow(i + j);
                if (j != numOfInput) {
                    Cell cell = row.getCell(0);
                    price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                } else {
                    Cell cell = row.getCell(0);
                    target = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                }/*from w ww. j a v a  2s.c  o  m*/
            }
            GoldPrice gp = new GoldPrice(price, target);
            dataSetTraining.add(gp);
        }
    }
    file.close();

}

From source file:tubessc.Dataset.java

public void addDataSetTestingExcel(String InputFile, int numOfInput) throws IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i < rowEnd; i++) {
        double price[] = new double[numOfInput];
        double target = 0;
        if ((i + numOfInput) <= rowEnd) {
            for (int j = 0; j <= numOfInput; j++) {
                Row row = sheet.getRow(i + j);
                if (j != numOfInput) {
                    Cell cell = row.getCell(0);
                    price[j] = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                } else {
                    Cell cell = row.getCell(0);
                    target = Double.parseDouble(String.valueOf(cell.getNumericCellValue()));
                }/*w w w.ja  v  a  2s .c o m*/
            }
            GoldPrice gp = new GoldPrice(price, target);
            dataSetTesting.add(gp);
        }
    }
    file.close();

}

From source file:tubessc.Dataset.java

public void calculateFluctuation(String InputFile, String OutputFile)
        throws FileNotFoundException, IOException {
    FileInputStream file = new FileInputStream(new File(InputFile));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFWorkbook output = new XSSFWorkbook();
    XSSFSheet sheetOutput = output.createSheet("new sheet");
    FileOutputStream fileOut = new FileOutputStream(OutputFile);
    int rowStart = sheet.getFirstRowNum();
    int rowEnd = sheet.getLastRowNum();
    for (int i = rowStart; i <= rowEnd - 1; i++) {
        Row rowIn1 = sheet.getRow(i);//from  w w w  .ja v  a 2  s .  c om
        Cell cellIn1 = rowIn1.getCell(0);
        Row rowIn2 = sheet.getRow(i + 1);
        Cell cellIn2 = rowIn2.getCell(0);
        double value1 = Double.parseDouble(String.valueOf(cellIn1.getNumericCellValue()));
        double value2 = Double.parseDouble(String.valueOf(cellIn2.getNumericCellValue()));
        Row rowOut = sheetOutput.createRow(i);
        Cell cellOut = rowOut.createCell(0);
        cellOut.setCellValue(value2 - value1);
    }
    output.write(fileOut);
    fileOut.close();
}