如何在Java项目中加速Excel数据集的读取流程？

Hey there! Let's tackle that slow Excel reading bottleneck you're dealing with—10-11 minutes to process 9 XLS files is way longer than it should be. Let's walk through the most likely culprits and actionable fixes to get that time down drastically.

First, I’m guessing you’re using Apache POI’s user model (like HSSFWorkbook) under the hood—this approach loads the entire Excel file into memory, which is slow and memory-heavy, especially for files with large datasets. That’s probably the biggest contributor to your long load times.

1. Switch to POI's Event-Driven (Streaming) Model #

For .xls files, use the HSSFEventUserModel instead of the standard HSSFWorkbook. This streams the file instead of loading everything into memory, which is orders of magnitude faster for large datasets.

Here’s a quick example skeleton for streaming a single XLS file:

import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.record.*;
import java.io.FileInputStream;

public class ExcelStreamReader {
    public void readXlsStream(String filePath, int sheetIndex, double[][][][] dataArray, int fileIndex) throws Exception {
        FileInputStream fis = new FileInputStream(filePath);
        HSSFRequest request = new HSSFRequest();
        
        // Register a listener for sheet content
        request.addListenerForAllRecords(new HSSFListener() {
            private int currentRow = -1;
            private int currentCol = 0;
            private int currentMatrix = 0; // Adjust based on your 4D array structure
            private int currentSubMatrix = 0;

            @Override
            public void processRecord(Record record) {
                switch (record.getSid()) {
                    case BoundSheetRecord.sid:
                        // Handle sheet boundaries if needed
                        break;
                    case RowRecord.sid:
                        RowRecord rowRecord = (RowRecord) record;
                        currentRow = rowRecord.getRowNumber();
                        currentCol = 0;
                        // Reset matrix/submatrix counters if your rows map to these dimensions
                        break;
                    case NumberRecord.sid:
                        NumberRecord numRecord = (NumberRecord) record;
                        if (currentRow >= 0) {
                            // Populate your 4D array here based on row/col positions
                            dataArray[fileIndex][currentMatrix][currentSubMatrix][currentCol] = numRecord.getValue();
                            currentCol++;
                        }
                        break;
                    // Handle other record types if you have string data, etc.
                }
            }
        });

        HSSFEventFactory factory = new HSSFEventFactory();
        factory.processEvents(request, fis);
        fis.close();
    }
}

2. Parallelize File Processing #

Since your 9 Excel files are independent, you can process them in parallel using a thread pool. This can cut your total time down to roughly the time it takes to process the slowest single file (instead of adding up all individual times).

Example using ExecutorService:

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ParallelExcelReader {
    public static void main(String[] args) throws Exception {
        String[] excelSheets = {"f1.xls","f2.xls","f3.xls","f4.xls","f5.xls","f6.xls","f7.xls","f8.xls","f9.xls"};
        double[][][][] arraysSigmaMatrices = new double[9][30][13][13];
        ExecutorService executor = Executors.newFixedThreadPool(5); // Adjust pool size based on your CPU cores

        for (int i = 0; i < excelSheets.length; i++) {
            int fileIndex = i;
            executor.submit(() -> {
                try {
                    ExcelStreamReader reader = new ExcelStreamReader();
                    reader.readXlsStream(excelSheets[fileIndex], 0, arraysSigmaMatrices, fileIndex);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            });
        }

        executor.shutdown();
        executor.awaitTermination(1, java.util.concurrent.TimeUnit.HOURS); // Wait for all tasks to finish
    }
}

3. Optimize Data Handling #

• Avoid unnecessary object creation inside your read loop: Reuse variables instead of instantiating new ones for every cell.
• Skip empty rows/columns: Add checks to skip processing rows or columns that don’t contain meaningful data—this reduces the number of operations.
• Pre-initialize your arrays properly: You’re already doing this, but make sure you’re not reinitializing sub-arrays during reading (which would add overhead).

4. Verify POI Version & Dependencies #

Make sure you’re using the latest stable version of Apache POI (as of now, 5.2.5). Older versions have performance bugs that have been fixed in newer releases. Also, ensure you have all required dependencies (like poi-scratchpad for some HSSF features) included correctly.

With these changes, you should see a massive reduction in read time—likely down to seconds or a couple of minutes at most, depending on the size of each file.

内容的提问来源于stack exchange，提问作者KamelK