Excel Codes for selenium - Draft

10) //Read Excel file             
File src = new File("C:\\Selenium\\Datasheet.xlsx");       
FileInputStream fis = new FileInputStream(src);       
XSSFWorkbook wb = new XSSFWorkbook(fis);       
XSSFSheet sh = wb.getSheetAt(0);       
int TotalRowCount = sh.getLastRowNum();

11)//Read Excel Cell Value       
String cellValueUsername = sh.getRow(1).getCell(0).getStringCellValue();       
System.out.println(cellValueUsername);

12) //Write Excel File         
FileOutputStream Fout = new FileOutputStream(new File( "C:\\Selenium\\Datasheet.xlsx"));
wb.write(Fout);       
Fout.close();

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

import org.apache.poi.ss.usermodel.CellType;

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

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

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

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

@DataProvider(name = "validCredentialsSupplier")

public Object[][] supplyTestData() {

Object[][] data = Utilities.getTestDataFromExcel("Login");

return data;

}

public static Object[][] getTestDataFromExcel(String sheetName) {

File excelFile = new File(

System.getProperty("user.dir") + "/src/main/java/com/tutorialsninja/qa/testdata/TestData.xlsx");

XSSFWorkbook workbook = null;

try {

FileInputStream fisExcel = new FileInputStream(excelFile);

workbook = new XSSFWorkbook(fisExcel);

} catch (Throwable e) {

e.printStackTrace();

}

XSSFSheet sheet = workbook.getSheet(sheetName);

int rows = sheet.getLastRowNum();

short cols = sheet.getRow(0).getLastCellNum();

Object[][] data = new Object[rows][cols];

for (int i = 0; i < rows; i++) {

XSSFRow row = sheet.getRow(i + 1);

for (int j = 0; j < cols; j++) {

XSSFCell cell = row.getCell(j);

CellType cellType = cell.getCellType();

switch (cellType) {

case STRING:

data[i][j] = cell.getStringCellValue();

break;

case NUMERIC:

data[i][j] = Integer.toString((int) cell.getNumericCellValue());

break;

case BOOLEAN:

data[i][j] = cell.getBooleanCellValue();

break;

}

}

}

return data;

}

}