SELENIUM BASICS:

  • What is Automation Testing - Advantages & Types of Tools
  • What is Selenium IDE, Selenium RC, Selenium WebDriver, Selenium Grid?
  • Selenium IDE, Firebug & Firepath Installation

  • SELENIUM WEBDRIVER:

  • Selenium WebDriver Architecture
  • Selenium Installation
  • Challenges and Limitations of Selenium
  • First Selenium WebDriver Script
  • Working with Firefox Browser
  • Solution - Failed To Launch Browser Using Selenium WebDriver
  • Working with Chrome Browser
  • Working with IE Browser
  • Difference Between FindElement & FindElements
  • Install Firebug, Fire path and other add on for Selenium
  • Locators in Selenium
  • Dynamic Xpath
  • Dynamic CSS
  • Handle drop-downs
  • How to work with file upload
  • Handle Alerts & Popups
  • Handle Multiple Windows
  • Mouse Hover event in Selenium
  • Right Click
  • Double Click
  • Drag and Drop
  • Capture Screenshots in Selenium
  • Parameterization
  • Synchronization
  • Implicit Wait, Explicit Wait and Fluent Wait in Selenium
  • Implicit Waits
  • Explicit WebDriver Waits
  • Fluent Wait In Selenium
  • Apache POI
  • How To Read Data From Excel File
  • How To Write Data In Excel File
  • Database Testing Using MSSQL
  • Database Testing Using MySQL
  • Database Testing Using DB2
  • Handle Ajax Call in Selenium
  • Listeners in Selenium
  • Handling JavaScript in Selenium
  • Stale Element Reference Exception in Selenium
  • Integrate Sikuli with Selenium
  • Headless Browser Testing 
  • Headless Browser Testing using HtmlUnitDriver
  • Headless Browser Testing using PhanthomJSDriver

  • TESTNG:

  • TestNG Installation
  • TestNG Complete Tutorial

  • REPORTS:

  • Extent Reports Introduction
  • Extent Reports Generation
  • Capturing Screenshots & Including In Extent Reports
  • Extent Reports Version 4

  • BUILD & CONTINUOUS INTEGRATION TOOLS:

  • ANT Build Tool - How To Download & Install
  • Maven Build Tool - How To Download & Install
  • Creating Maven Selenium Project
  • GIT Integration with Selenium
  • Jenkins - How To Download & Install
  • Jenkins - Executing TestNG Scripts
  • Jenkins - Generating Reports After Test Execution
  • Jenkins - Executing Maven Project
  • Create build and Execute build for nightly execution
  • Send Email report based on build Status
  • Selenium Continuous Integration with Jenkins [Selenium – Maven – Git – Jenkins] – Step By Step Guide

  • CROSS BROWSER TESTING:

  • Running Selenium Tests On BrowserStack
  • Kobiton - Mobile Testing Platform with Real Devices

  • MOBILE TESTING:

  • Mobile Testing Platform with Real Devices

  • AUTOMATION FRAMEWORKS:

  • What is Framework & Types of Framework
  • Page Object Model Design Pattern
  • Data Driven Framework
  • How to explain Test Automation Framework or Selenium Automation Framework Architecture In Interview

  • API Testing:

  • Learn API Testing
  • Postman Tutorial (API Testing with Postman)

  • INTERVIEW QUESTIONS:

  • Test Automation Framework Interview Questions
  • Selenium Interview Questions
  • TestNG Interview Questions
  • Java Interview Questions
  • Python Interview Questions
  • API Testing Interview Questions

  • SAMPLE RESUME:

  • Automation Tester Resume
  • 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:

    1. 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.)
    2. 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.

    Console Output:

    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.

    Read Excel Files Using Apache POI

    Get our latest blog posts delivered to your inbox

    Subscribe and get popular blog posts about software testing industry.

    Rajkumar

    8 thoughts on “How to Read Excel Files Using Apache POI In Selenium WebDriver”

    1. 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.

    2. 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

    3. 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.

    4. 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.

    5. Hi Rajkumar, excel read is not working with poi-2.17 and chrome driver 2.33 version. please help me out

    Leave a Comment

    Share via
    Copy link
    Powered by Social Snap