¿Cómo cargar un archivo xlsx grande con Apache POI?

Tengo un gran archivo .xlsx (141 MB, que contiene 293413 líneas con 62 columnas cada uno) Necesito realizar algunas operaciones dentro.

Tengo problemas para cargar este archivo ( OutOfMemoryError ), ya que POI tiene una gran huella de memoria en los libros XSSF (xlsx).

Esta pregunta SO es similar, y la solución presentada es boost la memoria asignada / máxima de la VM.

Parece funcionar para ese tipo de tamaño de archivo (9 MB), pero para mí, simplemente no funciona, incluso si se asigna toda la memoria del sistema disponible. (Bueno, no es sorprendente teniendo en cuenta que el archivo es más de 15 veces más grande)

Me gustaría saber si hay alguna manera de cargar el libro de una manera que no consum toda la memoria y, sin embargo, sin hacer el procesamiento basado en el XML subyacente del XSSF. (En otras palabras, mantener una solución puritana de POI)

Si no es difícil, puede decirlo (“No es así”) y señalarme el camino hacia una solución “XML”.

Estaba en una situación similar con un entorno de servidor web. El tamaño típico de las cargas era ~ 150k filas y no habría sido bueno consumir una tonelada de memoria de una sola solicitud. El Apache POI Streaming API funciona bien para esto, pero requiere un rediseño total de su lógica de lectura. Ya tenía un montón de lógica de lectura usando la API estándar que no quería volver a hacer, así que escribí esto en su lugar: https://github.com/monitorjbl/excel-streaming-reader

No es un reemplazo completo para la clase estándar XSSFWorkbook , pero si solo está iterando a través de las filas, se comporta de manera similar:

 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()); } } 

Hay algunas advertencias sobre su uso; debido a la forma en que se estructuran las hojas XLSX, no todos los datos están disponibles en la ventana actual de la transmisión. Sin embargo, si solo está tratando de leer datos simples de las celdas, funciona bastante bien para eso.

Una mejora en el uso de la memoria se puede hacer mediante el uso de un archivo en lugar de un flujo. (Es mejor utilizar una API de transmisión, pero las API de transmisión tienen limitaciones, consulte http://poi.apache.org/spreadsheet/index.html )

Entonces, en lugar de

 Workbook workbook = WorkbookFactory.create(inputStream); 

hacer

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

Esto está de acuerdo con: http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

Archivos vs InputStreams

“Al abrir un libro de trabajo, ya sea .xls HSSFWorkbook o .xlsx XSSFWorkbook, el Workbook se puede cargar desde un archivo o un InputStream. Usar un objeto File permite un menor consumo de memoria, mientras que un InputStream requiere más memoria ya que tiene para almacenar todo el archivo “.

El soporte de Excel en Apache POI, HSSF y XSSF, admite 3 modos diferentes.

Uno de ellos es un “UserModel” completo, similar a DOM en la memoria, que admite lectura y escritura. Utilizando las interfaces SS (SpreadSheet) comunes, puede codificar tanto HSSF (.xls) como XSSF (.xlsx) básicamente de forma transparente. Sin embargo, necesita mucha memoria.

POI también es compatible con una forma de solo lectura de solo lectura para procesar los archivos, el modelo de evento. Esto es mucho más bajo que el UserModel, y te acerca mucho al formato de archivo. Para HSSF (.xls) obtienes un flujo de registros y, opcionalmente, algo de ayuda para manejarlos (células que faltan, seguimiento de formato, etc.). Para XSSF (.xlsx) obtienes secuencias de eventos SAX de las diferentes partes del archivo, con ayuda para obtener la parte correcta del archivo y también el fácil procesamiento de bits comunes pero pequeños del archivo.

Solo para XSSF (.xlsx), POI también es compatible con escritura de transmisión de solo escritura, adecuada para escritura de bajo nivel pero con poca memoria. Sin embargo, solo admite archivos nuevos (son posibles ciertos tipos de anexos). No existe un HSSF equivalente, y debido a compensaciones de bytes de ida y vuelta y compensaciones de índices en muchos registros, sería bastante difícil de hacer …

Para su caso específico, como se describe en sus comentarios aclaratorios, creo que querrá usar el código XSSF EventModel. Consulte la documentación de POI para comenzar, luego intente ver estas tres clases en POI y Tika, que lo utilizan para obtener más detalles.

POI ahora incluye una API para estos casos. SXSSF http://poi.apache.org/spreadsheet/index.html No carga todo en la memoria por lo que podría permitirle manejar ese archivo.

Nota: He leído que SXSSF funciona como una API de escritura. La carga se debe hacer usando XSSF sin entradas en el archivo (para evitar una carga completa en la memoria)

Revisa esta publicación Muestro cómo usar el analizador SAX para procesar un archivo XLSX.

https://stackoverflow.com/a/44969009/4587961

En resumen, extendí org.xml.sax.helpers.DefaultHandler que procesa la estructura XML para archivos XLSX. t es un analizador de eventos – 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 cellCache = new LinkedList<>(); private List 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 getRowCache() { return rowCache; } } 

Y luego analizo el XML que preside el archivo XLSX

 private List 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 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 res = handler.getRowCache(); bisSheet.close(); return res; } public XMLReader fetchSheetParser(ContentHandler handler) throws SAXException { XMLReader parser = new SAXParser(); parser.setContentHandler(handler); return parser; } 

Puede usar SXXSF en lugar de usar HSSF. Podría generar Excel con 200000 filas.

Basado en el paquete de pruebas y respuestas de monitorjbl explorado desde poi, el siguiente me funcionó en un archivo xlsx de varias hojas con 200K registros (tamaño> 50 MB):

 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); } } } }