Procesamiento de un archivo xlsx grande

Necesito ajustar automáticamente todas las filas en un archivo xlsx grande (30k + filas).

El siguiente código a través de apache poi funciona en archivos pequeños, pero se apaga con OutOfMemoryError en los grandes:

 Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); for (Row row : sheet) { row.setHeight((short) -1); } workbook.write(outputStream); 

Actualización: Desafortunadamente, boost el tamaño del OutOfMemoryError dynamic no es una opción: OutOfMemoryError aparece en -Xmx1024m y 30k filas no es un límite superior.

Intenta usar la API del evento. Consulte Event API (HSSF solamente) y XSSF y SAX (Event API) en la documentación de POI para obtener más información. Un par de citas de esa página:

HSSF:

La API del evento es más nueva que la API del usuario. Está destinado a desarrolladores intermedios que estén dispuestos a aprender un poco sobre las estructuras de API de bajo nivel. Es relativamente simple de usar, pero requiere una comprensión básica de las partes de un archivo de Excel (o deseo de aprender). La ventaja es que puede leer un XLS con una huella de memoria relativamente pequeña.

XSSF:

Si la huella de memoria es un problema, entonces para XSSF, puede obtener los datos XML subyacentes y procesarlos usted mismo. Esto está destinado a desarrolladores intermedios que están dispuestos a aprender un poco de la estructura de bajo nivel de los archivos .xlsx, y que están encantados de procesar XML en Java. Es relativamente simple de usar, pero requiere una comprensión básica de la estructura del archivo. La ventaja es que puede leer un archivo XLSX con una huella de memoria relativamente pequeña.

Para el resultado, se describe un enfoque posible en la publicación del blog Streaming xlsx files . (Básicamente, use XSSF para generar un archivo XML contenedor, luego transmita el contenido real como texto sin formato en la parte xml apropiada del archivo zip xlsx).

Una mejora dramática 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 “.

Estaba teniendo el mismo problema con mucho menos fila, pero grandes cadenas.

Como no tengo que mantener mis datos cargados, descubrí que puedo usar SXSSF en lugar de XSSF.

Tienen interfaces similares, lo que ayuda si tienes muchos códigos ya escritos. Pero con SXSSF es posible establecer la cantidad de filas que mantiene cargadas.

Aqui esta el link. http://poi.apache.org/spreadsheet/how-to.html#sxssf

Si desea ajustar automáticamente o establecer estilos o escribir todas las filas en un archivo xlsx grande (30k + filas), use SXSSFWorkbook.Aquí está el código de muestra que le ayuda …

 SXSSFWorkbook wb = new SXSSFWorkbook(); SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("writetoexcel"); Font font = wb.createFont(); font.setBoldweight((short) 700); // Create Styles for sheet. XSSFCellStyle Style = (XSSFCellStyle) wb.createCellStyle(); Style.setFillForegroundColor(new XSSFColor(java.awt.Color.LIGHT_GRAY)); Style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); Style.setFont(font); //iterating r number of rows for (int r=0;r < 30000; r++ ) { Row row = sheet.createRow(r); //iterating c number of columns for (int c=0;c < 75; c++ ) { Cell cell = row.createCell(c); cell.setCellValue("Hello"); cell.setCellStyle(Style); } } FileOutputStream fileOut = new FileOutputStream("E:" + File.separator + "NewTest.xlsx"); 

Utilicé Event API para un archivo HSSF (.xls) y descubrí una terrible falta de documentación sobre el orden de los registros.

Aquí hay un ejemplo que encontré que manejará archivos XLSX muy grandes. Mi prueba hasta ahora se ve bien. Es capaz de manejar archivos muy grandes sin problemas de memoria.

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java

Si está escribiendo en XLSX, encontré una mejora al escribir en diferentes hojas del mismo archivo de Excel. También puede encontrar una mejora al escribir en diferentes archivos de Excel. Pero primero intente escribir en diferentes hojas.

El mejor ejemplo para esto se describe en el siguiente subproceso de desbordamiento de stack: Error al leer archivos de Excel grandes (xlsx) a través de POI de Apache

El fragmento de código en la respuesta principal en ese tema ilustra las envolturas de POI de Apache en el análisis SAX xml, y cómo se puede recorrer trivialmente todas las hojas y luego sobre cada celda individual.

El código está obsoleto con la implementación actual de la API de POI de Apache, ya que la API de endrow () proporciona el número de fila actual que ha terminado de procesarse.

Con ese fragmento de código, debería ser trivial que analice un gran archivo XLSX celda por celda. Por ejemplo, para cada hoja; para cada celda de fila; fila ha terminado el evento. Podría crear trivialmente la lógica de la aplicación, donde en la fila de cada fila se crea un Mapa de nombreColon a cellValue.

Tuve el mismo problema con 800,000 celdas y 3M personajes donde XSSF asigna 1GB de montón.

Utilicé Python con openpyxl y numpy para leer el archivo xlsx (del código Java) y primero lo convierto en un texto normal. Luego cargué el archivo de texto en java. Puede parecer tener grandes gastos generales, pero de hecho es rápido.

El script de python se ve como

 import openpyxl as px import numpy as np # xlsx file is given through command line foo.xlsx fname = sys.argv[1] W = px.load_workbook(fname, read_only = True) p = W.get_sheet_by_name(name = 'Sheet1') a=[] # number of rows and columns m = p.max_row n = p.max_column for row in p.iter_rows(): for k in row: a.append(k.value) # convert list a to matrix (for example maxRows*maxColumns) aa= np.resize(a, [m, n]) # output file is also given in the command line foo.txt oname = sys.argv[2] print (oname) file = open(oname,"w") mm = m-1 for i in range(mm): for j in range(n): file.write( "%s " %aa[i,j] ) file.write ("\n") # to prevent extra newline in the text file for j in range(n): file.write("%s " %aa[m-1,j]) file.close() 

Luego, en mi código Java, escribí

 try { // `pwd`\python_script foo.xlsx foo.txt String pythonScript = System.getProperty("user.dir") + "\\exread.py "; String cmdline = "python " + pythonScript + workingDirectoryPath + "\\" + fullFileName + " " + workingDirectoryPath + "\\" + shortFileName + ".txt"; Process p = Runtime.getRuntime().exec(cmdline); int exitCode = p.waitFor(); if (exitCode != 0) { throw new IOException("Python command exited with " + exitCode); } } catch (IOException e) { System.out.println( e.getMessage() ); } catch (InterruptedException e) { ReadInfo.append(e.getMessage() ); } 

Después de eso, obtendrás foo.txt que es similar a foo.xlsx, pero en formato de texto.

Usé el analizador SAX para procesar la estructura XML. Funciona para archivos XLSX.

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