CODEWITHSUNDEEP
javaexceliterator
Aditya
Aditya

Reputation: 505

How to loop through ArrayList and get data which was read from Excel

I have Excel sheet with username, emailIDs, phone and location in different cells. I have added all the cell values into Arraylist. However, Now I need to loop through to indexes to read username each time and pass it to text boxes in other application.

I have written code till adding to ArrayList but not sure how to read each value from list every time and stuck here.

Means, first time I should read username as "A", email id "[email protected]", phone number as "12333", location as "xzy" from list. When loop continues second time, I should be able to read username "B" and values mapped to it.

public  ArrayList<String>  getdata() throws IOException {

    FileInputStream fis = new FileInputStream("C:\\Users\\vnaditya\\Downloads\\Book2.xlsx");
    XSSFWorkbook XS = new XSSFWorkbook(fis);

    int numberofsheets = XS.getNumberOfSheets();

    for(int i=0; i< numberofsheets; i++) {
        if(XS.getSheetName(i).equalsIgnoreCase("Sheet1")) {
            XSSFSheet XSS = XS.getSheetAt(i);

            Iterator<Row> r = XSS.iterator();
            while (r.hasNext()) {
                Row row = r.next();
                Iterator <Cell> cell = row.cellIterator();

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

                    String message = cellvalue.getStringCellValue();
                    System.out.println(message);

                    a.add(cellvalue.getStringCellValue());
                    // System.out.println(a);
                    // System.out.println(a.size());
                 }
            }
        }
    }

Expected: need to read username and other cell values every time.

Upvotes: 0

Views: 945

Answers (2)

Bentaye
Bentaye

Reputation: 9756

You can create a User class to hold the info of each user:

class User {
    private String username, emailIDs, phone, location;

    public User(String username, String emailIDs, String phone, String location) {
        this.username = username;
        this.emailIDs = emailIDs;
        this.phone = phone;
        this.location = location;
    }

    // Getters/Setters

}

Then read each row inside a User object, and return a List<User>

public static List<User> getdata() throws IOException {
    List<User> data = new ArrayList<>();

    FileInputStream fis = new FileInputStream("C:\\Users\\vnaditya\\Downloads\\Book2.xlsx");
    XSSFWorkbook XS = new XSSFWorkbook(fis);
    int numberofsheets = XS.getNumberOfSheets();

    for (int i = 0; i < numberofsheets; i++) {
        if (XS.getSheetName(i).equalsIgnoreCase("Sheet1")) {

            XSSFSheet XSS = XS.getSheetAt(i);
            Iterator<Row> r = XSS.iterator();

            String username, emailIDs, phone, location;
            User user;
            while (r.hasNext()) {
                Row row = r.next();
                username = row.getCell(0).getStringCellValue();
                emailIDs = row.getCell(1).getStringCellValue();
                phone = row.getCell(2).getStringCellValue();
                location = row.getCell(3).getStringCellValue();
                user = new User(username, emailIDs, phone, location);
                data.add(user);
            }
        }
    }
    return data;
}

Upvotes: 1

nullPointer
nullPointer

Reputation: 4574

The ideal solution here would be to create a custom class (eg "User") with all the fields you need, eg username / id / email etc with the relevant getters and setters methods. Then for each line you parse from the Excel you can create a new User object storing the corresponding values for it, and eventually add it to an ArrayList<User> . Then you could easily iterate over it and get the values for each User object as needed.

Upvotes: 1

Related Questions