javahssfworkbook

how to read values from xlsx file to store value through getter and setter to object and finally to array list


I have tried to read values from excel file to store each and every row and cells from XSSFWorkbook in an object and add it to arraylist.But its adding only last element in arraylist. Including my code below.

 try 
           {  
               ArrayList<OrderInfo> orderList = new ArrayList<OrderInfo>();
               XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("C:/mytemp/Order Details.xlsx"));

               for (Sheet sheet : wb ) {
                   OrderInfo order_Info = new OrderInfo();
                      for (Row row : sheet) {
                          int rowId = row.getRowNum();
                          if(rowId != 0){
                               Cell del_zone = row.getCell(0);
                               order_Info.setDel_zone(del_zone.getStringCellValue());
                               Cell cust_id = row.getCell(1);
                               order_Info.setCustomer_id(cust_id.getStringCellValue());
                          }
                      }
                      orderList.add(order_Info);
                  }

                  for(int i = 0;i<orderList.size();i++){
                   logger.info("*******Print List Object******"+orderList.get(i).getDel_zone()+"****Cust_Id****"+orderList.get(i).getCustomer_id());
                  }

               }
           catch (Exception e) 
           {
                   System.err.println("Exception :" + e.getMessage());
           }

Solution

  • orderList.add(order_Info) should be inside the for (Row row : sheet) loop, you have it outside

    Also it needs to be created inside the loop: OrderInfo order_Info = new OrderInfo(); should be later

               for (Sheet sheet : wb ) {
                      for (Row row : sheet) {
                          int rowId = row.getRowNum();
                          if(rowId != 0){
                               Cell del_zone = row.getCell(0);
                               OrderInfo order_Info = new OrderInfo();
                               order_Info.setDel_zone(del_zone.getStringCellValue());
                               Cell cust_id = row.getCell(1);
                               order_Info.setCustomer_id(cust_id.getStringCellValue());
                               orderList.add(order_Info);
                          }
                      }
                  }