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:
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?
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