exception while using excel workbook

Question

I am getting these exceptions in my code while i m writing some data in excel workbook using poi jars:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(Unknown Source)
    at java.security.SecureClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.access$100(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at WorkBookDemo.main(WorkBookDemo.java:27)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.UnsupportedFileFormatException
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 13 more

I added following jars:

xmlbeans-2.4.0
poi-ooxml-schemas-3.11
poi-3.11
commons-logging-1.1
dom4j-1.6.1

log4j-1.2.17

import java.io.File;
import java.io.FileOutputStream;

import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WorkBookDemo {

   public static void main(String[] args)
   {
        //Blank workbook
        XSSFWorkbook workbook = new XSSFWorkbook();

        //Create a blank sheet
        XSSFSheet sheet = workbook.createSheet("Employee Data");

        //This data needs to be written (Object[])
        Map<String, Object[]> data = new TreeMap<String, Object[]>();
        data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
        data.put("2", new Object[] {1, "Amit", "Shukla"});
        data.put("3", new Object[] {2, "Lokesh", "Gupta"});
        data.put("4", new Object[] {3, "John", "Adwards"});
        data.put("5", new Object[] {4, "Brian", "Schultz"});

       //Iterate over data and write to sheet
        Set<String> keyset = data.keySet();
        int rownum = 0;
        for (String key : keyset)
        {
            Row row = sheet.createRow(rownum++);
            Object [] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr)
            {
                Cell cell = row.createCell(cellnum++);
                if(obj instanceof String)
                    cell.setCellValue((String)obj);
                else if(obj instanceof Integer)
                    cell.setCellValue((Integer)obj);
            }
        }
        try
        {
        //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File("exps.xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("exps.xlsx written successfully on disk.");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

}

Ankur Piyush · Accepted Answer · 2015-02-09 12:23:47Z

2

FileOutputStream out = new FileOutputStream(new File("exps.xlsx"));

A file output stream is an output stream for writing data to a File or to a FileDescriptor. Whether or not a file is available or may be created depends upon the underlying platform. Some platforms, in particular, allow a file to be opened for writing by only one FileOutputStream (or other file-writing object) at a time. In such situations the constructors in this class will fail if the file involved is already open.

Since you are explicitly creating a File object, and passing the same to FileOutputStream constructor. It assumes that the file "exps.xlsx" is already created. [Reference.]

Incase, it is not, you simply pass the name of the file in FileOutputStream constructor.

FileOutputStream out = new FileOutputStream("exps.xlsx");

This will automatically create the file, incase it is already not created.

Moreover, I tested your given code and used the following Maven dependency and it worked.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10</version>
</dependency>

Incase you are not using a maven project, you can explicitly add the aforementioned jars of the aforementioned versions.

edited Feb 9, 2015 at 12:23

answered Feb 8, 2015 at 20:34

Ankur Piyush

3081 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gagravarr Over a year ago

The POI jars must always be from the same version - your advice here is dangerous as it mixes 3.9 and 3.10!

emin Over a year ago

compability issues can happen.

Ankur Piyush Over a year ago

Sry, my bad. mis-put the same in hurry!;

Khushboo Over a year ago

It was not working for me...the versions for all POI jars were different. I picked up similar versions for all 3 jars and now they are same.

emin · Accepted Answer · 2015-02-08 20:24:02Z

2

You have missing jar-files. I ran your code in my workspace and I added required jars to build path. It worked successively.

org/apache/poi/UnsupportedFileFormatException is under poi-x.xx-xxx-xx.jar

No error in your code it is correct. Add jars to build path.

answered Feb 8, 2015 at 20:24

emin

76210 silver badges21 bronze badges

Comments

subash · Accepted Answer · 2016-09-14 22:19:07Z

2

the below list of jars required for apachePOI library should all be of same version like shown below :

org.apache.poi.3.11
org.apache.poi-ooxml.3.11
org.apache.poi-ooxml-schemas.3.11

if the above jars of the same library are of different versions, then u will get the exception as "java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException"

answered Sep 14, 2016 at 22:19

subash

213 bronze badges

Comments

Sunanda Dey · Accepted Answer · 2015-05-06 10:06:01Z

0

you can check for duplicate poi jars as well, suppose you need poi-ooxml-3.9. jar. but in your lib folder there is both poi-ooxml-3.11.jar, poi-ooxml-3.9.jar then remove poi-ooxml-3.11.jar, it should work then.

answered May 6, 2015 at 10:06

Sunanda Dey

1

1 Comment

Gagravarr Over a year ago

Why would you remove the newer jar? Surely you'd want to keep the newest one, with all the bug fixes in it, and remove the old one?

Collectives™ on Stack Overflow

exception while using excel workbook

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related