In the last post, we have already discussed how to read excel data using POI but 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.
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
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
public class ReadExcel
{
@Test
public void test1() throws Throwable, IOException
{
// return the path of local path of project
String path=System.getProperty("user.dir");
// load workbook
XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(new File(path+"/TestData/AppTestData.xlsx")));
// read numeric data
int data= (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 framework you 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. 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.
Can you please create a article on, how to use xlxs with the Data Provider ?
Hi Sanjay,
I’ll post it soon…
I have a excel sheet i have 3 columns
where the First Column is of String data and the
Second column is Integer always and the
Third column is String
SO how to achieve this while reading the excel using Apache POI
Hi Sudheendra,
In your code, when you navigate to the corresponding column, based on the type use getStringCellData() or getNumericCellData() methods based on CellType.
When developing framework how can u be particular about what datatype value is there in the cell. Cant we write generic steps to retrieve data from excel irrespective of any data(any datatype).
Hi Kumar,
You are correct. This post was specifically for reading numeric type cell data to make it easy to grasp for new learners. Now as you mentioned, at the framework level cell type has to be determined first then we should need to get data from cells. POI provides cellType ENUM(https://poi.apache.org/apidocs/4.0/org/apache/poi/ss/usermodel/CellType.html) which have to be used while building the framework.
Hi,
My excel value is 12. I format as text. Still in console it is received as 12.0.
I tried changing it as numeric and decimal value also 0
Need help.
In util class while mentioning sheet, I declared the column as string.
Hi Thulasi,
If you are sure that value which you are trying to read from excel cell will always lie into int range then you can use this way
String.valueOf((int)wb.getSheetAt(sheetIndex).getRow(urRowNumber).getCell(columnNumber).getNumericCellValue()) where wb is XSSFWorkbook object
hi how can we read NUMERIC AND STRING IN SAME EXCEL SHEET
Hi Raju,
You can use cell type check before reading any data from any cell like if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)…..if else(cell.getCellType() == cell.CELL_TYPE_STRING)
Hi Mukesh, How can we write test execution status(Pass/Fail) into excel sheet depends upon the status of test case execution,which will be dymanic in nature.
Yes ANkita, We can do that based on condition you can use try catch and you can write excel accordingly.
http://learn-automation.com/readwrite-excel-files-in-selenium/
I need to know that how to read both the string and integer values from the excel
Yes Asha here you go http://learn-automation.com/readwrite-excel-files-in-selenium/
Thank you mukesh