Tuesday 8 October 2013

Read & Write Excel + JXL API [Web Driver]

Read & Write data from Excel using JXL API with Selenium Webdriver combination. Make sure JXL API in your buildpath.

@Test
public void readandwrite() throws Exception {

// Read data from excel sheet
FileInputStream fi = new FileInputStream("C:\\input.xls");
Workbook wrkbook = Workbook.getWorkbook(fi);
Sheet wrksheet = wrkbook.getSheet(0);
String a[][] = new String[wrksheet.getRows()][wrksheet.getColumns()];
// Write the input data into another excel file
FileOutputStream fo = new FileOutputStream("C:\\output.xls");
WritableWorkbook wwb = Workbook.createWorkbook(fo);
WritableSheet ws = wwb.createSheet("customsheet", 0);

System.out.println("Total Rows: " + wrksheet.getRows());
System.out.println("Total Columns: " + wrksheet.getColumns());

for (int i = 0; i < wrksheet.getRows(); i++) {

for (int j = 0; j < wrksheet.getColumns(); j++) {
a[i][j] = wrksheet.getCell(j, i).getContents();
Label l = new Label(j, i, a[i][j]);
Label l1 = new Label(2, 0, "Result");
ws.addCell(l);
ws.addCell(l1);
}
}

for(int rowCnt = 1; rowCnt < wrksheet.getRows(); rowCnt++) {

driver.get("www.xyz.com");
//Enter search keyword by reading data from Excel [Here it read from 1st column]
driver.findElement(By.id("Value")).sendKeys(wrksheet.getCell(0, rowCnt).getContents());
driver.findElement(By.id("Value")).click(); 
Thread.sleep(5000);

boolean resultfound =  isElementPresent(By.id("Value"));

if(resultfound) {
Label l3 = new Label(2, rowCnt, "pass");  //Writes data into 3rd column
ws.addCell(l3);
else {
Label l2 = new Label(2, rowCnt, "fail");  //Writes data into 3rd column
ws.addCell(l2);
}
}
wwb.write();
wwb.close();
}



5 comments:

  1. how to read a url's from excel sheet using selenium webdriver

    ReplyDelete
  2. Great work. Thanks.

    ReplyDelete
  3. hii everyone
    how to read the data and write data into the same excel file instead of creating new excel sheet every time...if anyone knows please help me...
    thnaks in advance

    ReplyDelete
  4. am getting java.lang.IndexOutOfBoundsException: Index: 0, Size: 0

    ReplyDelete
  5. Nice blog..! I really loved reading through this article. Thanks for sharing.You done a great job.DataScience with Python Training in Bangalore


    ReplyDelete