How to open .xlsx files with POI SS?

Question

I am trying to open .xlsx files with POI SS with this code (taken from http://poi.apache.org/spreadsheet/quick-guide.html#ReadWriteWorkbook):

InputStream inp = new FileInputStream("workbook.xls");
//InputStream inp = new FileInputStream("workbook.xlsx");

Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
if (cell == null)
    cell = row.createCell(3);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("a test");

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

and I get this error message:

Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException

I add the xbean.jar to my library and to my run-time libraries.

how can I resolve this exception?

Thanks !

Answer 1

First: Fix the Exception

There are two solutions:

1. As Gagravarr already mentioned: you need dom4j to fix your exception.
2. As Jon already mentioned: you have to update your dependencies, so you don't need dom4j anymore.

If you're using Maven, you can add the necessary dependencies with: (Maybe check for newer versions at: Maven Repository: org.apache.poi)

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.4</version>
</dependency>

Then: Open the File

If you've fixed the exception, you can open your file.xlsx file with the following code:

String path = "Relative/Path/To/Your/File/file.xlsx";
File file = new File(path);

XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
// Use your sheet ...

Further tips

• Like Gagravarr, I also recommend to use a file instead of a file input stream.
• If you want to open a certain sheet you can use workbook.getSheet(String name);
• If you don't know the relative path to your file according to your project, you can easily check it with System.out.println("Relative path: " + System.getProperty("user.dir"));

Regards, winklerrr

Answer 2

I haven't analyzed your error message, but after seeing the code, I see that there is something not correct.
The Wookbook is not work with *.xlsx (Office 2007 and after) files. So you have to use the XSSFWorkbook. You may want to change the wb initialization to:

XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(inp);

Answer 3

This can help you.

InputStream is = new FileInputStream(pathOfYourXlsxFile);
                XSSFWorkbook workbook = new XSSFWorkbook(is);
                //Get first sheet from the workbook
                XSSFSheet sheet = workbook.getSheetAt(0);

For your Exception you have to put dom4j.jar to your classpath.You can find it here

Answer 4

You need dom4j, that's what the exception is telling you

You might want to look at the components page on the POI website which lists the dependencies

Also, since you have a File, open directly with that, don't go via an InputStream. There's a section in the Apache POI FAQ on that, basically using a file is quicker and lower memory than buffering the whole thing via a stream!

Answer 5

I realize the thread is old, but it led me today to the answer I needed for the same problem.

The docs on the page suggested above (i.e include dom4j.jar) say that's no longer necessary:

"The OOXML jars used to require DOM4J, but the code has now been changed to use JAXP and no additional dom4j jars are required."

I found that including the following resolved the problem:

poi-3.11-20141221.jar
poi-ooxml-3.11-20141221.jar
poi-ooxml-schemas-3.11-20141221.jar
xmlbeans-2.6.0.jar