You are reading the article How To Read/Write Data From Excel File: Selenium Poi updated in October 2023 on the website Vibergotobrazil.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Read/Write Data From Excel File: Selenium Poi
File IO is a critical part of any software process. 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.
File IO is a critical part of any software process. 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 tutorial, 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.
Apache POI in SeleniumThe 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 like .xls and .xlsx. Users can easily create, modify and read/write into excel files. POI stands for “Poor Obfuscation Implementation.”
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 will be the choice. Let’s study these implementations in detail.
If you are using Maven in your project, the Maven dependency will be
When you download the zip file for this jar, you need to unzip it and add these all jars to the class path of your project.
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 class representation of XLS file.
Sheet: XSSFSheet and HSSFSheet classes implement this interface.
XSSFSheet: Is a class representing a sheet 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 class 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 cell in a row of XLS file.
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 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 ReadGuru99ExcelFile { public void readExcel(String filePath,String fileName,String sheetName) throws IOException{ File file = new File(filePath+"\"+fileName); FileInputStream inputStream = new FileInputStream(file); Workbook guru99Workbook = null; String fileExtensionName = fileName.substring(fileName.indexOf(".")); if(fileExtensionName.equals(".xlsx")){ guru99Workbook = new XSSFWorkbook(inputStream); } else if(fileExtensionName.equals(".xls")){ guru99Workbook = new HSSFWorkbook(inputStream); } Sheet guru99Sheet = guru99Workbook.getSheet(sheetName); int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum(); for (int i = 0; i < rowCount+1; i++) { Row row = guru99Sheet.getRow(i); for (int j = 0; j < row.getLastCellNum(); j++) { } System.out.println(); } } public static void main(String...strings) throws IOException{ ReadGuru99ExcelFile objExcelFile = new ReadGuru99ExcelFile(); String filePath = System.getProperty("user.dir")+"\src\excelExportAndFileIO"; objExcelFile.readExcel(filePath,"ExportExcel.xlsx","ExcelGuru99Demo"); } }Note: We are not using the Testng framework here. Run the class as Java Application using function read excel in Selenium as shown in above example.
Write data on Excel fileComplete Example: Here 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 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{ File file = new File(filePath+"\"+fileName); FileInputStream inputStream = new FileInputStream(file); Workbook guru99Workbook = null; String fileExtensionName = fileName.substring(fileName.indexOf(".")); if(fileExtensionName.equals(".xlsx")){ guru99Workbook = new XSSFWorkbook(inputStream); } else if(fileExtensionName.equals(".xls")){ guru99Workbook = new HSSFWorkbook(inputStream); } Sheet sheet = guru99Workbook.getSheet(sheetName); int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum(); Row row = sheet.getRow(0); Row newRow = sheet.createRow(rowCount+1); for(int j = 0; j < row.getLastCellNum(); j++){ Cell cell = newRow.createCell(j); cell.setCellValue(dataToWrite[j]); } inputStream.close(); FileOutputStream outputStream = new FileOutputStream(file); guru99Workbook.write(outputStream); outputStream.close(); } public static void main(String...strings) throws IOException{ String[] valueToWrite = {"Mr. E","Noida"}; WriteGuru99ExcelFile objExcelFile = new WriteGuru99ExcelFile(); objExcelFile.writeExcel(System.getProperty("user.dir")+"\src\excelExportAndFileIO","ExportExcel.xlsx","ExcelGuru99Demo",valueToWrite); } } Excel Manipulation using JXL APIJXL is also another famous jar to read Excel file in Java and writing files. Nowadays, POI is used in most of the projects, but before POI, JXL was only Java API for Excel manipulation. It is a very small and simple API for excel reading in Selenium.
TIPS: My suggestion is not to use JXL in any 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 want to work with JXL, you can download it from this link
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 can work with English, French, Spanish, German.
Copying a Chart and image insertion in Excel is possible
Drawback:
We can write Excel 97 and later only (writing in Excel 95 is not supported).
JXL does not support XLSX format of excel file.
Summary:
Excel file can be read by Java IO operation. For that, we need to use Apache POI Jar.
There are two kinds of a workbook in Excel file, XLSX and XLS files.
POI has different Interfaces Workbook, Sheet, Row, Cell.
These interfaces are implemented by 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 work with XLSX format of excel.
You're reading How To Read/Write Data From Excel File: Selenium Poi
Update the detailed information about How To Read/Write Data From Excel File: Selenium Poi on the Vibergotobrazil.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!