Adding data to ArrayList while reading data from Excel using POI Apache

Question

I am trying to read data from Excel sheets using POI Apache. The problem I am having is that I want to read the data of all cell of a row at the same time and store it in ArrayList of Type Class but the output is only cell by cell.

Here is the class that open the excel sheet and read the data cell by cell.

package testing;

import javax.swing.JFileChooser;
import java.io.File;
import java.io.FileInputStream;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelDemo 
{
    ArrayList<Data> list = new ArrayList<>();
    String path;

   public ReadExcelDemo(String path)
   {
       this.path = path;

        try
        {
            FileInputStream file = new FileInputStream(new File(path));

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

             System.out.println("");

            //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.print(cell.getNumericCellValue() + "\t");

                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                    }
                }


                System.out.println("");
            }
            file.close();
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
    }
}

Data Class

package testing;


public class Data {


    int ID;
    String F_Name,L_Name;




    public Data(int ID, String F_Name, String L_Name) {
        this.ID = ID;
        this.F_Name = F_Name;
        this.L_Name = L_Name;
    }


    public int getID() {
        return ID;
    }

    public String getF_Name() {
        return F_Name;
    }

    public String getL_Name() {
        return L_Name;
    }

I want to add the cell data in Arraylist like this at a single time

List.add(new Data(1,"Amit","shukla"));

but the data the iterator return is one by one like first it outputs 1 then amit and then shukla which is really difficult to add to arraylist

I tried so much to add data to ArrayList at a single line but I couldn't. It would be really helpful if you guy help me solve this problem.

Answer 1

this.path = path;

    try
    {
        FileInputStream file = new FileInputStreaHashMap<K, V>ile(path));
        HashMap<Integer, Data> mp= new HashMap<Integer, Data>();
        //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);

         System.out.println("");

        //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
                int i=0;
                int j=0;
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                            i=Integer.parseInt(cell.getNumericCellValue());
                            Data d= new Data();
                            d.setId(cell.getNumericCellvalue());


                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        if( j==0){
                        Data data= mp.get(i);
                        data.setName(cell.getStringCellValue());
                        mp.put(i, data);
                        j=j+1;
                        }
                        else
                        {
                            Data data= mp.get(i);
                            data.setLastName(cell.getStringCellValue());
                            mp.put(i, data);
                            j=0;
                        }
                        break;
                }
            }


            System.out.println("");
        }
        List<Data> dataList=  new ArrayList<Data>();
        for (Data d : mp.values()) {
           dataList.add(d);

        }
        file.close();
    } 
    catch (Exception e) 
    {
        e.printStackTrace();
    }

Answer 2

You can add a single row of an excel sheet by a single iteration using this,

public void ReadExcel(String filePath,String fileName,String sheetName) throws InterruptedException, IOException{
    File file = new File(filePath+"\\"+fileName);
    //Create an object of FileInputStream class to read excel file
    FileInputStream inputStream = new FileInputStream(file);
    Workbook AddCatalog = null;
    //Find the file extension by splitting file name in substring  and getting only extension name
    String fileExtensionName = fileName.substring(fileName.indexOf("."));
    //Check condition if the file is a .xls file or .xlsx file
    if(fileExtensionName.equals(".xls")){
        //If it is .xls file then create object of HSSFWorkbook class
        AddCatalog = new HSSFWorkbook(inputStream);
    }
    else if(fileExtensionName.equals(".xlsx")){
        //If it is .xlsx file then create object of XSSFWorkbook class
        AddCatalog = new XSSFWorkbook(inputStream);
    }
    //Read sheet inside the workbook by its name
    Sheet AddCatalogSheet = AddCatalog.getSheet(sheetName);
    //Find number of rows in excel file
    int rowcount = AddCatalogSheet.getLastRowNum()- AddCatalogSheet.getFirstRowNum();
    System.out.println("Total row number: "+rowcount);
    for(int i=1; i<rowcount+1; i++){
        //Create a loop to get the cell values of a row for one iteration
        Row row = AddCatalogSheet.getRow(i);
        List<String> arrName = new ArrayList<String>();
        for(int j=0; j<row.getLastCellNum(); j++){
            // Create an object reference of 'Cell' class
            Cell cell = row.getCell(j);
            // Add all the cell values of a particular row
            arrName.add(cell.getStringCellValue());
            }
        System.out.println(arrName);
        System.out.println("Size of the arrayList: "+arrName.size());
        // Create an iterator to iterate through the arrayList- 'arrName'
        Iterator<String> itr = arrName.iterator();
        while(itr.hasNext()){
            System.out.println("arrayList values: "+itr.next());
        }
        }

}

Answer 3

You have a problem in Switch and Case part change to this should work :

   switch (cell.getCellType())  {
         case NUMERIC:
               System.out.print(cell.getNumericCellValue() + "\t");
               break;
        case STRING:
              System.out.print(cell.getStringCellValue() + "\t");
              break;
      }