java - SXSSFWorkbook.write to FileOutputStream 写入大文件

Question

我正在尝试使用 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] 。 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(？!)。

Answer 1

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

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

示例Java代码:

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 和共享字符串表时。

使用:Java 12、apache poi 4.1.2、Ubuntu Linux。

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

并且条目压缩得非常好。

如果您查看 Excel.xlsx ZIP 存档，您会发现使用内联字符串时 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 个条目。

如果 Excel 本身保存 *.xlsx 文件，则当内容相等时，它会创建文件大小大致相同的文件。因此，Excel 本身使用相同的压缩级别。

当然，在将Excel.xlsx再次存储到*.zip存档时，可以进一步压缩*.xlsx文件。但这并不是 Excel 期望的 *.xlsx 文件。

Microsoft 声明为 What are the benefits of Open XML Formats? :

Compact files Files are automatically compressed and can be up to 75 percent smaller in some cases. The Open XML Format uses zip compression technology to store documents, offering potential cost savings as it reduces the disk space required to store files and decreases the bandwidth needed to send files via e-mail, over networks, and across the Internet. When you open a file, it is automatically unzipped. When you save a file, it is automatically zipped again. You do not have to install any special zip utilities to open and close files in Office.

这里重要的部分是:

When you open a file, it is automatically unzipped. When you save a file, it is automatically zipped again.

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

因此，由于 apache poi 创建了 Excel (Microsoft Office) 能够直接打开的文件，因此它使用相同的压缩方法和压缩方式级别为 Excel (Microsoft Office) 即可。