Example usage for org.apache.poi.xssf.usermodel XSSFSheet iterator

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet iterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. iterator

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet iterator.

Prototype

@Override
public Iterator<Row> iterator()

Source Link

Document

Alias for #rowIterator() to allow foreach loops

Usage

From source file:tools.parsing.ExcelReader.java

public void readExcel() {
    try {/*from  w w w  .jav  a  2 s .co m*/
        FileInputStream file = new FileInputStream(new File(this.filePath));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            Comment comment = new Comment();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getNumericCellValue() + "");
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getNumericCellValue() + "");
                    else
                        comment.setComment(cell.getNumericCellValue() + "");
                    break;
                case Cell.CELL_TYPE_STRING:
                    if (cell.getColumnIndex() == 0)
                        comment.setTopic(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 1)
                        comment.setCode(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 2)
                        comment.setAuthor(cell.getStringCellValue());
                    else if (cell.getColumnIndex() == 3)
                        comment.setDate(cell.getStringCellValue());
                    else
                        comment.setComment(cell.getStringCellValue());
                    break;
                }
            }
            this.comments.add(comment);
        }
        file.close();
    } catch (IOException e) {
        Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, e);
    }
}

From source file:Tools.PostProcessing.java

private Map<String, Integer> generateXLSXforS1(String XLSXfileName) throws FileNotFoundException, IOException {
    Map<String, Integer> statusMap = null;
    File myFile = new File(XLSXfileName);

    FileInputStream fis = new FileInputStream(myFile);
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

    //Return first sheet from the XLSX workbook
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    List<String> errorList = new ArrayList<>();
    Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
    //Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = mySheet.iterator();
    int lineCount = 1;
    // 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<String>(); //keep track info of each column

        while (it.hasNext()) {
            keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
            numCell++;/*from  w w  w  .j  ava2 s  .c  o  m*/
        }
        if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
            // System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
            //                int globalCount = 1;
            DataFormatter df = new DataFormatter();
            statusMap = new TreeMap<>();
            while (rowIterator.hasNext()) {
                lineCount++;
                Row row = rowIterator.next();
                Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2);
                String fullSSN = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell));
                colorDeletedLeaves(fullSSN, ctrlNumCell, myWorkBook, statusMap);
            }
            // output to a new xlsx file
            fis.close();
            FileOutputStream output;
            String targetFile = null;
            if (XLSXfileName.contains(".xlsx")) {
                targetFile = XLSXfileName.replace(".xlsx", "_forS1.xlsx");
            } else {
                targetFile = XLSXfileName + "_forS1.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();

        } else {
            JOptionPane.showMessageDialog(null, "XLSX file format is incorrect! Must be full SSN format");
        }
    }
    return statusMap;
}

From source file:Tools.PreProcessing.java

private List<String> readXlsxFile(String xlsxFileName) throws IOException {
    File myFile = new File(xlsxFileName);
    List<String> list = new ArrayList<>(); //store ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN that printed on the pdf file
    try {/*from  www. ja v a 2 s. co  m*/
        FileInputStream fis = new FileInputStream(myFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        List<String> errorList = new ArrayList<>();
        Map<String, Map<String, List<String>>> leaves = new HashMap<String, Map<String, List<String>>>();
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        int lineCount = 1;
        // 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<String>(); //keep track info of each column

            while (it.hasNext()) {
                keyList.add(it.next().getStringCellValue()); // add the title in xlsx to keyList                  
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V2.length) {
                System.out.println("XLSX2BatchHandler.java: V2, UCFR is not loaded.");
                int globalCount = 1;
                while (rowIterator.hasNext()) {
                    //int cellCount = 0;             
                    lineCount++;

                    Row row = rowIterator.next();
                    Cell cell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2);
                    String ctrlNumString = cell.getStringCellValue();
                    if (!ctrlNumString.equalsIgnoreCase("")) { //skip empty lines
                        Iterator<Cell> cellIterator = row.cellIterator();
                        Map<String, String> rowContainer = new HashMap<>(); //store info of each row                                       
                        rowContainerBuilder(rowContainer, keyList, cellIterator); // update rowContainer

                        //    public static final String[] SIGNED_LEAVE_TITLES = {"Ctrl Number", 
                        //            "SSN", "Last Name", "Sign-in Date", "Sign-out Date", "Leave Area",
                        //        "Leave Type", "Num of Days", "First Five","Projected Sign-in Date", "Projected Sign-out Date"};
                        //      make sure the key mataches the name in the header                        
                        String ctrlNum = GlobalVar
                                .readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2)));
                        //String ctrlNum = GlobalVar.readCtrlNum(rowContainer.get(keyList.get(GlobalVar.CTRL_NUM_CELL_INDEX_V2))); 
                        if (ctrlNum == null) {
                            JOptionPane.showMessageDialog(null,
                                    "Line " + lineCount + ": Invalid ctrl number received.");
                        }
                        // String lastName = rowContainer.get(keyList.get(GlobalVar.LAST_NAME_CELL_INDEX_V2));
                        String fullSSN = rowContainer.get(keyList.get(GlobalVar.FULL_SSN_CELL_INDEX_V2)); // full ssn                                
                        list.add(ctrlNum + GlobalVar.PRE_PROC_KEY_SYMBOL + fullSSN);

                    }
                }
                fis.close();

            } else {

                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct format!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        // finds the work book in stance for XLSX file

    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "XLSX2BatchHandler.java: Xlsx file not found!");
        Logger.getLogger(XLSX2BatchHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    return list;
}

From source file:ultis.ReadFile.java

private static ArrayList<String> readXlsxFile(String path) throws FileNotFoundException, IOException {
    ArrayList<String> lines = new ArrayList<>();

    // Create file input stream from input file
    FileInputStream fis = new FileInputStream(path);
    // Create Workbook instance holding reference to input file
    XSSFWorkbook workbook = new XSSFWorkbook(fis);

    // Loop for all sheets
    for (int i = 0; i < workbook.getNumberOfSheets(); ++i) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        // Iterate through each row in input file
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            String line = "";
            line += cellIterator.next().getStringCellValue().trim();
            line += "|";
            line += cellIterator.next().getStringCellValue().trim();
            lines.add(line);/*from   w ww. ja  v  a 2  s.co  m*/
        }
    }
    fis.close();

    return lines;
}

From source file:upload.Parser.java

public void parseXSLX() {

    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook;//from  ww  w. j ava2 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:usac.centrocalculo.data.LecturaURyS.java

public void readXlsx(File inputFile) {
    try {//from   w ww  . j  a  v  a  2 s . 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:userinterface.DataInitialization.java

public static void initializeData() {
    try {/*from  w  ww .j  a v a 2s. co m*/

        FileInputStream file = new FileInputStream(
                new File("..\\..\\aed_fall_2016_project_apoorva_lakhmani_001256312\\FinalProjectData.xlsx"));
        //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);
        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            assignDonor(row);
            //call from another calss using object.method
        }
        file.close();

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

From source file:userinterface.DataInitialization.java

public static void initializeDataForPatient() {
    try {/*  w  ww. ja v  a2  s .com*/

        FileInputStream file = new FileInputStream(
                new File("..\\..\\aed_fall_2016_project_apoorva_lakhmani_001256312\\FinalProjectData.xlsx"));
        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(1);
        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            assignPatient(row);
            //call from another calss using object.method
        }
        file.close();

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

From source file:util.DocumentFunction.java

public static String readXlsxFile(String filename) {
    StringBuilder text = new StringBuilder();
    try {//from ww  w.java2 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;/*ww  w  .j ava  2s .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;
}