Writing and Reading Links in excel fetched from website and writing status of link in excel

WRITING IN EXCEL: 

package excelExportAndFileIO;

import java.io.IOException;

import java.util.List;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

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

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

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

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

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

public class Write {

public static void main(String...strings )  throws IOException

{

Write object =new Write();

String driverPath=System.getProperty("user.dir")+"/drivers/chromedriver.exe";

System.setProperty("webdriver.chrome.driver",driverPath);

WebDriver driver = new ChromeDriver();

  driver.get("https://www.thesun.co.uk/");

System.out.println("opening of webpage");

List<WebElement> links=driver.findElements(By.tagName("a"));

System.out.println("feaching links from webpage");

System.out.println("writting links");

String filePath= System.getProperty("user.dir")+"\\excel\\ExportExcel.xlsx";

String sheetName="sheet1";

File file =    new File(filePath);

FileInputStream inputStream = new FileInputStream(filePath);

Workbook writeWorkbook = new XSSFWorkbook(inputStream);

Sheet sheet = writeWorkbook.getSheet(sheetName);

int rowCount=0;

int columnCount=0;

for(WebElement link:links)

{

Row newRow = sheet.createRow(rowCount++);

Cell cell = newRow.createCell(columnCount);

cell.setCellValue(link.getAttribute("href"));

}

System.out.println("URL-----"+links.size());

FileOutputStream outputStream = new FileOutputStream(file);

writeWorkbook.write(outputStream);

driver.close();

}

}

READING LINKS FROM EXCEL AND OPENING IN BROWSER:

package excelExportAndFileIO;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

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

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

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

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

import org.openqa.selenium.By;

import org.openqa.selenium.Keys;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.chrome.ChromeDriver;

public class Read {

public String readExcel(int rowNum,String sheetName) throws IOException{

String filePath = System.getProperty("user.dir")+"\\excel\\ExportExcel.xlsx";

FileInputStream inputStream = new FileInputStream(filePath);

Workbook readWorkbook = null;

readWorkbook = new XSSFWorkbook(inputStream);

Sheet readSheet = readWorkbook.getSheet(sheetName);

int rowCount = readSheet.getLastRowNum();

System.out.println("Row Count"+rowCount);

Row row = readSheet.getRow(rowNum);

return getCellData(row);

}

public static String getCellData(Row row){

   

    String value="";

    try{

    if(row.getCell(0).getCellType()!=Cell.CELL_TYPE_BLANK){

    value= row.getCell(0).getStringCellValue();

      

    }

    }catch(NullPointerException e){

    }

return value;

    }

public static void main(String...strings) throws IOException{

Read objExcelFile = new Read();

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

String driverPath=System.getProperty("user.dir")+"/drivers/chromedriver.exe";

System.setProperty("webdriver.chrome.driver",driverPath);

WebDriver driver = new ChromeDriver();

driver.get(objExcelFile.readExcel(i,"Sheet1"));

System.out.println(objExcelFile.readExcel(i,"Sheet1"));

}

}

}

VALIDATION OF LINKS IN EXCEL:

package excelExportAndFileIO;

import java.io.IOException;

import java.net.HttpURLConnection;

import java.net.URL;

import java.util.List;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

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

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

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

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

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

public class ValidLink {

public static void main(String...strings )  throws IOException{

ValidLink object =new ValidLink();

String driverPath=System.getProperty("user.dir")+"/drivers/chromedriver.exe";

System.setProperty("webdriver.chrome.driver",driverPath);

WebDriver driver = new ChromeDriver();

  driver.get("https://www.thesun.co.uk");

  //driver.get("https://www.google.com");

System.out.println("opening of webpage");

List<WebElement> links=driver.findElements(By.tagName("a"));

System.out.println("feaching links from webpage");

System.out.println("writting links");

String filePath= System.getProperty("user.dir")+"\\excel\\linkValid.xlsx";

String sheetName="sheet1";

File file =    new File(filePath);

FileInputStream inputStream = new FileInputStream(filePath);

Workbook writeWorkbook = new XSSFWorkbook(inputStream);

Sheet sheet = writeWorkbook.getSheet(sheetName);

int rowCount=0;

int columnCount1=1,columnCount2=2;

for(WebElement link:links)

{

Row newRow = sheet.createRow(rowCount++);

Cell cell = newRow.createCell(columnCount1);

cell.setCellValue(link.getAttribute("href"));

try 

       {

String linkUrl=link.getAttribute("href");

URL url = new URL(linkUrl);

HttpURLConnection httpURLConnect=(HttpURLConnection)url.openConnection();

httpURLConnect.setConnectTimeout(3000);

httpURLConnect.connect();

newRow.createCell(columnCount2).setCellValue(httpURLConnect.getResponseCode());

if(httpURLConnect.getResponseCode()==200)

{

newRow.createCell(columnCount2).setCellValue(httpURLConnect.getResponseCode());

rowCount++;

System.out.println(linkUrl+" - "+httpURLConnect.getResponseMessage());

}

     

       }

catch (Exception e)

{

           

       }

break;

  

}

System.out.println("URL-----"+links.size());

FileOutputStream outputStream = new FileOutputStream(file);

writeWorkbook.write(outputStream);

driver.close();

}}