Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt

Introduction

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

Prototype

@Override
public HSSFSheet getSheetAt(int index) 

Source Link

Document

Get the HSSFSheet object at the given index.

Usage

From source file:jexcelcompi.JExcelCompi.java

static void HojaNceldaUnitaria(String nombre, int numHoja, int fila, int columna) {
    try {/*from  w  w w  . ja  v a 2 s  .  co m*/
        FileInputStream fis = new FileInputStream(new File(nombre));
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        HSSFSheet sheet = wb.getSheetAt(numHoja);
        System.out.println(wb.getSheetAt(numHoja).getRow(fila).getCell(columna));

    } catch (IOException ex) {
        Logger.getLogger(JExcelCompi.class.getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:jexcelcompi.JExcelCompi.java

public void ejemplo() throws FileNotFoundException, IOException {
    FileInputStream fis = new FileInputStream(new File("hoja2.xls"));
    HSSFWorkbook wb = new HSSFWorkbook(fis);
    HSSFSheet sheet = wb.getSheetAt(0);
    FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
    int x = 0;//  ww w.j  av a2  s  .co m
    for (Row row : sheet) {
        for (Cell cell : row) {
            switch (formulaEvaluator.evaluateInCell(cell).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: {
                System.out.println(cell.getNumericCellValue() + "\t\t");

            }
                //case Cell.CELL_TYPE_STRING:
                //System.out.println(cell.getStringCellValue()+"\t\tcadena");    
                x++;
            }

        }
        System.out.println();
    }

}

---

From source file:jschsftp.JSCHsftp.java

public static void main(String[] args) throws Exception {
    JSCHsftp mJSCHsftp = new JSCHsftp();
    mJSCHsftp.createConnection();//from   ww w. j  av  a  2  s .  co m
    FileInputStream fin = new FileInputStream(strXlsPath);
    HSSFWorkbook myWorkBook = new HSSFWorkbook(fin);
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator<Row> rowIterator = mySheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        String strFolderName = getCellValue(row.getCell(FOLDER_NAME_INDEX));
        System.out.println("Home Directory >> " + mJSCHsftp.mChannelSftp.getHome());
        System.out.println("Present Directory >> " + mJSCHsftp.mChannelSftp.pwd());
        if (mJSCHsftp.mChannelSftp != null)
            mJSCHsftp.startTransfer(mJSCHsftp.mChannelSftp, "./" + strFolderName, strLocalPath);
    }
    mJSCHsftp.closeConnections();
}

---

From source file:kupkb_experiments.ExperimentSpreadSheetParser.java

License:Open Source License

public ExperimentSpreadSheetParser(File file) {

    keyValue = new HashMap<String, List<Cell>>();

    compoundAttributeToColumn = new HashMap<String, Integer>();

    String expId = "exp_" + String.valueOf(System.currentTimeMillis());
    String analysisId = "analysis_" + String.valueOf(System.currentTimeMillis());
    roleCell = new ArrayList<Cell>();

    workbookManager = new WorkbookManager();
    InputStream inputStream = null;
    try {//  ww w .  j a v a 2  s .co m
        workbookManager.loadWorkbook(file);

        workbook = workbookManager.getWorkbook();
        validationManager = workbookManager.getOntologyTermValidationManager();

        inputStream = file.toURI().toURL().openStream();
        HSSFWorkbook workbook = new HSSFWorkbook(new BufferedInputStream(inputStream));

        this.sheet = workbook.getSheetAt(0);

        int lastRow = sheet.getLastRowNum();
        for (int x = 0; x <= lastRow; x++) {
            HSSFRow row = sheet.getRow(x);
            if (row != null) {
                firstPass(row);
            }
        }

    } catch (IOException e) {
        e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
    }

    if (!keyValue.containsKey(SpreadhseetVocabulary.EXPERIMENT_ID.getKeyName())) {
        System.err.println("Didn't find an experiment id in the spreadsheet");
        System.exit(0);
    } else {

        Cell c = keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ID.getKeyName()).get(0);
        String texpId = getValueForKey(c);
        if (!texpId.equals("")) {
            expId = texpId;
        }

        experiment = new KUPExperiment(expId);

        if (experiment == null) {
            System.err.println("Can't create experiment");
            System.exit(0);
        } else {

            if (keyValue.get(SpreadhseetVocabulary.COMPOUND_LIST.getKeyName()) != null) {
                String desc = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.COMPOUND_LIST.getKeyName()).get(0));
                if (!desc.equals("")) {
                    experiment.setListType(desc);
                }
            }

            if (keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ASSAY.getKeyName()) != null) {
                String desc = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.EXPERIMENT_ASSAY.getKeyName()).get(0));
                if (!desc.equals("")) {
                    experiment.setAssayType(desc);
                }
            }

            if (keyValue.get(SpreadhseetVocabulary.PRE_ANALYTICAL.getKeyName()) != null) {
                String desc = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.PRE_ANALYTICAL.getKeyName()).get(0));
                if (!desc.equals("")) {
                    experiment.setPreAnalyticalTechnuique(desc);
                }
            }

            if (keyValue.get(SpreadhseetVocabulary.ANALYSIS_TYPE.getKeyName()) != null) {
                String desc = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.ANALYSIS_TYPE.getKeyName()).get(0));
                if (!desc.equals("")) {
                    experiment.setAnalysisType(desc);
                }
            }
        }
    }

    // now go through the analysis roles
    Set<KUPAnnotation> annotations = new HashSet<KUPAnnotation>();
    String uniqueString = "";
    int rolecounter = 0;
    for (Cell currentRole : roleCell) {

        // search from current cell down until you get to the next role
        int rowIndex = currentRole.getRowIndex();
        // first is the Role

        uniqueString = "_" + String.valueOf(System.currentTimeMillis() + rolecounter);
        analysisId = expId + uniqueString;

        KUPAnnotation annotation = new KUPAnnotation(analysisId);
        String roleValue = getValueForKey(currentRole);
        if (roleValue.equals("")) {
            rolecounter++;
            continue;
        }
        annotation.setRole(roleValue);
        rowIndex++;

        // now keep going until you find the next role
        Set<String> qualities = new HashSet<String>();
        Set<String> bioMaterials = new HashSet<String>();

        while (keepGettingRole(rowIndex)) {
            HSSFRow currentRow = sheet.getRow(rowIndex);
            // get the first cell

            Cell cell = currentRow.getCell(0);
            System.err.println(cell.getStringCellValue());
            if (cell.getStringCellValue().toLowerCase()
                    .equals(SpreadhseetVocabulary.EXPERIMENT_CONDITION.getKeyName())) {
                String t = getValueForKey(cell);
                IRI iri = lookupId(cell, t);
                if (iri != null) {
                    t = iri.toString();
                }

                annotation.setCondition(t);
                System.out.println("Setting exp condition: " + t);
            } else if (cell.getStringCellValue().toLowerCase()
                    .equals(SpreadhseetVocabulary.SPECIES.getKeyName())) {
                String t = getValueForKey(cell);
                IRI iri = lookupId(cell, t);
                if (iri != null) {
                    t = iri.toString();
                }

                annotation.setTaxonomy(t);
                System.out.println("Setting taxonomy: " + t);
            } else if (cell.getStringCellValue().toLowerCase()
                    .equals(SpreadhseetVocabulary.DISEASE.getKeyName())) {
                String t = getValueForKey(cell);
                String[] diseaseValues = t.split("\\s*\\|\\s*");
                Set<String> diseasesSet = new HashSet<String>();
                for (String s : diseaseValues) {
                    s = s.trim();
                    IRI iri = lookupId(cell, s);
                    if (iri != null) {
                        s = iri.toString();
                    }
                    System.out.println("Setting disease: " + s);
                    diseasesSet.add(s);
                }
                annotation.getHasDisease().addAll(diseasesSet);
            } else if (cell.getStringCellValue().toLowerCase()
                    .equals(SpreadhseetVocabulary.BIOMATERIAL.getKeyName())) {

                String desc = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.BIOMATERIAL.getKeyName()).get(rolecounter));
                String[] values = desc.split("\\s*\\|\\s*");
                for (String s : values) {
                    s = s.trim();

                    IRI iri = lookupId(cell, s);
                    if (iri != null) {
                        s = iri.toString();
                    }
                    System.out.println("Setting biomaterial: " + s);

                    bioMaterials.add(s);
                }
            } else if (cell.getStringCellValue().toLowerCase()
                    .equals(SpreadhseetVocabulary.EXPERIMENT_DESCRIPTION.getKeyName())) {
                String description = getValueForKey(
                        keyValue.get(SpreadhseetVocabulary.EXPERIMENT_DESCRIPTION.getKeyName()).get(0));
                if (!description.equals("")) {
                    System.out.println("Setting description: " + description);
                    experiment.setAssayDescription(description);
                }
            } else {
                String t = getValueForKey(cell);
                if (!t.equals("")) {
                    IRI iri = lookupId(cell, t);
                    if (iri != null) {
                        t = iri.toString();
                    }
                    System.out.println("Setting quality: " + t);

                    qualities.add(t);
                }
                // the rest are qualities

            }
            rowIndex++;
        }
        annotation.getBioMaterial().addAll(bioMaterials);
        annotation.getQualities().addAll(qualities);

        annotations.add(annotation);
        rolecounter++;

    }

    // finally parse the compound lists
    CompoundList comList = new CompoundList(expId + uniqueString);
    for (int r = compoundListStart; r <= sheet.getLastRowNum(); r++) {

        if (sheet.getRow(r) == null) {
            continue;
        }

        CompoundList.ListMember listMember = comList.newListMember();
        //            Cell firstCell = sheet.getRow(r).getCell(1);
        //            if (firstCell != null) {
        //                if (!firstCell.getStringCellValue().equals("")) {

        for (String key : compoundAttributeToColumn.keySet()) {

            if (key.equals(SpreadhseetVocabulary.GENE_SYMBOL.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setGeneSymbol(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.GENE_ID.getKeyName())
                    || key.equals(SpreadhseetVocabulary.ENTREZ_GENE_ID.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null) {

                    String s = cell.getStringCellValue();

                    if (s.contains("E")) {
                        String tmps = s.substring(s.indexOf("E"));
                        s = s.replace(tmps, "");
                        s = s.replace(".", "");
                    } else if (s.endsWith(".0")) {
                        s = s.replace(".0", "");
                    }
                    listMember.setGeneId(s);
                }
            }
            if (key.equals(SpreadhseetVocabulary.UNIPROT_ID.getKeyName())
                    || key.equals(SpreadhseetVocabulary.UNIPROT_ACC.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setUniprotID(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.HMDB_ID.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setHmdbid(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.MICROCOSM.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setMicrocosmid(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.EXPRESSION_STRENGTH.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setExpressionStrength(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.DIFFERENTIAL.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setDifferential(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.RATIO.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setRatio(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.P_VALUE.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setPValue(cell.getStringCellValue());
            }
            if (key.equals(SpreadhseetVocabulary.FDR.getKeyName())) {
                // get the value in the cell
                int col = compoundAttributeToColumn.get(key);
                Cell cell = sheet.getRow(r).getCell(col);
                if (cell != null)
                    listMember.setFdrValue(cell.getStringCellValue());
            }
        }
        comList.getMembers().add(listMember);
        //                }
        //            }
    }

    KUPAnalysis analysis = new KUPAnalysis(analysisId);
    analysis.getCompoundList().add(comList);
    analysis.getAnnotations().addAll(annotations);
    experiment.getAnalysis().add(analysis);

}

---

From source file:learning.fisshplate.LearningPoiTest.java

License:Apache License

/**
 * ??//  w  w w. ja  v  a  2 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);//www. j a  v  a 2 s. 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 {//w w  w . ja  va  2s  . 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("" + 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 {//from  w  w  w  .  ja  va 2  s  . 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);
    }

}

---

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 {/*from  ww  w  . jav a 2 s. 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("" + 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  ww . j a va 2  s.  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("" + 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);
    }
}

---

• «
• 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
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »