Thursday, October 18, 2012

Java : Read / Write Excel file (.xls or .xlsx) using Apache POI


We will see how we can read or write excel file (.xls or .xlsx) using Apache POI. To know more about Apache POI, click here.

You can download latest version of JAR files from http://poi.apache.org/download.html.

In our case we are using Apache POI 3.8. To run below example you will need to download poi-bin-3.8-20120326.zip file from http://poi.apache.org/download.html. You will get below JAR files, add those in your claspath.

  • dom4j-1.6.1.jar
  • xmlbeans-2.3.0.jar
  • poi-3.8-20120326.jar
  • poi-ooxml-3.8-20120326.jar
  • poi-ooxml-schemas-3.8-20120326.jar

Please see the self explanatory Java code.

Below are known errors and exceptions faced while writing above code

1. XSSFWorkbook cannot be resolved to a type

Solution : add poi-ooxml-3.8-20120326.jar in classpath. You can download from above link.

2. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 2 more

Solution: Add xmlbeans-2.3.0.jar in classpath

3. Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException
at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:154)
at org.apache.poi.openxml4j.opc.OPCPackage.(OPCPackage.java:141)
at org.apache.poi.openxml4j.opc.Package.(Package.java:54)
at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:67)
at org.apache.poi.openxml4j.opc.OPCPackage.create(OPCPackage.java:293)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.newPackage(XSSFWorkbook.java:316)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:169)
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.dom4j.DocumentException
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 9 more

Solution: Add dom4j-1.6.1.jar in classpath

4. Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
at com.test.TestExcel.writeXLSXFile(TestExcel.java:135)
at com.test.TestExcel.main(TestExcel.java:165)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
... 2 more

Solution: Add poi-ooxml-schemas-3.8-20120326.jar in classpath

5. Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
    at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:148)
    at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:623)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:230)
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
    ... 33 more

Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:327)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:308)
    at com.test.ReadWriteExcelFile.readXLSFile(ReadWriteExcelFile.java:24)
    at com.test.ReadWriteExcelFile.main(ReadWriteExcelFile.java:163)

Solution :Check the excel file type, whether its xls or xlsx and call appropriate method as shown in above program

6. Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:845)
    at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(XSSFCell.java:294)
    at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(XSSFCell.java:246)
    at com.test.ReadWriteExcelFile.readXLSXFile(ReadWriteExcelFile.java:121)
    at com.test.ReadWriteExcelFile.main(ReadWriteExcelFile.java:167)

Solution: Check for cell.getCellType(). if HSSFCell.CELL_TYPE_STRING then call cell.getStringCellValue(), if HSSFCell.CELL_TYPE_NUMERIC then call cell.getNumericCellValue() and so on.