Selenium Program to Read the Inputs From Excel

File IO is a critical part of whatever software procedure. We often create a file, open information technology & update something or delete it in our Computers. Same is the case with Selenium Automation. Nosotros need a process to manipulate files with Selenium.

Java provides usa different classes for File Manipulation with Selenium. In this tutorial, we are going to larn how can we read and write on Excel file with the help of Coffee IO bundle and Apache POI library.

Apache POI in Selenium

The Apache POI in Selenium is a widely used API for selenium data driven testing. It is a POI library written in Coffee that gives users an API for manipulating Microsoft documents like .xls and .xlsx. Users can easily create, modify and read/write into excel files. POI stands for "Poor Obfuscation Implementation."

• How to handle excel file using POI (Maven POM Dependency)
• Classes and Interfaces in POI
• Read/Write operation
• Read data from Excel file
• Write data on Excel file
• Excel Manipulation using JXL API

Exporting Excel

How to handle excel file using POI (Maven POM Dependency)

To Read and Write Excel file in Coffee, Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.

To read XLS files, an HSSF implementation is provided by POI library.

To read XLSX, XSSF implementation of POI library will exist the option. Permit's study these implementations in item.

If you are using Maven in your project, the Maven dependency will be

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency>

Or y'all tin can simply download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file

When you lot download the naught file for this jar, you demand to unzip it and add together these all jars to the class path of your projection.

Classes and Interfaces in POI:

Classes and Interfaces in Apache POI

Following is a list of different Java Interfaces and classes in POI for reading XLS and XLSX file-

• Workbook: XSSFWorkbook and HSSFWorkbook classes implement this interface.
• XSSFWorkbook: Is a class representation of XLSX file.
• HSSFWorkbook: Is a form representation of XLS file.
• Sheet: XSSFSheet and HSSFSheet classes implement this interface.
• XSSFSheet: Is a grade representing a sail in an XLSX file.
• HSSFSheet: Is a course representing a canvas in an XLS file.
• Row: XSSFRow and HSSFRow classes implement this interface.
• XSSFRow: Is a course representing a row in the sheet of XLSX file.
• HSSFRow: Is a class representing a row in the sheet of XLS file.
• Cell: XSSFCell and HSSFCell classes implement this interface.
• XSSFCell: Is a class representing a cell in a row of XLSX file.

• HSSFCell: Is a class representing a prison cell in a row of XLS file.

Read/Write operation-

For our example, we will consider below given Excel file format

Read data from Excel file

Complete Example: Here we are trying to read data from Excel in Selenium:

package excelExportAndFileIO;  import coffee.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 ReadGuru99ExcelFile {      public void readExcel(String filePath,String fileName,String sheetName) throws IOException{      //Create an object of File course to open xlsx file      File file =    new File(filePath+"\\"+fileName);      //Create an object of FileInputStream form to read excel file      FileInputStream inputStream = new FileInputStream(file);      Workbook guru99Workbook = null;      //Discover the file extension by splitting file proper noun in substring  and getting but extension name      String fileExtensionName = fileName.substring(fileName.indexOf("."));      //Cheque condition if the file is xlsx file      if(fileExtensionName.equals(".xlsx")){      //If it is xlsx file and so create object of XSSFWorkbook course      guru99Workbook = new XSSFWorkbook(inputStream);      }      //Bank check condition if the file is xls file      else if(fileExtensionName.equals(".xls")){          //If it is xls file and then create object of HSSFWorkbook class          guru99Workbook = new HSSFWorkbook(inputStream);      }      //Read sheet inside the workbook by its name      Canvas guru99Sheet = guru99Workbook.getSheet(sheetName);      //Find number of rows in excel file      int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum();      //Create a loop over all the rows of excel file to read it      for (int i = 0; i < rowCount+1; i++) {          Row row = guru99Sheet.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()+"|| ");          }          System.out.println();     }       }        //Primary function is calling readExcel function to read data from excel file      public static void main(String...strings) throws IOException{      //Create an object of ReadGuru99ExcelFile class      ReadGuru99ExcelFile objExcelFile = new ReadGuru99ExcelFile();      //Prepare the path of excel file      String filePath = System.getProperty("user.dir")+"\\src\\excelExportAndFileIO";      //Telephone call read file method of the class to read information      objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelGuru99Demo");      }  }

Note: We are not using the Testng framework hither. Run the class as Java Awarding using office read excel in Selenium as shown in above example.

Write data on Excel file

Complete Instance: Here we are trying to write data from Excel file by calculation new row in Excel file

package excelExportAndFileIO;  import coffee.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 WriteGuru99ExcelFile {      public void writeExcel(String filePath,String fileName,String sheetName,String[] dataToWrite) throws IOException{          //Create an object of File course 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 guru99Workbook = cipher;          //Find the file extension by splitting  file name in substring and getting just extension name          Cord fileExtensionName = fileName.substring(fileName.indexOf("."));          //Cheque status if the file is xlsx file          if(fileExtensionName.equals(".xlsx")){          //If it is xlsx file then create object of XSSFWorkbook class          guru99Workbook = new XSSFWorkbook(inputStream);          }          //Check condition if the file is xls file          else if(fileExtensionName.equals(".xls")){              //If it is xls file and then create object of XSSFWorkbook course              guru99Workbook = new HSSFWorkbook(inputStream);          }          //Read excel canvas past canvass name          Canvas sheet = guru99Workbook.getSheet(sheetName);      //Get the current count of rows in excel file      int rowCount = canvass.getLastRowNum()-sheet.getFirstRowNum();      //Get the first row from the sheet      Row row = sheet.getRow(0);      //Create a new row and append information technology at terminal of sail      Row newRow = sail.createRow(rowCount+one);      //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);          jail 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      guru99Workbook.write(outputStream);      //close output stream      outputStream.close(); 	     }      public static void chief(String...strings) throws IOException{          //Create an assortment with the data in the same order in which you look to be filled in excel file          String[] valueToWrite = {"Mr. East","Noida"};          //Create an object of current class          WriteGuru99ExcelFile objExcelFile = new WriteGuru99ExcelFile();          //Write the file using file name, canvas name and the data to be filled          objExcelFile.writeExcel(Organization.getProperty("user.dir")+"\\src\\excelExportAndFileIO","ExportExcel.xlsx","ExcelGuru99Demo",valueToWrite);      }  }

Excel Manipulation using JXL API

JXL is too another famous jar to read Excel file in Java and writing files. Nowadays, POI is used in about of the projects, but before POI, JXL was only Coffee API for Excel manipulation. It is a very small and simple API for excel reading in Selenium.

TIPS: My suggestion is not to utilise JXL in any new project because the library is non in active development from 2010 and lack of the feature in compare to POI API.

Download JXL:

If you lot want to work with JXL, you can download it from this link

https://sourceforge.cyberspace/projects/jexcelapi/files/jexcelapi/2.six.12/

You lot can too get demo example inside this zipped file for JXL.

Some of the features:

• JXL is able to read Excel file in Selenium for 95, 97, 2000, XP, 2003 workbook.
• Nosotros can piece of work with English, French, Spanish, German.
• Copying a Chart and image insertion in Excel is possible

Drawback:

• Nosotros can write Excel 97 and later on only (writing in Excel 95 is not supported).
• JXL does not back up XLSX format of excel file.
• It Generates spreadsheet in Excel 2000 format.

Summary:

• Excel file can be read by Java IO operation. For that, nosotros need to utilize Apache POI Jar.
• There are 2 kinds of a workbook in Excel file, XLSX and XLS files.
• POI has unlike Interfaces Workbook, Sail, Row, Prison cell.
• These interfaces are implemented by corresponding XLS (HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell) and XLSX (XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell) file manipulation classes.
• JXL is some other API for Excel handling in Selenium.
• JXL cannot piece of work with XLSX format of excel.

lupoheireass.blogspot.com

Source: https://www.guru99.com/all-about-excel-in-selenium-poi-jxl.html

Share this post