How to read xlsx file in Apache POI in Android/Java

Question

I am trying to read xls and xlsx files. able to read xls but for xlsx getting Exception:-

java.lang.NoClassDefFoundError: Failed resolution of: Ljavax/xml/stream/XMLEventFactory;
        at org.apache.poi.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller.<clinit>(PackagePropertiesMarshaller.java:45)
        at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:161)
        at org.apache.poi.openxml4j.opc.OPCPackage.<init>(OPCPackage.java:141)
        at org.apache.poi.openxml4j.opc.Package.<init>(Package.java:37)
        at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:87)
        at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:272)
        at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:258)

in "Workbook myWorkBook = new XSSFWorkbook(fileStream);" this line.

Apache POI jar files:-

poi-3.11-20141221.jar

poi-ooxml-3.11.jar

poi-ooxml-schemas-3.11.jar

poi-scratchpad-3.11-20141221.jar

xmlbeans-2.3.0.jar

also add

stax-api-1.0.1.jar

but not able to build this jar file in Android Studio.

i am not getting how to do this.

Answer 1

Try running without stax-api. Using stax-api.jar is not helpful in poi > 3.9 and may cause conflict if you are using Java 6 or above. As per Apache poi components page

The OOXML jars require a stax implementation, but now that Apache POI requires Java 6, that dependency is provided by the JRE and no additional stax jars are required. The OOXML jars used to require DOM4J, but the code has now been changed to use JAXP and no additional dom4j jars are required.

Answer 2

Add following dependencies in your code.

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

To write on excel file there are two options.

1. To write in .xls file.

   Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("My Sheet"); Row row= sheet.createRow(0); Cell cell = row.createCell(0);

2. To write in .xlsx file

   Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("My Sheet"); Row row= sheet.createRow(0); Cell cell = row.createCell(0);

To read from excel file:

Workbook workbook = WorkbookFactory.create(inputStream);