Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Java - Apache POI - Convert XLS/XLSX to CSV
/*
* Dependencies: Apache POI Library from http://poi.apache.org/
*/
package poi_excels;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
/**
*
* @author Munawwar
*/
public class ExcelReading {
public static void echoAsCSV(Sheet sheet) {
Row row = null;
for (int i = 0; i < sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
for (int j = 0; j < row.getLastCellNum(); j++) {
System.out.print("\"" + row.getCell(j) + "\";");
}
System.out.println();
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
InputStream inp = null;
try {
inp = new FileInputStream("myxlsx/sample.xlsx");
Workbook wb = WorkbookFactory.create(inp);
for(int i=0;i<wb.getNumberOfSheets();i++) {
System.out.println(wb.getSheetAt(i).getSheetName());
echoAsCSV(wb.getSheetAt(i));
}
} catch (InvalidFormatException ex) {
Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
} catch (FileNotFoundException ex) {
Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
inp.close();
} catch (IOException ex) {
Logger.getLogger(ExcelReading.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
@tamilselvam

This comment has been minimized.

Show comment
Hide comment
@tamilselvam

tamilselvam Dec 10, 2012

Hi Team,

I am getting error, Please help me to resolve.

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlOptions
at org.apache.poi.POIXMLDocumentPart.(POIXMLDocumentPart.java:53)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:72)
at ExcelReading.main(ExcelReading.java:38)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlOptions
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)
... 3 more

Hi Team,

I am getting error, Please help me to resolve.

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlOptions
at org.apache.poi.POIXMLDocumentPart.(POIXMLDocumentPart.java:53)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:72)
at ExcelReading.main(ExcelReading.java:38)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlOptions
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)
... 3 more

@jan-swiecki

This comment has been minimized.

Show comment
Hide comment
@jan-swiecki

jan-swiecki Apr 26, 2013

@tamilselvam, you need poi.jar and poi-ooxml.jar.

@tamilselvam, you need poi.jar and poi-ooxml.jar.

@sinarf

This comment has been minimized.

Show comment
Hide comment
@sinarf

sinarf May 7, 2014

Hello,

Thanks for the code, but there is a tiny but in it.
The last line of each sheet is not parsed. I changed the line 26 by

for (int i = 0; i < sheet.getLastRowNum() + 1 ; i++) {

sinarf commented May 7, 2014

Hello,

Thanks for the code, but there is a tiny but in it.
The last line of each sheet is not parsed. I changed the line 26 by

for (int i = 0; i < sheet.getLastRowNum() + 1 ; i++) {
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment