NoClassDefFoundError: UnsupportedFileFormatException while working with excel sheet using java

Question

I'm writing a Java program which reads from a .xlsx file and give the output in .csv format. This is my code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.text.SimpleDateFormat; 
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;   
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;

public class xlsxToCsv {

    public static void main(String[] args) {

        long startTime = System.currentTimeMillis();

        File inputFile = new File("C:\\inputFile.xlsx");
        File outputFile = new File("C:\\outputFile.csv");

        xlsx(inputFile, outputFile);

        long stopTime = System.currentTimeMillis();
        long elapsedTime = stopTime - startTime;

        System.out.println(elapsedTime);

   }

   private static void xlsx(File inputFile, File outputFile) {

        //for storing data into CSV files
        StringBuffer data = new StringBuffer();

        try {

            Writer w = new OutputStreamWriter(new FileOutputStream(outputFile), "UTF-8");

            // Get the workbook object for XLS file
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputFile));

            // Get first sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell;
            Row row;

            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) 
            {
                row = rowIterator.next();
                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) 
                {
                    cell = cellIterator.next();

                    switch (cell.getCellType()) 
                    {
                        case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_NUMERIC:
                            if(DateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                System.out.println(date.toString());
                                SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
                                String d = sdf.format(date);
                                System.out.println(d);
                                data.append(d + ",");   
                            }
                            else if(cell.getNumericCellValue() == (int)cell.getNumericCellValue())
                                data.append((int)cell.getNumericCellValue() + ",");
                            else if(cell.getNumericCellValue() == (long)cell.getNumericCellValue())
                                data.append((long)cell.getNumericCellValue() + ",");
                            else
                                data.append(cell.getNumericCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_STRING:
                                data.append((cell.getStringCellValue()) + ",");
                                break;

                        case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;

                        default:
                                data.append(cell + ",");
                        }

                        //data.append('\n'); 
                }
                data.append("\r\n");
        }

        w.write(data.toString());
        w.close();

    } catch(FileNotFoundException e) {
        e.printStackTrace();
    } catch(IOException e) {
        e.printStackTrace();
    }
}

}

However, I'm getting the following error:

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 xlsxToCsv.xlsxToCsv.xlsx(xlsxToCsv.java:47)
at xlsxToCsv.xlsxToCsv.main(xlsxToCsv.java:28)
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)
... 14 more

I have included the following jars:

dom4j-1.6.jar
poi-3.9.jar
poi-ooxml-3.11.jar
poi-ooxml-schemas-3.8-20120326.jar
xmlbeans-2.3.0.jar

I've checked the file format is .xlsx and also the directory but I don't understand what the problem is.

How can I remove this error?

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(inputFile)); — Gaurav Mahawar, May 12 '15 at 06:31
All your poi versions are different. Add poi jars which are all from the same version i.e 3.9. And all the jar files provided with apache poi to your project's classpath. — LittlePanda, May 12 '15 at 06:36

score 16 · Accepted Answer · answered May 12 '15 at 07:10

This is covered in the Apache POI FAQ page:

Can I mix POI jars from different versions?

No. This is not supported.

All POI jars in use must come from the same version. A combination such as poi-3.11.jar and poi-ooxml-3.9.jar is not supported, and will fail to work in unpredictable ways.

You list yourself as using poi-3.9.jar and poi-ooxml-3.11.jar which are from different versions, and won't work.

You need to ensure that all your POI jars come from the same version. I'd suggest you grab the latest POI release from the download page (3.12 as of writing), and use a consistent set of jars from that

score 0 · Answer 2 · edited Feb 18 '16 at 20:28

I tried the same and got the same error. It is because poi jars are duplicated in the pom.xml file.

I got these warnings which I ignored first. Later I tried removing duplicate poi jars in pom.xml, which resolved the issue.

This is the warning I got before:

[WARNING] Some problems were encountered while building the effective model for MavenProj:Perfumania:jar:0.0.1-SNAPSHOT [WARNING] 'dependencies.dependency.(groupId:artifactId:type:classifier)' must be unique: org.apache.poi:poi:jar -> duplicate declaration of version 3.9 @ line 36,

score 0 · Answer 3 · answered Jun 27 '19 at 19:58

0

The reason is versions of poi,poi-ooxml,poi-ooxml-schemas should be same for example version should be 3.6 in you rpom.xml file

answered Jun 27 '19 at 19:58

raghuram

1

score -1 · Answer 4 · answered May 12 '15 at 06:29

-1

NoClassDefFoundError comes when JVM is unable to found library at run time. May be you added library in file path but it is not available at run time.

answered May 12 '15 at 06:29

Moosa

79
1
2
12

NoClassDefFoundError: UnsupportedFileFormatException while working with excel sheet using java

4 Answers4