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();
}
}
Complete selenium web driver tutorial
