Read From an Excel File Into Java

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

Java provides us unlike classes for File Manipulation with Selenium. In this tutorial, we are going to learn how tin nosotros read and write on Excel file with the help of Coffee IO parcel 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 Java that gives users an API for manipulating Microsoft documents similar .xls and .xlsx. Users can hands 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 performance
• 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 Java, 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 volition be the selection. Allow's study these implementations in item.

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

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>iv.i.1</version> </dependency>

Or you lot can just download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file

When you lot download the zip file for this jar, you demand to unzip information technology and add these all jars to the class path of your project.

Classes and Interfaces in POI:

Classes and Interfaces in Apache POI

Post-obit is a list of different Coffee 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 class 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 class representing a sheet 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 canvas of XLS file.
• Cell: XSSFCell and HSSFCell classes implement this interface.
• XSSFCell: Is a form representing a cell in a row of XLSX file.

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

Read/Write operation-

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

Read information from Excel file

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

packet excelExportAndFileIO;  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.Canvas;  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 class to open up 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 = zero;      //Find the file extension by splitting file proper noun in substring  and getting only extension proper 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 grade      guru99Workbook = new XSSFWorkbook(inputStream);      }      //Check condition if the file is xls file      else if(fileExtensionName.equals(".xls")){          //If it is xls file and so create object of HSSFWorkbook course          guru99Workbook = new HSSFWorkbook(inputStream);      }      //Read sheet within the workbook by its proper name      Sheet guru99Sheet = guru99Workbook.getSheet(sheetName);      //Discover number of rows in excel file      int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum();      //Create a loop over all the rows of excel file to read information technology      for (int i = 0; i < rowCount+one; i++) {          Row row = guru99Sheet.getRow(i);          //Create a loop to print prison cell values in a row          for (int j = 0; j < row.getLastCellNum(); j++) {              //Impress Excel data in console              System.out.print(row.getCell(j).getStringCellValue()+"|| ");          }          System.out.println();     }       }        //Main function is calling readExcel function to read information from excel file      public static void main(Cord...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";      //Call read file method of the form to read data      objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelGuru99Demo");      }  }

Notation: We are not using the Testng framework hither. Run the class every bit Java Application using function read excel in Selenium as shown in above example.

Write data on Excel file

Complete Example: Hither we are trying to write data from Excel file by adding new row in Excel file

package excelExportAndFileIO;  import java.io.File;  import java.io.FileInputStream;  import java.io.FileOutputStream;  import coffee.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.Sail;  import org.apache.poi.ss.usermodel.Workbook;  import org.apache.poi.xssf.usermodel.XSSFWorkbook;  public class WriteGuru99ExcelFile {      public void writeExcel(String filePath,String fileName,Cord 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 guru99Workbook = null;          //Find the file extension by splitting  file name in substring and getting but extension proper noun          Cord fileExtensionName = fileName.substring(fileName.indexOf("."));          //Bank check condition if the file is xlsx file          if(fileExtensionName.equals(".xlsx")){          //If information technology 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 then create object of XSSFWorkbook grade              guru99Workbook = new HSSFWorkbook(inputStream);          }          //Read excel sheet past sheet proper name          Sheet sheet = guru99Workbook.getSheet(sheetName);      //Go the current count of rows in excel file      int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();      //Go the kickoff row from the sheet      Row row = canvass.getRow(0);      //Create a new row and suspend information technology at last of sail      Row newRow = sail.createRow(rowCount+ane);      //Create a loop over the jail cell of newly created Row      for(int j = 0; j < row.getLastCellNum(); j++){          //Make full 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 main(Cord...strings) throws IOException{          //Create an array with the data in the aforementioned order in which you expect to be filled in excel file          Cord[] valueToWrite = {"Mr. E","Noida"};          //Create an object of current class          WriteGuru99ExcelFile objExcelFile = new WriteGuru99ExcelFile();          //Write the file using file proper name, canvass proper noun and the data to exist filled          objExcelFile.writeExcel(System.getProperty("user.dir")+"\\src\\excelExportAndFileIO","ExportExcel.xlsx","ExcelGuru99Demo",valueToWrite);      }  }

Excel Manipulation using JXL API

JXL is likewise some other famous jar to read Excel file in Java and writing files. Nowadays, POI is used in near of the projects, but before POI, JXL was just Java API for Excel manipulation. It is a very small and simple API for excel reading in Selenium.

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

Download JXL:

If you desire to work with JXL, yous tin download it from this link

https://sourceforge.net/projects/jexcelapi/files/jexcelapi/2.half dozen.12/

You can also 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.
• We tin work with English, French, Spanish, German.
• Copying a Nautical chart and image insertion in Excel is possible

Drawback:

• Nosotros can write Excel 97 and later 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 tin can be read by Java IO operation. For that, we demand to use Apache POI Jar.
• There are two kinds of a workbook in Excel file, XLSX and XLS files.
• POI has different Interfaces Workbook, Canvass, Row, Prison cell.
• These interfaces are implemented past corresponding XLS (HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell) and XLSX (XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell) file manipulation classes.
• JXL is another API for Excel handling in Selenium.
• JXL cannot piece of work with XLSX format of excel.

Read From an Excel File Into Java

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

Share this post