In this video, We are going to learn How to Read Excel File in Selenium with Apache POI The Apache POI Project’s mission is to create and maintain Java APIs for manipulating various file formats.
In the last post, we have already discussed how to read excel data using POIbut we have missed one important point which can become the blocker for you. We can read numeric data from Excel using apache poi for Selenium webdriver.
How to Read Excel File in Java. In this section, we are going to learn how we can read data from an excel file. In Java, reading excel file is not similar to read word file because of cells in excel file. JDK does not provide direct API to read or write Microsoft Excel or Word document. We have to rely on the third-party library that is Apache POI. 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. For reading files from the.xlsx file we need the following.jar files to be imported to the Build Path.Downloads the followings and import the version. Poi-ooxml-3.11-beta2.jar poi-ooxml-schemas-3.11-beta2.jar xmlbeans-2.6.0.jar stax-api-1.0.1.jar. If you are not using Maven then add following JAR files in your poi-3.11-beta2.jar commons-codec.
Our test data contains numeric data, string data, and sometimes combination as well which is totally dynamic in nature. so we need to create such library which will take care of dynamic data.
Read numeric data from Excel using apache poi
Program to read numeric data from Excel using apache poi
2 4 6 8 10 12 14 16 18 20 22 24 26 28 | import java.io.FileInputStream; import org.apache.poi.xssf.usermodel.XSSFWorkbook; { @Test { // return the path of local path of project XSSFWorkbook wb=newXSSFWorkbook(newFileInputStream(newFile(path+'/TestData/AppTestData.xlsx'))); // read numeric data intdata=(int)wb.getSheetAt(0).getRow(0).getCell(1).getNumericCellValue(); // print on console System.out.println('Data from Excel is >>> '+data); } |
Few observations while reading the different set of Data
- You need to make sure which kind of data you want to read.
- To read String value you can call getStringCellValue() method
- To read Numeric value you can call getNumericCellValue() which will return double value which we can typecast into int and then we can use in our test data.
You can check below screenshot which will explain what are the different type of methods which can help us to read data.
While implementing Data-driven frameworkyou have to make sure that data which is coming from excel should be used in script effectively.
The Same concept will be applied while writing to excel as well. Scaleform video encoder. While passing the data to excel you can call respective methods.
Let’s see the below screenshot for more details.
In above screenshot,you can see it will ask you to pass the specific data to write in excel.
Hope above post will help you to read the numeric data from excel.
If you have any doubt then let me know in comment section. Alldata dvd set.
For reading files from the .xlsx file we need the following .jar files to be imported to the Build Path.Downloads the followings and import the version.
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
Poi For Xlsx Read In Selenium For Mac Osx
If you are not using Maven then add following JAR files in your
poi-3.11-beta2.jar
commons-codec-1.9.jar
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar
Poi For Xlsx Read In Selenium For Mac Os
Along with thel2wish.xlsx file.
Set the location of file in accordance to your system.
Along with the following dependency as in my pom.xml .This pom.xml files i was created for my project i am posting my code bellow.
Main program file contains the following code
Poi For Xlsx Read In Selenium For Mac Windows 10
Poi For Xlsx Read In Selenium For Mac Os
POM.XML