Use these,
package com;
import java.io.File;
import java.io.IOException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.io.FileUtils;
import org.openqa.selenium.OutputType;
import org.openqa.selenium.TakesScreenshot;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.remote.DesiredCapabilities;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
public class Example {
WebDriver driver = null;
@Test(dataProvider="getData")
public void SearchTerm(String browser,String str1, String str2) {
/*int c = str2.split(str1).length - 1;
if(c>0)
System.out.println("Number of times found: "+c);*/
if(browser.equalsIgnoreCase("chrome")) {
System.setProperty("webdriver.chrome.driver", "D:\\Selenium\\Drivers\\chromedriver.exe");
driver = new ChromeDriver();
}else if(browser.equalsIgnoreCase("Firefox")){
System.setProperty("webdriver.gecko.driver", "D:\\Selenium\\Drivers\\geckodriver.exe");
DesiredCapabilities capabilities = DesiredCapabilities.firefox();
capabilities.setCapability("marionette", true);
driver = new FirefoxDriver(capabilities);
}
int i = 0;
Pattern p = Pattern.compile(str1);
Matcher m = p.matcher( str2 );
while (m.find()) {
i++;
}
System.out.println(i);
}
@Test
public void takeScreenshot() throws IOException {
File reportFile;
reportFile = new File("");;
while (reportFile.exists());
File screenshot = ((TakesScreenshot) driver).getScreenshotAs(OutputType.FILE);
FileUtils.copyFile(screenshot, reportFile);
}
@DataProvider(name = "getData", parallel = true )
public Object[][] getData() throws Exception{
XlsxReader xls = new XlsxReader("Data");
int rows = xls.getRowCount("Sheet1");
int cols = xls.getColumnCount("Sheet1");
Object[][] data = new Object[rows-1][cols];
for(int rNum=2;rNum<=rows;rNum++) {
for(int cNum=0;cNum<cols;cNum++) {
data[rNum-2][cNum] = xls.getCellData("Sheet1", cNum, rNum);
System.out.println(xls.getCellData("Sheet1", cNum, rNum));
}
}
return data;
}
}
And this xlsxreader.java
package com;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.NumberToTextConverter;
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;
public class XlsxReader
{
private XSSFWorkbook workbook;
private XSSFSheet Sheet;
private XSSFRow row;
private XSSFCell cell;
private FileInputStream fis;
private int iIndex;
private static String sFileName1 = null;
public XlsxReader(String sFileName)
{
sFileName1 = sFileName;
try
{
//app = new ConfigManager("App");
String dir = null;
File directory = new File (".");
dir = directory.getCanonicalPath();
String sFilePath = dir + "\\Data\\" + sFileName + ".xlsx";
//String sFilePath = "D:\\FrameWorks\\SeleniumJava\\WorkSpace\\GuidewirePC\\Data\\Data.xlsx";
File file = new File(sFilePath);
if(file.exists())
{
fis = new FileInputStream(sFilePath);
workbook = new XSSFWorkbook(fis);
fis.close();
}
else
{
//UtilityMethods.infoBox("File with name-'"+sFileName+"' doesn't exists in Data Folder, Please Re-check given file name", "Config.properties");
System.out.println("doesn't exists in Data Folder" );
System.exit(0);
}
}
catch (Exception e)
{
System.out.println("Exceptione is ="+e.getMessage());
//UtilityMethods.infoBox(e.getMessage(), "Exception");
System.exit(0);
}
}
public void setCellData(String sheetName,String Result, int RowNum, int ColNum) throws Exception {
iIndex = workbook.getSheetIndex(sheetName);
if(iIndex==-1)
{
//UtilityMethods.infoBox("Sheet with name-'"+sheetName+"' doesn't exists in this excel file, please Re-check given sheet name","Missing sheet");
System.exit(0);
}
Sheet = workbook.getSheetAt(iIndex);
row = Sheet.getRow(RowNum);
Cell cell2 = row.createCell(ColNum);
cell2.setCellValue(Result);
//cell = Sheet.getRow(RowNum).getCell(ColNum);
//cell.setCellValue(Result);
String dir = null;
File directory = new File (".");
dir = directory.getCanonicalPath();
String sFilePath = dir + "\\Data\\" + sFileName1 + ".xlsx";
FileOutputStream webdata=new FileOutputStream(sFilePath);
workbook.write(webdata);
}
public int getRowCount(String sheetName) throws Exception
{
int number = 0;
if(isSheetExist(sheetName))
{
Sheet = workbook.getSheetAt(iIndex);
number=Sheet.getLastRowNum()+1;
}
return number;
}
/**
* Purpose- To get column count of specified sheet
* @param sheetName- Sheet name should be provided
* @return- Returns value of column count
* @throws Exception
*/
public int getColumnCount(String sheetName) throws Exception
{
if(isSheetExist(sheetName))
{
Sheet = workbook.getSheet(sheetName);
row = Sheet.getRow(0);
if(row==null)
return -1;
return row.getLastCellNum();
}
return 0;
}
public String getCellData(String sheetName,String colName,int rowNum,int rowPadding,int columnPadding){
try{
if(isSheetExist(sheetName))
{
if(rowNum <=0)
{
//UtilityMethods.infoBox("Row number should be greater than 0", "");
System.exit(0);
return "";
}
int col_Num=-1;
Sheet = workbook.getSheetAt(iIndex);
row=Sheet.getRow(rowPadding);
for(int i=columnPadding;i<row.getLastCellNum();i++)
{
if(row.getCell(i).getStringCellValue().trim().contains(colName.trim()))
{
col_Num=i;
break;
}
}
if(col_Num==-1)
{
//UtilityMethods.infoBox("Column with specified name"+colName+" is not being displayed", "Config.properties");
System.exit(0);
return "";
}
row = Sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(col_Num);
if(cell==null)
return "";
if(cell.getCellType()==Cell.CELL_TYPE_STRING)
return cell.getStringCellValue();
else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA )
{
String cellText=NumberToTextConverter.toText(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell))
{
// format in form of D/M/YY
double d = cell.getNumericCellValue();
Calendar cal =Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
int Year = cal.get(Calendar.YEAR);
int Day = cal.get(Calendar.DAY_OF_MONTH);
int Month = cal.get(Calendar.MONTH)+1;
cellText = Day + "/" + Month + "/" + (String.valueOf(Year)).substring(2);
}
return cellText;
}
else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
return "";
else
return String.valueOf(cell.getBooleanCellValue());
}
return "";
}
catch(Exception e)
{
System.out.println("Exceptione is =" + e.getMessage());
//UtilityMethods.infoBox("row "+rowNum+" or column "+colName +" does not exist in xls", "Config.properties");
return "row "+rowNum+" or column "+colName +" does not exist in xls";
}
}
public String getCellData(String sheetName,int colNum,int rowNum){
try{
if(isSheetExist(sheetName))
{
if(rowNum <=0)
{
//UtilityMethods.infoBox("Row number should be greater than 0", "");
System.exit(0);
return "";
}
Sheet = workbook.getSheetAt(iIndex);
row = Sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(colNum);
if(cell==null)
return "";
if(cell.getCellType()==Cell.CELL_TYPE_STRING)
return cell.getStringCellValue();
else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA )
{
// String cellText = String.valueOf(cell.getNumericCellValue());
String cellText=NumberToTextConverter.toText(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell))
{
// format in form of D/M/YY
double d = cell.getNumericCellValue();
Calendar cal =Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
int Year = cal.get(Calendar.YEAR);
int Day = cal.get(Calendar.DAY_OF_MONTH);
int Month = cal.get(Calendar.MONTH)+1;
cellText = Day + "/" + Month + "/" + (String.valueOf(Year)).substring(2);
}
return cellText;
}
else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
return "";
else
return String.valueOf(cell.getBooleanCellValue());
}
return "";
}
catch(Exception e)
{
System.out.println("Exceptione is =" + e.getMessage());
//UtilityMethods.infoBox("row "+rowNum+" or column "+colNum +" does not exist in xls", "Config.properties");
return "row "+rowNum+" or column "+colNum +" does not exist in xls";
}
}
}
