Example usage for org.apache.poi.hssf.usermodel HSSFSheet 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:javaexecelprocess.JavaExecelProcess.java

public boolean isDBFormat() {
    boolean ret = true;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    List<Integer> fieldsType = getFieldsType();
    if (null == fieldsType) {
        ret = false;/*from w  ww.  jav  a  2s .  c o m*/
        return ret;
    }
    for (int i = iFirstRow + 1; i <= iLastRow; i++) {
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        for (int j = iFirstCol; j < iLastCol; j++) {
            HSSFCell cell = row.getCell(j);
            //                String cessStr = cell.toString();
            int cellType = cell.getCellType();
            //                if(HSSFCell.CELL_TYPE_BLANK == cellType
            //                        || HSSFCell.CELL_TYPE_ERROR == cellType){
            Integer colType = fieldsType.get(j);
            if (colType.intValue() != cellType) {
                ret = false;
                break;
            }
        }

        if (false == ret) {
            break;
        }
    }

    return ret;
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

private List<Integer> getFieldsType() {
    List<Integer> fieldsType = null;

    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    HSSFRow row = activeSheet.getRow(iFirstRow + 1);
    int iFirstCol = row.getFirstCellNum();
    int iLastCol = row.getLastCellNum();
    int iCols = row.getPhysicalNumberOfCells();
    if (0 != iCols) {
        fieldsType = new ArrayList<>();
    }/*from  w w  w  .j  a v a2  s.  c o  m*/
    for (int j = iFirstCol; j < iLastCol; j++) {
        HSSFCell cell = row.getCell(j);
        int cellType = cell.getCellType();
        fieldsType.add(cellType);
    }

    return fieldsType;
    //        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
}

---

From source file:javaexecelprocess.JavaExecelProcess.java

public void insertDataFromExcel() {
    String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'h111', 'h222');";
    //        String templateInsert = "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES ('2', 'h111', 'h222');";
    String sqlHead = "";
    sqlHead += "INSERT INTO `" + fields.get(0) + "` (`id`,";
    int i = 0;//from   ww w .  j a v  a  2s. com
    for (i = 0; i < fields.size() - 2; i++) {
        sqlHead += " `" + fields.get(i + 1) + "`,";
    }
    sqlHead += " `" + fields.get(i + 1) + "`) VALUES (NULL,";

    PreparedStatement pst = null;
    HSSFSheet activeSheet = wb.getSheetAt(0);
    int iFirstRow = activeSheet.getFirstRowNum();
    int iLastRow = activeSheet.getLastRowNum();
    for (i = iFirstRow + 1; i <= iLastRow; i++) {
        String sql = sqlHead;
        HSSFRow row = activeSheet.getRow(i);
        int iFirstCol = row.getFirstCellNum();
        int iLastCol = row.getLastCellNum();
        int j = 0;
        for (j = iFirstCol; j < iLastCol - 1; j++) {
            HSSFCell cell = row.getCell(j);
            String cessStr = cell.toString();
            sql += " '" + cessStr + "',";
        }
        HSSFCell cell = row.getCell(j);
        String cessStr = cell.toString();
        sql += " '" + cessStr + "');";
        try {
            pst = mysqlConn.prepareStatement(sql);
            pst.execute();
        } catch (SQLException ex) {
            Logger.getLogger(JavaExecelProcess.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("insert data exception.");
        }
    }
    //        sql += "INSERT INTO `mytbl1` (`id`, `myf1`, `myf2`) VALUES (NULL, 'f1111', 'f2222');";

}

---

From source file:jp.dbcls.bp3d.kaorif.visiblebody.nerves.VisibleBodyNerves.java

License:Open Source License

/**
 * ???(manuallyMapped.txt)?//from w  ww.jav a2s.c  o m
 * @throws Exception
 */
public void readManuallyMapped() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.MANUALLY_MAPPED));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet("manuallyMapped");

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        HSSFCell cell = null;

        String original = "";
        cell = row.getCell(0);
        if (cell != null) {
            original = cell.getRichStringCellValue().toString().trim();
        }

        String av = "";
        cell = row.getCell(1);
        if (cell != null) {
            av = cell.getRichStringCellValue().toString().trim();
        }

        String renamed = "";
        cell = row.getCell(2);
        if (cell != null) {
            renamed = cell.getRichStringCellValue().toString().trim();
        }

        String remark = "";
        for (int j = 3; j < row.getLastCellNum(); j++) {
            cell = row.getCell(j);
            if (cell != null) {
                remark += cell.getRichStringCellValue().toString().trim() + "\t";
            }
        }

        VisibleBodyManuallyMapped mm = new VisibleBodyManuallyMapped();
        mm.setOriginal(original);
        mm.setAv(av);
        mm.setRenamed(renamed);
        mm.setRemark(remark);

        this.manuallyMapped.add(mm);

    }
}

---

From source file:jp.dbcls.bp3d.kaorif.visiblebody.vessels.VisibleBodyVessels.java

License:Open Source License

/**
 * this.ORDERED_BY_KAORIF???//  w w w  .j  av a  2 s  . c  o m
 * 
 * @throws Exception
 */
public void readOrderedByKaorif() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(this.ORDERED_BY_KAORIF));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet("CirculatorySystem");

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        boolean isRequired = row.getCell(0).getBooleanCellValue();

        String en = "";
        HSSFCell cell = row.getCell(1);
        if (cell != null) {
            en = cell.getRichStringCellValue().toString().trim();
        }

        String av = "";
        cell = row.getCell(2);
        if (cell != null) {
            av = cell.getRichStringCellValue().toString().trim();
        }

        VisibleBodyEntry vbe = getEntry(en, av);
        if (vbe != null) {
            vbe.setOrderedByKaorif(isRequired);
        } else {
            System.out.println("readOrderedByKaorif not found=" + en + " " + av);
        }
    }

}

---

From source file:jp.dbcls.bp3d.ta.bits.TABits.java

License:Open Source License

/**
 * o101_TAJwFMA.xls?/*from  www.ja  v a2  s.co  m*/
 * @throws Exception
 */
public void readXls() throws Exception {
    POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(INFILE));
    HSSFWorkbook wb = new HSSFWorkbook(filein);
    HSSFSheet sheet = wb.getSheet(SHEET);

    for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
        HSSFRow row = sheet.getRow(i);

        int j = 0;
        String edit = "";
        if (row.getCell(j) != null) {
            edit = row.getCell(j).getRichStringCellValue().toString().trim();
        }
        j++;

        /** edit=DELETE????? **/
        if (edit.equals(TABitsEntry.DELETE)) {
            continue;
        }

        String taId = row.getCell(j++).getRichStringCellValue().toString().trim();

        double taTab = 0.0;
        int cellType = row.getCell(j).getCellType();
        if (cellType == Cell.CELL_TYPE_NUMERIC) {
            taTab = row.getCell(j++).getNumericCellValue();
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            taTab = Double.parseDouble(
                    row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(">", ""));
        } else {
            j++;
            System.out.println("[Error]@readXls.TA:Unknown CellType" + cellType);
        }

        String taKanji = row.getCell(j++).getRichStringCellValue().toString().trim();
        String taEn = row.getCell(j++).getRichStringCellValue().toString().trim().replace("[*]", "");

        String taKana = "";
        if (japaneseDisambiguatedForm2kana.containsKey(taKanji)) {
            taKana = japaneseDisambiguatedForm2kana.get(taKanji);
        } else if (japaneseEquivalentForm2kana.containsKey(taKanji)) {
            taKana = japaneseEquivalentForm2kana.get(taKanji);
        } else if (en2kana.containsKey(taEn)) {
            taKana = en2kana.get(taEn);
        }

        List<String> fmaIds = Arrays.asList(
                row.getCell(j++).getRichStringCellValue().toString().replaceAll(":", "").trim().split("[|]"));
        String fmaOBOName = row.getCell(j++).getRichStringCellValue().toString().trim().replaceAll(":", "");

        TABitsEntry template = createTAEntry();
        template.setTaId(taId);
        template.setTaTab(taTab);
        template.setTaEn(taEn);
        template.setTaKanji(taKanji);
        template.setTaKana(taKana);

        if (fmaOBOName.contains("NONE")) { // FMAOBONAME="NONE"????TA?fmaobo2??                  
            Set<FMAOBOEntry> hits = new HashSet<FMAOBOEntry>();
            for (String en : Arrays.asList(taEn.split("[;]"))) {
                en = en.replaceAll("[*]", "").trim();
                if (fmaobo.contains(en)) { //TA?fmaobo?????
                    hits.add(fmaobo.getByName(en));
                }
            }
            if (hits.size() == 0) { // FMA?????
                TABitsEntry ent = (TABitsEntry) template.clone();
                ent.setClassification(TABitsEntry.NOFMA);
                entries.add(ent);
            } else {
                for (FMAOBOEntry fmaEnt : hits) {
                    TABitsEntry ent = (TABitsEntry) template.clone();
                    ent.setFma(fmaEnt);
                    if (fmaIds.contains(fmaEnt.getId())) {
                        ent.setClassification(TABitsEntry.IDENTICAL); // FMA????????
                    } else {
                        ent.setClassification(TABitsEntry.NOTIDENTICAL); // FMA???????
                    }
                    entries.add(ent);
                }
            }
        } else { // FMAOBONAME="NONE"??????????
            for (String fmaId : fmaIds) {
                TABitsEntry ent = (TABitsEntry) template.clone();
                ent.setClassification(TABitsEntry.ORIGINAL);
                if (fmaobo.contains(fmaId)) {
                    ent.setFma(fmaobo.getById(fmaId));
                    ent.setClassification(TABitsEntry.ORIGINAL);
                } else {
                    ent.setClassification(TABitsEntry.NOFMAOBO2);
                    System.out.println("[Warning]@TABits.readXLs:" + fmaId + ":" + ent.getTaEn()
                            + " is not found in fmaobo2");
                }

                if (!edit.isEmpty()) {
                    ent.setEdit(edit);
                }

                entries.add(ent);
            }
        }
    }
}

---

From source file:learning.fisshplate.LearningPoiTest.java

License:Apache License

/**
 * ??// w  w w . j  a  va2 s.  co  m
 *
 * @throws Exception
 */
public void testInithialize() throws Exception {
    String filePath = "src/test/resources/LearningPOITest.xls";
    HSSFWorkbook input = setupInputWorkbook(filePath);
    HSSFSheet inputSheet = input.getSheetAt(0);

    for (int rowNo = 0; rowNo <= inputSheet.getLastRowNum(); rowNo++) {
        HSSFRow row = inputSheet.getRow(rowNo);
        if (row == null) {
            continue;
        }
        for (int columnNo = 0; columnNo <= row.getLastCellNum(); columnNo++) {
            HSSFCell cell = row.getCell(columnNo);
            if (cell == null) {
                continue;
            }
            HSSFRichTextString richText = new HSSFRichTextString(null);
            cell.setCellValue(richText);
            HSSFCellStyle style = input.createCellStyle();
            style.setFillPattern(HSSFCellStyle.NO_FILL);
            cell.setCellStyle(style);
        }
    }

    FileOutputStream fos = new FileOutputStream("target/outLearningTest.xls");
    input.write(fos);
    fos.close();
}

---

From source file:learning.fisshplate.LearningPoiTest.java

License:Apache License

public void testCreateRowTest() throws Exception {
    InputStream is = getClass().getResourceAsStream("/MapBuilderTest_template.xls");
    HSSFWorkbook wb = new HSSFWorkbook(is);
    HSSFSheet ws = wb.getSheetAt(0);
    for (int i = 0; i <= ws.getLastRowNum(); i++) {
        HSSFRow hssfRow = ws.getRow(i);//from  w ww  .  j a  v a 2s .  c o m
        if (hssfRow != null) {
            ws.removeRow(hssfRow);
        }
    }

    FileOutputStream os = new FileOutputStream("target/createRowTest.xls");
    wb.write(os);
    os.close();
    is.close();

}

---

From source file:library.restore.java

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
    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
        jtfFileLocation.setText(chooser.getSelectedFile().getPath() + fileSeparator);
        //students=getText(chooser.getSelectedFile().getPath()+fileSeparator);
        students = jtfFileLocation.getText();
        System.out.println("" + students + "");
    } else {/*from  w  w w .ja va  2  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("" + students + "");
        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 sid = row.getCell(0).getStringCellValue();
            String fname = row.getCell(1).getStringCellValue();
            String lastname = row.getCell(2).getStringCellValue();
            String form = row.getCell(3).getStringCellValue();
            String classs = row.getCell(4).getStringCellValue();
            String imgurlK = row.getCell(5).getStringCellValue();

            String imgurl = imgurlK.replace("\\", "\\\\");
            String sql = "INSERT INTO students VALUES('" + sid + "','" + fname + "','" + lastname + "','" + form
                    + "','" + classs + "','" + imgurl + "')";
            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 jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
    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
        jtfFileLocation1.setText(chooser.getSelectedFile().getPath() + fileSeparator);
        //students=getText(chooser.getSelectedFile().getPath()+fileSeparator);
        books = jtfFileLocation1.getText();
        System.out.println("" + books + "");
    } else {/* w  w  w.j  a  v  a 2s.  com*/
        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("" + books + "");
        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 id = row.getCell(0).getStringCellValue();
            String title = row.getCell(1).getStringCellValue();
            String ediion = row.getCell(2).getStringCellValue();
            String status = row.getCell(3).getStringCellValue();

            //String imgurl =imgurlK.replace("\\", "\\\\");
            String sql = "INSERT INTO books VALUES('" + id + "','" + title + "','" + ediion + "','" + 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);
    }

}

---

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