如何在Spring框架中通过Servlet Response流式生成并输出XLSX文件？

问题核心 #

SXSSFWorkbook的write()方法只能调用一次，因为XLSX本质是ZIP压缩包结构，必须按规范顺序输出所有部件，循环调用会破坏文件结构。要实现"边生成边输出"的效果，有两种可行方案：

方案一：基于SXSSF的内存优化输出（推荐，代码简洁） #

SXSSF是POI专为大文件设计的API，会将超出内存阈值的行写入临时文件，最终一次性写入输出流时，会自动将临时文件内容逐步输出，既避免内存溢出，也减少用户等待时间。

实现代码 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;

public class XlsxStreamingService {
    private static final Logger log = LoggerFactory.getLogger(XlsxStreamingService.class);

    public void generateStreamingXlsx(HttpServletResponse response) throws Exception {
        // 设置Response头信息
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=\"streaming_data.xlsx\"");
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");

        try (OutputStream out = new BufferedOutputStream(response.getOutputStream());
             // 内存中保留100行，超出的写入临时文件
             SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100)) {

            Sheet sheet = sxssfWorkbook.createSheet("数据列表");

            // 创建样式
            Font defaultFont = sxssfWorkbook.createFont();
            defaultFont.setFontHeightInPoints((short) 12);
            CellStyle headerStyle = sxssfWorkbook.createCellStyle();
            headerStyle.setFont(defaultFont);
            CellStyle bodyStyle = sxssfWorkbook.createCellStyle();
            bodyStyle.setFont(defaultFont);

            // 写入表头
            Row headerRow = sheet.createRow(0);
            Cell headerCell = headerRow.createCell(0);
            headerCell.setCellValue("序号");
            headerCell.setCellStyle(headerStyle);

            // 批量写入数据
            for (int i = 0; i < 10000; i++) {
                Row dataRow = sheet.createRow(i + 1);
                Cell dataCell = dataRow.createCell(0);
                dataCell.setCellValue("数据行 " + (i + 1));
                dataCell.setCellStyle(bodyStyle);

                // 每100行刷新一次输出流，避免缓冲区积压
                if (i % 100 == 0) {
                    out.flush();
                }
            }

            // 一次性写入整个工作簿（SXSSF会自动处理临时文件内容，逐步输出）
            sxssfWorkbook.write(out);
            // 清理临时文件
            sxssfWorkbook.dispose();
        } catch (Exception e) {
            log.error("生成流式XLSX失败", e);
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "文件生成失败");
        }
    }
}

方案优势 #

• 代码简洁，无需手动处理OOXML结构
• 内存占用可控，适合生成十万级以上行的大文件
• 写入输出流时，SXSSF会逐步将临时文件内容发送到客户端，减少用户等待时间

方案二：手动构建OOXML流式输出（真正的边生成边传） #

如果需要极致的流式传输（比如超大规模数据，客户端需要实时接收数据），可以直接操作XLSX的ZIP包结构和XML部件，每生成一行数据就写入输出流。

实现代码 #

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamWriter;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class TrueStreamingXlsxService {
    private static final Logger log = LoggerFactory.getLogger(TrueStreamingXlsxService.class);

    public void generateTrueStreamingXlsx(HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=\"true_stream.xlsx\"");
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma", "no-cache");

        try (OutputStream rawOut = response.getOutputStream();
             ZipOutputStream zipOut = new ZipOutputStream(rawOut)) {

            // 1. 写入XLSX核心部件：[Content_Types].xml
            writeContentTypes(zipOut);
            // 2. 写入关系部件：_rels/.rels
            writePackageRels(zipOut);
            // 3. 写入工作簿部件：xl/workbook.xml
            writeWorkbook(zipOut);
            // 4. 写入样式部件：xl/styles.xml
            writeStyles(zipOut);
            // 5. 流式写入工作表数据：xl/worksheets/sheet1.xml
            writeStreamingWorksheet(zipOut, rawOut);

        } catch (Exception e) {
            log.error("生成真正流式XLSX失败", e);
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "文件生成失败");
        }
    }

    private void writeContentTypes(ZipOutputStream zipOut) throws Exception {
        zipOut.putNextEntry(new ZipEntry("[Content_Types].xml"));
        String content = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">" +
                "<Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/>" +
                "<Default Extension=\"xml\" ContentType=\"application/xml\"/>" +
                "<Override PartName=\"/xl/workbook.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\"/>" +
                "<Override PartName=\"/xl/worksheets/sheet1.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\"/>" +
                "<Override PartName=\"/xl/styles.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\"/>" +
                "</Types>";
        zipOut.write(content.getBytes(StandardCharsets.UTF_8));
        zipOut.closeEntry();
    }

    private void writePackageRels(ZipOutputStream zipOut) throws Exception {
        zipOut.putNextEntry(new ZipEntry("_rels/.rels"));
        String content = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">" +
                "<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.xml\"/>" +
                "</Relationships>";
        zipOut.write(content.getBytes(StandardCharsets.UTF_8));
        zipOut.closeEntry();
    }

    private void writeWorkbook(ZipOutputStream zipOut) throws Exception {
        zipOut.putNextEntry(new ZipEntry("xl/workbook.xml"));
        String content = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">" +
                "<sheets>" +
                "<sheet name=\"流式数据\" sheetId=\"1\" r:id=\"rId1\"/>" +
                "</sheets>" +
                "</workbook>";
        zipOut.write(content.getBytes(StandardCharsets.UTF_8));
        zipOut.closeEntry();
    }

    private void writeStyles(ZipOutputStream zipOut) throws Exception {
        zipOut.putNextEntry(new ZipEntry("xl/styles.xml"));
        String content = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" +
                "<fonts count=\"1\"><font><sz val=\"12\"/><name val=\"Calibri\"/></font></fonts>" +
                "<fills count=\"1\"><fill><patternFill patternType=\"none\"/></fill></fills>" +
                "<borders count=\"1\"><border><left/><right/><top/><bottom/></border></borders>" +
                "<cellStyleXfs count=\"1\"><xf fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs>" +
                "<cellXfs count=\"2\">" +
                "<xf fontId=\"0\" fillId=\"0\" borderId=\"0\" applyFont=\"1\"/>" +
                "<xf fontId=\"0\" fillId=\"0\" borderId=\"0\" applyFont=\"1\"/>" +
                "</cellXfs>" +
                "</styleSheet>";
        zipOut.write(content.getBytes(StandardCharsets.UTF_8));
        zipOut.closeEntry();
    }

    private void writeStreamingWorksheet(ZipOutputStream zipOut, OutputStream rawOut) throws Exception {
        zipOut.putNextEntry(new ZipEntry("xl/worksheets/sheet1.xml"));
        XMLStreamWriter writer = XMLInputFactory.newInstance().createXMLStreamWriter(zipOut, "UTF-8");

        writer.writeStartDocument("UTF-8", "1.0");
        writer.writeStartElement("worksheet", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        writer.writeStartElement("sheetData");

        // 写入表头
        writer.writeStartElement("row");
        writer.writeAttribute("r", "1");
        writer.writeStartElement("c");
        writer.writeAttribute("r", "A1");
        writer.writeAttribute("s", "0");
        writer.writeStartElement("v");
        writer.writeCharacters("序号");
        writer.writeEndElement(); // v
        writer.writeEndElement(); // c
        writer.writeEndElement(); // row

        // 流式写入10000行数据
        for (int i = 0; i < 10000; i++) {
            writer.writeStartElement("row");
            writer.writeAttribute("r", String.valueOf(i + 2));
            writer.writeStartElement("c");
            writer.writeAttribute("r", "A" + (i + 2));
            writer.writeAttribute("s", "1");
            writer.writeStartElement("v");
            writer.writeCharacters("数据行 " + (i + 1));
            writer.writeEndElement(); // v
            writer.writeEndElement(); // c
            writer.writeEndElement(); // row

            // 每100行刷新一次，确保数据实时发送到客户端
            if (i % 100 == 0) {
                writer.flush();
                zipOut.flush();
                rawOut.flush();
            }
        }

        writer.writeEndElement(); // sheetData
        writer.writeEndElement(); // worksheet
        writer.writeEndDocument();
        writer.flush();
        zipOut.closeEntry();
    }
}

方案优势 #

• 真正实现边生成边输出，客户端无需等待整个文件生成
• 完全不依赖POI的内存缓存，内存占用极低
• 适合生成百万级以上行的超大规模文件

内容的提问来源于stack exchange，提问作者Jang Sungil