Apache POI-How to Read the Inputs and Print the Output in Excel Using Apache POI in Selenium

Introduction of Apache POI:

It is used to Create,Modify Edit the MS office files Using Java Programs.

It is open source library developed by Apache software foundation

Mainly It contain Two classes

HSSFWorkbook--->Horriable Style Sheet Format

It Support only .xls File Format in the Version(97-2003)

XSSFWorkbook----->XML SpreadSheet Format 


It Support only .xlsx and .xls File Format in the Version(97-2003)&(above 2010 versions) 

In the Below Program How to Read the Input From Excel and Print the Output in Excel

Here I am Creating Two Classes

1)Excel Util

In the inside of class creating Four Methods
a) setexcel--->To Declare a Method for To Open the Excel and Sheet
b)getcell------>To  Declare a Method for read the Inputs in the Particular row and column
c)setcell------->To Declare a Method for Print the Method In the Particular row and column
d)setexcel1 ------->To Declare a Method for To close the Excel and Sheet

2)Inputs  

To Call all the Methods in the Input class using inheritance concept and Test the Elemenets with multiple inputs and print the output in Excel

import java.io.IOException;

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

public class Inputs extends ExcelUtil {

    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub

        ExcelUtil.setexcel("d:\\test.xlsx","Sheet0");
       
        WebDriver g=new FirefoxDriver();
       
        g.get("http://newtours.demoaut.com/");
       
        for(int i=1;i<=2;i++)
        {
       
        g.findElement(By.name("userName")).sendKeys(ExcelUtil.getcell(i,0));
       
        g.findElement(By.name("password")).sendKeys(ExcelUtil.getcell(i, 1));
       
        g.findElement(By.name("login")).click();
       
        String at=g.getTitle();
       
        if(at.equals("test"))
        {
            ExcelUtil.setcell("pass", i, 2);
        }
        else
        {
            ExcelUtil.setcell("fail", i, 2);
        }
       
        ExcelUtil.setexcel1("d:\\test.xlsx","Sheet0");
        }
       
    }

}


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelUtil {
   

public static XSSFWorkbook wb;

public static XSSFSheet sh;

public static XSSFRow r;

public static XSSFCell c;

public static void setexcel(String path,String Sheetname) throws IOException

{
    FileInputStream fs = new FileInputStream(path);
    // Access the required test data sheet
    wb = new XSSFWorkbook(fs);
    sh = wb.getSheet(Sheetname);
}

public static String getcell(int row,int col)
{
c=sh.getRow(row).getCell(col);
   
    String value = null;
   
    if(c.getCellType() == c.CELL_TYPE_NUMERIC) {
        int i = (int)c.getNumericCellValue();
        value = String.valueOf(i);
    }
        else if(c.getCellType()==c.CELL_TYPE_NUMERIC)
        {
            long d= (long)c.getNumericCellValue();
            value=String.valueOf(d);
        }
         else  {
        value = c.getStringCellValue();
        }
   
    return value;
}

public static void setcell(String result,int rno,int cno)
{
     r  = sh.getRow(rno);
     c = r.getCell(cno, r.RETURN_BLANK_AS_NULL);
                if (c == null) {
                    c = r.createCell(cno);
                    c.setCellValue(result);
                    } else {
                        c.setCellValue(result);
}
}

public static void setexcel1(String paths,String sheets) throws

{
    FileOutputStream fileOut = new FileOutputStream(paths);
    wb.write(fileOut);
    fileOut.flush();
  fileOut.close();
}
   
   
}