Showing posts with label Read Data from Excel. Show all posts
Showing posts with label Read Data from Excel. Show all posts

Friday, May 30, 2014

How to read and write the data from Excel file using apache poi.

1) Method to read data from excel file-

public static String getCellValue(String pathOfFile, String sheetName, int rowNum, int cellNum) throws InvalidFormatException, IOException{
        FileInputStream fis = new FileInputStream(pathOfFile);
        Workbook wb = WorkbookFactory.create(fis);
        int type = wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getCellType();
        String value = "";
        if(type==Cell.CELL_TYPE_STRING){
            value = wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getStringCellValue();   
        }else if(type==Cell.CELL_TYPE_NUMERIC){
            int numValue = (int) wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getNumericCellValue();
            value = ""+numValue;
        }else if(type==Cell.CELL_TYPE_BOOLEAN){
            boolean boolValue =  wb.getSheet(sheetName).getRow(rowNum).getCell(cellNum).getBooleanCellValue();
            value = ""+boolValue;
        }
        return value;
    }
   
2) Method to write the data into file


public static void writeData(String pathOfFile, String sheetName, int rowNum, int cellNum, String value) throws InvalidFormatException, IOException{
        FileInputStream fis = new FileInputStream(pathOfFile);
        Workbook wb = WorkbookFactory.create(fis);
        wb.getSheet(sheetName).getRow(rowNum).createCell(cellNum).setCellValue(value);
        //wb.getSheet(sheetName).createRow(rowNum).createCell(cellNum).setCellValue(value); //use this if you are writing in new row.
        FileOutputStream fos = new FileOutputStream(pathOfFile);
        wb.write(fos);
    }

Saturday, May 10, 2014

Login Logout scenario for yahoo.com

Here is the code to login and logout in yahoo.com. Here its fetching the userid and password from excel sheet.

import java.awt.AWTException;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.concurrent.TimeUnit;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.interactions.Actions;

public class YahooLoginLogOut {
    public static void main(String[] args) throws AWTException, InterruptedException, InvalidFormatException, IOException {
    //get the data from excel sheet
            FileInputStream fis = new FileInputStream("./InputData.xlsx");
            Workbook wb = WorkbookFactory.create(fis);
            String emailId = wb.getSheet("Sheet2").getRow(0).getCell(0).getStringCellValue();
            String password = wb.getSheet("Sheet2").getRow(1).getCell(0).getStringCellValue();
           
            //open browser and Yahoo
            WebDriver driver = new FirefoxDriver();
            driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);
            driver.get("https://in.yahoo.com/");
            Actions act = new Actions(driver);
            //login
            act.moveToElement(driver.findElement(By.xpath("//em[text()='Sign In']"))).perform();
            driver.findElement(By.xpath("//span//a[@class='login-svc ylogin login-btn-purple rapid-noclick-resp login-btn-small']")).click();
            driver.findElement(By.id("username")).sendKeys(emailId);
            driver.findElement(By.id("passwd")).sendKeys(password);
            driver.findElement(By.xpath("//button[contains(text(),'Sign in')]")).click();
           
            //sign out
            act.moveToElement(driver.findElement(By.xpath("//img[contains(@class,'tab-icon')]"))).perform();
            driver.findElement(By.xpath("//span[text()='Sign Out']")).click();
           
            driver.close();
    }
}