Issue: I am trying to read an XLSX file for input values. The code works for XLS but app crashes for XLSX files. But the implementation of HSSF and XSSF seems to be more or less same. Below is the error logged.
So Far: Tried many SO answers. Downloaded and attached the below jars. But still no difference.
xmlbeans-3.1.0.jar
poi-ooxml-4.1.0.jar
poi-ooxml-schemas-4.1.0.jar
commons-compress-1.18.jar
curvesapi-1.06.jar
poi-4.1.0.jar
commons-codec-1.12.jar
commons-collections4-4.3.jar
commons-math3-3.6.1.jar
Code:
if (GetFileExtension(FilePath).equals(".xls")) {
wb = new HSSFWorkbook(inStream);
sheet1 = wb.getSheetAt(0);
Formeval = new HSSFFormulaEvaluator((HSSFWorkbook) wb);
}else if (GetFileExtension(FilePath).equals(".xlsx")) {
wb = new XSSFWorkbook(inStream);
sheet1 = wb.getSheetAt(0);
Formeval = new XSSFFormulaEvaluator((XSSFWorkbook) wb);
}
Error log:
Process: com.example.newtest, PID: 14295
java.lang.BootstrapMethodError: Exception from call site #238 bootstrap method
at org.apache.poi.openxml4j.opc.PackagePartCollection.<init>(PackagePartCollection.java:47)
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:241)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:732)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:310)
at org.apache.poi.ooxml.util.PackageHelper.open(PackageHelper.java:47)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:309)
at com.example.newtest.DNsuper.onActivityResult(DNsuper.java:963)
at android.app.Activity.dispatchActivityResult(Activity.java:7276)
at android.app.ActivityThread.deliverResults(ActivityThread.java:4264)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:4312)
at android.app.ActivityThread.-wrap19(Unknown Source:0)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1644)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6494)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)
Caused by: java.lang.NoClassDefFoundError: Invalid descriptor: AFTER.
at org.apache.poi.openxml4j.opc.PackagePartCollection.<init>(PackagePartCollection.java:47)
at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:241)
at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:732)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:310)
at org.apache.poi.ooxml.util.PackageHelper.open(PackageHelper.java:47)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:309)
at com.example.newtest.DNsuper.onActivityResult(DNsuper.java:963)
at android.app.Activity.dispatchActivityResult(Activity.java:7276)
at android.app.ActivityThread.deliverResults(ActivityThread.java:4264)
at android.app.ActivityThread.handleSendResult(ActivityThread.java:4312)
at android.app.ActivityThread.-wrap19(Unknown Source:0)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1644)
at android.os.Handler.dispatchMessage(Handler.java:106)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6494)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:438)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:807)
There is a compatability issue between Apache POI and Google's Android [long pending scenario - get to know after weeks of research ] which makes XSSF[.XLSX] read complicated but not HSSF(.XLS). Back porting to previous libraries fixed the issue.
Library - dependencies: [ JAVA Jars - GRADLE - MAVEN ] Use below versions and ignore update message.
For GRADLE: Keep this in build.gradle(:app)
implementation "org.apache.poi:poi:3.10-FINAL"
implementation "org.apache.poi:poi-ooxml:3.10-FINAL"
implementation "org.apache.poi:poi-ooxml-schemas:3.10-FINAL"
implementation group: 'org.apache.xmlbeans', name: 'xmlbeans', version: '2.3.0'
implementation group: 'stax', name: 'stax-api', version: '1.0.1'
implementation group: 'commons-codec', name: 'commons-codec', version: '1.5'
implementation group: 'xml-apis', name: 'xml-apis', version: '1.0.b2'
implementation group: 'dom4j', name: 'dom4j', version: '1.6.1'
implementation group: 'org.apache.httpcomponents' , name: 'httpclient-android' , version: '4.3.5.1'
.XLSX and .XLS Reader: (i.e) XSSF and HSSF workbook respectively
import android.content.ContentValues;
import android.util.Log;
import androidx.appcompat.app.AppCompatActivity;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.Iterator;
public class Import2SQLite extends AppCompatActivity {
public static final String DNN = "DO";// 0 text(String)
public static final String ITT = "IT";// 1 integer
public static final String DNQ = "DN";// 2 text(String)
public static void ExcelToSqlite(ActiononDB dbAdapter, Sheet sheet) {
for (Iterator<Row> rit = sheet.rowIterator();
rit.hasNext(); ) {
Row row = rit.next();
ContentValues contentValues = new ContentValues();
row.getCell(0, Row.CREATE_NULL_AS_BLANK ).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(1, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
row.getCell(2, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
contentValues.put(DNN, row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
contentValues.put(ITT, row.getCell(1, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
contentValues.put(DNQ, row.getCell(2, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
try {
if (dbAdapter.insert("Usertypes", contentValues) < 0) {
return;
}
} catch (Exception ex) {
Log.d("Exception in importing", ex.getMessage());
}
}
}
}