Example usage for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getLastRowNum

Introduction

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

Prototype

@Override
public int getLastRowNum()

Source Link

Document

Gets the number last row on the sheet.

Usage

From source file:com.zxy.commons.poi.excel.ExcelUtils.java

License:Apache License

/**
 * ?Excelsheet//from  w ww.  ja va2  s.  co m
 * 
 * @param inputPath ???Excel
 * @return Excel?
 * @throws IOException IOException
 */
public static Map<String, Table<Integer, String, String>> readAll2table(String inputPath) throws IOException {
    Map<String, Table<Integer, String, String>> tables = Maps.newLinkedHashMap();
    FileInputStream inputStream = null;
    HSSFWorkbook wb = null;
    try {
        inputStream = new FileInputStream(inputPath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
        // HSSFWorkbook
        POIFSFileSystem fs = new POIFSFileSystem(bufferedInputStream);
        wb = new HSSFWorkbook(fs);
        List<String> columnNames = Lists.newLinkedList();
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            Table<Integer, String, String> table = TreeBasedTable.create();
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            String sheetName = st.getSheetName();
            for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                    HSSFCell cell = row.getCell(columnIndex);
                    if (cell != null) {
                        if (rowIndex == 0) { // 
                            columnNames.add(cell.getStringCellValue());
                        } else {
                            String value = cell.getStringCellValue();
                            table.put(rowIndex, columnNames.get(columnIndex), value);
                        }
                    }
                }
            }
            tables.put(sheetName, table);
        }
        return tables;
    } finally {
        if (wb != null) {
            wb.close();
        }
        if (inputStream != null) {
            inputStream.close();
        }
    }
}

From source file:controller.ExternalIOController.java

public static void gravarResultadoTeste(QueenSolver solver) throws IOException {
    File f = new File("ResultadoExperimento.xls");
    HSSFWorkbook workbook = null;//  w w w  .j  av  a 2 s . c o  m
    HSSFSheet sheet = null;
    FileOutputStream fileOut = null;
    HSSFRow row = null;

    if (f.isFile() && f.canRead()) {
        workbook = new HSSFWorkbook(new FileInputStream(f));
        sheet = workbook.getSheetAt(0);
        row = sheet.createRow(sheet.getLastRowNum() + 1);
        row.createCell(0).setCellValue(sheet.getLastRowNum());
        row.createCell(1).setCellValue(solver.getN());
        row.createCell(2).setCellValue(solver.getIteracoes());
        row.createCell(3).setCellValue(solver.getUltimoTempo());
        fileOut = new FileOutputStream(f);
    } else {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet("TestSheet");
        HSSFRow rowhead = sheet.createRow(0);
        rowhead.createCell(0).setCellValue("Numero");
        rowhead.createCell(1).setCellValue("N");
        rowhead.createCell(2).setCellValue("Iteraes");
        rowhead.createCell(3).setCellValue("Tempo de Servio (MS)");
        row = sheet.createRow(1);
        row.createCell(0).setCellValue(sheet.getLastRowNum());
        row.createCell(1).setCellValue(solver.getN());
        row.createCell(2).setCellValue(solver.getIteracoes());
        row.createCell(3).setCellValue(solver.getUltimoTempo());
        fileOut = new FileOutputStream(new File("ResultadoExperimento.xls"));
    }
    workbook.write(fileOut);
    fileOut.close();
}

From source file:controller.ExternalIOController.java

    o() throws IOException{
    File f = new File("ResultadoExperimento.xls");
    HSSFWorkbook workbook = null;/*from ww  w . j a  v a2  s  . c o m*/
    HSSFSheet sheet = null;  
    FileOutputStream fileOut = null;
    HSSFRow row = null;
        
    if(f.isFile() && f.canRead()){
        workbook = new HSSFWorkbook(new FileInputStream(f));
        sheet = workbook.getSheetAt(0);
        row = sheet.getRow(0);
        row.createCell(4).setCellValue("Nmeros Gerados por Distribuio");
        for (int i = 0; i < DistributionController.getNumerosGerados().size(); i++) {
            row = sheet.getRow(i + 1);
            if(row == null){
                row = sheet.createRow(sheet.getLastRowNum() + 1);
            }
            row.createCell(4).setCellValue(DistributionController.getNumerosGerados().get(i));
        }
        fileOut = new FileOutputStream(f);
    }else{
        throw new IOException();
    }
    workbook.write(fileOut);
    fileOut.close();
}

From source file:data.pkg.ReadWriteExcelFile.java

public static void readXLSFile(String path) throws IOException {

    File excel = new File(path);
    FileInputStream fis = new FileInputStream(excel);
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet ws = wb.getSheet("Sheet1");

    int rowNum = ws.getLastRowNum() + 1;
    int colNum = ws.getRow(0).getLastCellNum();

    for (int i = 0; i < rowNum; i++) {
        HSSFRow row = ws.getRow(i);/*from  w w  w  .ja v  a  2s . c om*/
        for (int j = 0; j < colNum; j++) {
            HSSFCell cell = row.getCell(j);
            if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {

                long value = Long.parseLong(cell.toString());

                if (j == 0) {
                    dataOpen.addDeslocamento(value);
                    System.out.println(value);
                }
                if (j == 1) {
                    dataOpen.addForca(value);
                    System.out.println(value);
                }
            }
        }
    }

}

From source file:ddf.metrics.reporting.internal.rest.MetricsEndpointTest.java

License:Open Source License

@Test
public void testGetMetricsDataAsXls() throws Exception {
    // Create RRD file that Metrics Endpoint will detect
    int dateOffset = 900; // 15 minutes in seconds
    createRrdFile(dateOffset);//from   ww  w .j ava 2 s  .  c o  m

    UriInfo uriInfo = createUriInfo();

    // Get the metrics data from the endpoint
    MetricsEndpoint endpoint = getEndpoint();
    endpoint.setMetricsDir(TEST_DIR);

    Response response = endpoint.getMetricsData("uptime", "xls", null, null, Integer.toString(dateOffset),
            "my label", "my title", uriInfo);

    cleanupRrd();

    InputStream xls = (InputStream) response.getEntity();
    assertThat(xls, not(nullValue()));

    HSSFWorkbook wb = new HSSFWorkbook(xls);
    assertThat(wb.getNumberOfSheets(), equalTo(1));
    HSSFSheet sheet = wb.getSheet("Uptime");
    assertThat(sheet, not(nullValue()));

    // Expect 7 rows: title + blank + column headers + 2 rows of samples + blank +
    // totalQueryCount
    assertThat(sheet.getPhysicalNumberOfRows(), equalTo(7));

    // first row should have title in first cell
    HSSFRow row = sheet.getRow(0);
    assertThat(row, not(nullValue()));
    assertThat(row.getCell(0).getStringCellValue(), startsWith("Uptime for"));

    // third row should have column headers in first and second cells
    row = sheet.getRow(2);
    assertThat(row.getCell(0).getStringCellValue(), equalTo("Timestamp"));
    assertThat(row.getCell(1).getStringCellValue(), equalTo("Value"));

    // should have 2 rows of samples' data
    row = sheet.getRow(3);
    assertThat(row.getCell(0).getStringCellValue(), not(nullValue()));
    assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
    row = sheet.getRow(4);
    assertThat(row.getCell(0).getStringCellValue(), not(nullValue()));
    assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));

    // last row should have totalQueryCount in first cell
    row = sheet.getRow(sheet.getLastRowNum());
    assertThat(row.getCell(0).getStringCellValue(), startsWith("Total Count:"));
    assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
}

From source file:ddf.metrics.reporting.internal.rrd4j.RrdMetricsRetrieverTest.java

License:Open Source License

private void verifyWorksheet(HSSFSheet sheet, String metricName, int expectedNumberOfDataRows,
        boolean hasTotalCount) {
    // 3 = title + blank row + column headers
    int expectedTotalRows = 3 + expectedNumberOfDataRows;
    if (hasTotalCount) {
        expectedTotalRows += 2;/*from w w  w . j a v  a 2s.  co  m*/
    }
    assertThat(sheet.getPhysicalNumberOfRows(), equalTo(expectedTotalRows));

    // first row should have title in first cell
    HSSFRow row = sheet.getRow(0);
    assertThat(row, not(nullValue()));
    assertThat(row.getCell(0).getStringCellValue(), startsWith(metricName + " for"));

    // third row should have column headers in first and second cells
    row = sheet.getRow(2);
    assertThat(row.getCell(0).getStringCellValue(), equalTo("Timestamp"));
    assertThat(row.getCell(1).getStringCellValue(), equalTo("Value"));

    // verify rows with the sample data, i.e., timestamps and values
    int endRow = 3 + expectedNumberOfDataRows;
    for (int i = 3; i < endRow; i++) {
        row = sheet.getRow(i);
        assertThat(row.getCell(0).getStringCellValue(), not(nullValue()));
        assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
    }

    row = sheet.getRow(sheet.getLastRowNum());
    if (hasTotalCount) {
        assertThat(row.getCell(0).getStringCellValue(), startsWith("Total Count:"));
        assertThat(row.getCell(1).getNumericCellValue(), not(nullValue()));
    } else {
        assertThat(row.getCell(0).getStringCellValue(), not(startsWith("Total Count:")));
    }
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//from w  w w.j  a  va 2s. c o  m
 *
 * @param excelSheet
 *            Die excel-Datei.
 * @return Gibt eine Liste mit zweidimensionalen Arrays fr jeweils ein sheet in der excel-Datei
 *         zurck.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<String[][]> exportWorkbook(final File excelSheet) throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);

    final int numberOfSheets = wb.getNumberOfSheets();
    final List<String[][]> sheetList = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();

        final int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows + 1][columns];
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            excelSheetInTDArray[i][j] = Boolean.toString(cell.getBooleanCellValue());
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            excelSheetInTDArray[i][j] = "";
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            excelSheetInTDArray[i][j] = cell.getCellFormula();
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            excelSheetInTDArray[i][j] = Double.toString(cell.getNumericCellValue());
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            excelSheetInTDArray[i][j] = cell.getRichStringCellValue().getString();
                        }
                    }
                }
            }
        }
        sheetList.add(excelSheetInTDArray);
    }
    wb.close();
    return sheetList;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Exportiert die bergebene excel-Datei in eine geschachtelte Liste mit Listen von sheets und
 * Listen von den Zeilen der sheets von der excel-Datei.
 *
 * @param excelSheet//  w w w  .  j  a  va 2 s . c o  m
 *            Die excel-Datei.
 * @return Gibt eine Liste mit Listen von den sheets in der excel-Datei zurck. Die Listen mit
 *         den sheets beinhalten weitere Listen mit String die jeweils eine Zeile
 *         reprsentieren.
 * @throws IOException
 *             Fals ein Fehler beim Lesen aufgetreten ist.
 * @throws FileNotFoundException
 *             Fals die excel-Datei nicht gefunden wurde.
 */
public static List<List<List<String>>> exportWorkbookAsStringList(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    final List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        final List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                final List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    final HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        final int cellType = cell.getCellType();
                        if (cellType == Cell.CELL_TYPE_BLANK) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
                            reihe.add(Boolean.toString(cell.getBooleanCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_ERROR) {
                            reihe.add("");
                        } else if (cellType == Cell.CELL_TYPE_FORMULA) {
                            reihe.add(cell.getCellFormula());
                        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
                            reihe.add(Double.toString(cell.getNumericCellValue()));
                        } else if (cellType == Cell.CELL_TYPE_STRING) {
                            reihe.add(cell.getRichStringCellValue().getString());
                        }
                    }
                }
                excelSheetList.add(reihe);
            }
        }
        sl.add(excelSheetList);
    }
    wb.close();
    return sl;
}

From source file:de.alpharogroup.export.excel.poi.ExportExcelExtensions.java

License:Open Source License

/**
 * Replace null cells into empty cells./*  www  . j  a  va 2s. com*/
 *
 * @param excelSheet
 *            the excel sheet
 * @return the HSSF workbook
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws FileNotFoundException
 *             the file not found exception
 */
public static HSSFWorkbook replaceNullCellsIntoEmptyCells(final File excelSheet)
        throws IOException, FileNotFoundException {
    final POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    final HSSFWorkbook wb = new HSSFWorkbook(fs);
    final int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        final int rows = sheet.getLastRowNum();
        final int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i <= rows; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j, Cell.CELL_TYPE_BLANK);
                    }
                }
            }
        }
    }
    return wb;
}

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

License:Open Source License

public void mergeIDs() {
    System.err.println("Merging...");
    try (HSSFWorkbook wb = new HSSFWorkbook(
            new POIFSFileSystem(new FileInputStream(DBKernel.HSHDB_PATH + "mergeList.xls")))) {
        //FileInputStream is = new FileInputStream("C:\\Users\\Armin\\Desktop\\AllKrisen\\EFSA\\mergeList.xls");
        HSSFSheet mergeSheet = wb.getSheet("mergeList");
        int numRows = mergeSheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            try {
                HSSFRow row = mergeSheet.getRow(i);
                if (row != null) {
                    HSSFCell cell = row.getCell(0);
                    if (cell != null) {
                        Integer oldEfsaID = (int) cell.getNumericCellValue();
                        if (cell != null) {
                            cell = row.getCell(1);
                            Integer newEfsaID = (int) cell.getNumericCellValue();
                            DBKernel.mergeIDs(DBKernel.getDBConnection(), "Station", oldEfsaID, newEfsaID);
                        }//from   w  w w.j  a va  2 s .  c  o m
                    }
                }
            } catch (Exception e) {
                System.err.println(e.getMessage() + "\t" + i);
            }
        }
    } catch (Exception e) {
    }
    System.err.println("Merging...Fin!");
}