Как загрузить большой файл xlsx с помощью Apache POI?

Ответ 1

Я был в похожей ситуации с окружением веб-сервера. Типичный размер загрузок составлял ~ 150 тыс. Строк, и было бы неплохо потреблять тонну памяти из одного запроса. API Apache POI Streaming API хорошо подходит для этого, но для этого требуется полная редизайн вашей логики чтения. У меня уже была куча логики чтения с использованием стандартного API, который я не хотел повторять, поэтому я написал это вместо: https://github.com/monitorjbl/excel-streaming-reader

Это не полная замена стандартного класса XSSFWorkbook, но если вы просто выполняете итерацию через строки, он ведет себя аналогично:

import com.monitorjbl.xlsx.StreamingReader;

InputStream is = new FileInputStream(new File("/path/to/workbook.xlsx"));
StreamingReader reader = StreamingReader.builder()
        .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
        .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
        .sheetIndex(0)        // index of sheet to use (defaults to 0)
        .read(is);            // InputStream or File for XLSX file (required)

for (Row r : reader) {
  for (Cell c : r) {
    System.out.println(c.getStringCellValue());
  }
}     

Есть некоторые предостережения от его использования; из-за структурирования листов XLSX не все данные доступны в текущем окне потока. Однако, если вы просто пытаетесь прочитать простые данные из ячеек, для этого он работает очень хорошо.

Ответ 2

Улучшение использования памяти может быть выполнено с помощью файла вместо Stream. (Лучше использовать потоковый API, но Streaming API имеет ограничения, см. http://poi.apache.org/spreadsheet/index.html)

Итак, вместо

Workbook workbook = WorkbookFactory.create(inputStream);

делать

Workbook workbook = WorkbookFactory.create(new File("yourfile.xlsx"));

Это соответствует: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

Файлы против InputStreams

"Когда вы открываете книгу, либо .xls HSSFWorkbook, либо .xlsx XSSFWorkbook, рабочая книга может быть загружена либо из файла, либо из InputStream. Использование объекта File позволяет снизить потребление памяти, в то время как InputStream требует больше памяти так как он должен буферизировать весь файл."

Ответ 3

Поддержка Excel в Apache POI, HSSF и XSSF поддерживает 3 разных режима.

One - это полный, DOM-Like в памяти "UserModel", который поддерживает чтение и запись. Используя общие интерфейсы SS (SpreadSheet), вы можете в принципе прозрачно кодировать как HSSF (.xls), так и XSSF (.xlsx). Однако для этого требуется много памяти.

POI также поддерживает потоковый доступный только для чтения способ обработки файлов EventModel. Это намного более низкоуровневый, чем UserModel, и приближает вас к файловому формату. Для HSSF (.xls) вы получаете поток записей и, возможно, некоторую помощь при их обработке (отсутствующие ячейки, отслеживание формата и т.д.). Для XSSF (.xlsx) вы получаете потоки событий SAX из разных частей файла, с помощью которых можно получить нужную часть файла, а также легко обрабатывать обычные, но мелкие биты файла.

Только для XSSF (.xlsx) POI также поддерживает поточную запись только для записи, подходящую для записи низкого уровня, но с низкой памятью. В основном он поддерживает только новые файлы (возможно, некоторые виды append). Не существует эквивалента HSSF, и из-за смещений байтов и смещений байтов и смещений индекса во многих записях было бы довольно сложно сделать...

В вашем конкретном случае, как описано в ваших поясняющих комментариях, я думаю, вы захотите использовать код XSSF EventModel. Для начала просмотрите документацию POI, затем попробуйте найти эти три classes в POI и Tika, которые используют его для более подробности.

Ответ 4

В настоящее время POI включает API для этих случаев. SXSSF http://poi.apache.org/spreadsheet/index.html Он не загружает все в память, чтобы он мог обрабатывать такой файл.

Примечание. Я прочитал, что SXSSF работает как API для написания. Загрузка должна выполняться с использованием XSSF без ввода потока в файл (чтобы избежать полной загрузки его в памяти)

Ответ 5

Проверьте этот пост. Я покажу, как использовать SAX-парсер для обработки файла XLSX.

fooobar.com/questions/191623/...

Короче говоря, я расширил org.xml.sax.helpers.DefaultHandler, который обрабатывает структуру XML для файла XLSX. это анализатор событий - SAX.

class SheetHandler extends DefaultHandler {

    private static final String ROW_EVENT = "row";
    private static final String CELL_EVENT = "c";

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;

    private List<String> cellCache = new LinkedList<>();
    private List<String[]> rowCache = new LinkedList<>();

    private SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
                             Attributes attributes) throws SAXException {
        // c => cell
        if (CELL_EVENT.equals(name)) {
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        } else if (ROW_EVENT.equals(name)) {
            if (!cellCache.isEmpty()) {
                rowCache.add(cellCache.toArray(new String[cellCache.size()]));
            }
            cellCache.clear();
        }

        // Clear contents cache
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        // v => contents of a cell
        // Output after we've seen the string contents
        if(name.equals("v")) {
            cellCache.add(lastContents);
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }

    public List<String[]> getRowCache() {
        return rowCache;
    }
}

А затем я анализирую XML файл XLSX, который был представлен ранее.

private List<String []> processFirstSheet(String filename) throws Exception {
    OPCPackage pkg = OPCPackage.open(filename, PackageAccess.READ);
    XSSFReader r = new XSSFReader(pkg);
    SharedStringsTable sst = r.getSharedStringsTable();

    SheetHandler handler = new SheetHandler(sst);
    XMLReader parser = fetchSheetParser(handler);
    Iterator<InputStream> sheetIterator = r.getSheetsData();

    if (!sheetIterator.hasNext()) {
        return Collections.emptyList();
    }

    InputStream sheetInputStream = sheetIterator.next();
    BufferedInputStream bisSheet = new BufferedInputStream(sheetInputStream);
    InputSource sheetSource = new InputSource(bisSheet);
    parser.parse(sheetSource);
    List<String []> res = handler.getRowCache();
    bisSheet.close();
    return res;
}

public XMLReader fetchSheetParser(ContentHandler handler) throws SAXException {
    XMLReader parser = new SAXParser();
    parser.setContentHandler(handler);
    return parser;
}

Ответ 6

Вы можете использовать SXXSF вместо использования HSSF. Я мог бы генерировать excel с 200000 строк.

Ответ 7

Основываясь на ответе monitorjbl и тестовом наборе, изученном в poi, у меня сработало следующее для многостраничного файла xlsx с записями 200 КБ (размер> 50 МБ):

import com.monitorjbl.xlsx.StreamingReader;
. . .
try (
        InputStream is = new FileInputStream(new File("sample.xlsx"));
        Workbook workbook = StreamingReader.builder().open(is);
) {
    DataFormatter dataFormatter = new DataFormatter();
    for (Sheet sheet : workbook) {
        System.out.println("Processing sheet: " + sheet.getSheetName());
        for (Row row : sheet) {
            for (Cell cell : row) {
                String value = dataFormatter.formatCellValue(cell);
            }
        }
    }
}