Example usage for org.apache.poi.ss.usermodel Sheet rowIterator

List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. rowIterator

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet rowIterator.

Prototype

Iterator<Row> rowIterator();

Source Link

Document

Returns an iterator of the physical rows

Usage

From source file:ket_noi_excel.ket_noi_excel_cd.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {/*w  w w .  ja  v  a  2  s  .co  m*/
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableCD
                    + " (`Tn SP`, `M SP`, `Tn Ca S`, `Tn Nhc S`, "
                    + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)"
                    + " VALUES (?,?,?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else if (i == 6 || i == 11)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}

From source file:ket_noi_excel.ket_noi_excel_dvd.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {/*from   w  w  w .  ja v  a2  s .  c o m*/
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableDVD
                    + " (`Tn SP`, `M SP`, `Tn Din Vin`, `Tn ?o Din`, "
                    + "`Gi Bn`, `S Lng`, `Th Loi`, `Nm Pht Hnh`, `Ngy Nhp`, `S Phiu`, `Chit Khu`)"
                    + " VALUES (?,?,?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else if (i == 6 || i == 11)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}

From source file:ket_noi_excel.ket_noi_excel_sach.java

public void loadFile(String path, boolean isXLS) {
    PreparedStatement pst = null;
    System.out.println("? ch?n");
    try {/*from  w w  w.  jav  a 2  s  .  c o m*/
        org.apache.poi.ss.usermodel.Sheet sheet = null;
        if (isXLS == true) {
            org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        } else {
            org.apache.poi.ss.usermodel.Workbook workbook = new XSSFWorkbook(new FileInputStream(path));
            sheet = workbook.getSheetAt(0);
        }
        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
            Row row = rit.next();
            int i = 1;
            String query = "INSERT INTO " + tableSach + " (`Tn SP`, `M SP`,"
                    + " `Tn T/G`, `Th Loi`,`Gi Bn`, `S Lng`, `Ngy Nhp`,  "
                    + "`S Phiu`, `Chit Khu`) VALUES (?,?,?,?,?,?,?,?,?);";
            pst = conn.prepareStatement(query);
            for (Iterator<org.apache.poi.ss.usermodel.Cell> cit = row.cellIterator(); cit.hasNext();) {
                org.apache.poi.ss.usermodel.Cell cell = cit.next();
                cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                if (i == 6 || i == 9)
                    pst.setInt(i, Integer.parseInt(cell.getRichStringCellValue().toString()));
                else if (i == 5)
                    pst.setDouble(i, Double.parseDouble(cell.getRichStringCellValue().toString()));
                else
                    pst.setString(i, cell.getRichStringCellValue().toString());
                i++;
                if (i >= 12)
                    break;
            }
            if (pst.executeUpdate() > 0) {
                System.out.println("Thm thnh cng");
            } else {
                System.out.println("Li khi thm sn phm\n");
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    } catch (SQLException ex) {
        Logger.getLogger(ket_noi_excel_cd.class.getName()).log(Level.SEVERE, null, ex);
    }
    JOptionPane.showMessageDialog(null, "? thm d liu thnh cng");
}

From source file:Logica.L_Exel.java

public String Importar(File archivo, JTable tablaD) {
    String respuesta = "Revisr";
    DefaultTableModel model = new DefaultTableModel();
    tablaD.setModel(model);//from   w w w .j  a  v  a2 s .c  o  m
    try {
        wb = WorkbookFactory.create(new FileInputStream(archivo));
        Sheet Hoja = wb.getSheetAt(0);
        Iterator filaIterator = Hoja.rowIterator();
        int indiceFila = -1;
        while (filaIterator.hasNext()) {
            indiceFila++;
            Row fila = (Row) filaIterator.next();
            Iterator columnaIterator = fila.cellIterator();
            Object[] ListaColumna = new Object[7];
            int indicecolumna = -1;
            while (columnaIterator.hasNext()) {
                indicecolumna++;
                Cell celda = (Cell) columnaIterator.next();
                if (indiceFila == 0) {
                    model.addColumn(celda.getStringCellValue());
                } else {
                    if (celda != null) {
                        switch (celda.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            ListaColumna[indicecolumna] = (int) Math.round(celda.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            ListaColumna[indicecolumna] = celda.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            ListaColumna[indicecolumna] = celda.getBooleanCellValue();
                            break;
                        default:
                            ListaColumna[indicecolumna] = celda.getDateCellValue();
                            break;
                        }
                    }
                }
            }
            if (indiceFila != 0)
                model.addRow(ListaColumna);
        }
        respuesta = "Funciona";
    } catch (Exception e) {
    }
    return respuesta;
}

From source file:Model.ExcelModel.java

public String ImportFile(File fileImported, JTable dataTable) {
    String importResult = "System couldn't import the file";

    DefaultTableModel loadModel = new DefaultTableModel();
    dataTable.setModel(loadModel);/*from w w w  .  j ava2 s . c o m*/
    dataTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

    try {
        innerWorkbook = WorkbookFactory.create(new FileInputStream(fileImported));
        Sheet sheetTemporal = innerWorkbook.getSheetAt(0);
        Iterator rowIterator = sheetTemporal.rowIterator();

        int rowIndex = -1;
        while (rowIterator.hasNext()) {
            rowIndex++;

            Row temporalRow = (Row) rowIterator.next();
            Iterator columnIterator = temporalRow.cellIterator();

            Object[] columnList = new Object[12];

            int columnIndex = -1;
            while (columnIterator.hasNext()) {
                columnIndex++;
                Cell temporalCell = (Cell) columnIterator.next();
                if (rowIndex == 0)
                    loadModel.addColumn(temporalCell.getStringCellValue());
                else {
                    if (temporalCell != null) {
                        switch (temporalCell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            columnList[columnIndex] = (int) Math.round(temporalCell.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            columnList[columnIndex] = temporalCell.getStringCellValue();
                            break;
                        default:
                            columnList[columnIndex] = temporalCell.getDateCellValue();
                            break;
                        }//End Switch/Case get.CellType
                    } //End temporalCell!=null condition
                } //End Else rowIndex==0
            } //End columnIterator
            if (rowIndex != 0)
                loadModel.addRow(columnList);
        } //End rowIterator

        importResult = "Successful Import";

    } catch (Exception e) {
    }
    return importResult;

}

From source file:Modelo.ModeloExcel.java

public String Importar(File archivo, JTable tablaD) {
    String respuesta = "No se pudo realizar la importacin.";
    DefaultTableModel modeloT = new DefaultTableModel();
    tablaD.setModel(modeloT);//from   ww w  .  j  a  v  a 2s . co m
    try {
        wb = WorkbookFactory.create(new FileInputStream(archivo));
        Sheet hoja = wb.getSheetAt(0);
        Iterator filaIterator = hoja.rowIterator();
        int indiceFila = -1;
        while (filaIterator.hasNext()) {
            indiceFila++;
            Row fila = (Row) filaIterator.next();
            Iterator columnaIterator = fila.cellIterator();
            Object[] listaColumna = new Object[5];
            int indiceColumna = -1;
            while (columnaIterator.hasNext()) {
                indiceColumna++;
                Cell celda = (Cell) columnaIterator.next();
                if (indiceFila == 0) {
                    modeloT.addColumn(celda.getStringCellValue());
                } else {
                    if (celda != null) {
                        switch (celda.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            listaColumna[indiceColumna] = (int) Math.round(celda.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            listaColumna[indiceColumna] = celda.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            listaColumna[indiceColumna] = celda.getBooleanCellValue();
                            break;
                        default:
                            listaColumna[indiceColumna] = celda.getDateCellValue();
                            break;
                        }
                    }
                }
            }
            if (indiceFila != 0)
                modeloT.addRow(listaColumna);
        }
        respuesta = "Importacin exitosa";
    } catch (Exception e) {
    }
    return respuesta;
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readAdditionalMetadata(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Metadatos Adicionales");
    DateFormat dateFormatA = new SimpleDateFormat("MM/dd/yyyy");
    DateFormat dateFormatB = new SimpleDateFormat("yyyy-MM-dd");
    eml.setHierarchyLevel(readCellValue(sheet.getRow(5).getCell(1)));
    eml.setLogoUrl(readCellValue(sheet.getRow(7).getCell(1)));
    try {/*  w ww. j  a va 2 s.c  o  m*/
        if (readCellValue(sheet.getRow(5).getCell(4)).matches("\\d{4}-\\d{2}-\\d{2}")) {
            eml.setPubDate(dateFormatB.parse(readCellValue(sheet.getRow(5).getCell(4))));
        } else if (readCellValue(sheet.getRow(5).getCell(4)).matches("\\d{2}/\\d{2}/\\d{4}")) {
            eml.setPubDate(dateFormatA.parse(readCellValue(sheet.getRow(5).getCell(4))));
        } else {
            throw new InvalidFormatException("Error al procesar fecha inicial y final en cobertura temporal: ");
        }
    } catch (ParseException e) {
        throw new InvalidFormatException("Error al procesar fecha inicial y final en cobertura temporal: " + e);
    }
    eml.setPurpose(readCellValue(sheet.getRow(9).getCell(1)));
    switch (readCellValue(sheet.getRow(11).getCell(1))) {
    case "Ningna licencia seleccionada":
        eml.setIntellectualRights(readCellValue(sheet.getRow(12).getCell(1)));
        break;
    case "Creative Commons CCZero":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.cczero.text"));
        break;
    case "Open Data Commons Public Domain Dedication and Licence (PDDL)":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.pddl.text"));
        break;
    case "Open Data Commons Attribution License":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.odcby.text"));
        break;
    case "Open Data Commons Open Database License (ODbL)":
        eml.setIntellectualRights(baseAction.getText("eml.intellectualRights.license.odbl.text"));
        break;
    default:
        throw new InvalidFormatException("El tipo de licencia elegida es invlida.");
    }
    eml.setAdditionalInfo(readCellValue(sheet.getRow(14).getCell(1)));
    List<String> alternateIdentifiers = new ArrayList<String>();
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Identificador Alterno:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()) {
                alternateIdentifiers.add(readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
            }
        }
    }
    eml.setAlternateIdentifiers(alternateIdentifiers);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readAssociatedParties(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Partes Asociadas");
    Iterator<Row> rowIterator = sheet.rowIterator();
    Agent agent;/*  w w w  .jav  a 2  s  .co m*/
    Address address;
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Nombre:")) {
            agent = new Agent();
            agent.setFirstName(
                    readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setLastName(
                    readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setPosition(
                    readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setOrganisation(
                    readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setPhone(
                    readCellValue(sheet.getRow(row.getRowNum() + 9).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setEmail(
                    readCellValue(sheet.getRow(row.getRowNum() + 11).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setHomepage(
                    readCellValue(sheet.getRow(row.getRowNum() + 11).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            agent.setRole(
                    readCellValue(sheet.getRow(row.getRowNum() + 13).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address = new Address();
            address.setAddress(
                    readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address.setCity(
                    readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            address.setProvince(
                    readCellValue(sheet.getRow(row.getRowNum() + 7).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            address.setCountry(
                    readCellValue(sheet.getRow(row.getRowNum() + 7).getCell(4, Row.CREATE_NULL_AS_BLANK)));
            address.setPostalCode(
                    readCellValue(sheet.getRow(row.getRowNum() + 9).getCell(1, Row.CREATE_NULL_AS_BLANK)));
            agent.setAddress(address);
            eml.addAssociatedParty(agent);
        }
    }
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readCitations(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Referencias");
    Citation citation = new Citation();
    citation.setIdentifier(readCellValue(sheet.getRow(5).getCell(1)));
    citation.setCitation(readCellValue(sheet.getRow(7).getCell(1)));
    eml.setCitation(citation);/*from ww w . j  a v  a 2  s . c o  m*/
    BibliographicCitationSet val = new BibliographicCitationSet();
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Identificacin de la Referencia:")) {
            if (!readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)).isEmpty()
                    || !readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)).isEmpty()) {
                val.add(readCellValue(sheet.getRow(row.getRowNum() + 3).getCell(1)),
                        readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1)));
            }
        }
    }
    eml.setBibliographicCitationSet(val);
}

From source file:net.sibcolombia.sibsp.service.portal.implementation.ResourceManagerImplementation.java

License:Creative Commons License

private void readCollectionData(Eml eml, Workbook template) throws InvalidFormatException {
    Sheet sheet = template.getSheet("Datos de la Coleccin");
    eml.setCollectionName(readCellValue(sheet.getRow(5).getCell(1)));
    eml.setCollectionId(readCellValue(sheet.getRow(5).getCell(4)));
    eml.setParentCollectionId(readCellValue(sheet.getRow(7).getCell(1)));
    eml.setSpecimenPreservationMethod(readCellValue(sheet.getRow(7).getCell(4)));
    Iterator<Row> rowIterator = sheet.rowIterator();
    Row row;/*  w w  w.  j  a  va  2 s .  c  om*/
    List<JGTICuratorialUnit> jgtiCuratorialUnits = new ArrayList<JGTICuratorialUnit>();
    JGTICuratorialUnit jgtiCuratorialUnit;
    while (rowIterator.hasNext()) {
        row = rowIterator.next();
        if (readCellValue(sheet.getRow(row.getRowNum()).getCell(1, Row.CREATE_NULL_AS_BLANK))
                .equalsIgnoreCase("Tipo de Mtodo:")) {
            switch (readCellValue(sheet.getRow(row.getRowNum() + 1).getCell(1))) {
            case "Rango de conteo":
                jgtiCuratorialUnit = new JGTICuratorialUnit();
                jgtiCuratorialUnit.setRangeStart(
                        (int) sheet.getRow(row.getRowNum() + 5).getCell(2).getNumericCellValue());
                jgtiCuratorialUnit
                        .setRangeEnd((int) sheet.getRow(row.getRowNum() + 5).getCell(4).getNumericCellValue());
                jgtiCuratorialUnit.setUnitType(readCellValue(sheet.getRow(row.getRowNum() + 5).getCell(6)));
                jgtiCuratorialUnits.add(jgtiCuratorialUnit);
                break;
            case "Conteo con incertidumbre":
                jgtiCuratorialUnit = new JGTICuratorialUnit();
                jgtiCuratorialUnit
                        .setRangeMean((int) sheet.getRow(row.getRowNum() + 8).getCell(2).getNumericCellValue());
                jgtiCuratorialUnit.setUncertaintyMeasure(
                        (int) sheet.getRow(row.getRowNum() + 8).getCell(4).getNumericCellValue());
                jgtiCuratorialUnit.setUnitType(readCellValue(sheet.getRow(row.getRowNum() + 8).getCell(6)));
                jgtiCuratorialUnits.add(jgtiCuratorialUnit);
                break;
            }
        }
    }
    eml.setJgtiCuratorialUnits(jgtiCuratorialUnits);
}