Escribir un conjunto de resultados grande en un archivo Excel usando POI

Esto es una especie de línea w / Escribir un ResultSet grande en un archivo, pero el archivo en cuestión es un archivo de Excel.

Estoy usando la biblioteca de POI de Apache para escribir un archivo de Excel con un gran conjunto de datos recuperados de un objeto ResultSet. Los datos pueden variar desde unos miles de registros hasta alrededor de 1 millón; no estoy seguro de cómo esto se traduce en bytes del sistema de archivos en formato Excel.

El siguiente es un código de prueba que escribí para verificar el tiempo que lleva escribir un conjunto de resultados tan grande y también la implicación en el rendimiento de la CPU y la memoria.

protected void writeResultsetToExcelFile(ResultSet rs, int numSheets, String fileNameAndPath) throws Exception { BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(fileNameAndPath)); int numColumns = rs.getMetaData().getColumnCount(); Workbook wb = ExcelFileUtil.createExcelWorkBook(true, numSheets); Row heading = wb.getSheetAt(0).createRow(1); ResultSetMetaData rsmd = rs.getMetaData(); for(int x = 0; x < numColumns; x++) { Cell cell = heading.createCell(x+1); cell.setCellValue(rsmd.getColumnLabel(x+1)); } int rowNumber = 2; int sheetNumber = 0; while(rs.next()) { if(rowNumber == 65001) { log("Sheet " + sheetNumber + "written; moving onto to sheet " + (sheetNumber + 1)); sheetNumber++; rowNumber = 2; } Row row = wb.getSheetAt(sheetNumber).createRow(rowNumber); for(int y = 0; y < numColumns; y++) { row.createCell(y+1).setCellValue(rs.getString(y+1)); wb.write(bos); } rowNumber++; } //wb.write(bos); bos.close(); } 

No mucha suerte con el código anterior. El archivo que se crea parece crecer rápidamente (~ 70Mb por segundo). Así que detuve la ejecución después de unos 10 minutos (eliminó la JVM cuando el archivo alcanza los 7Gb) e intenté abrir el archivo en Excel 2007. En el momento en que lo abro, el tamaño del archivo se convierte en 8k (!) Y solo el encabezado y el primero fila se crean. No estoy seguro de lo que me falta aquí.

¿Algunas ideas?

Oh. Creo que estás escribiendo el libro de trabajo 944,000 veces. Su llamada wb.write (bos) está en el bucle interno. No estoy seguro de que esto sea bastante consistente con la semántica de la clase Workbook? Por lo que puedo decir en los Javadocs de esa clase, ese método escribe todo el libro en la secuencia de salida especificada. Y va a escribir cada fila que ha agregado hasta ahora una vez por cada fila a medida que la cosa crezca.

Esto explica por qué estás viendo exactamente 1 fila, también. El primer libro (con una fila) que se va a escribir en el archivo es todo lo que se muestra, y luego 7 GB de basura a partir de entonces.

Usando SXSSF poi 3.8

 package example; import java.io.FileInputStream; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SXSSFexample { public static void main(String[] args) throws Throwable { FileInputStream inputStream = new FileInputStream("mytemplate.xlsx"); XSSFWorkbook wb_template = new XSSFWorkbook(inputStream); inputStream.close(); SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); wb.setCompressTempFiles(true); SXSSFSheet sh = (SXSSFSheet) wb.getSheetAt(0); sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk for(int rownum = 4; rownum < 100000; rownum++){ Row row = sh.createRow(rownum); for(int cellnum = 0; cellnum < 10; cellnum++){ Cell cell = row.createCell(cellnum); String address = new CellReference(cell).formatAsString(); cell.setCellValue(address); } } FileOutputStream out = new FileOutputStream("tempsxssf.xlsx"); wb.write(out); out.close(); } } 

Requiere:

  • poi-ooxml-3.8.jar,
  • poi-3.8.jar,
  • poi-ooxml-schemas-3.8.jar,
  • stax-api-1.0.1.jar,
  • xml-apis-1.0.b2.jar,
  • xmlbeans-2.3.0.jar,
  • commons-codec-1.5.jar,
  • dom4j-1.6.1.jar

Enlace útil

A menos que tenga que escribir fórmulas o formatear, debería considerar escribir un archivo .csv. Infinitamente más simple, infinitamente más rápido y Excel hará la conversión a .xls o .xlsx automática y correctamente por definición.

Puede usar la implementación SXSSFWorkbook de Workbook , si usa estilo en su Excel, puede usar el estilo de caché de Flyweight Pattern para mejorar su rendimiento. enter image description here

Por ahora tomé el consejo de @Gian y limité el número de registros por Libro de trabajo a 500k y pasé el rest al siguiente Libro de trabajo. Parece estar trabajando decente. Para la configuración anterior, me tomó alrededor de 10 minutos por libro de trabajo.

Actualicé BigGridDemo para admitir varias hojas.

BigExcelWriterImpl.java

 package com.gdais.common.apache.poi.bigexcelwriter; import static com.google.common.base.Preconditions.*; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.OutputStreamWriter; import java.io.Writer; import java.util.Enumeration; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import javax.annotation.Nonnull; import javax.annotation.Nullable; import org.apache.commons.io.FilenameUtils; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.google.common.base.Function; import com.google.common.collect.ImmutableList; import com.google.common.collect.Iterables; public class BigExcelWriterImpl implements BigExcelWriter { private static final String XML_ENCODING = "UTF-8"; @Nonnull private final File outputFile; @Nullable private final File tempFileOutputDir; @Nullable private File templateFile = null; @Nullable private XSSFWorkbook workbook = null; @Nonnull private LinkedHashMap addedSheets = new LinkedHashMap(); @Nonnull private Map sheetTempFiles = new HashMap(); BigExcelWriterImpl(@Nonnull File outputFile) { this.outputFile = outputFile; this.tempFileOutputDir = outputFile.getParentFile(); } @Override public BigExcelWriter createWorkbook() { workbook = new XSSFWorkbook(); return this; } @Override public BigExcelWriter addSheets(String... sheetNames) { checkState(workbook != null, "workbook must be created before adding sheets"); for (String sheetName : sheetNames) { XSSFSheet sheet = workbook.createSheet(sheetName); addedSheets.put(sheetName, sheet); } return this; } @Override public BigExcelWriter writeWorkbookTemplate() throws IOException { checkState(workbook != null, "workbook must be created before writing template"); checkState(templateFile == null, "template file already written"); templateFile = File.createTempFile(FilenameUtils.removeExtension(outputFile.getName()) + "-template", ".xlsx", tempFileOutputDir); System.out.println(templateFile); FileOutputStream os = new FileOutputStream(templateFile); workbook.write(os); os.close(); return this; } @Override public SpreadsheetWriter createSpreadsheetWriter(String sheetName) throws IOException { if (!addedSheets.containsKey(sheetName)) { addSheets(sheetName); } return createSpreadsheetWriter(addedSheets.get(sheetName)); } @Override public SpreadsheetWriter createSpreadsheetWriter(XSSFSheet sheet) throws IOException { checkState(!sheetTempFiles.containsKey(sheet), "writer already created for this sheet"); File tempSheetFile = File.createTempFile( FilenameUtils.removeExtension(outputFile.getName()) + "-sheet" + sheet.getSheetName(), ".xml", tempFileOutputDir); Writer out = null; try { out = new OutputStreamWriter(new FileOutputStream(tempSheetFile), XML_ENCODING); SpreadsheetWriter sw = new SpreadsheetWriterImpl(out); sheetTempFiles.put(sheet, tempSheetFile); return sw; } catch (RuntimeException e) { if (out != null) { out.close(); } throw e; } } private static Function getSheetName = new Function() { @Override public String apply(XSSFSheet sheet) { return sheet.getPackagePart().getPartName().getName().substring(1); } }; @Override public File completeWorkbook() throws IOException { FileOutputStream out = null; try { out = new FileOutputStream(outputFile); ZipOutputStream zos = new ZipOutputStream(out); Iterable sheetEntries = Iterables.transform(sheetTempFiles.keySet(), getSheetName); System.out.println("Sheet Entries: " + sheetEntries); copyTemplateMinusEntries(templateFile, zos, sheetEntries); for (Map.Entry entry : sheetTempFiles.entrySet()) { XSSFSheet sheet = entry.getKey(); substituteSheet(entry.getValue(), getSheetName.apply(sheet), zos); } zos.close(); out.close(); return outputFile; } finally { if (out != null) { out.close(); } } } private static void copyTemplateMinusEntries(File templateFile, ZipOutputStream zos, Iterable entries) throws IOException { ZipFile templateZip = new ZipFile(templateFile); @SuppressWarnings("unchecked") Enumeration en = (Enumeration) templateZip.entries(); while (en.hasMoreElements()) { ZipEntry ze = en.nextElement(); if (!Iterables.contains(entries, ze.getName())) { System.out.println("Adding template entry: " + ze.getName()); zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = templateZip.getInputStream(ze); copyStream(is, zos); is.close(); } } } private static void substituteSheet(File tmpfile, String entry, ZipOutputStream zos) throws IOException { System.out.println("Adding sheet entry: " + entry); zos.putNextEntry(new ZipEntry(entry)); InputStream is = new FileInputStream(tmpfile); copyStream(is, zos); is.close(); } private static void copyStream(InputStream in, OutputStream out) throws IOException { byte[] chunk = new byte[1024]; int count; while ((count = in.read(chunk)) >= 0) { out.write(chunk, 0, count); } } @Override public Workbook getWorkbook() { return workbook; } @Override public ImmutableList getSheets() { return ImmutableList.copyOf(addedSheets.values()); } } 

SpreadsheetWriterImpl.java

 package com.gdais.common.apache.poi.bigexcelwriter; import java.io.IOException; import java.io.Writer; import java.util.Calendar; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellReference; class SpreadsheetWriterImpl implements SpreadsheetWriter { private static final String XML_ENCODING = "UTF-8"; private final Writer _out; private int _rownum; SpreadsheetWriterImpl(Writer out) { _out = out; } @Override public SpreadsheetWriter closeFile() throws IOException { _out.close(); return this; } @Override public SpreadsheetWriter beginSheet() throws IOException { _out.write("" + ""); _out.write("\n"); return this; } @Override public SpreadsheetWriter endSheet() throws IOException { _out.write(""); _out.write(""); closeFile(); return this; } /** * Insert a new row * * @param rownum * 0-based row number */ @Override public SpreadsheetWriter insertRow(int rownum) throws IOException { _out.write("\n"); this._rownum = rownum; return this; } /** * Insert row end marker */ @Override public SpreadsheetWriter endRow() throws IOException { _out.write("\n"); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, String value, int styleIndex) throws IOException { String ref = new CellReference(_rownum, columnIndex).formatAsString(); _out.write(""); _out.write("" + value + ""); _out.write(""); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, String value) throws IOException { createCell(columnIndex, value, -1); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, double value, int styleIndex) throws IOException { String ref = new CellReference(_rownum, columnIndex).formatAsString(); _out.write(""); _out.write("" + value + ""); _out.write(""); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, double value) throws IOException { createCell(columnIndex, value, -1); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, Calendar value) throws IOException { createCell(columnIndex, value, -1); return this; } }