NoClassDefFoundError: UnsupportedFileFormatException while working with excel sheet using java

Gaurav Mahawar picture Gaurav Mahawar · May 12, 2015 · Viewed 22.7k times · Source

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?

Answer

Gagravarr picture Gagravarr · May 12, 2015

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