QaMantra: How to read data from Excel using Column Name in Java Selenium

How to read data from Excel using Column Name in Java Selenium

Java provides different API to Read and Write a File with help of Selenium. In this Post, we will learn how can we read and write from Excel file by using Java IO package,JXL and Apache POI library.
            If your are using any Automation Framework you must know About Read and Write fro Excel, We have to use This Concept while working in Data Driven Framework .


Step to Read Data from Excel Using Apache POI jar


To Read or Write From Excel,Apache Community provides a very widely used library POI. This library have features to read and write both XLS and XLSX file format of Excel.

HSSF implementation is provided by POI library to read 'XLS' Files and XSSF implementation of POI library to read 'XLSX' File ,Lets go to Deep Die .......


Types of Classes and InterFace in Apache POI 


 

Excel Classes

    

 Read Data from Excel File

  • Go To Java Project 
  • Right Click on Java Project
  • Create a New Folder with Name 'testData'
  • Copy Any Blank Excel File from Your PC and Paste Inside Folder 'testData'
  • Open Excel File 
  • Enter The Excel Sheet Name 'LoginDataSheet'
  • Enter Any Website Login Credential Whom you want To Read
  


Excel File


ExcelName



package com.abof.generic; // As Per you Package Name 

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.ss.usermodel.WorkbookFactory;

public class GetCellValueBasedonColumnName {

 public static String readdatafromExcelusingcolumnName(String ColumnName)
   throws EncryptedDocumentException, InvalidFormatException, IOException {
  String SheetName = "LoginDataSheet";
  File file = new File(".\\testData\\data.xlsx");
  FileInputStream fi = new FileInputStream(file);
  Workbook wb = WorkbookFactory.create(fi);
  Sheet sheet = wb.getSheet(SheetName);
  // it will take value from first row
  Row row = sheet.getRow(0);
// it will give you count of row which is used or filled
  short lastcolumnused = row.getLastCellNum();

  int colnum = 0;
  for (int i = 0; i < lastcolumnused; i++) {
   if (row.getCell(i).getStringCellValue().equalsIgnoreCase(ColumnName)) {
    colnum = i;
    break;
   }
  }

  // it will take value from Second row
  row = sheet.getRow(1);
  Cell column = row.getCell(colnum);
  String CellValue = column.getStringCellValue();

  return CellValue;

 }}
       
//Main method called to ReadExcel Method
 public static void main(String[] args)
   throws EncryptedDocumentException, InvalidFormatException, IOException {
  readdatafromExcelusingcolumnName("UserName");
 
 System.out.println("user Name value is : "+value);
} }
output :  user Name value is : vivekjha.rgec@gmail.com

Please Read Previous Article For More Clarity on Hssf and xssf workbook :








No comments: