SXSSFWorkbook.write to FileOutputStream 写入大文件

我正在尝试使用 SXSSFWorkbook 从头开始​​编写 Excel 电子表格。

      wb = SXSSFWorkbook(500)
      wb.isCompressTempFiles = true
      sh = streamingWorkbook.createSheet(t.getMessage("template.sheet.name"))

一切都很好，但是当我调用最终代码时：

    val out = FileOutputStream(localPath)
    wb.write(out)
    out.close()
    // dispose of temporary files backing this workbook on disk
    wb.dispose()

我得到了一个巨大的 Excel 文件，而不是我期望的压缩 XLSX 文件。我尝试手动压缩该文件，可以将 120MB 的文件压缩到 9MB。那么我错过了什么？

使用：Kotlin 和

    implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'  // For `.xlsx` files

-- 更新 1

我的印象是 xlsx 本质上是包含 xml 数据的压缩文件[1] https://rzymek.github.io/post/excel-zip64/。 POI 通过 XSSFWorkbook 和 SXSSFWorkbook 输出的内容至少可以压缩 10 个数量级。我使用这个简单的代码来演示：

fun main() {
  val workbook = XSSFWorkbook()
  writeRowsAndSave(workbook, "test.xlsx")
  workbook.close()

  val streamingWorkbook = SXSSFWorkbook(IN_MEMORY_ROWS_WINDOW_SIZE)
  streamingWorkbook.isCompressTempFiles = true
  writeRowsAndSave(streamingWorkbook, "test-streaming.xlsx")
  streamingWorkbook.dispose()
}

private fun writeRowsAndSave(workbook: Workbook, fileName: String) {
  val ROWS_COUNT = 2_000
  val COLS_COUNT = 1_000

  val sheet = workbook.createSheet("Test Sheet 1")
  for (i in 1..ROWS_COUNT) {
    val row = sheet.createRow(i)
    println("Row $i")
    for(j in 1..COLS_COUNT) {
        row.createCell(j).setCellValue("Test $i")
    }
  }

  FileOutputStream("./$fileName").use {
      workbook.write(it)
  }
}

这会生成每个 5MB 的文件，压缩后大约有 439KB（？！）。

SXSSFWorkbook默认使用内联字符串而不是共享字符串表。这意味着SXSSFWorkbook直接在工作表中写入文本，即使它是同一文本的多次。XSSFWorkbook和 Excel 的 GUI 都使用共享字符串表，其中文本获取索引，相同的文本仅存储一次，然后在工作表中使用索引。但这不会对生成的文件大小产生太大影响*.xlsx.

SXSSFWorkbook，以及所有其他Office Open XML格式化文件，apache poi创建，使用压缩org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream。使用 deflate 作为压缩算法Deflater.DEFAULT_COMPRESSION作为默认压缩级别。一个可以覆盖protected ZipArchiveOutputStream createArchiveOutputStream(OutputStream out) of SXSSFWorkbook设置另一个压缩级别。但这也不会对生成的文件大小产生太大影响*.xlsx.

Example Java code:

import java.io.File;
import java.io.OutputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.commons.compress.archivers.zip.Zip64Mode;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;
import java.util.zip.Deflater;

class CreateSXSSFDifferentCompression {

 static SXSSFWorkbook createSXSSFWorkbook(int compressionLevel, int rowAccessWindowSize, 
                                          boolean compressTmpFiles, boolean useSharedStringsTable) {
  SXSSFWorkbook workbook = null;
  if (compressionLevel != Deflater.DEFAULT_COMPRESSION) {
   workbook = new SXSSFWorkbook(null, rowAccessWindowSize, compressTmpFiles, useSharedStringsTable) {
    protected ZipArchiveOutputStream createArchiveOutputStream(OutputStream out) {
     ZipArchiveOutputStream zos = new ZipArchiveOutputStream(out);
     zos.setUseZip64(Zip64Mode.AsNeeded);  
     zos.setLevel(compressionLevel);
     return zos;
    }    
   }; 
  } else {
   workbook = new SXSSFWorkbook(null, rowAccessWindowSize, compressTmpFiles, useSharedStringsTable);
  }
  return workbook;
 }

 public static void main(String[] args) throws Exception {

  SXSSFWorkbook workbook = null;

  // uses Deflater.DEFAULT_COMPRESSION and inline strings
  //workbook = createSXSSFWorkbook(Deflater.DEFAULT_COMPRESSION, 500, true, false); 

  // uses Deflater.DEFAULT_COMPRESSION and shared strings table
  //workbook = createSXSSFWorkbook(Deflater.DEFAULT_COMPRESSION, 500, true, true); 

  // uses Deflater.BEST_COMPRESSION and inline strings
  workbook = createSXSSFWorkbook(Deflater.BEST_COMPRESSION, 500, true, false); 

  // uses Deflater.BEST_COMPRESSION and shared strings table
  //workbook = createSXSSFWorkbook(Deflater.BEST_COMPRESSION, 500, true, true); 

  int ROWS_COUNT = 2000;
  int COLS_COUNT = 1000;

  Sheet sheet = workbook.createSheet("Test Sheet 1");
  for (int i = 1 ; i <= ROWS_COUNT; i++) {
   Row row = sheet.createRow(i);
   //System.out.println("Row " + i);
   for(int j = 1; j <= COLS_COUNT; j++) {
    row.createCell(j).setCellValue("Test " + i);
   }
  }

  FileOutputStream out = new FileOutputStream("./Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
  workbook.dispose();

  File file = new File("./Excel.xlsx");
  System.out.println(file.length());

 }
}

这导致Excel.xlsx文件大小：

5,031,034 字节 当使用 Deflater.DEFAULT_COMPRESSION 和内联字符串时。

4,972,663 字节 当使用 Deflater.DEFAULT_COMPRESSION 和共享字符串表时。

4,972,915 字节 当使用 Deflater.BEST_COMPRESSION 和内联字符串时。

和 4,966,749 字节 当使用 Deflater.BEST_COMPRESSION 和共享字符串表时。

Used: Java 12, apache poi 4.1.2, Ubuntu Linux.

对于 2,000 行 x 1,000 列的电子表格，我既不认为其巨大，也不认为不同设置的影响很大。

而且条目压缩得非常好。

如果你调查Excel.xlsxZIP 存档，您会发现未压缩的大小xl/worksheets/sheet1.xml使用内联字符串时为 112,380,273 字节。未压缩的大小为xl/sharedStrings.xml则为 138 字节，仅包含非常基本的 XML。

如果使用共享字符串表，则未压缩的大小为xl/worksheets/sheet1.xml是 68,377,273 字节，未压缩的大小为xl/sharedStrings.xml大小为 49,045 字节，包含 2,000 个条目。

If Excel本身保存*.xlsx文件，当内容相等时，它会创建具有大致相同文件大小的文件。所以Excel本身使用相同的压缩级别。

当然可以压缩*.xlsx存储时文件较多Excel.xlsx into a *.zip再次存档。但那不会是什么Excel期望成为一个*.xlsx file.

Microsoft状态于Open XML 格式有哪些好处？ https://support.office.com/en-gb/article/open-xml-formats-and-file-name-extensions-5200d93c-3449-4380-8e11-31ef14555b18#bm2:

压缩文件文件会自动压缩，在某些情况下最多可缩小 75%。 Open XML 格式使用 zip 压缩技术来存储文档，提供潜在的成本 节省，因为它减少了存储文件所需的磁盘空间 减少通过电子邮件发送文件所需的带宽 网络，以及整个互联网。当你打开一个文件时，它是 自动解压。当您保存文件时，它会自动 再次拉上拉链。您无需安装任何特殊的 zip 实用程序即可 在 Office 中打开和关闭文件。

这里重要的部分是：

当您打开文件时，它会自动解压缩。当您保存一个 文件，它会再次自动压缩。

这意味着，如果apache poi将使用其他方法或压缩级别来压缩文件Microsoft Office本身，那么Microsoft Office无法对文件执行此操作apache poi已经创建了。

所以，自从apache poi创建文件Excel (Microsoft Office）可以直接打开，使用与Excel (Microsoft Office） 会做。