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

Question

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.

@Bentaye I have got the individual User object values - username, emailIDs, phone, location but not sure how to iterate them such that one value is passed one time to input. Kindly help – — Aditya
– Aditya, Commented Apr 26, 2019 at 11:14

Bentaye · Accepted Answer · 2019-04-26 08:29:29Z

1

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;
}

answered Apr 26, 2019 at 8:29

Bentaye

9,8065 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aditya Over a year ago

I have got the individual User object values - username, emailIDs, phone, location but not sure how to iterate them such that one value is passed one time to input. Kindly help

Bentaye Over a year ago

I do not understand what you mean, you can iterate through the List<user> with a for loop for(User user : yourList)

Aditya Over a year ago

I used the for loop to loop through the list for (User user: a) { user.getFullName(); } .This is returning me all the usernames from excel but not one at time. I'm new to Java so struggling a bit to understand.

Bentaye Over a year ago

if you want only one use the List.get: a.get(0) will return the first user, a.get(1) will return the second, etc

nullPointer · Accepted Answer · 2019-04-26 08:04:25Z

1

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.

answered Apr 26, 2019 at 8:04

nullPointer

4,5891 gold badge20 silver badges31 bronze badges

1 Comment

Deepesh kumar Gupta Over a year ago

It's a conventional idea, but I would have preferred using a json rather. Some creativity..

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related