cancel
Showing results for 
Search instead for 
Did you mean: 

NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellValue()

Highlighted
Occasional Contributor

NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellValue()

Hello,

I am trying to read data from .XLSX file using Latest Apache POI libraries  4.0.1.  However I amgetting follwoing error when I am trying to getCellType() or cell.getStringCellValue() etc.

However it could read wb.getPhysicalNumberOfRows() , row.getCell(x) properly without error.

error - NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTExtensionList

Steps followed - 

 

  1. Download the latest release version of the Apache POI libraries 4.0.1.
  2. Place the extracted libraries (all of the .jar files in the main extraction directory, \lib and ooxml-lib directories) to the ReadyAPI installation directory\bin\ext directory and restart ReadyAPI.
  3. Use this script:

    package application;

    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Iterator;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;

    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;
    import org.apache.poi.ss.usermodel.DataFormatter;


    InputStream ExcelFileToRead = new FileInputStream("D:\\Test.xlsx");
    XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
    XSSFWorkbook test = new XSSFWorkbook();
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow row;
    XSSFCell cell;

    Iterator rows = sheet.rowIterator();
    while (rows.hasNext())
    {
    row=(XSSFRow) rows.next();
    Iterator cells = row.cellIterator();
    while (cells.hasNext())
    {
    cell=(XSSFCell) cells.next();
    if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
    {
    System.out.print(cell.getStringCellValue()+" ");
    }
     else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
     {
     System.out.print(cell.getNumericCellValue()+" ");
     }
    else
     {
     }
    }
    System.out.println();
    }

Please let me know how to resolve this error.

 

regards,

Manali

 

11 REPLIES 11
Super Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

You are probably missing the ooxml-schemas-1.4.jar file. See item 3 on the following FAQ page for details:

 

https://poi.apache.org/help/faq.html

 

Super Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

@mkajale2 Manali,

 

Please add ooxml-schemas-1.3.jar to sopaui_home/bin/ext, restart and try again.

 

https://mvnrepository.com/artifact/org.apache.poi/ooxml-schemas/1.3

 

Don't forget to accept as solution and give kudos.

Occasional Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

Hi,

I tried by adding ooxml-schemas-1.4.jar  file into ext folder but getting same error - 

PFA: screenshot of ext folder jars.

 

regards,

Manali

 

 

 

 

Occasional Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

even with ooxml-schemas-1.3.jar added to ext folder giving same error.

Can u please chk the screenshot provided and suggest if any jar missing or need to remove?

 

regards,

Manali

 

Super Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

@mkajale2 Monica:-

Please download POI-3.14 or POI 3.16 and ooxml-schemas-1.3.jar and put inside ext folder.

 

https://mvnrepository.com/artifact/org.apache.poi/poi/3.14

 

Please have a look at solution, I have provided and it worked:-

 

https://community.smartbear.com/t5/SoapUI-Open-Source/HOW-TO-READ-EXCEL-FILE-IN-GROOVY/m-p/178248#M2...

Super Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

@mkajale2, you stated that you are using POI 4.0.1 in your original question, according to the Apache POI FAQ:

 

https://poi.apache.org/help/faq.html

 

You should be using ooxml-schemas-1.4.jar

 

Please review item 3 of the apache POI FAQ linked above, the title of item 3 is:

 

"I'm using the poi-ooxml-schemas jar, but my code is failing with "java.lang.NoClassDefFoundError: org/openxmlformats/schemas/*something*""

 

Which matches the error in you original post.

Occasional Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

@Radford 

 

As mentioned in earlier post I have used ooxml-schemas-1.4.jar  but was getting same error.

Please refer screenshot of the ext folder contains jar files.

 

regards,

Manali

Occasional Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

@avidCoder 

 

I have latest POI 4.0.1 and  ooxml-schemas-1.4.jar so want the solution based on that.

If we have any known technical limitation then I will try out with POI-3.14 or POI 3.16 and ooxml-schemas-1.3.jar as suggested by you.

 

Plz share if u have faced issue with latest one.

 

thx - 

Manali

Super Contributor

Re: NoClassDefFoundError: while reading XLSX file using XSSF cls for getCellType() /getStringCellVal

Sorry, I missed your original reply, To be honest I'm not sure what to suggest next? I will mention the obvious, are you restarting Ready API after altering the contents of the ext directory?

 

The one thing that I'm not 100% sure of is whether the ooxml-schemas-1.4.jar file replaces poi-ooxml-schemas4.0.1.jar or sits along side it? The Apache FAQ uses the rather ambiguous text (my emphasis):

 

"Every so often, you may try to use part of the file format which isn't included in the minimal poi-ooxml-schemas jar. In this case, you should switch to the full ooxml-schemas-1.4.jar."

 

Does switch mean replace??? Perhaps try removing the file poi-ooxml-schemas4.0.1.jar but this is just a guess.

New Here?
Join us and watch the welcome video:
Top Kudoed Authors