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:upload.Parser.java

public void parseXSLX() {

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook;//www  . java 2 s.  c  o  m
    try {
        workbook = new XSSFWorkbook(in);

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

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getStringCellValue());
                    break;
                }
            }
        }
    } catch (IOException ex) {
        Logger.getLogger(Parser.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:us.physion.ovation.ui.editor.xls.XLSXReader.java

License:Open Source License

public static void load(File f, LoadHandler handler) throws IOException {
    try (FileInputStream fis = new FileInputStream(f)) {
        XSSFWorkbook workbook = new XSSFWorkbook(fis); // for xls HSSFWorkbook

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            List<String[]> entries = new ArrayList<>();

            Sheet sheet = workbook.getSheetAt(i);
            for (Row row : sheet) {
                List<String> rowData = new ArrayList<>();
                for (Iterator<Cell> cells = row.cellIterator(); cells.hasNext();) {
                    Cell cell = cells.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        rowData.add(cell.getStringCellValue().trim());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        rowData.add(Double.toString(cell.getNumericCellValue()));
                        break;
                    }/*from w  w w  .j  ava2s .c om*/
                }
                entries.add(rowData.toArray(new String[0]));
            }

            int columnCount = 0;
            for (String[] row : entries) {
                columnCount = Math.max(columnCount, row.length);
            }

            entries = reallocEntries(entries, columnCount);

            TabularData data = new TabularData(entries, getColumnNames(columnCount), f);

            handler.handle(sheet.getSheetName(), data);
        }
    }
}

From source file:usac.centrocalculo.data.LecturaURyS.java

public void readXlsx(File inputFile) {
    try {//from  w  w w.java2s.c  o m
        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();
            ArrayList<String> nodo = new ArrayList<>();
            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {

                case Cell.CELL_TYPE_BOOLEAN:
                    nodo.add(cell.toString());
                    //System.out.println(cell.getBooleanCellValue());
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    nodo.add(cell.toString());
                    //System.out.println(cell.getNumericCellValue());
                    break;

                case Cell.CELL_TYPE_STRING:
                    nodo.add(cell.toString());
                    //System.out.println(cell.getStringCellValue());
                    break;

                case Cell.CELL_TYPE_BLANK:
                    nodo.add("-");
                    break;

                default:
                    nodo.add(cell.toString());
                }
            }
            fillList(nodo);
        }
    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
}

From source file:util.DocumentFunction.java

public static String readXlsxFile(String filename) {
    StringBuilder text = new StringBuilder();
    try {//from   www  . jav a  2 s.  c  om
        FileInputStream file = new FileInputStream(new File(filename));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(i);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                boolean breakPoint = true;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        text.append(cell.getNumericCellValue() + " ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        text.append(cell.getStringCellValue() + " ");
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        breakPoint = false;
                        break;
                    }
                }
                if (breakPoint) {
                    text.append("\n");
                }
            }
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return text.toString();
}

From source file:util.DocumentFunction.java

public static Map<Boolean, List<ImportedStudent>> readStudentXlsxFile(String filename, int course_id) {
    //        StringBuilder text = new StringBuilder();
    Map<Boolean, List<ImportedStudent>> stMap = new TreeMap<>();
    List<ImportedStudent> exStudentList = new ArrayList<>();
    ImportedStudent st = null;/*  w w w  .j a va2  s .c  o  m*/
    try {
        FileInputStream file = new FileInputStream(new File(filename));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        boolean breakPoint = true;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(i);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            String email = null, firstname = null, lastname = null;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                int rownum = row.getRowNum();

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

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();

                    if (cell.getColumnIndex() == 0) {
                        email = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 1) {
                        firstname = cell.getStringCellValue();
                    } else if (cell.getColumnIndex() == 2) {
                        lastname = cell.getStringCellValue();
                    }
                }

                if (row.getRowNum() == 0) {
                    if (email.equalsIgnoreCase("Email") && firstname.equalsIgnoreCase("Firstname")
                            && lastname.equalsIgnoreCase("Lastname")) {
                        continue;
                    } else {
                        stMap.put(false, exStudentList);
                        breakPoint = false;
                        break;
                    }
                } else {
                    st = new ImportedStudent();
                    st.setCourse_id(course_id);
                    st.setEmail(email);
                    st.setFirstname(firstname);
                    st.setLastname(lastname);
                    exStudentList.add(st);
                }
                //                    System.out.println(email + "/" + firstname + "/" + lastname);
            }
        }
        if (breakPoint) {
            stMap.put(true, exStudentList);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return stMap;
}

From source file:util.DocumentFunction.java

public static String readXlsFile(String filename) {
    StringBuilder text = new StringBuilder();
    try {//  www .  j  a  va2 s . c  om
        FileInputStream file = new FileInputStream(new File(filename));

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

        //Get first/desired sheet from the workbook
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            HSSFSheet sheet = workbook.getSheetAt(i);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                boolean breakPoint = true;
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        text.append(cell.getNumericCellValue() + " ");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        text.append(cell.getStringCellValue() + " ");
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        breakPoint = false;
                        break;
                    }
                }
                if (breakPoint) {
                    text.append("\n");
                }
            }
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return text.toString();
}

From source file:Utilities.BatchInDJMSHelper.java

public void compareXlsxBatch(String xlsxFileName, Map<String, Map<String, String>> legitLvMap) {
    //Map<String, Map<String, String>> LegitLvMap = 
    File xlsxFile = new File(xlsxFileName);
    try {/*  ww  w. j ava  2s  .com*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            // List<String> keyList = new ArrayList<>(); //keep track info of each column
            while (it.hasNext()) {
                //keyList.add(it.next().getStringCellValue());   
                it.next();
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length) { // correct xlsx file                 
                int rowNum = 1;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    //row.getRowStyle();
                    rowNum++;
                }
                for (int i = 1; i < rowNum; i++) {
                    Row row = mySheet.getRow(i);
                    foregroundColorSetUp(row, myWorkBook, numCell, legitLvMap); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;

                String targetFile = null;
                if (xlsxFileName.contains(".xlsx")) {
                    targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
                } else {
                    targetFile = xlsxFileName + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();

            } else {
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(BatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:utilities.DebtMgmtBatchInDJMS.java

private void buildSSNMap(String xlsxFileName) {
    File xlsxFile = new File(xlsxFileName);
    try {/*from   w  ww.  j  ava  2s  .c om*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);

        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;

            DataFormatter df = new DataFormatter(); //for ssn

            // if (numCell == TITLE_LEN){  // correct xlsx file                 
            int rowNum = 1;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();

                //row.getRowStyle();
                Cell cell = row.getCell(SSN_INDEX);
                String ssn = df.formatCellValue(cell); //return ***-**-****
                ssn = GlobalVar.fullSSNgenerator(ssn);
                SSN_MAP.put(ssn, INITIAL_MSG); //initial ssn map .  value is for the status
            }
            //                } else {
            //                    JOptionPane.showMessageDialog(null, "Invalid Xlsx file!");
            //                }
        }
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:Utility.CSV_File_Generator.java

public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) {
    FileOutputStream out = null;/*w w  w .j a v  a 2 s  .  c o  m*/
    try {

        out = new FileOutputStream(new File(file_details("Excel_Traffic_Rows")));

        int col_index;
        //Create blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        //Create a blank sheet
        XSSFSheet intermediate = workbook.createSheet("Test Data");
        //Create row object
        Row row;
        XSSFRow intermediate_row;

        XSSFRow actual_row = original_sheet.getRow(index);

        //This data needs to be written (Object[])
        TreeMap<String, TreeMap<String, Cell>> row_map = new TreeMap<String, TreeMap<String, Cell>>();

        if (index == 0) {
            TreeMap<String, Cell> cols = new TreeMap<String, Cell>();

            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            int i = 1;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cols.put(Integer.toString(i++), cell);
            }
            row_map.put("0", cols);

            Set<String> keyid = row_map.get("0").keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = cols.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            row_map.clear();
        } else {
            int count = 0;
            while (count < index) {
                Iterator<Row> rowIterator = resultSheet.iterator();
                while (rowIterator.hasNext()) {
                    //Reading a row from the existing result sheet
                    TreeMap<String, Cell> data_row = new TreeMap<String, Cell>();
                    row = rowIterator.next();
                    Iterator<Cell> cell = row.cellIterator();
                    col_index = 0;
                    while (cell.hasNext()) {
                        Cell c = cell.next();
                        data_row.put(Integer.toString(col_index++), c);
                    }

                    row_map.put(Integer.toString(count), data_row);
                    count++;
                }
                //writing the row read into the new workbook(intermediate)
                Set<String> keyid = row_map.keySet();
                for (String key : keyid) {
                    int column_counter = 0;
                    intermediate_row = intermediate.createRow(Integer.parseInt(key));
                    TreeMap<String, Cell> map = row_map.get(key);
                    Set<String> row_data = map.keySet();
                    for (String cell_data : row_data) {
                        Cell original = map.get(cell_data);
                        Cell new_cell = intermediate_row.createCell(column_counter++);
                        switch (original.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            new_cell.setCellValue(original.getNumericCellValue());
                            break;
                        case Cell.CELL_TYPE_STRING:
                            new_cell.setCellValue(original.getStringCellValue());
                            break;
                        }
                    }
                }
            }
            XSSFRow temp = intermediate.createRow(index);
            Iterator<Cell> cellIterator = actual_row.cellIterator();
            TreeMap<String, Cell> required_data = new TreeMap<String, Cell>();

            int i = 0;
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                required_data.put(Integer.toString(i++), cell);
            }
            row_map.put(Integer.toString(index), required_data);

            required_data = row_map.get(Integer.toString(index));

            Set<String> keyid = required_data.keySet();
            int cellid = 0;
            for (String key : keyid) {
                Cell original = required_data.get(key);
                Cell cell = temp.createCell(cellid++);
                switch (original.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.setCellValue(original.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    cell.setCellValue(original.getStringCellValue());
                    break;
                }
            }
            workbook.write(out);
            out.flush();
            row_map.clear();
        }
        out.close();
    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Traffic Data is: " + index + " row.");
}

From source file:View.IHM_ListerDevoir.java

private void btImportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btImportActionPerformed

    JFileChooser choix = new JFileChooser();
    choix.showDialog(choix, null);/*from  w  w w . j  a v a2 s  .c om*/
    File f = choix.getSelectedFile();

    try {
        FileInputStream file = new FileInputStream(f);

        //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);

        // On copie les eleves de la classe dans notre collection archive
        DAO_Etudiant dao = new DAO_Etudiant(null);
        dao.archive(cbxClasse.getSelectedItem().toString());

        //Iterate through each rows one by one
        Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            org.apache.poi.ss.usermodel.Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();

                if (!cell.getStringCellValue().equals("NOM") && !cell.getStringCellValue().equals("Prnom")) {
                    String sNom = cell.getStringCellValue();
                    String sPrenom = "";

                    if (cellIterator.hasNext()) {
                        cell = cellIterator.next();
                        sPrenom = cell.getStringCellValue();
                    }

                    if (sNom != "" && sPrenom != "") {
                        Etudiant etud = new Etudiant(sNom, sPrenom, cbxClasse.getSelectedItem().toString());
                        System.out.println(etud.toString());
                        DAO_Etudiant dao_etud = new DAO_Etudiant(null);
                        dao_etud.create(etud);
                    }
                }
                //                    //Check the cell type and format accordingly
                //                    switch (cell.getCellType())
                //                    {
                //                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                //                            System.out.print(cell.getNumericCellValue() + " ");
                //                            break;
                //                        case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                //                            System.out.print(cell.getStringCellValue() + ";");
                //                            break;
                //                    }   
            }
        }
        file.close();
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }

}