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:library.restore.java

private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton5ActionPerformed
    String fileSeparator = System.getProperty("file.separator");
    JFileChooser chooser = new JFileChooser();
    //set JFileChooser default path
    chooser.setCurrentDirectory(new java.io.File("."));
    //set JFileChooser title
    chooser.setDialogTitle("open");
    //use as you need DIRECTORIES_ONLY, FILES_ONLY etc
    chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);

    //false, if you want to view all kind of file type in JFileChooser
    //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser
    chooser.setAcceptAllFileFilterUsed(false);

    if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) {
        //write selected path inside JTextField
        jtfFileLocation2.setText(chooser.getSelectedFile().getPath() + fileSeparator);
        //students=getText(chooser.getSelectedFile().getPath()+fileSeparator);
        loaned = jtfFileLocation2.getText();
        System.out.println("" + loaned + "");
    } else {//  ww  w.j  a va  2s  .c om
        System.out.println("No Selection");
    }
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root",
                "123ERYcog.");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        // this.fileurlp = this.student.replace("\\", "\\\\");
        FileInputStream input = new FileInputStream("" + loaned + "");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            String no = row.getCell(0).getStringCellValue();
            String bid = row.getCell(1).getStringCellValue();
            String sid = row.getCell(2).getStringCellValue();
            String title = row.getCell(3).getStringCellValue();

            String fname = row.getCell(4).getStringCellValue();
            String lname = row.getCell(5).getStringCellValue();

            String form = row.getCell(6).getStringCellValue();
            String classs = row.getCell(7).getStringCellValue();
            String edition = row.getCell(8).getStringCellValue();
            String updated_at = row.getCell(9).getStringCellValue();
            //String title = row.getCell(3).getStringCellValue();

            String sql = "INSERT INTO loaned VALUES('" + no + "','" + bid + "','" + sid + "','" + title + "','"
                    + fname + "','" + lname + "','" + form + "','" + classs + "','" + edition + "','"
                    + updated_at + "')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.execute();
            System.out.println("Import rows " + i);
        }
        con.commit();
        pstm.close();
        con.close();
        input.close();
        JOptionPane.showMessageDialog(null, "Success import excel to mysql table");
        System.out.println("Success import excel to mysql table");
    } catch (ClassNotFoundException e) {
        System.out.println(e);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, "error import excel to mysql table");
        System.out.println(ex);
    } catch (IOException ioe) {
        System.out.println(ioe);
    }

}

From source file:library.restore.java

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
    String fileSeparator = System.getProperty("file.separator");
    JFileChooser chooser = new JFileChooser();
    //set JFileChooser default path
    chooser.setCurrentDirectory(new java.io.File("."));
    //set JFileChooser title
    chooser.setDialogTitle("open");
    //use as you need DIRECTORIES_ONLY, FILES_ONLY etc
    chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);

    //false, if you want to view all kind of file type in JFileChooser
    //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser
    chooser.setAcceptAllFileFilterUsed(false);

    if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) {
        //write selected path inside JTextField
        jtfFileLocation3.setText(chooser.getSelectedFile().getPath() + fileSeparator);
        //students=getText(chooser.getSelectedFile().getPath()+fileSeparator);
        loanedcourse = jtfFileLocation3.getText();
        System.out.println("" + loanedcourse + "");
    } else {/* w w  w  .  j a v  a2 s  .c  o m*/
        System.out.println("No Selection");
    }
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root",
                "123ERYcog.");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        // this.fileurlp = this.student.replace("\\", "\\\\");
        FileInputStream input = new FileInputStream("" + loanedcourse + "");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            String no = row.getCell(0).getStringCellValue();
            String bid = row.getCell(1).getStringCellValue();
            String sid = row.getCell(2).getStringCellValue();
            String title = row.getCell(3).getStringCellValue();

            String fname = row.getCell(4).getStringCellValue();
            String lname = row.getCell(5).getStringCellValue();

            String form = row.getCell(6).getStringCellValue();
            String classs = row.getCell(7).getStringCellValue();
            String edition = row.getCell(8).getStringCellValue();
            String updated_at = row.getCell(9).getStringCellValue();
            //String title = row.getCell(3).getStringCellValue();

            String sql = "INSERT INTO loanedcourse VALUES('" + no + "','" + bid + "','" + sid + "','" + title
                    + "','" + fname + "','" + lname + "','" + form + "','" + classs + "','" + edition + "','"
                    + updated_at + "')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.execute();
            System.out.println("Import rows " + i);
        }
        con.commit();
        pstm.close();
        con.close();
        input.close();
        JOptionPane.showMessageDialog(null, "Success import excel to mysql table");
        System.out.println("Success import excel to mysql table");
    } catch (ClassNotFoundException e) {
        System.out.println(e);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, "error import excel to mysql table");
        System.out.println(ex);
    } catch (IOException ioe) {
        System.out.println(ioe);
    }
}

From source file:library.restore.java

private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
    String fileSeparator = System.getProperty("file.separator");
    JFileChooser chooser = new JFileChooser();
    //set JFileChooser default path
    chooser.setCurrentDirectory(new java.io.File("."));
    //set JFileChooser title
    chooser.setDialogTitle("open");
    //use as you need DIRECTORIES_ONLY, FILES_ONLY etc
    chooser.setFileSelectionMode(JFileChooser.FILES_ONLY);

    //false, if you want to view all kind of file type in JFileChooser
    //true, if you want to create FileFilter and open only file type you want to be view in JFileChooser
    chooser.setAcceptAllFileFilterUsed(false);

    if (chooser.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) {
        //write selected path inside JTextField
        jtfFileLocation4.setText(chooser.getSelectedFile().getPath() + fileSeparator);
        //students=getText(chooser.getSelectedFile().getPath()+fileSeparator);
        libfines = jtfFileLocation4.getText();
        System.out.println("" + libfines + "");
    } else {// w w w . j  a va2 s  . co m
        System.out.println("No Selection");
    }
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/library", "root",
                "123ERYcog.");
        con.setAutoCommit(false);
        PreparedStatement pstm = null;
        // this.fileurlp = this.student.replace("\\", "\\\\");
        FileInputStream input = new FileInputStream("" + libfines + "");
        POIFSFileSystem fs = new POIFSFileSystem(input);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            String pid = row.getCell(0).getStringCellValue();
            String adm = row.getCell(1).getStringCellValue();
            String amount = row.getCell(2).getStringCellValue();
            String day = row.getCell(3).getStringCellValue();
            String status = row.getCell(4).getStringCellValue();

            //String imgurl =imgurlK.replace("\\", "\\\\");
            String sql = "INSERT INTO libfines VALUES('" + pid + "','" + adm + "','" + amount + "','" + day
                    + "','" + status + "')";
            pstm = (PreparedStatement) con.prepareStatement(sql);
            pstm.execute();
            System.out.println("Import rows " + i);
        }
        con.commit();
        pstm.close();
        con.close();
        input.close();
        JOptionPane.showMessageDialog(null, "Success import excel to mysql table");
        System.out.println("Success import excel to mysql table");
    } catch (ClassNotFoundException e) {
        System.out.println(e);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, "error import excel to mysql table");
        System.out.println(ex);
    } catch (IOException ioe) {
        System.out.println(ioe);
    }

}

From source file:mainpackage.TestOutputExcel.java

public void updateFile(File file) {
    FileInputStream fIP = null;//from w w w.  j  a  va 2  s  .  c  o m
    try {
        fIP = new FileInputStream(file);
    } catch (FileNotFoundException ex) {
        System.out.println(ex.getMessage());
    }
    try {
        workbook = new HSSFWorkbook(fIP);
    } catch (IOException ex) {
        System.out.println(ex.getMessage());
    }
    HSSFSheet sheet = workbook.getSheet("Data");

    int rowCount = list.size();
    int cellCount = 2;
    for (int i = sheet.getLastRowNum(); i < rowCount; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < cellCount; j++) {
            Cell cell = row.createCell(j);
            if (j == 0) {
                cell.setCellValue(timeFormat.format(new Date()));
            } else if (j == 1) {
                cell.setCellValue(list.get(i));
            }
        }
    }
    list.clear();
    //        try {
    //            fIP.close();
    //        } catch (IOException ex) {
    //            Logger.getLogger(TestOutputExcel.class.getName()).log(Level.SEVERE, null, ex);
    //        }
}

From source file:mat.server.service.impl.XLSGenerator.java

/** Creates the xls row.
 * //from ww  w  . jav a  2s .  c  om
 * @param wkst - HSSFSheet.
 * @param values - String Array.
 * @param style - HSSFCellStyle.
 * @return HSSFRow. * */
protected final HSSFRow createXLSRow(final HSSFSheet wkst, final String[] values, final HSSFCellStyle style) {
    return createXLSRow(wkst, values, wkst.getLastRowNum() + 1, style);
}

From source file:net.chaosserver.timelord.data.ExcelDataReaderWriter.java

License:Open Source License

/**
 * Adds the list of notes associated with the sheet to end of the sheet.
 *
 * @param sheet the sheet to add notes to
 * @param noteList the list of notes to add to the sheet
 */// w w w. j av a  2s  .co m
private void createNotesRows(HSSFSheet sheet, List<String> noteList) {
    int notesRow = sheet.getLastRowNum() + 1;

    if (noteList != null) {
        Iterator<String> noteListIterator = noteList.iterator();
        while (noteListIterator.hasNext()) {
            notesRow++;
            String note = (String) noteListIterator.next();
            HSSFRow row = sheet.createRow(notesRow);
            HSSFCell cell = row.createCell((short) 0);
            cell.setCellValue(note);
        }
    }
}

From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java

License:Open Source License

public static int endOfRow(HSSFSheet sheet) {

    int lastRowNum = sheet.getLastRowNum();
    if (lastRowNum > 0) {
        return (lastRowNum + 1);
    } else {//ww w  .  jav a  2  s .com
        return sheet.getPhysicalNumberOfRows() > 0 ? 1 : 0;
    }
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Exportiert die bergebene excel-Datei in eine Liste mit zweidimensionalen Arrays fr jeweils
 * ein sheet in der excel-Datei.//from   w  w w  .  ja v a  2  s.  c  om
 *
 * @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(File excelSheet) throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

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

        int columns = sheet.getRow(0).getLastCellNum();
        String[][] excelSheetInTDArray = null;
        excelSheetInTDArray = new String[rows][columns];
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        excelSheetInTDArray[i][j] = "";
                    } else {
                        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);
    }
    return sheetList;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache 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/*from  w ww . 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(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    List<List<List<String>>> sl = new ArrayList<>();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        List<List<String>> excelSheetList = new ArrayList<>();
        for (int i = 0; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (null != row) {
                List<String> reihe = new ArrayList<>();
                for (int j = 0; j < columns; j++) {
                    HSSFCell cell = row.getCell(j);
                    if (null == cell) {
                        reihe.add("");
                    } else {
                        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);
    }
    return sl;
}

From source file:net.sourceforge.jaulp.export.excel.poi.ExportExcelUtils.java

License:Apache License

/**
 * Replace null cells into empty cells.// w ww. ja  va  2  s. c o  m
 *
 * @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(File excelSheet)
        throws IOException, FileNotFoundException {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelSheet));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    int numberOfSheets = wb.getNumberOfSheets();
    for (int sheetNumber = 0; sheetNumber < numberOfSheets; sheetNumber++) {
        HSSFSheet sheet = null;
        sheet = wb.getSheetAt(sheetNumber);
        int rows = sheet.getLastRowNum();
        int columns = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < rows; i++) {
            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;
}