Apache POI for Selenium Data-Driven Tests | Handling Binary [.xls] Workbook

Apache POI lets you manipulate Microsoft documents like .xls and .xlsx; HSSF, Horrible Spread sheet Format implements Excel ’97 (.xls) file format. [I have optimized this framework to work efficient with Selenium]

Handling Binary [.xls] Workbook

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class className{
private static WebDriver driver;
private static String baseUrl;

@BeforeTest
public void setUp() {
driver = new FirefoxDriver();
baseUrl = "https://xyz.com";
driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
}

@Test(dataProvider = "DP")
public static int findcount(String username, String password)
throws InterruptedException {
int res = 0;

driver.get(baseUrl + "/login.v");
driver.findElement(By.id("login-username")).sendKeys(username);
driver.findElement(By.id("login-password")).sendKeys(password);
driver.findElement(By.id("login-submit")).click();

return res;
}

@DataProvider(name = "DP")
public static String[][] excelRead() throws Exception {
File excel = new File("C:\\Test\\data.xls");
FileInputStream fis = new FileInputStream(excel);

HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet ws = wb.getSheet("Sheet1");

int rowNum = ws.getLastRowNum() + 1;
int colNum = ws.getRow(0).getLastCellNum();
String[][] data = new String[(rowNum - 1)][colNum];

int k = 0;
for (int i = 1; i < rowNum; i++) {
HSSFRow row = ws.getRow(i);
for (int j = 0; j < colNum; j++) {
HSSFCell cell = row.getCell(j);
String value = cellToString(cell);
data[k][j] = value;
System.out.println("the value is: " + value);
}
k++;
}

return data;
}

@AfterTest
public void tearDown() throws Exception {
driver.quit();
}

public static void main(String[] args) throws Exception {
String[][] data;
data = excelRead();
if (data != null) {
int j = 0;
String aValue = null;
for (int i = 1; i < data.length; i++) {
int pageCount = findcount(data[i][1], data[i][2]); //vary depending upon the Excel columns
if (pageCount > j) {
j = pageCount;
aValue = data[i][1];
}
System.out.println("The count of the data[" + i + "][1] is: "
+ data[i][1] + " = " + pageCount);
}
System.out.println("Greater value: " + aValue + " = " + j);
} else {
System.out.println("No records...");
}
}

public static String cellToString(HSSFCell cell) {

Object result;
switch (cell.getCellType()) {

case Cell.CELL_TYPE_NUMERIC:
result = cell.getNumericCellValue();
break;

case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;

case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;

case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;

default:
throw new RuntimeException("Unknown Cell Type");
}

return result.toString();

}

}

testng.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Suite" allow-return-values="true" parallel="none">
  <test name="Test">
    <classes>      
      <class name="package.className"/>      
    </classes>
  </test> <!-- Test -->
</suite> <!-- Suite -->