java - 我想从多个工作表的 Excel 文件中读取大于 10 mb 的数据

Question

我的代码适用于较小的 Excel 文件，但不适用于较大的 Excel 文件，它会给出错误 OutOfMemory。

public void onReadClick (View view) {
    printlnToUser("reading XLSX file from resources");
    InputStream stream = getResources().openRawResource(R.raw.test3);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(stream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        int rowsCount = sheet.getPhysicalNumberOfRows();
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        for (int r = 0; r<10; r++) {
            Row row = sheet.getRow(r);
            int cellsCount = row.getPhysicalNumberOfCells();
            for (int c = 0; c<cellsCount; c++) {
                String value = getCellAsString(row, c, formulaEvaluator);
                String cellInfo = "r:"+r+"; c:"+c+"; v:"+value;
                printlnToUser(cellInfo);
            }
        }
    } catch (Exception e) {
        /* proper exception handling to be here */
        printlnToUser(e.toString());
    }
}

it leades to the following run time error. please help soon.

看起来你的帖子主要是代码；请添加更多详细信息。这很烦人，删除它。

2019-06-06 03:30:43.535 5224-5224/pro.kondratev.xlsxpoiexample E/AndroidRuntime: FATAL EXCEPTION: main
Process: pro.kondratev.xlsxpoiexample, PID: 5224
java.lang.IllegalStateException: Could not execute method for android:onClick
    at android.view.View$DeclaredOnClickListener.onClick(View.java:4707)
    at android.view.View.performClick(View.java:5619)
    at android.view.View$PerformClick.run(View.java:22298)
    at android.os.Handler.handleCallback(Handler.java:754)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:165)
    at android.app.ActivityThread.main(ActivityThread.java:6375)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:912)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:802)
 Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at android.view.View$DeclaredOnClickListener.onClick(View.java:4702)
    at android.view.View.performClick(View.java:5619) 
    at android.view.View$PerformClick.run(View.java:22298) 
    at android.os.Handler.handleCallback(Handler.java:754) 
    at android.os.Handler.dispatchMessage(Handler.java:95) 
    at android.os.Looper.loop(Looper.java:165) 
    at android.app.ActivityThread.main(ActivityThread.java:6375) 
    at java.lang.reflect.Method.invoke(Native Method) 
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:912) 
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:802) 
 Caused by:java.lang.OutOfMemoryError: OutOfMemoryError thrown while trying to throw OutOfMemoryError; no stack trace available

Answer 1

尝试一下 SXSSFWorkbook。尝试使用非常高效且高性能的流式 SXSSFWorkbook 类，而不是 XSSFWorkbook(它将整个 Excel 工作簿保留在内存中)，如下所示:

SXSSFWorkbook workbook = new SXSSFWorkbook(100);

其中 100 是将保留在内存中并实时处理的默认行数，其他已处理的行将被刷新。