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 .
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 .......
Read Data from Excel File
Write Data To Excel File
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
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 .......
- For user who is using Maven Project can copy Dependency from here
org.apache.poi poi 3.17
- For user Who is using Simple Java Project Can know How To Download Apache POI Jar and Add To JAVA Build Path
Types of Files in MS-Excel
- .XLS File
- .XLSX File
Types of Classes and InterFace in Apache POI
![]() |
Apache Interface and Classes |
Read Data from Excel File
- Go To Java Project
- Right Click on Java Project
- Create a New Folder with Name DataFromExcel
- Copy Any Blank Excel File from Your PC and Paste Inside Folder 'DataFromExcel'
- Open Excel File
- Enter The Excel Sheet Name 'LoginDataSheet'
- Enter Any Website Login Credential Whom you want To Read
![]() |
LoginDataSheet |
![]() |
SheetName |
package com.abof.scripts; // As Per Your Java Project Name
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
public class ReadQaMantraExcelFile {
public void readExcelFile(String filePath,String fileName,String sheetName) throws IOException{
//Create an object of File class to open xlsx file
File file = new File(filePath+"\\"+fileName);
//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);
Workbook workbook = null;
//Find the file extension by splitting file name in substring and getting only extension name
String fileExtensionName = fileName.substring(fileName.indexOf("."));
//Check condition if the file is xlsx file
if(fileExtensionName.equals(".xlsx")){
//If it is xlsx file then create object of XSSFWorkbook class
workbook = new XSSFWorkbook(inputStream);
}
//Check condition if the file is xls file
else if(fileExtensionName.equals(".xls")){
//If it is xls file then create object of XSSFWorkbook class
workbook = new HSSFWorkbook(inputStream);
}
//Read sheet inside the workbook by its name
Sheet sheet = workbook.getSheet(sheetName);
//Find number of rows in excel file
int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
//Create a loop over all the rows of excel file to read it
for (int i = 0; i < rowCount+1; i++) {
Row row = sheet.getRow(i);
//Create a loop to print cell values in a row
for (int j = 0; j < row.getLastCellNum(); j++) {
//Print Excel data in console
System.out.print(row.getCell(j).getStringCellValue()+"|| ");
}
}
}
//Main function is calling readExcel function to read data from excel file
public static void main(String [] args) throws IOException{
//Create an object of ReadQaMantraExcelFile class
ReadQaMantraExcelFile objExcelFile = new ReadQaMantraExcelFile ();
//Prepare the path of excel file
String filePath = System.getProperty("user.dir")+"\\src\\testData";
//Call read file method of the class to read data
objExcelFile.readExcelFile(filePath,"data.xlsx","LoginDataSheet");
}
}
Write Data To Excel File
package com.abof.scripts;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
public class WriteqamantraExcelFile {
public void writeExcel(String filePath,String fileName,String sheetName,String[] dataToWrite) throws IOException{
//Create an object of File class to open xlsx file
File file = new File(filePath+"\\"+fileName);
//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);
Workbook qamantraWorkbook = null;
//Find the file extension by splitting file name in substring // and getting only extension name
String fileExtensionName = fileName.substring(fileName.indexOf("."));
//Check condition if the file is xlsx file
if(fileExtensionName.equals(".xlsx")){
//If it is xlsx file then create object of XSSFWorkbook class
qamantraWorkbook = new XSSFWorkbook(inputStream);
}
//Check condition if the file is xls file
else if(fileExtensionName.equals(".xls")){
//If it is xls file then create object of XSSFWorkbook class
qamantraWorkbook = new HSSFWorkbook(inputStream);
}
//Read excel sheet by sheet name
Sheet sheet = qamantraWorkbook.getSheet(sheetName);
//Get the current count of rows in excel file
int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
//Get the first row from the sheet
Row row = sheet.getRow(0);
//Create a new row and append it at last of sheet
Row newRow = sheet.createRow(rowCount+1);
//Create a loop over the cell of newly created Row
for(int j = 0; j < row.getLastCellNum(); j++){
//Fill data in row
Cell cell = newRow.createCell(j);
cell.setCellValue(dataToWrite[j]);
}
//Close input stream
inputStream.close();
//Create an object of FileOutputStream class to create write data //in excel file
FileOutputStream outputStream = new FileOutputStream(file);
//write data in the excel file
qamantraWorkbook.write(outputStream);
//close output stream
outputStream.close();
}
public static void main(String args []) throws IOException{
//Create an array with the data in the same order in which you //expect to be filled in excel file
String[] valueToWrite = {"abc@gmail.com","Pass@123"};
//Create an object of current class
WriteqamantraExcelFile objExcelFile = new WriteqamantraExcelFile();
//Write the file using file name, sheet name and the data to be filled
objExcelFile.writeExcel(System.getProperty("user.dir")+"\\src\\testData","data.xlsx","LoginDataSheet",valueToWrite);
}
}
No comments:
Post a comment