How to read and write data in excel using selenium web driver with java

Read Data from excel in Java.

public static XSSFSheet readExcel(String Path, String SheetName) {

try {

System.out.println(Path);

// Open the Excel file

FileInputStream ExcelFile = new FileInputStream(Path);

// Access the required test data sheet

ExcelWBook = new XSSFWorkbook(ExcelFile);

ExcelWSheet = ExcelWBook.getSheet(SheetName);

} catch (Exception e) {

System.out.println(e);

}

return ExcelWSheet;

}

How to read data from excel in selenium web driver with java.

To call the above method:

XSSFSheet excelSheet = TestListener.readExcel("DataFiles\\testData.xlsx", "Sheet1");

To get the Data.

String msg = excelSheet.getRow(0).getCell(1).getStringCellValue();

Complete Source code to read data from excel in selenium web driver.

import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

import pageObjects.InputFromsPage;

public class Main {

static WebDriver driver;

public static XSSFSheet excelSheet;

public static XSSFCell cell;



public static void main(String[] args) {



System.setProperty("webdriver.chrome.driver", "D:\\SeleniumAutomation\\ChromeDriver\\chromedriver.exe");

driver = new ChromeDriver();

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



// Call the method to read data from excel........ and pass two parameter path

// and sheet name.

excelSheet = TestListener.readExcel("DataFiles\\testData.xlsx", "Sheet1");



// Navigate the URL.

driver.navigate().to("http://www.seleniumeasy.com/test/");



// Click to menu bar to get input forms

driver.findElement(By.xpath(InputFromsPage.inputFromsMenu)).click();

driver.findElement(By.xpath(InputFromsPage.simpleFromDemo)).click();



// Get data from excelSheet.

String msg = excelSheet.getRow(0).getCell(1).getStringCellValue();



// Pass the data in the input field.

driver.findElement(By.xpath(InputFromsPage.inputMSG)).sendKeys(msg);

driver.findElement(By.xpath(InputFromsPage.showButton)).click();



}

}

Write data in excel in java.

public static void write(String path, String data) throws IOException, InvalidFormatException {

// Create Connection.............

InputStream inp = new FileInputStream(path);

// Access excel file.....

Workbook wb = WorkbookFactory.create(inp);

// Get the SheetNumber index start with 0.

Sheet sheet = wb.getSheetAt(0);

// Get the last row number of the current sheet.

int num = sheet.getLastRowNum();

// Increase the last row number with 1.

Row row = sheet.createRow(++num);

// Create a new cell and set the value or data.

row.createCell(0).setCellValue(data);

// Now this Write the output to a file

FileOutputStream fileOut = new FileOutputStream(path);

// Write the data

wb.write(fileOut);

// close the connection

fileOut.close();

}

To call the method:

TestListener.write("Path of the file", "Data to Write");

Complete example to write data in excel using selenium web driver with java.

import java.io.IOException;

import java.util.concurrent.TimeUnit;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

import pageObjects.InputFromsPage;



public class Main {

static WebDriver driver;



public static void main(String[] args) {



System.setProperty("webdriver.chrome.driver", "D:\\SeleniumAutomation\\ChromeDriver\\chromedriver.exe");

driver = new ChromeDriver();

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



// Navigate the URL.

driver.navigate().to("http://www.seleniumeasy.com/test/");



// Click to menu bar to get input forms

driver.findElement(By.xpath(InputFromsPage.inputFromsMenu)).click();

driver.findElement(By.xpath(InputFromsPage.simpleFromDemo)).click();



// Pass the data in the input field.

driver.findElement(By.xpath(InputFromsPage.inputMSG)).sendKeys("Hello");



// Get the button text

String BtnText = driver.findElement(By.xpath(InputFromsPage.showButton)).getText();

try {



// Call the write method

TestListener.write("DataFiles\\testData.xlsx", BtnText);

} catch (InvalidFormatException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

driver.findElement(By.xpath(InputFromsPage.showButton)).click();



}



}

 

 

 

Add a Comment

Your email address will not be published. Required fields are marked *

15 − twelve =