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. Thanks for the great information in your blog Selenium Training in Chennai

    ReplyDelete