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

The easiest way to achieve this as follows

1. Create an array list with all the excel column names.
2. Create a list for holding each row as an object
3. Create a HashMap for holding column name and cell value as key value pair
4. Iterate through each row and iterate to each cell of that row. and pull the cell values into the HashMap we created
5. After reading all the cells of current row, map this HashMap to custom pojo class using Jackson object mapper. And add this object to the list that we created in step 2.
6. now repeat the step 4 and 5.

You can refer to the code below

                //declare list of column headers in the excel file
            List<String> header = new ArrayList<>(Arrays.asList("ID","FirstName","LastName"));
            //list for holding each row of data as list of custom objects
            List<CustomObject> rawDataList = new ArrayList<>();
            while (rowIterator.hasNext()) {

                Row row = rowIterator.next();
                //map for holding cell values of current row as key value pair
                Map<String, String> rowDataMap = new HashMap<>();
                Cell cell;
                for (int k = 0; k < row.getLastCellNum(); k++) {

                    if (null != (cell = row.getCell(k))) {
                        switch (cell.getCellType()) {
                        case NUMERIC:
                            //Get the column name from header array and push the cell value into hashmap
                            rowDataMap.put(header.get(k),NumberToTextConverter.toText(cell.getNumericCellValue()));
                            break;
                        case STRING:
                            //Get the column name from header array and push the cell value into hashmap
                            rowDataMap.put(header.get(k), cell.getStringCellValue());
                            break;

                        }
                    }
                }                   
                //create object of jackson object mnapper (Better configure as a bean)
                ObjectMapper mapper = new ObjectMapper()
                          .configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
                mapper.configure(DeserializationFeature.ACCEPT_SINGLE_VALUE_AS_ARRAY, true);
                mapper.configure(DeserializationFeature.ACCEPT_EMPTY_ARRAY_AS_NULL_OBJECT, true);
                
                // after reading all the cells of current row map the hashmap into 
                //custom pojo class using object mapper 
                CustomObject rawData = mapper.convertValue(rowDataMap, CustomObject.class);
                //add the cusom pojo object into the list
                rawDataList.add(rawData);

            }

Answer 2

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

Answer 3

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 4

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