Motivation

Simple automation script to take the information from excel in the list from and automate tasks in the browser

package com.workon.rbbsendissue;

import java.util.HashMap;
import java.util.Map;

import org.openqa.selenium.By;
import org.openqa.selenium.Dimension;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

//Latest modified for the cnotleave 
public class SendIssue {


    private static WebDriver driver;
    private static Map<String, Object> vars;
    static JavascriptExecutor js;

    public static String rootDir = System.getProperty("user.dir");
    public static ExcelReader excel = new ExcelReader(rootDir + "\\src\\main\\resources\\excel\\testdata.xlsx");


//    public static ExcelReader excel = new ExcelReader(rootDir + "\\src\\main\\resources\\excel\\cnotleave.xlsx");

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

        System.out.println("inside main");

        String sheetName = "RequestSheet";
        int rows = excel.getRowCount(sheetName);

        System.out.println(rows);
        for (int rNum = 2; rNum <= rows; rNum++) {

            String RequestId = excel.getCellData(sheetName, "REQUESTS", rNum);
            System.out.println(RequestId);

            sendIssueOperation(RequestId);

        }

    }

    public static void sendIssueOperation(String requestId) throws InterruptedException {

        System.out.println("inside send Issue Operation");
        String firefoxdriverpath = System.getProperty("user.dir")
                + "\\src\\main\\resources\\executable\\geckodriver.exe";

        System.setProperty("webdriver.gecko.driver", firefoxdriverpath);

        driver = new FirefoxDriver();
        js = (JavascriptExecutor) driver;
        vars = new HashMap<String, Object>();

    //    String url = "https://rb-wam-q.bosch.com/workon01/workflow03q/browse/" + requestId;

        String url = "https://rb-wam-ap.bosch.com/workon02ap/browse/" + requestId;


        //https://rb-wam.bosch.com/workon01/workflow03/browse/

        driver.get(url);
        // 2 | setWindowSize | 1552x840 |
        driver.manage().window().setSize(new Dimension(1552, 840));

        Thread.sleep(3000);

        // 3 | click | linkText=Send Update Issue |
//        driver.findElement(By.linkText("Send Update Issue")).click();


//        driver.findElement(By.xpath("//a[normalize-space()='Updated']")).click();



        try {
            driver.findElement(By.xpath("//a[normalize-space()='Updated']")).click();
            driver.quit();

        } catch (org.openqa.selenium.NoSuchElementException e) {
            driver.quit();
        }


        Thread.sleep(3000);
        // 4 | click | id=comment |
//        driver.findElement(By.id("comment")).click();
        // 5 | click | id=comment |
//        driver.findElement(By.id("comment")).click();
        // 6 | type | id=comment | testing oease ignore
//        driver.findElement(By.id("comment")).sendKeys(" Retriggering Issue Status Update to OneQ ");

//        Thread.sleep(2000);
        // 7 | click | id=WorkOnSubmitButton |
//        driver.findElement(By.id("WorkOnSubmitButton")).click();

//        Thread.sleep(2000);

//        driver.quit();

        Thread.sleep(2000);
    }
}

Excel

package com.workon.rbbsendissue;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {

    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 ExcelReader(String path) {

        this.path=path;
        try {
            fis = new FileInputStream(path);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheetAt(0);
            fis.close();
        } catch (Exception e) {

            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 "";

        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)) {

                  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;



                 }



              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;



             }



          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.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){

        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++){

            if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(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.setCellValue(data);
        XSSFCreationHelper createHelper = workbook.getCreationHelper();

        //cell style for hyperlinks

        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){


        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);


        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){


        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)){

                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;

    }




}