Java- how to insert row in a db table after reading excel file

Question

I am able to read the excel file data from the below code, but I am unable to get the logic behind how to get Excel data in order to store in POJO class after reading the excel file.

In short: I am confused how to send these read Excel data to my model class to be stored in my database table?

The following code prints correctly the excel rows in my Eclipse console:

                     ..............
                          ..............
        public String execute()
        {
            try
            { 
                 String filePath=servletRequest.getSession().getServletContext().getRealPath("/"); 
                 File fileToCreate= new File(filePath,this.excelDataFileName);
                 FileUtils.copyFile(this.excelData, fileToCreate); 
                 UploadExcel obj=new UploadExcel();
                 obj.readExcel(excelData.getAbsolutePath()); 

            }
            catch(Exception e){
                e.printStackTrace();
                addActionError(e.getMessage());
                return INPUT;
            }
            return SUCCESS;
        }


        /*
         *Method to read the each sheet ,row & column of the excel sheet of the uploaded xls file  
        */
        public void readExcel(String filePath)
        { 
            try
             {
                FileInputStream file=new FileInputStream(new File(filePath));

                //Getting the instance for XLS file 
                 HSSFWorkbook workbook=new HSSFWorkbook(file);

                 //Get First sheet from the workbook
                HSSFSheet sheet=workbook.getSheetAt(0);

                 ArrayList myList = new ArrayList();
                //Iterate start from the first sheet of the uploaded excel file
                  Iterator<Row> rowIterator = sheet.iterator();
                  while(rowIterator.hasNext())
                  {
                      Row  row=rowIterator.next();

                      if(row.getRowNum()==0)
                      {
                          continue;//skip to read the first row of file
                      }

                      //For each row, iterate through each columns
                      Iterator<Cell> cellIterator=row.cellIterator();

                      while(cellIterator.hasNext())
                      {
                          Cell cell=cellIterator.next();
                          if(cell.getColumnIndex()==0)
                          {
                              continue;
                          }
                          switch(cell.getCellType())
                          {
                             case Cell.CELL_TYPE_BOOLEAN:
                                  System.out.print(cell.getBooleanCellValue() + "\t\t");
                                 // myList.add(cell.getBooleanCellValue());
                                  break;
                             case Cell.CELL_TYPE_NUMERIC:
                                 System.out.print(cell.getNumericCellValue()+ "\t\t");
                                //  myList.add(cell.getNumericCellValue());
                                 break;
                             case Cell.CELL_TYPE_STRING:
                                 System.out.print(cell.getStringCellValue()+ "\t\t");
                                // myList.add(cell.getStringCellValue());
                                 break;
                          }

                      }     
                      System.out.println(""); 


                  }
                  file.close();
                FileOutputStream out=
                        new FileOutputStream(new File(filePath));

                  workbook.write(out);
                  out.close();



            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }




    }

In console output:

TEXTit      6695 PROSPECT RD        Nova Scotia     B3z 3t1 

row2sdfsda      61695 P         sfsdfdsf        23B3z 3t1   

What I thought is: I have to get the row one by one and I will add this row data to my POJO class object and send it to dao, and finally use saveOrupdate(tableobj) of hibernate method to save the data into my db table.

But I was unable to think how I can add these pieces of data to my Pojo class.

Hope someone can help me here.

Answer 1

You can inject/pass reference to your DAO into UploadExcel class. Then when in readExcel() method you can create new ENTITY and fill it with values.

                  Entity entity = new Entity();


                  while(cellIterator.hasNext())
                  {
                      Cell cell=cellIterator.next();
                      if(cell.getColumnIndex()==0)
                      {
                          continue;
                      }
                      switch(cell.getCellType())
                      {
                         case Cell.CELL_TYPE_BOOLEAN:
                              entity.setBooleanValue(cell.getBooleanValue);
                              break;
                         ...
                      }

                  }   

Finally you insert/createOrUpdate your entity by your DAO.

                  dao.insertOrUpdate(entity);

* EDIT * Code to explain comment

Entity is just an example of custom entity which models data you read from excel. If for example your excel contains data about Customer then you can create customer entity.

public class Customer {

    private String name;
    private String surname;
    private Integer age;

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
       return name;
    }

    // + getters and setters for other fields
}    

Then you send this entity filled with excel data to your DAO (which manages customer table), and insert given entity into DB.

* EDIT2 *

Remove "dao.saveOrUpdateCompany(company);" from following code:

    public String execute()
    {
        try
        { 
             String filePath=servletRequest.getSession().getServletContext().getRealPath("/"); 
             File fileToCreate= new File(filePath,this.excelDataFileName);
             FileUtils.copyFile(this.excelData, fileToCreate); 
             UploadExcel obj=new UploadExcel();
             obj.readExcel(excelData.getAbsolutePath());  
        }
        catch(Exception e){
            e.printStackTrace();
            addActionError(e.getMessage());
            return INPUT;
        }
        return SUCCESS;
    }

You want one new entity PER ROW, now you just create one entity in the beginning of class

Read comments following code

    public void readExcel(String filePath)
    { 
        try
         {
             List sheetData = new ArrayList();
            FileInputStream file=new FileInputStream(new File(filePath));

            //Getting the instance for XLS file 
             HSSFWorkbook workbook=new HSSFWorkbook(file);

             //Get First sheet from the workbook
            HSSFSheet sheet=workbook.getSheetAt(0);

            //Iterate start from the first sheet of the uploaded excel file
              Iterator<Row> rowIterator = sheet.iterator();

              while (rowIterator.hasNext()) {
                   Row row = (Row) rowIterator.next();

                   // CHANGE 
                   Company company = new Company();
                   company.setName(getCellValue(row.getCell((short)1)));

                   // HERE YOU CAN SAVE COMPANY 
                   dao.saveOrUpdateCompany(company);

                   // OR ADD COMPANY TO LIST 
                   // List<Company> companies = new ArrayList<Company>();
                   // Declare just one list for entire class not per row
                   // In this case you call custom made DAO method which batch save
                   // all company entities in list but you call this custom method
                   // at the end of excel processing (your original dao code position).
                   // Try it without list first  
                   list.add(company);    


              }



              System.out.println("Seet data size-"+sheetData.size());
              file.close();
            FileOutputStream out=
                    new FileOutputStream(new File(filePath));

              workbook.write(out);
              out.close();


        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

* EDIT3 *

I think this should finally work

       try{
            session=HibernateUtil.getSessionFactory().openSession();
            transaction=session.beginTransaction();
            for(int i=0;i<companies.size();i++)
            {   
                // THIS IS BAD 
                //Company com=new Company();
                //You need this
                Company com = companies.get(i);
                session.saveOrUpdate(com);
            }
            transaction.commit();
            status++;

        }

Answer 2

     Iterator iterator = workSheet.rowIterator();
          while (iterator.hasNext()) {
         Row row = (Row) iterator.next();
// check each cell for null and by using getCellValue() method. and inject the value in to user defined pojo.
    }


 private String getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return cell.getNumericCellValue() + "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_ERROR){
            return cell.getErrorCellValue() + "";
        } 
        else {
            return null;
        }
    }