Read excel file in selenium webdriver using jxl

 package coaching;

import java.io.FileInputStream;

import java.io.IOException;

import java.time.Duration;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

public class JxlData {

WebDriver driver;

String [][] data = null;

@DataProvider(name="loginData")

public String [][] loginDataProvider() throws BiffException, IOException{

data=getExcelData();

// string change to object

// jxl jar used only xls format(97-2003 worksheet)

return data;

}

  public String[][] getExcelData() throws BiffException, IOException{

  

  FileInputStream excel = new FileInputStream("F:\\Suriya\\suri.xls");

   Workbook workbook = Workbook.getWorkbook(excel);

  Sheet sheet = workbook.getSheet(0); // sheet name

  int rowCount = sheet.getRows();

  int columnCount = sheet.getColumns();

  String testData[][] = new String[rowCount-1][columnCount];

  

  for (int i=1; i<rowCount;i++) {

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

  testData[i-1][j]=sheet.getCell(j, i).getContents();

  }

  }

   return testData;

  }

  @Test(dataProvider="loginData")

  public void login(String uName, String pword) {

  System.setProperty("webdriver.chrome.driver", "F:\\Suriya\\chromedriver.exe");

driver = new ChromeDriver();

driver.manage().window().maximize();

driver.manage().deleteAllCookies();

driver.manage().timeouts().pageLoadTimeout(Duration.ofSeconds(30));

driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(30));

driver.get("URL");

WebElement username = driver.findElement(By.id("userName"));

username.sendKeys(uName);

WebElement password = driver.findElement(By.id("pwd"));

password.sendKeys(pword);

WebElement login = driver.findElement(By.cssSelector(".btn:nth-child(3)"));

login.click();

    }

}

Selenium using Data Provider Method

 package coaching;

import java.time.Duration;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

public class DataProviderTest {

WebDriver driver;

@DataProvider(name = "Authentication")

 public static Object[][] credentials() {

           // The number of times data is repeated, test will be executed the same no. of times

          // Here it will execute two times

           return new Object[][] { { "suriya", "Test@123" }, { "parithy", "Test@123" }};

     }

@Test(dataProvider = "Authentication")

  public void test(String username, String password) {

System.setProperty("webdriver.chrome.driver", "F:\\Suriya\\chromedriver.exe");

driver = new ChromeDriver();

driver.manage().window().maximize();

driver.manage().deleteAllCookies();

driver.manage().timeouts().pageLoadTimeout(Duration.ofSeconds(30));

driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(30));

driver.get("URL");

WebElement username1 = driver.findElement(By.id("userName"));

username1.sendKeys(username);

                       WebElement password1 = driver.findElement(By.id("pwd"));

password1.sendKeys(password);

                      WebElement login = driver.findElement(By.cssSelector(".btn:nth-child(3)"));

login.click();

   }

}

Nodejs get data from Mysql database

 var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "12345",

  port: "3000",

  database: "project",

});

con.connect(function(err) {

  if (err) throw err;

  console.log("Connected!");

});

con.query('SELECT * FROM users', function (err, rows, fields) {

    if (err) throw err;

  console.log('The solution is: ', rows[2].standard);

  });

  con.end();

Convert PDF to Word using Python

 # Importing the Converter() class
from pdf2docx import Converter

# Specifying the pdf & docx files
pdf_file = 'ieep.pdf'
docx_file = 'sample.docx'
try:
cv = Converter(pdf_file)
cv.convert(docx_file)
cv.close()
except:
    print('Conversion Failed')
else:
    print('File Converted Successfully')

Selenium Tests using Data Provider and TestNG

 package coaching;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileReader;

import java.io.IOException;

import java.text.SimpleDateFormat;

import java.time.Duration;

import java.util.Date;

import java.util.Properties;

import org.apache.commons.io.FileUtils;

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

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

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

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

import org.openqa.selenium.By;

import org.openqa.selenium.OutputType;

import org.openqa.selenium.TakesScreenshot;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.chrome.ChromeDriver;

import org.testng.ITestResult;

import org.testng.annotations.AfterMethod;

import org.testng.annotations.AfterTest;

import org.testng.annotations.BeforeMethod;

import org.testng.annotations.BeforeTest;

import org.testng.annotations.DataProvider;

import org.testng.annotations.Test;

import com.relevantcodes.extentreports.ExtentReports;

import com.relevantcodes.extentreports.ExtentTest;

import com.relevantcodes.extentreports.LogStatus;

public class Login_Page {

public WebDriver driver;

public ExtentReports extent;

public ExtentTest extentTest; //helps to generate the logs in test report.

@BeforeTest

public void setExtent(){

// initialize the HtmlReporter

extent = new ExtentReports("./src/test/resources/Reports/LoginPageReport.html", true); // true - new data insert into report,false-append the old data

//To add system or environment info by using the addSystemInfo method.

extent.addSystemInfo("User Name", "Suriya");

extent.addSystemInfo("Environment", "Automation Testing");

extent.addSystemInfo("Application","Learning Online Course "); 

extent.addSystemInfo("Test Scenario","Login Functionality");

}

@AfterTest

public void endReport(){

extent.flush(); // Flush method is used to erase any previous data on the report and create a new report.

//extent.close(); 

}

public static String getScreenshot(WebDriver driver, String screenshotName) throws IOException{

String dateName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());

TakesScreenshot ts = (TakesScreenshot) driver;

File source = ts.getScreenshotAs(OutputType.FILE);

// after execution, you could see a folder "FailedTestsScreenshots"

// under src folder

String destination = System.getProperty("user.dir") + "/FailedTestsScreenshots/" + screenshotName + dateName

+ ".png";

File finalDestination = new File(destination);

FileUtils.copyFile(source, finalDestination);

return destination;

}

@BeforeMethod

public void setup() throws InterruptedException, IOException {

System.setProperty("webdriver.chrome.driver", "F:\\Suriya\\chromedriver.exe");

driver = new ChromeDriver();

driver.manage().window().maximize();

driver.manage().deleteAllCookies();

driver.manage().timeouts().pageLoadTimeout(Duration.ofSeconds(30));

driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(30));

driver.get("URL");

}

@Test (dataProvider = "getData") 

public void login(String Username, String Password) {

extentTest = extent.startTest("Enter username and password");

driver.manage().timeouts().implicitlyWait(Duration.ofSeconds(30));

WebElement username = driver.findElement(By.id("userName"));

username.sendKeys(Username);

WebElement password = driver.findElement(By.id("pwd"));

password.sendKeys(Password);

WebElement login = driver.findElement(By.cssSelector(".btn:nth-child(3)"));

login.click();

}

@DataProvider(name = "getData") 

// Declare a method whose return type is an array of object. 

    public Object[ ][ ] dataProviderMethod() 

    {

// Create an object of an array object and declare parameters 3 and 2. 

// 3 represents the number of times your test has to be repeated. 

// 2 represents the number of parameters in test data. Here, we are providing two parameters. 

     Object[ ][ ] data = new Object[3][2]; 

// 1st row. 

      data[0][0] = "John"; 

      data[0][1] = "23RYRTE7E5"; 

// 2nd row. 

     data[1][0] = "Sanjana"; 

     data[1][1] = "40EEUE5"; 

// 3rd row. 

     data[2][0] = "Deep"; 

     data[2][1] = "01E575E"; 

      return data; 

   } 

@AfterMethod

public void Down(ITestResult result) throws IOException{

if(result.getStatus()==ITestResult.FAILURE){

extentTest.log(LogStatus.FAIL, "TEST CASE FAILED IS "+result.getName()); //to add name in extent report

extentTest.log(LogStatus.FAIL, "TEST CASE FAILED IS "+result.getThrowable()); //to add error/exception in extent report

String screenshotPath = getScreenshot(driver, result.getName());

extentTest.log(LogStatus.FAIL, extentTest.addScreenCapture(screenshotPath)); //to add screenshot in extent report

//extentTest.log(LogStatus.FAIL, extentTest.addScreencast(screenshotPath)); //to add screencast/video in extent report

}

else if(result.getStatus()==ITestResult.SKIP){

extentTest.log(LogStatus.SKIP, "Test Case SKIPPED IS " + result.getName());

}

else if(result.getStatus()==ITestResult.SUCCESS){

extentTest.log(LogStatus.PASS, "Test Case PASSED IS " + result.getName());

}

extent.endTest(extentTest); //ending test and ends the current test and prepare to create html report

driver.quit();

}

}