3

I'm reading excel using Apache POI but it keeps giving error for XSSFWorkbook class definition found error. I used different versions of Apache-poi jar libraries (i.e 4.1 , 4.0 and 3.12) none of them seem to fix this error. Here's a screenshot of libraries currently imported enter image description here Whats wrong inside the code ?

try {   
        File fileSrc = new File("C://Eclipse-Workspace//TestData//TestDataSet.xlsx");
            FileInputStream fis = new FileInputStream(fileSrc); 
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFCell cellData = sheet.getRow(rowNo).getCell(colNo);
workbook.close();
return cellData.toString();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

Error shown:

 java.lang.NoClassDefFoundError: 
    org/apache/poi/ss/usermodel/WorkbookFactory
    at Utilities.DataAccessorExcel.excelReader(DataAccessorExcel.java:33)
    at TestCases.LoginTest.verifyLogin(LoginTest.java:66)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native 
    Method)
    atjava.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMe 
    thodAccessorImpl.java:62)
    atjava.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Dele 
    gatingMethodAccessorImpl.java:43)at 
    java.base/java.lang.reflect.Method.invoke(Method.java:567)
Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
Soni K
  • 156
  • 2
  • 6
  • 19

2 Answers2

3

You have to include poi jar file. It's version will be 4.1.0. If you are using Maven pom.xml, include the following dependency.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>

If you are not using Maven, download the jar files from the following locations.

http://central.maven.org/maven2/org/apache/poi/poi/4.1.0/poi-4.1.0.jar

http://central.maven.org/maven2/org/apache/poi/poi-ooxml/4.1.0/poi-ooxml-4.1.0.jar

For reading an excel file with Apache POI, you need the following jar files if you do not want to use Maven.

  1. poi-ooxml-4.1.0.jar
  2. poi-ooxml-schemas-4.1.0.jar
  3. xmlbeans-3.1.0.jar
  4. commons-compress-1.18.jar
  5. curvesapi-1.06.jar
  6. poi-4.1.0.jar
  7. commons-codec-1.12.jar
  8. commons-collections4-4.3.jar
  9. commons-math3-3.6.1.jar

I provide below a brief code snippet about how to use.

FileInputStream file =
        new FileInputStream(
            new File(
                "testdata\\TestData_01.xlsx"));
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();

    while (rowIterator.hasNext()) {
      Row row = rowIterator.next();
      Cell cell0 = row.getCell(0);
      if (cell0 != null) {
        System.out.println("First Column Data : "+cell0.getStringCellValue());
      }
      Cell cell1 = row.getCell(1);
      if (cell1 != null) System.out.println("Second Column Data : "+cell1.getStringCellValue());


    }
Sambit
  • 7,625
  • 7
  • 34
  • 65
  • It still doesn't work, with replaced all the existing jar files with the ones mentioned in above. Also, I am not using maven to build, it's simply from eclipse. I am reading data from excel inside a test case(calling a method) i.e running testng.xml. It's giving the same Failure Exception. – Soni K May 22 '19 at 15:29
  • If you are not using maven, let me tell you the list of jar files to use. – Sambit May 22 '19 at 15:31
  • I have uploaded an image to this question, it shows the exact jar files I was using earlier. – Soni K May 22 '19 at 15:34
  • I have updated the answer with all the required jar files with version details, please check. – Sambit May 22 '19 at 15:38
  • Okay, I tried again as per updated answer. It's giving same error ..looks like the problem is with XSSFWorkbook . as its unable to find the class "java.lang.NoClassDefFoundError: org/apache/poi/xssf/usermodel/XSSFWorkbook at Utilities.DataAccessorExcel.excelReader(DataAccessorExcel.java:36) at TestCases.LoginTest.verifyLogin(LoginTest.java:64) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) " – Soni K May 22 '19 at 16:07
  • There is no issue with XSSFWorkbook, create a project, download all the jar files, configure and try to run. – Sambit May 22 '19 at 16:09
  • Umm..Its throwing the exception at the XSSFWorkbook workbook = new XSSFWorkbook(file); Debugger breaks at that line. I tried it over and over again..still doesn't seem to fix the problem. – Soni K May 22 '19 at 16:29
  • Let me send you the details. – Sambit May 22 '19 at 16:42
  • Never mind..I'll figure it out.Thanks for your solution. – Soni K May 22 '19 at 16:45
  • it works fine if I run it separately(in a new project with main method) but doesn't work when i try to read data from TestNg testclass and throws same error – Soni K May 22 '19 at 18:35
  • Hope, you got to know, you can modify the code for test class in TestNg. – Sambit May 22 '19 at 18:38
0

I created the project again, and placed POI libraries under ClassPath, but I found that the POI libraries were added as ModulePath and not under ClassPath. Anyhow,I tried this before, I started creating a new project. And moved testng.xml at src folder level. It's working fine as expected.

Soni K
  • 156
  • 2
  • 6
  • 19