How to Read Excel Files Using Apache POI In Selenium WebDriver
Let’s see how to Read excel files using Apache POI in Selenium WebDriver:
Assuming that you have already downloaded and configured Apache POI jars in your project. If not, follow the below steps:
Step 1– Download Apache POI jar file
Download link of Apache POI Jars.
Step 2– Add download jar files
Steps to add jar files:
Select Project and Right click on the Project – Go to ‘Build path’ – Go to ‘Configure build path’ – Click on ‘lib’ section – Add external jar
Complete Guide: Handling Excel Files Using Apache POI
Precondition:
- Create an xlsx file and save it at particular location. Enter some data to read using Selenium. Close the created excel file before executing the script. (I have placed an excel file ‘Test.xlsx’ in my D Driver and my sheet name is TestData.)
- Go to option “Format Cells” and under Number Tab option, select Text and click on OK. By default it will be general, you need to make it as Number. If you don’t make it as text, there is a chance of NullPointerException error.
Must Read: How To Install Apache ANT
Below mentioned script shows how to read excel files in Selenium using Apache POI.
package softwareTestingMaterial;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//How to read excel files using Apache POI
public class ReadExcel {
public static void main (String [] args) throws IOException{
//I have placed an excel file 'Test.xlsx' in my D Driver
FileInputStream fis = new FileInputStream("D:\\Test.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
//I have added test data in the cell A1 as "SoftwareTestingMaterial.com"
//Cell A1 = row 0 and column 0. It reads first row as 0 and Column A as 0.
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
System.out.println(cell);
System.out.println(sheet.getRow(0).getCell(0));
//String cellval = cell.getStringCellValue();
//System.out.println(cellval);
}
}
Console Output:
SoftwareTestingMaterial.com
Also Read: How to Write Excel Files Using Apache POI In Selenium WebDriver
If you are not regular reader of my blog then I highly recommend you to signup for the free email newsletter using the below link.
Hi sir,
If Excel sheet has multiple rows and columns, in that time how can I take particular row and column
Hi Manohar, Use for loop.. Something like this..
for(int i=0;i<=rowCount;i++){ for(int i=0;i<=colCount;i++){ } }Thank you Sir
Welcome Manohar.
Hi Sir,
i need complete Selenium notes how u have given manual. please send it to my mail id
your way of explanation is very clear . thank you so much for your wonderful job
dear sir,
I am trying your code, but I got an error
“Exception in thread “main” java.lang.NoClassDefFoundError: org/openxmlformats/schemas/drawingml/x2006/main/ThemeDocument
at java.lang.Class.getDeclaredConstructors0(Native Method)
at java.lang.Class.privateGetDeclaredConstructors(Class.java:2671)
at java.lang.Class.getConstructor0(Class.java:3075)
at java.lang.Class.getDeclaredConstructor(Class.java:2178)
at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:55)
at org.apache.poi.POIXMLFactory.createDocumentPart(POIXMLFactory.java:58)
at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:580)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:165)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:300)
at readExcelFile.main(readExcelFile.java:18)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocument
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
… 10 more
”
this is my code:
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class readExcelFile {
public static void main (String [] args) throws IOException{
//I have placed an excel file ‘Test.xlsx’ in my D Driver
FileInputStream fis = new FileInputStream(“/Users/ekobudiharto/Documents/soapUIworkspace/skyvva/testExcelFile.xlsx”);
XSSFWorkbook wb = new XSSFWorkbook(fis);
//XSSFSheet sheet = wb.getSheetAt(0);
////I have added test data in the cell A1 as “SoftwareTestingMaterial.com”
////Cell A1 = row 0 and column 0. It reads first row as 0 and Column A as 0.
//Row row = sheet.getRow(0);
//Cell cell = row.getCell(0);
//System.out.println(cell);
//System.out.println(sheet.getRow(0).getCell(0));
////String cellval = cell.getStringCellValue();
////System.out.println(cellval);
}
}
can you tell me what cause the error? I already imported the libraries.
Hi Eko Budiharto,
Are you using Maven Project? which version of poi jars you are using..
If it is a maven project – Add poi-ooxml-schemas jar dependencies in your pom.xml file
If it is a java project – Add poi-ooxml-schemas jar in the lib directory.
Thanks.
Hi Rajkumar, excel read is not working with poi-2.17 and chrome driver 2.33 version. please help me out