Example usage for org.apache.poi.ss.usermodel Row cellIterator

List of usage examples for org.apache.poi.ss.usermodel Row cellIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. cellIterator

Introduction

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

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:net.creativeidesign.timekeeper_v1.util.TaskImport.java

public boolean doImport() {
    try {/*from w  ww.  j  a  v  a  2  s . com*/
        JFrame parentFrame = new JFrame();

        FileFilter filter = new FileNameExtensionFilter("MS Excel .xlsx", "xlsx");
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Please select an excel file to import from");
        fileChooser.setFileFilter(filter);

        int userSelection = fileChooser.showSaveDialog(parentFrame);
        File fileToSave = fileChooser.getSelectedFile();
        if (fileToSave == null)
            return false;
        String filePathName = fileToSave.getAbsolutePath();

        FileInputStream file = new FileInputStream(new File(filePathName));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        importedItems = new ArrayList<>();
        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        rowIterator.next();//skip first row [it should be the header]
        while (rowIterator.hasNext()) {
            ToDoItemModel tmpItem = new ToDoItemModel();
            int iCellCnt = 0;

            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                String cellStr = null;
                double cellNum = 0;
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cellNum = cell.getNumericCellValue();
                    System.out.print(cell.getNumericCellValue() + "\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellStr = cell.getStringCellValue();
                    System.out.print(cell.getStringCellValue() + "\t");
                    break;
                }//end switch (cell.getCellType())

                switch (iCellCnt) {
                case 0:
                    tmpItem.setiId((int) cellNum);
                    break;
                case 1:
                    tmpItem.setStrTitle(cellStr);
                    break;
                case 2:
                    tmpItem.setStrDescription(cellStr);
                    break;
                case 3:
                    tmpItem.setDtDateUntil(cellStr);
                    break;
                case 4:
                    tmpItem.setiCategory((int) cellNum);
                    break;
                }//end switch(iCellCnt)

                ++iCellCnt;
            }
            System.out.println("");
            importedItems.add(tmpItem);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "Wohoo, something went wrong, please try again later", "File Saved",
                JOptionPane.INFORMATION_MESSAGE);
    }

    return compareItemsInDB();
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void createHeading(Sheet sheet) {
    Row r = sheet.createRow(0);
    r.createCell(0).setCellValue("Lidnummer");
    r.createCell(1).setCellValue("Achternaam");
    r.createCell(2).setCellValue("Tussenvoegsel");
    r.createCell(3).setCellValue("Voornaam");
    r.createCell(4).setCellValue("Initialen");
    r.createCell(5).setCellValue("Geslacht");
    r.createCell(6).setCellValue("Straat");
    r.createCell(7).setCellValue("Adres");
    r.createCell(8).setCellValue("Postcode");
    r.createCell(9).setCellValue("Plaats");
    r.createCell(10).setCellValue("Telefoonnummer");
    r.createCell(11).setCellValue("Mobiel");
    r.createCell(12).setCellValue("Mail lid");
    r.createCell(13).setCellValue("Naam ouder/verzorger 1");
    r.createCell(14).setCellValue("Mail ouder/verzorger 1");
    r.createCell(15).setCellValue("Telefoonnummer ouder/verzorger 1");
    r.createCell(16).setCellValue("Naam ouder/verzorger 2");
    r.createCell(17).setCellValue("Mail ouder/verzorger 2");
    r.createCell(18).setCellValue("Telefoonnummer ouder/verzorger 2");
    r.createCell(19).setCellValue("Speltak");
    r.createCell(20).setCellValue("Functie");
    r.createCell(21).setCellValue("Geboortedatum");
    r.createCell(22).setCellValue("Functie startdatum");
    r.createCell(23).setCellValue("Overige informatie");

    Iterator<Cell> it = r.cellIterator();
    while (it.hasNext()) {
        Cell c = it.next();//from w w  w.ja v a2s  . com
        c.setCellStyle(headingStyle);
    }

    sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, 23));
}

From source file:nl.meine.scouting.solparser.writer.ExcelWriter.java

License:Open Source License

private void processPersonUpdates(Row newRow, Row oldRow) {
    String newSpeltak = newRow.getCell(NUM_SPELTAK_CELL).getStringCellValue();
    boolean isNew = oldRow == null;
    boolean isOvervlieger = false;
    if (!isNew) {
        String previousSpeltak = oldRow.getCell(NUM_SPELTAK_CELL).getStringCellValue();
        ;/*from   www .j ava 2s  . co m*/
        if (!previousSpeltak.equals(newSpeltak)) {
            isOvervlieger = true;
        }
    }

    for (Iterator<Cell> it = newRow.cellIterator(); it.hasNext();) {
        Cell newCell = it.next();
        if (isNew) {
            updateCellColor(newCell, COLOR_NEW);
        } else {
            int colIndex = newCell.getColumnIndex();
            Cell oldCell = oldRow.getCell(colIndex);
            String newValue = newCell.getStringCellValue();
            String oldValue = oldCell.getStringCellValue();
            if (isOvervlieger) {
                updateCellColor(newCell, COLOR_OVERVLIEGER);
            }
            if (!newValue.equals(oldValue) && colIndex != NUM_SPELTAK_CELL) {
                updateCellColor(newCell, COLOR_UPDATED);
            }

        }
    }
}

From source file:nl.rabobank.fixtures.aiep.testdata.traffic.LicensePlateDataLoader.java

public void fetchTestData() throws MalformedURLException, IOException {

    InputStream excelFileToRead = null;

    if (isTest) {
        excelFileToRead = getClass().getResourceAsStream("/aiep_testdata_fitnesse.xlsx");
    } else {//  w  w w . ja v a 2  s . c  om
        excelFileToRead = new URL(TestDataLoader.TESTDATAPATH).openStream();
    }

    XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead);
    XSSFSheet sheet = wb.getSheetAt(0);

    Iterator<Row> rows = sheet.rowIterator();
    boolean newRow = true;

    List<Row> myRowList = Lists.newArrayList(rows);

    if (this.testStubObject != null) {
        for (Row row : myRowList) {
            Iterator<Cell> cells = row.cellIterator();
            List<Cell> myRowCellsList = Lists.newArrayList(cells);

            for (Cell cell : myRowCellsList) {
                cell.setCellType(Cell.CELL_TYPE_STRING);

                if (cell.getStringCellValue().equals(this.testStubObject) && cell.getColumnIndex() == 0) {
                    newRow = false;
                }
                if (cell.getColumnIndex() == 1 && !newRow) {
                    setLicenseplate(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 2 && !newRow) {
                    setBrand(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 3 && !newRow) {
                    setModel(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 4 && !newRow) {
                    setType(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 5 && !newRow) {
                    setGear(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 6 && !newRow) {
                    setFuel(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 7 && !newRow) {
                    setProductionYear(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 8 && !newRow) {
                    setPurchaseYear(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 9 && this.testStubObject.startsWith("Motor") && !newRow) {
                    setSideCarValue(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 10 && this.testStubObject.startsWith("Trailer")) {
                    setChassisNumber(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 11 && !newRow) {
                    setWeight(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 12 && !newRow) {
                    setPurchaseValue(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 13 && !newRow) {
                    setInsuredInventory(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 14 && !newRow) {
                    setSameLicensePlateAsCar(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 15 && !newRow) {
                    setCatalogueValue(cell.getStringCellValue());
                }
                if (cell.getColumnIndex() == 16 && !newRow) {
                    setBodyShape(cell.getStringCellValue());
                }
            }
            if (!newRow) {
                break;
            }
        }
    }
    excelFileToRead.close();
}

From source file:npv.importer.XlsImporter.java

private void findTag(HSSFSheet sheet, String searchTag) {
    //looking for '#Ri' tag
    HSSFRow row = sheet.getRow(0);/*from  www. j av  a 2 s  .c o  m*/
    Iterator<Row> rowIterator = sheet.iterator();
    Iterator<Cell> cellIterator;
    boolean isFound = false;

    while (rowIterator.hasNext()) {
        if (!isFound) {
            Row rRow = rowIterator.next();
            cellIterator = rRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell rCell = cellIterator.next();
                if (rCell.getCellType() == Cell.CELL_TYPE_STRING && rCell.getStringCellValue().equals(tag)) {
                    rPosition[0] = rRow.getRowNum();
                    rPosition[1] = rCell.getColumnIndex();
                    isFound = true;
                    break;
                }
            }
        } else {
            break;
        }
    }
}

From source file:opisiame.model.Import_animateur_excel.java

public Import_animateur_excel(String adresse) throws IOException {
    //openning the document
    //System.out.print("appelle constructeur import animateur excel \n");
    nb_element = 0;/*ww  w .  j ava2  s  .  com*/
    ouverture_fichier(adresse);
    //System.out.print("je suppose que a doit marcher vu qu'il n'y a pas d'erreur \n");

    //reading the document
    Sheet sheet = classeur.getSheetAt(0);
    //creer un itrateur sur les colonnes
    Iterator<Row> iterator = sheet.iterator();

    while (iterator.hasNext()) {
        ++nb_element;
        Row row = iterator.next();
        Iterator<Cell> cell_iterator = row.cellIterator();

        Cell cell = cell_iterator.next();
        //Liste_id.add((int) cell.getNumericCellValue());
        //cell = cell_iterator.next();
        Liste_nom.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_prenom.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_login.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_mdp.add(cell.getStringCellValue());
    }

    /*for (int i = 0; i < Liste_nom.size(); ++i){
       //System.out.print(Liste_id.get(i) + " ");
       System.out.print(Liste_nom.get(i) + " ");
       System.out.print(Liste_prenom.get(i) + " ");
       System.out.print(Liste_login.get(i) + " ");
       System.out.print(Liste_mdp.get(i) + " \n");
    }*/

    /* while (cell_iterator.hasNext()) {
        Cell cell = cell_iterator.next();
                
                
            
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue() + " 1");
                System.out.print("\n");
                        
                break;
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue() + " 2");
                System.out.print("\n");
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(cell.getBooleanCellValue() + " 3");
                System.out.print("\n");
                break;
        }*/
    update_database();

}

From source file:opisiame.model.Import_eleve_excel.java

public Import_eleve_excel(String adresse) throws IOException {
    //openning the document
    //System.out.print("appelle constructeur import eleve excel \n");
    nb_element = 0;//  ww w  .  j  av  a  2  s.c om
    ouverture_fichier(adresse);
    //System.out.print("je suppose que a doit marcher vu qu'il n'y a pas d'erreur \n");

    //reading the document
    Sheet sheet = classeur.getSheetAt(0);
    //creer un itrateur sur les colonnes
    Iterator<Row> iterator = sheet.iterator();

    nb_element = 0;
    while (iterator.hasNext()) {
        ++nb_element;
        Row row = iterator.next();
        Iterator<Cell> cell_iterator = row.cellIterator();

        Cell cell = cell_iterator.next();

        Liste_Id.add((int) cell.getNumericCellValue());
        cell = cell_iterator.next();
        Liste_nom.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_prenom.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_filiere.add(cell.getStringCellValue());
        cell = cell_iterator.next();
        Liste_annee.add((int) cell.getNumericCellValue());
    }

    //test des sorties
    /* for (int i = 0; i < Liste_nom.size(); ++i) {
    System.out.print(Liste_nom.get(i) + " ");
    System.out.print(Liste_prenom.get(i) + " ");
    System.out.print(Liste_filiere.get(i) + " ");
    System.out.print(Liste_annee.get(i) + " \n");
    }*/
    update_database();

}

From source file:Opm_Package.OpenFileName.java

public List<String> readCommits(String file) throws Exception {
    // String excelFilePath = "Books.xlsx";
    //FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    List<String> lists = new ArrayList<>();
    Workbook workbook = readFileName(file);
    Sheet firstSheet = workbook.getSheetAt(1);
    Iterator<Row> iterator = firstSheet.iterator();

    while (iterator.hasNext()) {
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        int y = 0;
        while (cellIterator.hasNext()) {

            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                if (y > 7 & !cell.getStringCellValue().equals("-")) {
                    lists.add(cell.getStringCellValue());
                }/*from  w  w w .ja v a2s  .  c  om*/
                //System.out.print(cell.getStringCellValue());

                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                System.out.print(cell.getNumericCellValue());
                break;
            }
            // System.out.print(" - ");
            y++;
        }
        //System.out.println();
    }

    workbook.close();
    // inputStream.close();
    for (int x = 0; x < lists.size(); x++) {
        String[] splits = lists.get(x).split(":-");
        //System.out.println(lists.get(x)+"\t length = "+splits.length);
    }

    return lists;

}

From source file:Opm_Package.OpenFileName.java

public List<List<String>> readCommits2(String file, int count) throws Exception {
    // String excelFilePath = "Books.xlsx";
    //FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    List<String> lists = new ArrayList<>();
    List<String> datelists = new ArrayList<>();
    List<List<String>> pullslists = new ArrayList<>();
    List<List<String>> alllists = new ArrayList<>();

    List<String> prOpen = new ArrayList<>();
    List<String> prClosed = new ArrayList<>();
    List<String> isOpen = new ArrayList<>();
    List<String> isClosed = new ArrayList<>();
    List<String> forks = new ArrayList<>();
    List<String> watch = new ArrayList<>();

    Workbook workbook = readFileName(file);
    Sheet firstSheet = workbook.getSheetAt(count);
    Iterator<Row> iterator = firstSheet.iterator();
    int p = 0;//w w  w  . ja v a  2s  .  c o  m
    while (iterator.hasNext()) {
        p++;
        Row nextRow = iterator.next();
        Iterator<Cell> cellIterator = nextRow.cellIterator();
        int y = 0, d = 0;
        List<String> cList = new ArrayList<>();
        List<String> plist = new ArrayList<>();
        while (cellIterator.hasNext()) {

            Cell cell = cellIterator.next();

            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                if (y == 0 && p > 1) {
                    datelists.add(cell.getStringCellValue());
                }
                if (y > 7 && p > 1) {
                    d = 1;
                    lists.add(cell.getStringCellValue());
                }
                if (y > 8 && p > 1) {
                    datelists.add(datelists.get(datelists.size() - 1));
                }
                if (cell.getStringCellValue().equals(null)) {
                    System.out.print(y + "\t null");
                }
                if (y == 1 && p > 1) {
                    prOpen.add(cell.getStringCellValue());
                }
                if (y == 2 && p > 1) {
                    prClosed.add(cell.getStringCellValue());
                }
                if (y == 3 && p > 1) {
                    isOpen.add(cell.getStringCellValue());
                }
                if (y == 4 && p > 1) {
                    isClosed.add(cell.getStringCellValue());
                }
                if (y == 5 && p > 1) {
                    forks.add(cell.getStringCellValue());
                }
                if (y == 6 && p > 1) {
                    watch.add(cell.getStringCellValue());
                }
                cList.add(cell.getStringCellValue());
                System.out.print(y + "\t " + cell.getStringCellValue());

                break;
            case Cell.CELL_TYPE_BOOLEAN:
                // System.out.print(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                // System.out.print(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                //  System.out.print(y+"\t NoData ");
                break;

            }
            ///System.out.print(" - ");
            y++;
        }
        pullslists.add(plist);
        if (cList.size() == 8) {
            lists.add("-");
        }
        //System.out.print("\t\t "+pullslists+"\t\t"+1);
        System.out.println();
    }

    workbook.close();
    // inputStream.close();
    for (int x = 0; x < lists.size(); x++) {
        String[] splits = lists.get(x).split(":-");
        //System.out.println(lists.get(x)+"\t length = "+splits.length);
    }

    /// Add all the Lists to the new List
    alllists.add(lists);
    alllists.add(datelists);
    alllists.add(prOpen);
    alllists.add(prClosed);
    alllists.add(isOpen);
    alllists.add(isClosed);
    alllists.add(forks);
    alllists.add(watch);

    /// Return the lists to the Merger_Class  ...
    return alllists;

}

From source file:opn.greenwebs.FXMLDocumentController.java

@FXML
private void handleQuoteClick(MouseEvent event) {
    MouseButton butt = event.getButton();
    if (butt == MouseButton.PRIMARY) {
        if (event.getClickCount() == 2) {
            if (event.getSource() instanceof TableView) {
                TableView view = (TableView) event.getSource();
                if (view.getSelectionModel().getSelectedItem() instanceof Quote) {
                    Quote quote = (Quote) view.getSelectionModel().getSelectedItem();
                    File[] fileList = fQuoteDir
                            .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote()));
                    if (fileList.length > 0) {
                        try {
                            Desktop.getDesktop().open(fileList[0]);
                        } catch (IOException ex) {
                            Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null,
                                    ex);
                        }/*from   www.  j  a  va 2s  .  c  om*/
                    }
                }
            }
        } else if (event.getSource() instanceof TableView) {
            TableView view = (TableView) event.getSource();
            if (view.getSelectionModel().getSelectedItem() instanceof Quote) {
                Quote quote = (Quote) view.getSelectionModel().getSelectedItem();
                txtCustomer.setText(quote.getSspCustomer());
                txtPhone.setText(quote.getSspPhone());
                File[] fileList = fQuoteDir
                        .listFiles((File file) -> file.getName().startsWith(quote.getSspQuote()));
                if (fileList.length > 0) {
                    System.out.println("the filelist is bigger than 0");
                    try (FileInputStream fis = new FileInputStream(fileList[0]);
                            XSSFWorkbook book = new XSSFWorkbook(fis)) {
                        ItemData.clear();
                        XSSFSheet sheet = book.getSheet("Digital Version");
                        Iterator<Row> itr = sheet.rowIterator();
                        int nRow = 0;
                        mainWhile: while (itr.hasNext()) {
                            Row row = itr.next();
                            nRow++;
                            if (nRow >= 21) {
                                Iterator<Cell> cellIterator = row.cellIterator();
                                List lstItem = new ArrayList();
                                int nCell = 0;
                                while (cellIterator.hasNext()) {
                                    Cell cell = cellIterator.next();
                                    switch (cell.getCellType()) {
                                    case Cell.CELL_TYPE_STRING:
                                        lstItem.add(cell.getStringCellValue());
                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        lstItem.add(cell.getNumericCellValue());
                                        break;
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        lstItem.add(cell.getBooleanCellValue());
                                        break;
                                    case Cell.CELL_TYPE_BLANK:
                                        lstItem.add("");
                                        if (nCell == 0) {
                                            System.out.println("Quote Qty found a blank");
                                            break mainWhile;
                                        }

                                        break;
                                    case Cell.CELL_TYPE_FORMULA:
                                        lstItem.add(cell.getCellFormula());
                                        break;
                                    default:
                                    }
                                    nCell++;
                                }
                                insertData(lstItem, false);
                            } else if (nRow < 21) {

                                /*
                                getCellData(file, row, cell);
                                Row rowed = sheetStock.getRow(6);
                                Cell celled = rowed.getCell(10);
                                CellStyle cellStyle = celled.getCellStyle();
                                XSSFFont font = sheetStock.getWorkbook().createFont();
                                font.setFontHeight(14);
                                cellStyle.setFont(font);
                                celled.setCellValue(Date.from(instant));
                                celled.setCellStyle(cellStyle);
                                rowed = sheetStock.getRow(10);
                                celled = rowed.getCell(2);
                                        
                                inject(wb, Date.from(instant), 3, 14);
                                inject(wb, txtCustomer.getText(), 10, 2);
                                inject(wb, txtAddress.getText(), 11, 2);
                                inject(wb, txtCity.getText(), 12, 2);
                                inject(wb, txtProvince.getText(), 13, 2);
                                inject(wb, txtPhone.getText(), 14, 2);
                                inject(wb, txtContact.getText(), 15, 2);
                                inject(wb, txtFax.getText(), 14, 4);
                                inject(wb, txtEmail.getText(), 16, 2);
                                inject(wb, txtPO.getText(), 15, 4);
                                if (chkGST.isSelected()) {
                                inject(wb, "Y", 36, 8);
                                } else {
                                inject(wb, "N", 36, 8);
                                }
                                if (chkPST.isSelected()) {
                                inject(wb, "Y", 37, 8);
                                } else {
                                inject(wb, "N", 37, 8);
                                Data starts at row 21
                                Qty, Mfr, Sku, Descrip, Supplier, Suppart,Serial,SalePrice,EOS
                                        
                                 */
                            }

                        }
                        System.out.println("completely out of the while");
                    } catch (FileNotFoundException fe) {

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

                }
            }
        }
    }
}