What is Poi apache?How to read /Write Excel sheet in Selenium.

File Io is a anaytic part for any software  We frequently create a file, open it & update something or delete it in our Computers. Same is the case with Selenium Automation. We need a process to manipulate files with Selenium.

Java provides us different classes for File Manipulation with Selenium. In this  we are going to learn how can we read and write on excel file with the help of Java IO package and Apache  POI library.
What is Apache poi?
For Reading and writing in excel sheet  we have to configure poi jar files.

Step1:Download poi jar files from here.

We just need 5 jar files for reading excel files.

dom4j
xmlbeans-2.3.0.jar
poi-3.6
poi-ooxml-3.6
poi-ooxml-schemes
1.Add these jar files into the project.
2.Add selenium jar files into project.
For reference you can see how to add jar files set up a selenium project.

Click here

3.Yo need to
How to read excel file in selenium?
Step1:

Some prerequisites
(Create a project>Make a new class>Add jar files>Add poi jar files.)

How to read data from Excel sheet.
This is the link for downloading excel file.Please change the path according to your system.

How to read data from excel sheet.
How to get data from rows.
How to set data in Excel sheet.
public class ReadingExcelsheet {
public static void main(String[] args) {
//Make an object of Xls_Reader

Before making object of that class you have to copy this code from here.

Now copy this code into your project.
If you can't download from here.You can copy from below.
Xls_Reader datatable = new Xls_Reader("C:\\Users\\Ritika Gulati\\Documents\\Datatest.xlsx");
Please change path according to your system.
//How to count total number of rows in excel sheet
// datatable.getRowCount(sheetName)
int rowdata =  datatable.getRowCount("Records");
System.out.println("Total Rows in excel sheet           "+rowdata);
//How to get the data from excelsheet
// datatable.getCellData(sheetName, colNum, rowNum)

String data=  datatable.getCellData("Records", "City", 3);
System.out.println("This is data from 3rd row ,colomn name is city and sheet name records       "+data);
String data2=  datatable.getCellData("Records", "City", 2);
System.out.println("data from 2nd row,colomn name is city and sheet name records "+data2);
//How to set data in excel sheet
//datatable.setCellData(sheetName, colName, rowNum, data2)
datatable.setCellData("Records", "City", 10, "new data entered ");
}
}

//Below Reference code if you can't download  code from google drive.
You just have to copy this code and paste into your project.You just have to create an object of this class so that you can use all the methods.

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
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.*;


import java.io.*;
import java.util.Calendar;


public class Xls_Reader {
public static String filename = System.getProperty("user.dir")+"\\src\\config\\testcases\\TestData.xlsx";
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 Xls_Reader(String path) {

this.path=path;
try {
fis = new FileInputStream(path);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);
fis.close();
} catch (Exception e) {
// TODO Auto-generated catch block
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 "";
//System.out.println(cell.getCellType());
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.DAY_OF_MONTH) + "/" +
                     cal.get(Calendar.MONTH)+1 + "/" +
                     cellText;
       
          //System.out.println(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;
       
         // System.out.println(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 style
   CellStyle cs = workbook.createCellStyle();
   cs.setWrapText(true);
   cell.setCellStyle(cs);
   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){
//System.out.println("setCellData setCellData******************");
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);
//System.out.println("A");
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().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
   //by default hypelrinks are blue and underlined
   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){
//System.out.println("**************addColumn*********************");

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

//cell = row.getCell();
//if (cell == null)
//System.out.println(row.getLastCellNum());
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){
//System.out.println("ADDING addHyperLink******************");

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)){
    //System.out.println("**caught "+(i+index));
    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;

}

// to run this on stand alone
public static void main(String arg[]) throws IOException{

//System.out.println(filename);
Xls_Reader datatable = null;


datatable = new Xls_Reader("C:\\riti\\Fr_Wles\\data.xlsx");
for(int col=0 ;col< datatable.getColumnCount("TC"); col++){
System.out.println(datatable.getCellData("TC", col, 1));
}
}

}

Read More