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

Along with the l2wish.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

package test3;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import test1.ExcelHelper;

public class MyClass {
    Object[][] obb;
    ExcelHelper Excelelperpag = new ExcelHelper();
    @Test(dataProvider = "getexcel")
    public void pro(String uname, String pwd) {
        System.out.println(uname);
        System.out.println(pwd);
    }

    @DataProvider()
    public Object[][] getexcel() {
        FileInputStream fis;
        try {
            fis = new FileInputStream("D:\\l2wish.xlsx");
           XSSFWorkbook wb = new XSSFWorkbook(fis);
            Sheet sh = wb.getSheet("Sheet1");
            int count = sh.getLastRowNum();
            System.out.println("Count=" + count);
            obb = new Object[count][2];
            for (int i = 0; i < count; i++) {
                Row rw = sh.getRow(i);
                obb[i][0] = rw.getCell(0).getStringCellValue();
              obb[i][1] = rw.getCell(1).getStringCellValue();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return obb;
    }
}

POM.XML

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>test2</groupId>
    <artifactId>test3</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.seleniumhq.selenium</groupId>
            <artifactId>selenium-java</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.8</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11-beta2</version>
        </dependency>
            <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
         <dependency>
              <groupId>org.apache.maven.surefire</groupId>
              <artifactId>surefire</artifactId>
              <version>2.19.1</version>
              <type>pom</type>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
             <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.19.1</version>
                <configuration>
                  <suiteXmlFiles>
                            <suiteXmlFile>testng.xml</suiteXmlFile>
                  </suiteXmlFiles>
                </configuration>
              </plugin>
        </plugins>
    </build>
</project>

 

Leave a Reply

Your email address will not be published. Required fields are marked *