My Blog List

Wednesday, August 17, 2011

Apache POI - Read Excel - For use of Selenium TestNG DataProvider

Here is the Class designed for reading Excel file...
You can download the JAR files needed to work with Apache POI here.

package Excel;

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.Enumeration;
import java.util.Hashtable;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.DocumentException;

public class POI {
int rowIndex = 0, columnIndex = 0;
Workbook wb;
Sheet ws;
Row wr;
String fileName, sheetName;
Hashtable <String, String> [] data = null;

public  void open() throws IOException  {
if (fileName.indexOf("xlsx") < 0) {
wb = new HSSFWorkbook(new FileInputStream(new File(fileName)));
ws = wb.getSheet(sheetName);
} else {
wb = new XSSFWorkbook(fileName);
ws = (XSSFSheet) wb.getSheet(sheetName);
}
}

@SuppressWarnings("unchecked")
public Object[][] getData() throws IOException  {
data = new Hashtable[ws.getPhysicalNumberOfRows()];                            
wr = ws.getRow(0);

for(rowIndex = 1; rowIndex < ws.getPhysicalNumberOfRows(); rowIndex++) {
data[rowIndex - 1] = new Hashtable <String, String>();

for (columnIndex = 0; columnIndex < ws.getRow(rowIndex).getPhysicalNumberOfCells(); columnIndex++) {
data[rowIndex - 1].put(wr.getCell(columnIndex).toString(), ws.getRow(rowIndex).getCell(columnIndex).toString());
}
}

Object[][] obj = new Object[data.length - 1][1];
for(int i = 0; i < data.length - 1; i++) {
obj[i][0] = data[i];
}
return obj;
}


       public  void close() throws IOException  {
            wb = null
            ws = null
            wr = null
            data = null
}


}

6 comments:

  1. thanx for jar ......

    will u plz elaborate about Excel package.

    regards
    manvendra

    ReplyDelete
  2. XSSF class for Excel 2007 onwards and HSSF class for excel 2003 and below.
    Just reading row by row and getting all column values.. thats all...
    Again converting my data as 2D Object to pass in my DataProvider annotation of TestNG framework...

    ReplyDelete
    Replies
    1. How i can locate from where it pick data from website or in short how i can merge Selenium code with your above code-Apache POI - Read Excel - For use of Selenium TestNG DataProvider.

      Delete
  3. Excel Code :

    package dataDrivenTestNG3;



    /* http://stackoverflow.com/questions/15873406/dataprovider-in-testng-to-pass-data-from-excel-using-java-webdriver-with-apache */

    import java.io.FileInputStream;
    import java.io.IOException;
    //// import java.io.InputStream;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;

    public class ReadXLS
    {
    public Object[][] data;
    public String excelFile = "C:\\tools\\excel.xlsx" ;
    public String sheetName = "Sheet2" ;

    public Object[][] loadFromSpreadsheet() throws IOException
    {
    FileInputStream fis = new FileInputStream(excelFile);
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheet(sheetName);

    int numberOfColumns = countNonEmptyColumns(sheet);
    int numberOfRows = sheet.getLastRowNum() + 1;

    data = new Object[numberOfRows - 1][numberOfColumns - 1];

    for (int rowNum = 1; rowNum < numberOfRows; rowNum++)
    {
    Row row = sheet.getRow(rowNum);
    if (isEmpty(row))
    {
    break;
    }
    else
    {
    for (int column = 1; column < numberOfColumns; column++)
    {
    Cell cell = row.getCell(column);
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK)
    {
    data[rowNum - 1][column - 1] = " ";
    }
    else
    {
    data[rowNum - 1][column - 1] = objectFrom(workbook, cell);
    }
    }
    }
    }

    return data;
    }

    public boolean isEmpty(Row row)
    {
    Cell firstCell = row.getCell(0);
    boolean rowIsEmpty = (firstCell == null) || (firstCell.getCellType() == Cell.CELL_TYPE_BLANK);
    return rowIsEmpty;
    }


    /**
    * Count the number of columns, using the number of non-empty cells in the
    * first row.
    */
    public int countNonEmptyColumns(Sheet sheet)
    {
    Row firstRow = sheet.getRow(0);
    return firstEmptyCellPosition(firstRow);
    }


    public int firstEmptyCellPosition(Row cells)
    {
    int columnCount = 0;
    for (Cell cell : cells)
    {
    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
    {
    break;
    }
    columnCount++;
    }
    return columnCount;
    }

    public Object objectFrom(XSSFWorkbook workbook, Cell cell)
    {
    Object cellValue = null;
    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
    {
    cellValue = cell.getRichStringCellValue().getString();
    }
    else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN)
    {
    cellValue = cell.getBooleanCellValue();
    }
    return cellValue;
    }

    ////



    }

    ReplyDelete
    Replies
    1. This Error is being thrown "The data provider is trying to pass 1 parameters but the method dataDrivenTestNG3.NavigateURL#devLogin takes 2 and TestNG is unable in inject a suitable object" when trying to run the code below :

      can you help me with this data driven program :


      package dataDrivenTestNG3;

      import java.util.concurrent.TimeUnit;
      import org.openqa.selenium.By;
      import org.openqa.selenium.WebDriver;
      import org.openqa.selenium.WebElement;
      import org.openqa.selenium.chrome.ChromeDriver;
      import org.openqa.selenium.support.ui.ExpectedConditions;
      import org.openqa.selenium.support.ui.WebDriverWait;
      import org.testng.annotations.AfterClass;
      import org.testng.annotations.Test;
      import org.testng.annotations.DataProvider;
      import org.testng.annotations.BeforeClass;

      public class NavigateURL
      {

      public static WebDriver d;
      ReadXLS login = new ReadXLS();
      Object[][] data1;

      @BeforeClass
      public void test() throws Exception
      {
      System.setProperty("webdriver.chrome.driver", "C:\\Testing Tools\\chromedriver.exe");
      d = new ChromeDriver();
      d.get("http://www.orbitz.com/");
      }

      @DataProvider(name = "importDataSheet")
      public Object[][] importDataSheet() throws Exception
      {
      Object[][] importExcelFile = null;

      return importExcelFile;
      }

      @Test(dataProvider = "importDataSheet")
      public void devLogin(String From, String To) throws Exception
      {

      WebElement bookingType = (new WebDriverWait(d , 3)).until(ExpectedConditions.presenceOfElementLocated(By.xpath("//*[@id='products']/div/fieldset/div[1]/label[1]/div")));
      bookingType.click();

      WebElement fromCity = (new WebDriverWait(d , 5)).until(ExpectedConditions.presenceOfElementLocated(By.xpath("//*[@id='search']/div[1]/div/form/fieldset/div[1]/div[1]/label[1]/input")));
      fromCity.sendKeys(From);

      WebElement toCity = (new WebDriverWait(d , 5 )).until(ExpectedConditions.presenceOfElementLocated(By.xpath("//*[@id='search']/div[1]/div/form/fieldset/div[1]/div[2]/label[1]/input")));
      toCity.sendKeys(To);

      WebElement leaveDate = d.findElement(By.cssSelector("input[name='ar.rt.leaveSlice.date']"));
      leaveDate.sendKeys("06/7/13");

      WebElement returnDate = d.findElement(By.cssSelector("input[name='ar.rt.returnSlice.date']"));
      returnDate.sendKeys("06/8/13");

      d.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
      d.findElement(By.cssSelector("input[value='Search Flights']")).click();

      d.manage().timeouts().implicitlyWait(12, TimeUnit.SECONDS);
      WebElement newHome = d.findElement(By.xpath("//*[@id='preMatrix']/div/div/div[1]/div[1]/div/a"));
      System.out.println(newHome.getText());

      }

      @AfterClass
      public void closeBrowser() throws Exception
      {
      d.quit();
      }

      }

      Delete
  4. Thanks for sharing great information in your blog. Got to learn new things from your Blog . It was very nice blog to learn about Selenium

    ReplyDelete