Fetch the entire excel row based on a single cell value

You can use following Method. "sheet" is the sheet in which you need to perform search, "colName" is the name of column, as per your requirement, it will be fixed. and "textToFind" is the text you have to find in colName column.

public static XSSFRow test(XSSFSheet sheet, String colName, String textToFind){
    int colIndex=0;
    for (int colNum = 0; colNum<=sheet.getRow(0).getLastCellNum();colNum++){
        if (sheet.getRow(0).getCell(colNum).toString().equalsIgnoreCase(colName)){
            colIndex = colNum;
            break;
        }
    }
    for (int RowNum = 0; RowNum<sheet.getLastRowNum();RowNum++){
        if(sheet.getRow(RowNum).getCell(colIndex).toString().equalsIgnoreCase(textToFind)){
            return sheet.getRow(RowNum);
        }
    }
    System.out.println("No any row found that contains "+textToFind);
    return null;
}

1. Put this in lib folder of ur project poi-2.5.1.jar
2. Try to use this method in struts Action else u can simply use it in simple class avoiding/deleting HttpSession parameter from method arguments

3. void uploadBulkQuestions(ExcelUploadForm excelUploadForm,String filePath,HttpSession session) throws SQLException//you can neglect ExcelUploadForm obj in parameters { ExcelForm excelForm=null; /** * //Here ExcelForm is a simple pojo with setters and getters * *public class ExcelForm extends ActionForm { *String city; *String employeeName; *Integer age; * //setters and getters of above variables. * } */ short CityPosition=0; short Employee_NamePosition=1; short AgePosition=2; Connection con=null; ArrayList invalidFields=new ArrayList(); ArrayList validFields=new ArrayList(); boolean bulkUploadFlag=true;

   try
   {
       FileInputStream fs =new FileInputStream(filePath+"\\"+excelUploadForm.getExcelFile().getFileName());//here you can give ur ExcelFile Path{like--"D:\ExcelFiles\sample.xls"}(excelUploadForm in method parameter is an another form which pics a file through browse(i.e.,File Upload))) 
   
       HSSFWorkbook wb = new HSSFWorkbook(fs);
       for (int k = 0; k < wb.getNumberOfSheets(); k++)
       {
           HSSFSheet sheet = wb.getSheetAt(k);
           int rows  = sheet.getPhysicalNumberOfRows();
           HSSFRow firstRow   = sheet.getRow(0);
           int totalCells = firstRow.getPhysicalNumberOfCells(); 
           for(short i=0;i<totalCells;i++)
           {
               HSSFCell firstCell;
               firstCell=firstRow.getCell(i);
                    if(firstCell.getStringCellValue().trim().equalsIgnoreCase("City"))
                   {CityPosition=i;System.out.println("HSSFCellqidpos"+City);}
               else if(firstCell.getStringCellValue().trim().equalsIgnoreCase("Employee_Name"))
                   Employee_NamePosition=i;
               else if(firstCell.getStringCellValue().trim().equalsIgnoreCase("Age"))
                   AgePosition=i;
           }
   
           for (int r = 1; r < rows; r++)
           {
               excelForm = new ExcelForm();
               HSSFRow row   = sheet.getRow(r);
               HSSFCell cell;  
               if(CityPosition>=0)
               {
                   cell= row.getCell(CityPosition);
                   try{
                   excelForm.setCity(cell.getStringCellValue());
                   System.out.println("Check the Data of city"+excelForm.getCity());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               if(Employee_NamePosition>0)
               {
                   cell= row.getCell(Employee_NamePosition);
                   try{
                   excelForm.setEmployeeName(cell.getStringCellValue());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               if(AgePosition>0)
               {
                   cell= row.getCell(AgePosition);
                   try{
                   excelForm.setAge((int)cell.getNumericCellValue());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               //Validating Excel Data
               if(excelForm.getCity()==null || excelForm.getEmployeeName()==null || excelForm.getAge() < 0)
               {
                   System.out.println("inside invalidFields.........");
                   System.out.println(excelForm);
                   invalidFields.add(excelForm);
                   bulkUploadFlag=false;
               }
               else
               {   
               System.out.println("inside validQue.........");
               System.out.println(excelForm);  
               validFields.add(excelForm);
               }
   
           }
   
   
   
       }
   
   
   
   
       //Transaction Management to make sure all the validFields ArrayList obj data is inserted
   
       if(bulkUploadFlag)
       {
           con.setAutoCommit(false);
   
           try
           {
               //fetch data from validFields Array List Using iterator and insert into DB if u wish to do so...
               Iterator fieldsIterator=  validFields.iterator();
               excelForm =null;
               while(questionIterator.hasNext())
                   {
   
                   excelForm=(excelForm)fieldsIterator.next();                     
                       //storing in Questions DB table
                       PreparedStatement psFields = con.prepareStatement("........");//write ur query to insert
                       psFields.executeUpdate();
   
           }
   
           // If there is no error.
           con.commit();
           session.setAttribute("ValidFields", validFields);
   
           }
           catch(SQLException se)
           {
               // If there is any error.
                con.rollback();
                se.printStackTrace();
                session.setAttribute("BulkUploadError","No Data Uploded due to Some Errors in Excel File");     
           }
       }
       else
       {
           session.setAttribute("InvalidFields",invalidFields);
   
       }
   }
   catch(Exception e)
   {
       e.printStackTrace();
   }
   finally
   {
       con.close();
   }
   
   return null;
   

   }