1

When submitting a form by fetching values from excel file it is showing Internal Server Error but when I put static values in code and run it successfully submits the form without any error.

Here is my code:

package project1;

import org.testng.annotations.Test;
import org.testng.annotations.BeforeTest;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.Select;
import org.openqa.selenium.support.ui.WebDriverWait;
import org.testng.annotations.AfterTest;

public class TestNG {

      WebDriver driver;
        XSSFWorkbook workbook;
        XSSFSheet sheet;
        XSSFCell cell;
@BeforeTest

     public void initialization(){
         // To set the path of the Chrome driver.
     System.getProperty("webdriver.chrome.driver","D:\\Softwares\\chromedriver_win32\\chromedriver.exe");
     driver = new ChromeDriver();

         // To launch website
         driver.get("URL");
         // To maximize the browser
         driver.manage().window().maximize();
         // implicit wait
         driver.manage().timeouts().implicitlyWait(20, TimeUnit.SECONDS);
        }

  @Test

      public void gymLoginLogout() throws IOException{
             // Import excel sheet.
             File src=new File("D:\\Softwares\\eclipse mars\\automation.xlsx");   
             // Load the file.
             FileInputStream fis = new FileInputStream(src);
             // Load the workbook.
             workbook = new XSSFWorkbook(fis);
             // Load the sheet in which data is stored.
             sheet= workbook.getSheetAt(0);
             for(int i=1; i<=sheet.getLastRowNum(); i++){

             // Import data for Email.
             cell = sheet.getRow(i).getCell(0);
            // cell.setCellType(Cell.CELL_TYPE_STRING);
             driver.findElement(By.name("username")).clear();
             driver.findElement(By.name("username")).sendKeys(cell.getStringCellValue());

             // Import data for password.
             cell = sheet.getRow(i).getCell(1);
             //cell.setCellType(Cell.CELL_TYPE_STRING);
             driver.findElement(By.name("password")).clear();          
             driver.findElement(By.name("password")).sendKeys(cell.getStringCellValue());
             // To click on Login button
             driver.findElement(By.xpath("//button[@type='submit']")).click();

                 driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);  

                 //******************** SIDE MENU ****************//
                 driver.findElement(By.xpath("//ul[@id='side-menu']/li[3]/a/span")).click();


                 driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                 //********************* MANAGE STAFF ****************//
                 driver.findElement(By.linkText("Manage Staff")).click();           
                 driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                //*********** ADD REGISTRATION **********//         
                 driver.findElement(By.xpath("//button[@id='AddRegistration']")).click();           
                 driver.manage().timeouts().implicitlyWait(60, TimeUnit.SECONDS);   

                //********** WEB DRIVER WAIT COMMNADS ***********//

                 WebDriverWait  block = new WebDriverWait(driver,10);       
                 block.until(ExpectedConditions.visibilityOfElementLocated(By.id("RegId")));

                 sheet = workbook.getSheetAt(1);
                 System.out.println("no. of rows = "+sheet.getLastRowNum());
                 for(i = 2;i<=sheet.getLastRowNum();i++){
                     cell = sheet.getRow(i).getCell(0);
                     driver.findElement(By.id("RegId")).clear();
                     driver.findElement(By.id("RegId")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);
                     try {
                        Thread.sleep(1000);
                    } catch (InterruptedException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                     cell = sheet.getRow(i).getCell(1);
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);
                     driver.findElement(By.id("FirstName")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(2); 
                     driver.findElement(By.id("MiddleName")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(3);                 
                     driver.findElement(By.id("LastName")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(4);
                     driver.findElement(By.id("FatherName")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(7);
                     driver.findElement(By.id("EmailAddress")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(8);
                     driver.findElement(By.id("Street1")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(9);
                     driver.findElement(By.id("MobileNumber")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(10);
                     Select oSelect = new Select(driver.findElement(By.xpath("//select[@id='GenderId']")));
                     oSelect.selectByVisibleText(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(11);
                     Select oselect1 = new Select(driver.findElement(By.xpath("//select[@id='MaritalStatusId']")));
                     oselect1.selectByVisibleText(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(5);
                     driver.findElement(By.id("DateOfBirth")).sendKeys(cell.getStringCellValue());

                     cell = sheet.getRow(i).getCell(6);
                     driver.findElement(By.id("JoiningDate")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     driver.findElement(By.linkText("Next")).click();

                     WebDriverWait  block1 = new WebDriverWait(driver,10);      
                     block1.until(ExpectedConditions.visibilityOfElementLocated(By.xpath("//select[@id='RoleId']")));
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(13);
                     Select oSelect2 = new Select(driver.findElement(By.xpath("//select[@id='RoleId']")));
                     oSelect2.selectByVisibleText(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(14);
                     Select oSelect3 = new Select(driver.findElement(By.xpath("//select[@id='LocationId']")));
                     oSelect3.selectByVisibleText(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(15);
                     driver.findElement(By.id("Experience")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(16);
                     driver.findElement(By.id("Speciality")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(17);
                     driver.findElement(By.id("Qualification")).sendKeys(cell.getStringCellValue());
                     driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     cell = sheet.getRow(i).getCell(13);
                     if(cell.getStringCellValue().equalsIgnoreCase("Trainer")) {
                         cell = sheet.getRow(i).getCell(18);                                             
                         driver.findElement(By.id("MeasurmentStartDate")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(19);                         
                         driver.findElement(By.id("Weight")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(20);
                         driver.findElement(By.id("Height")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(21);
                         driver.findElement(By.id("Chest")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(22);
                         driver.findElement(By.id("Waist")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(23);
                         driver.findElement(By.id("Arms")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(24);
                         driver.findElement(By.id("Thighs")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(25);
                         driver.findElement(By.id("Calves")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(26);
                         driver.findElement(By.id("Neck")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                         cell = sheet.getRow(i).getCell(27);
                         driver.findElement(By.id("Hips")).sendKeys(cell.getStringCellValue());
                         driver.manage().timeouts().implicitlyWait(120, TimeUnit.SECONDS);

                     }
                     //driver.findElement(By.id("submit")).click();

                }
           }

     }

  @AfterTest
  public void afterTest() {

  }

}
  • does you ajax request is calling? – Vel Guru Mar 30 '18 at 8:18
  • What is the error that you are getting. Paste it in your questions description. – Alok Mar 30 '18 at 10:33
  • 1
    Internal Server Error usually means that some exception arises in the code and it is thrown up to the http server. There might be nearly infinite number of reasons. Obviousely the data you send to the server breaks the logic of the program that is running behind that server. You need to contact the dev team or at least check server logs to get the clue. – Alexey R. Mar 30 '18 at 14:44
  • Alexey is correct, a 500 Internal server error is a problem with the server, it is likely your static data and Excel data differ and you are submitting invalid data that is breaking the system under test. It is likely you've actually discovered a bug. – Martin Spamer Oct 17 '18 at 15:02
1

You don't seem to be checking the values from your spreadsheet import or if you have successfully opened it , this would then result in you searching for null or unexpected values which will cause selenium to return service errors.

You could put some print statements or asserts in to help you check but really I'd suggest putting the code that does the data extraction in a separate class and getting that working so it can fetch the values you expect.

You could write some simple unit tests to display that data , then when your class works enough to pass it's tests you can make your original code use the new class.

You could also polish that class a little more so it can be used as a test fixture in other tests that might need to read from Excel.

Excel is a very complex and changeable format , if you are only reading one sheet of data from it you might find it easier to export to CSV and parse that instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.