Excel reading java file for framework

public class Xls_Reader {
public static String filename = System.getProperty("user.dir")+"\\src\\config\\testcases\\TestData.xlsx";
public  String path;
public  FileInputStream fis = null;
public  FileOutputStream fileOut =null;
private XSSFWorkbook workbook = null;
private XSSFSheet sheet = null;
private XSSFRow row   =null;
private XSSFCell cell = null;

public Xls_Reader(String path) {

this.path=path;
try {
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
fis.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
// returns the row count in a sheet
public int getRowCount(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return 0;
else{
sheet = workbook.getSheetAt(index);
int number=sheet.getLastRowNum()+1;
return number;
}

}

// returns the data from a cell
public String getCellData(String sheetName,String colName,int rowNum){
try{
if(rowNum <=0)
return "";

int index = workbook.getSheetIndex(sheetName);
int col_Num=-1;
if(index==-1)
return "";

sheet = workbook.getSheetAt(index);
row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
col_Num=i;
}
if(col_Num==-1)
return "";

sheet = workbook.getSheetAt(index);
row = sheet.getRow(rowNum-1);
if(row==null)
return "";
cell = row.getCell(col_Num);

if(cell==null)
return "";
//System.out.println(cell.getCellType());
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());
 if (HSSFDateUtil.isCellDateFormatted(cell)) {
          // format in form of M/D/YY
 double d = cell.getNumericCellValue();

 Calendar cal =Calendar.getInstance();
 cal.setTime(HSSFDateUtil.getJavaDate(d));
           cellText =
            (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
          cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +
                     cal.get(Calendar.MONTH)+1 + "/" +
                     cellText;
         
          //System.out.println(cellText);

        }



 return cellText;
 }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
     return "";
 else
 return String.valueOf(cell.getBooleanCellValue());

}
catch(Exception e){

e.printStackTrace();
return "row "+rowNum+" or column "+colName +" does not exist in xls";
}
}

// returns the data from a cell
public String getCellData(String sheetName,int colNum,int rowNum){
try{
if(rowNum <=0)
return "";

int index = workbook.getSheetIndex(sheetName);

if(index==-1)
return "";


sheet = workbook.getSheetAt(index);
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());
 if (HSSFDateUtil.isCellDateFormatted(cell)) {
          // format in form of M/D/YY
 double d = cell.getNumericCellValue();

 Calendar cal =Calendar.getInstance();
 cal.setTime(HSSFDateUtil.getJavaDate(d));
           cellText =
            (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
          cellText = cal.get(Calendar.MONTH)+1 + "/" +
                     cal.get(Calendar.DAY_OF_MONTH) + "/" +
                     cellText;
         
         // System.out.println(cellText);

        }



 return cellText;
 }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
     return "";
 else
 return String.valueOf(cell.getBooleanCellValue());
}
catch(Exception e){

e.printStackTrace();
return "row "+rowNum+" or column "+colNum +" does not exist  in xls";
}
}

// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String colName,int rowNum, String data){
try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);

if(rowNum<=0)
return false;

int index = workbook.getSheetIndex(sheetName);
int colNum=-1;
if(index==-1)
return false;


sheet = workbook.getSheetAt(index);


row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equals(colName))
colNum=i;
}
if(colNum==-1)
return false;

sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum-1);
if (row == null)
row = sheet.createRow(rowNum-1);

cell = row.getCell(colNum);
if (cell == null)
       cell = row.createCell(colNum);

   // cell style
   //CellStyle cs = workbook.createCellStyle();
   //cs.setWrapText(true);
   //cell.setCellStyle(cs);
   cell.setCellValue(data);

   fileOut = new FileOutputStream(path);

workbook.write(fileOut);

   fileOut.close();

}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}


// returns true if data is set successfully else false
public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){
//System.out.println("setCellData setCellData******************");
try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);

if(rowNum<=0)
return false;

int index = workbook.getSheetIndex(sheetName);
int colNum=-1;
if(index==-1)
return false;


sheet = workbook.getSheetAt(index);
//System.out.println("A");
row=sheet.getRow(0);
for(int i=0;i<row.getLastCellNum();i++){
//System.out.println(row.getCell(i).getStringCellValue().trim());
if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))
colNum=i;
}

if(colNum==-1)
return false;
sheet.autoSizeColumn(colNum); //ashish
row = sheet.getRow(rowNum-1);
if (row == null)
row = sheet.createRow(rowNum-1);

cell = row.getCell(colNum);
if (cell == null)
       cell = row.createCell(colNum);

   cell.setCellValue(data);
   XSSFCreationHelper createHelper = workbook.getCreationHelper();

   //cell style for hyperlinks
   //by default hypelrinks are blue and underlined
   CellStyle hlink_style = workbook.createCellStyle();
   XSSFFont hlink_font = workbook.createFont();
   hlink_font.setUnderline(XSSFFont.U_SINGLE);
   hlink_font.setColor(IndexedColors.BLUE.getIndex());
   hlink_style.setFont(hlink_font);
   //hlink_style.setWrapText(true);

   XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
   link.setAddress(url);
   cell.setHyperlink(link);
   cell.setCellStyle(hlink_style);
   
   fileOut = new FileOutputStream(path);
workbook.write(fileOut);

   fileOut.close();

}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}



// returns true if sheet is created successfully else false
public boolean addSheet(String  sheetname){

FileOutputStream fileOut;
try {
workbook.createSheet(sheetname);
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
    fileOut.close();  
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}

// returns true if sheet is removed successfully else false if sheet does not exist
public boolean removeSheet(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return false;

FileOutputStream fileOut;
try {
workbook.removeSheetAt(index);
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
   fileOut.close();  
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
// returns true if column is created successfully
public boolean addColumn(String sheetName,String colName){
//System.out.println("**************addColumn*********************");

try{
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
int index = workbook.getSheetIndex(sheetName);
if(index==-1)
return false;

XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

sheet=workbook.getSheetAt(index);

row = sheet.getRow(0);
if (row == null)
row = sheet.createRow(0);

//cell = row.getCell();
//if (cell == null)
//System.out.println(row.getLastCellNum());
if(row.getLastCellNum() == -1)
cell = row.createCell(0);
else
cell = row.createCell(row.getLastCellNum());
     
       cell.setCellValue(colName);
       cell.setCellStyle(style);
     
       fileOut = new FileOutputStream(path);
workbook.write(fileOut);
   fileOut.close();  

}catch(Exception e){
e.printStackTrace();
return false;
}

return true;


}
// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try{
if(!isSheetExist(sheetName))
return false;
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet=workbook.getSheet(sheetName);
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
XSSFCreationHelper createHelper = workbook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);

 

for(int i =0;i<getRowCount(sheetName);i++){
row=sheet.getRow(i);
if(row!=null){
cell=row.getCell(colNum);
if(cell!=null){
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fileOut = new FileOutputStream(path);
workbook.write(fileOut);
   fileOut.close();
}
catch(Exception e){
e.printStackTrace();
return false;
}
return true;

}
  // find whether sheets exists
public boolean isSheetExist(String sheetName){
int index = workbook.getSheetIndex(sheetName);
if(index==-1){
index=workbook.getSheetIndex(sheetName.toUpperCase());
if(index==-1)
return false;
else
return true;
}
else
return true;
}

// returns number of columns in a sheet
public int getColumnCount(String sheetName){
// check if sheet exists
if(!isSheetExist(sheetName))
return -1;

sheet = workbook.getSheet(sheetName);
row = sheet.getRow(0);

if(row==null)
return -1;

return row.getLastCellNum();



}
//String sheetName, String testCaseName,String keyword ,String URL,String message
public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){
//System.out.println("ADDING addHyperLink******************");

url=url.replace('\\', '/');
if(!isSheetExist(sheetName))
return false;

   sheet = workbook.getSheet(sheetName);
 
   for(int i=2;i<=getRowCount(sheetName);i++){
    if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){
    //System.out.println("**caught "+(i+index));
    setCellData(sheetName, screenShotColName, i+index, message,url);
    break;
    }
   }


return true;
}
public int getCellRowNum(String sheetName,String colName,String cellValue){

for(int i=2;i<=getRowCount(sheetName);i++){
    if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){
    return i;
    }
   }
return -1;

}

// to run this on stand alone
public static void main(String arg[]) throws IOException{

//System.out.println(filename);
Xls_Reader datatable = null;


datatable = new Xls_Reader("PATH OF YOUR CONTROLLER xlsx SHEET");
for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){
System.out.println(datatable.getCellData("TC5", col, 1));
}
}


}

String operation functions

public class Stringfunction {


public static void main(String[] args) {
// object of string class is made
String x= "hello how are you. Is is a good day.";
//String c = new String("ssss sss");

System.out.println(x.charAt(10));
System.out.println(x.indexOf('a',11));
System.out.println(x.equals("hello"));
System.out.println(x.substring(10, 20));// end index -1
System.out.println(x.indexOf("XXXXXXX"));


// split
System.out.println("**********************");
String[] arr = x.split("o");

for(int i=0 ; i< arr.length ; i++){
System.out.println(arr[i]);
}
System.out.println("*********CONVERSION************");
// conversion
String y="1234";
int a=Integer.parseInt(y);
System.out.println(a);

String c=String.valueOf(a);
System.out.println(c);

}

}

XLs Reading and writing in java

public class ReadWrite_XLS {


public static void main(String[] args) {

Xls_Reader d = new Xls_Reader("C:\\iam.xlsx");
System.out.println(d.getRowCount("Data"));
System.out.println(d.getCellData("Data", "Place", 3));

d.setCellData("Data", "Name", 10, "hello");


//Xls_Reader d1 = new Xls_Reader("PATH");

}

}

Properties file: Reading Properties files

First you need to create  iam.properties file in our webdriver project


name=yogi
college=crazyfun
age=22

browser=firefox
testSiteName=xyz.com
environment=UAT
//**********************



public class ReadProperties {


public static void main(String[] args) throws IOException {

FileInputStream fs = new FileInputStream("F:\\Workspaces\\..\\..\\iam.properties");
Properties prop = new Properties();
prop.load(fs);

System.out.println(prop.getProperty("college"));
System.out.println(prop.getProperty("company"));
System.out.println(prop.getProperty("name"));


}

}

Creating log file for Selenium webdriver(Test run)

Code: Application Logs


log4j.logger.devpinoyLogger=DEBUG, dest1
log4j.appender.dest1=org.apache.log4j.RollingFileAppender
log4j.appender.dest1.maxFileSize=5000KB
log4j.appender.dest1.maxBackupIndex=3
log4j.appender.dest1.layout=org.apache.log4j.PatternLayout
log4j.appender.dest1.layout.ConversionPattern=%d{dd/MM/yyyy HH:mm:ss} %c %m%n
log4j.appender.dest1.File=C:\\Application.log
#do not append the old file. Create a new log file everytime
log4j.appender.dest1.Append=false

Logging Example:


public class LogExample {


public static void main(String[] args) {

// add log4j.jar
// add log4j.propertie directly inside the src folder
// create the object in the code

Logger APPLICATION_LOGS = Logger.getLogger("devpinoyLogger");
APPLICATION_LOGS.debug("hello");
APPLICATION_LOGS.debug("We are wrinting in to a log file");
APPLICATION_LOGS.debug("starting the test case xyz test");


}

}

File handling : Creating ,Reading ,Writing Text files

Code:

public class CreatingReadingWritingTextFiles {

public static void main(String[] args) throws IOException {

// creating

File f = new File("C:\\temp.txt");

f.createNewFile();

// writing

FileWriter w = new FileWriter("C:\\temp.txt");

BufferedWriter out = new BufferedWriter(w);

out.write("hello we are writing in a file");

out.newLine();

out.write("This is a new line");

out.flush();

//reading

FileReader r = new FileReader("C:\\temp.txt");

BufferedReader bfr = new BufferedReader(r);

String x ="";

//System.out.println(bfr.readLine());

//System.out.println(bfr.readLine());

//System.out.println(bfr.readLine());

while((x = bfr.readLine()) != null){

System.out.println(x);

}

}

}

Why choose selenium for automation testing ?

– Freedom to Choose language, you are expertise in

@ Selenium you can choose the language of your choice. This is an advantage which has an indirect benefit towards organization. For example if your organization is a Java based, you already have a knowledge and technical expertise available with range of tools (e.g. IDE, build systems, reporting tools etc.) that your development branch uses. It would be just the matter of knowledge sharing among the Development and QA folks

–Develop Script once, Run the same on multiple browser

@ Selenium we can develop test script using any one of the browser and can execute it with different browser, the advantage of running your tests on multiple browsers with the same set of scripts is an enormous advantage when you have to test your application on different browser.

–Special handling of Ajax Control

Handling Ajax Control is always a challenging job for Automation Testing; Selenium has got built in features to handle Ajax Control by Implementing Proper Sync

– Lightweight Tool

Generally Automation tools are heavyweight because of its in built features. @ Selenium does not need to install the tool. This is just a Firefox plugin, which records the performed action and play backs the same.  Even it is just a Firefox plugin , but it avails all the features what a normal automation tool should have – record and playback , Checkpoint , data Driving through java script (extension to IDE) , etc………..

—Support multiple test frameworks

Contrary to common thinking, selenium is not a testing tool. It is more like a GUI driving library.  This is a blessing.  Selenium allows users to wrap the test scripts in the framework of their choice. So if you like JUnit or TestNG, you have a choice and also taking advantage of reporting capabilities of this framework that your development team might be already using.

—Parallel Script Execution

This is the feature, which makes Selenium special. @ Selenium we can run same or different script in parallel over same or different browser.The Concept of parallel execution comes with Selenium Grid, which needs to be specially configured using one of the IDE like: - Eclipse.

Personal View @ Selenium…..

Often Selenium is free (open source) alternative to commercial tools like QuickTest Pro or Silk Test. .It is wise decision to go with Selenium if application interacts only with web applications.@ Selenium the resource should possess good Skillset, since it provides control to user for better customization unlike other tool which is enrich  with  built in feature like- Quick Test pro.