ReadExcelFile

 /**  
  * @author:Soumya.D  
  *   
  * Contains Code to read excel data   
  */  
 package com.hr.automation.utilities;  
 import java.io.FileInputStream;  
 import java.util.ArrayList;  
 import java.util.HashMap;  
 import java.util.List;  
 import java.util.Map;  
 import org.apache.poi.hssf.usermodel.HSSFRow;  
 import org.apache.poi.hssf.usermodel.HSSFSheet;  
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
 public class ReadExcelFile{  
 public List<Map<String, String>> getExcelRecords(String filePath,String sheetName)  
 {  
      HSSFRow row0=null;  
      HSSFWorkbook workbook=null;  
      HSSFSheet worksheet=null;  
      List<String> filtersinfoheader = new ArrayList<String>();  
      List<Map<String, String>> recordsList = new ArrayList<Map<String, String>>();  
      try  
      {  
           FileInputStream fileinputstreamobject = new FileInputStream(filePath);  
           workbook = new HSSFWorkbook(fileinputstreamobject);   
           worksheet= workbook.getSheet(sheetName);  
           row0=worksheet.getRow(0);  
           for(int i=0;row0.getCell(i)!=null;i++)  
           {  
                filtersinfoheader.add(i, row0.getCell(i).toString());       
           }  
           for(int i=1;worksheet.getRow(i)!=null;i++)  
           {  
                HashMap<String, String> recordData=new HashMap<String, String>();  
                for(int j=0;j<filtersinfoheader.size();j++)  
                {   
                     try  
                     {  
                          if(worksheet.getRow(i).getCell(j)==null)  
                               recordData.put(filtersinfoheader.get(j), null);  
                               else if(worksheet.getRow(i).getCell(j).toString().trim().isEmpty())  
                                    recordData.put(filtersinfoheader.get(j), worksheet.getRow(i).getCell(j).toString().trim());  
                               else  
                                    recordData.put(filtersinfoheader.get(j), worksheet.getRow(i).getCell(j).toString().trim());  
                     }  
                     catch(Exception e)  
                     {  
                          e.printStackTrace();  
                          return null;  
                     }  
                }  
                if(recordData.get("Runmode").equalsIgnoreCase("yes"))  
                {  
                     recordsList.add(recordData);       
                }  
           }  
      }  
      catch (Exception e)  
      {  
           e.printStackTrace();  
           return null;  
      }  
      return recordsList;            
 }  
 public List<Map<String, String>> copyExcelRecords(String filePath,String sheetName)  
 {  
      HSSFRow row0=null;  
      HSSFWorkbook workbook=null;  
      HSSFSheet worksheet=null;  
      List<String> filtersinfoheader = new ArrayList<String>();  
      List<Map<String, String>> recordsList = new ArrayList<Map<String, String>>();  
      try  
      {  
           FileInputStream fileinputstreamobject = new FileInputStream(filePath);  
           workbook = new HSSFWorkbook(fileinputstreamobject);   
           worksheet= workbook.getSheet(sheetName);  
           row0=worksheet.getRow(0);  
           for(int i=0;row0.getCell(i)!=null;i++)  
           {  
                filtersinfoheader.add(i, row0.getCell(i).toString());       
           }  
           for(int i=1;worksheet.getRow(i)!=null;i++)  
           {  
                HashMap<String, String> recordData=new HashMap<String, String>();  
                for(int j=0;j<filtersinfoheader.size();j++)  
                {   
                     try  
                     {  
                          if(worksheet.getRow(i).getCell(j)==null)  
                               recordData.put(filtersinfoheader.get(j), null);  
                               else if(worksheet.getRow(i).getCell(j).toString().trim().isEmpty())  
                                    recordData.put(filtersinfoheader.get(j), worksheet.getRow(i).getCell(j).toString().trim());  
                               else  
                                    recordData.put(filtersinfoheader.get(j), worksheet.getRow(i).getCell(j).toString().trim());  
                     }  
                     catch(Exception e)  
                     {  
                          e.printStackTrace();  
                          return null;  
                     }  
                }  
                recordsList.add(recordData);       
           }  
      }  
      catch (Exception e)  
      {  
           e.printStackTrace();  
           return null;  
      }  
      return recordsList;            
 }  
 public List<Map<String, String>> getRunStatusYesTestSteps(String filepath,String TestStepName,List<Map<String, String>> testcases)  
 {  
      HSSFRow rowStep0=null;  
      HSSFWorkbook workbookstep=null;  
      HSSFSheet worksheetstep=null;  
      List<String> testStepsHeader = new ArrayList<String>();  
      List<Map<String, String>> testStepslist = new ArrayList<Map<String, String>>();  
      HashMap<String,String> map = new HashMap<String, String>();  
      try  
      {  
           FileInputStream fileinputstreamobject = new FileInputStream(filepath);  
           workbookstep = new HSSFWorkbook(fileinputstreamobject);  
           worksheetstep= workbookstep.getSheet(TestStepName);  
           rowStep0=worksheetstep.getRow(0);  
           for(int i=0;rowStep0.getCell(i)!=null;i++)  
           {  
                testStepsHeader.add(i, rowStep0.getCell(i).toString());       
           }  
           for (int z=0;z<testcases.size();z++){  
                 map=(HashMap<String, String>) testcases.get(z);  
           for(int i=1;worksheetstep.getRow(i)!=null;i++)  
           {  
                if(worksheetstep.getRow(0).getCell(0).toString().equals("Test Case ID") && map.get("Test Case ID").equals(worksheetstep.getRow(i).getCell(0).toString()))  
                {  
                HashMap<String, String> recordsData=new HashMap<String, String>();  
                for(int j=0;j<testStepsHeader.size();j++)  
                {  
                     if(worksheetstep.getRow(i).getCell(j)==null)  
                          recordsData.put(testStepsHeader.get(j), null);  
                          else if(worksheetstep.getRow(i).getCell(j).toString().trim().isEmpty())  
                               recordsData.put(testStepsHeader.get(j), worksheetstep.getRow(i).getCell(j).toString().trim());  
                          else  
                               recordsData.put(testStepsHeader.get(j), worksheetstep.getRow(i).getCell(j).toString().trim());  
                }  
                testStepslist.add(recordsData);       
                }  
                               }  
           }  
           System.out.println(testStepslist.size());  
      }catch (Exception e) {  
           e.printStackTrace();  
      }  
      return testStepslist;            
 }  
 }