poi - leer excel desde java como si fuera una base de datos

API para escribir enormes archivos de Excel usando java

Question

Estoy buscando escribir en un archivo Excel (formato .xls MS Excel 2003) programáticamente usando Java. Los archivos de salida de Excel pueden contener ~ 200,000 filas que planeo dividir en una cantidad de hojas (64k filas por hoja, debido al límite de Excel).

He intentado usar las API de POI de apache, pero parece ser un problema de memoria debido al modelo de objetos API. Me veo obligado a agregar celdas / hojas al objeto libro de trabajo en la memoria y solo una vez que se agregan todos los datos, puedo escribir el libro en un archivo. Aquí hay una muestra de cómo el apache recomienda escribir archivos de Excel usando su API:

Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");

//Create a row and put some cells in it
Row row = sheet.createRow((short)0);

// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

Claramente, escribir ~ 20k filas (con unas 10-20 columnas en cada fila) me da el temido "java.lang.OutOfMemoryError: espacio de pila de Java".

He intentado aumentar el tamaño inicial de JVM y el tamaño máximo de almacenamiento dinámico utilizando los parámetros Xms y Xmx como Xms512m y Xmx1024. Todavía no puedo escribir más de 150k filas en el archivo.

Estoy buscando una manera de transmitir a un archivo de Excel en lugar de construir todo el archivo en la memoria antes de escribirlo en el disco, lo que con suerte ahorrará mucho uso de memoria. Cualquier API o solución alternativa sería apreciada, pero estoy restringido al uso de Java. ¡Gracias! :)

Answer 1

Trate de usar el libro de trabajo SXSSF , eso es genial para los documentos xls grandes, su documento de compilación y no comer RAM en absoluto, porque usa nio

Answer 2

¿Se produce este problema de memoria cuando inserta datos en la celda o cuando realiza cálculos / generación de datos?

Si va a cargar archivos en Excel que consisten en un formato predefinido de plantilla estática, entonces mejor guardar una plantilla y reutilizar varias veces. Normalmente, los casos de plantilla suceden cuando va a generar un informe de ventas diarias, etc.

De lo contrario, cada vez que necesite crear una nueva fila, borde, columna, etc. desde cero.

Hasta ahora, Apache POI es la única opción que encontré.

"Claramente, escribir ~ 20k filas (con unas 10-20 columnas en cada fila) me da el temido" java.lang.OutOfMemoryError: espacio de pila de Java ".

"TI empresarial"

Lo que PUEDE HACER es realizar una inserción de datos por lotes. Cree una tabla quequierak, cada vez que genere 1 página, descanse durante unos segundos, luego continúe con la segunda porción. Si le preocupan los cambios de datos dinámicos durante su tarea de cola, primero puede obtener la clave principal en Excel (ocultando y bloqueando la columna de la vista del usuario). La primera ejecución será insertar la clave principal, luego la segunda fila que se ejecuta en adelante leerá desde el bloc de notas y hará la parte de la tarea por porción.

Answer 3

Hicimos algo muy similar, la misma cantidad de datos, y tuvimos que cambiar a JExcelapi porque POI tiene tantos recursos. Prueba JexcelApi, ¡no te arrepentirás cuando tengas que manipular grandes archivos de Excel!

Answer 4

También está JExcelApi, pero usa más memoria. Creo que deberías crear un archivo .csv y abrirlo en Excel. le permite pasar una gran cantidad de datos, pero no podrá hacer ninguna "magia excel".

Answer 5

Todas las API de Java existentes intentan generar todo el documento en la RAM de una vez. Intente escribir un archivo XML que se ajuste al nuevo formato de archivo xslx. Para comenzar, sugiero construir un archivo pequeño en el formato deseado en Excel y guardarlo. Luego ábralo y examine la estructura y reemplace las partes que desea.

Wikipedia tiene un buen artículo sobre el formato general .

Answer 6

Considera usar el formato CSV. De esta forma ya no estará limitado por la memoria, sino solo durante la prepoblación de los datos para CSV, pero esto también puede hacerse de manera eficiente, por ejemplo, consultando subconjuntos de filas desde DB usando, por ejemplo, LIMIT/OFFSET y escribiéndolo inmediatamente. archivar en lugar de transportar todo el contenido de la tabla DB a la memoria de Java antes de escribir cualquier línea. La limitación de Excel de las filas de cantidad en una "hoja" aumentará a aproximadamente un millón.

Dicho esto, si los datos realmente provienen de un DB, entonces lo reconsideraría mucho si Java es la herramienta adecuada para esto. La mayoría de los DB decentes tienen una función de exportación a CSV que puede hacer esta tarea, sin duda, mucho más eficiente. En el caso de, por ejemplo, MySQL, puede usar el comando LOAD DATA INFILE para esto.

Answer 7

Eche un vistazo al serializador HSSF del proyecto Cocoon.

El serializador HSSF detecta eventos SAX y crea una hoja de cálculo en el formato XLS utilizado por Microsoft Excel

Answer 8

Tuve que dividir mis archivos en varios archivos de Excel para superar la excepción de espacio dinámico. Me di cuenta de que alrededor de 5k filas con 22 columnas se trataba, así que hice mi lógica para que cada 5k fila terminara el archivo, comenzara una nueva y simplemente numeara los archivos en consecuencia.

En los casos en los que tenía 20k + filas para escribir, tendría 4+ archivos diferentes que representaran los datos.

Answer 9

Desarrollamos una biblioteca de Java para este propósito y actualmente está disponible como proyecto de código abierto https://github.com/jbaliuka/x4j-analytic . Lo usamos para informes operativos. Generamos enormes archivos de Excel, ~ 200,000 deberían funcionar sin problemas, Excel logra abrir esos archivos también. Nuestro código utiliza POI para cargar la plantilla, pero el contenido generado se transmite directamente al archivo sin XML o la capa del modelo de objetos en la memoria.