使用POI将大型结果集写入Excel文件

Question

使用POI将大型结果集写入Excel文件

47

这与将大型ResultSet写入文件有些类似，但问题中涉及的文件是一个Excel文件。

我正在使用Apache POI库编写一个Excel文件，并从ResultSet对象中检索出一个大数据集。数据可能涵盖几千条记录到大约100万条记录，不确定如何将其转换为Excel格式中的文件系统字节。

以下是我编写的测试代码，用于检查编写如此大的结果集所需的时间以及相对于CPU和内存的性能影响。

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

以上的代码运行不太顺利。创建的文件似乎迅速增长（每秒约70Mb）。所以我停止了执行，大约10分钟后（当文件达到7Gb时杀死了JVM），并尝试在Excel 2007中打开该文件。一旦我打开它，文件大小就变成了8k(!)，只有标题和第一行被创建。不确定我错过了什么。

有任何想法吗？

- Ranga

是的，ResultSet 中的记录数约为 944,000。我正在运行在 32 位 XP Pro SP3 上，拥有大约 3GB 的 RAM @ 800 MHz 和 Intel i7 时钟频率为 2.8GHz。 - Ranga

我认为你不需要每次写入65000行就去新的工作表，除非这是你自己的要求。 - Wivani

@Wivani，这是因为Excel 2003在给定工作表中的行数上有65k的限制。我相信它还有每个工作表的列数限制-255。 - Ranga

1

但是POI并未使用Excel 2003的格式，因此该限制已经过时。 - Wivani

jeejava.com/handling-large-data-writing-to-excel-using-sxssf-apache-poi/ - user3470953

显示剩余2条评论

7个回答

10

哦，我认为你正在将工作簿写入944,000次。你的wb.write(bos)调用在内部循环中。我不确定这是否与Workbook类的语义完全一致？从我在该类的Javadocs中所了解到的，该方法会将整个工作簿写入指定的输出流中。并且它会随着事物增长而为每一行添加的所有行编写一次。

这也解释了为什么你只看到了1行。要显示的是第一个工作簿（仅有一行）写入文件 - 然后是7GB的垃圾。

- Gian

谢谢Gian。我这样做是因为在将整个工作簿存储到流之前，在内存中创建它时已经用完了堆空间（设置为1024M）。我的perm gen大小也设置为256M（足够高）。你可以看到，在bos.close()之前，我已经注释掉了wb.write(bos)。 - Ranga

是的，我猜想你已经两种方式都测试过了。我的猜测是，如果无法一次性生成整个文件，则将其拆分为不同的文件，然后再想办法合并它们（可能在Excel本身中进行合并）。 - Gian

3

除非你必须编写公式或格式，否则可以考虑将文件保存为 .csv 格式。这种方式更加简单和快速，Excel 会自动正确地将其转换为 .xls 或 .xlsx 格式。

- user207421

4

非常正确，但不幸的是我的要求是生成xls/xlsx文件 :) - Ranga

1

Excel也会搞乱看起来像数字但实际上不是的字段。你需要知道CSV将在Excel中打开。 - John Allen

3

您可以使用 SXSSFWorkbook 实现 Workbook，如果您在 Excel 中使用样式，可以通过 Flyweight Pattern 缓存样式以提高性能。

- Alireza Alallah

SXSSF在您的表格中被写作“Write File: no”。 - Erdal G.

2

您可以通过以下步骤提高Excel导出的性能：

1）从数据库获取数据时，避免将结果集转换为实体类列表，而是直接将其分配给List。

List<Object[]> resultList =session.createSQLQuery("SELECT t1.employee_name, t1.employee_id ... from t_employee t1 ").list();

替代

List<Employee> employeeList =session.createSQLQuery("SELECT t1.employee_name, t1.employee_id ... from t_employee t1 ").list();

2) 使用SXSSFWorkbook创建Excel工作簿对象，而不是使用XSSFWorkbook，并在数据不为空时使用SXSSFRow创建新行。

3) 使用java.util.Iterator迭代数据列表。

Iterator itr = resultList.iterator();

4) 使用列++将数据写入Excel中。

int rowCount = 0;
int column = 0;
while(itr.hasNext()){
 SXSSFRow row = xssfSheet.createRow(rowCount++);

 Object[] object = (Object[]) itr.next();
 //column 1     
 row.setCellValue(object[column++]); // write logic to create cell with required style in setCellValue method
 //column 2
 row.setCellValue(object[column++]);
 itr.remove();
}

5) 在迭代列表时，将数据写入Excel表格并使用remove方法从列表中删除该行。这是为了避免保留列表中不需要的数据并清除Java堆大小。

itr.remove();

- Sarath Kumar

1

我更新了BigGridDemo以支持多个工作表。

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<String, XSSFSheet> addedSheets = new LinkedHashMap<String, XSSFSheet>();

@Nonnull
private Map<XSSFSheet, File> sheetTempFiles = new HashMap<XSSFSheet, File>();

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<XSSFSheet, String> getSheetName = new Function<XSSFSheet, String>() {

    @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<String> sheetEntries = Iterables.transform(sheetTempFiles.keySet(),
                getSheetName);
        System.out.println("Sheet Entries: " + sheetEntries);
        copyTemplateMinusEntries(templateFile, zos, sheetEntries);

        for (Map.Entry<XSSFSheet, File> 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<String> entries) throws IOException {

    ZipFile templateZip = new ZipFile(templateFile);

    @SuppressWarnings("unchecked")
    Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) 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<XSSFSheet> 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("<?xml version=\"1.0\" encoding=\""
            + XML_ENCODING
            + "\"?>"
            +
            "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
    _out.write("<sheetData>\n");

    return this;
}

@Override
public SpreadsheetWriter endSheet() throws IOException {
    _out.write("</sheetData>");
    _out.write("</worksheet>");

    closeFile();
    return this;
}

/**
 * Insert a new row
 * 
 * @param rownum
 *            0-based row number
 */
@Override
public SpreadsheetWriter insertRow(int rownum) throws IOException {
    _out.write("<row r=\"" + (rownum + 1) + "\">\n");
    this._rownum = rownum;

    return this;
}

/**
 * Insert row end marker
 */
@Override
public SpreadsheetWriter endRow() throws IOException {
    _out.write("</row>\n");

    return this;
}

@Override
public SpreadsheetWriter createCell(int columnIndex, String value, int styleIndex)
        throws IOException {
    String ref = new CellReference(_rownum, columnIndex).formatAsString();
    _out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
    if (styleIndex != -1) {
        _out.write(" s=\"" + styleIndex + "\"");
    }
    _out.write(">");
    _out.write("<is><t>" + value + "</t></is>");
    _out.write("</c>");

    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("<c r=\"" + ref + "\" t=\"n\"");
    if (styleIndex != -1) {
        _out.write(" s=\"" + styleIndex + "\"");
    }
    _out.write(">");
    _out.write("<v>" + value + "</v>");
    _out.write("</c>");

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

- John B

我有一个问题需要导出大型 Excel 文件，大约有 800,000 条记录。你更新了哪个 BigGridDemo 支持多个工作表？是 BigExcelWriterImpl 还是在 docjar 中更新了？ - user525146

我们如何合并单元格？ - vaibhav

0

目前我采取了@Gian的建议，将每个工作簿的记录数量限制在500k以内，并将其余记录滚动到下一个工作簿中。看起来效果不错。对于上述配置，每个工作簿大约需要10分钟。

- Ranga

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Juan Rojas · Accepted Answer

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

}

需要：

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

有用的链接