处理大型xlsx文件

Question

处理大型xlsx文件

40

我需要自动调整大型（30k+行）xlsx文件中的所有行高。

以下代码通过apache poi在小文件上可以运行，但在大文件上会出现OutOfMemoryError错误：

Workbook workbook = WorkbookFactory.create(inputStream);
Sheet sheet = workbook.getSheetAt(0);

for (Row row : sheet) {
    row.setHeight((short) -1);
}

workbook.write(outputStream);

更新：不幸的是，增加堆大小不是一个选项 - 在 -Xmx1024m 处出现了 OutOfMemoryError 错误，而 30k 行并不是上限。

- miah

你在哪里运行这段代码？是在应用/网络服务器内部还是独立运行？ - JSS

我正在Tomcat 6.0中运行它。 - miah

Tomcat在启动时默认分配多少内存？ - JSS

9个回答

13

使用文件代替流可以显著提升内存使用效率。（虽然最好使用流式API，但是流式API也有限制，详见http://poi.apache.org/spreadsheet/index.html）

因此，不要再使用

Workbook workbook = WorkbookFactory.create(inputStream);

做

Workbook workbook = WorkbookFactory.create(new File("yourfile.xlsx"));

根据：http://poi.apache.org/spreadsheet/quick-guide.html#FileInputStream

文件 vs 输入流

打开工作簿（.xls HSSFWorkbook 或 .xlsx XSSFWorkbook）时，可以从文件或输入流中加载工作簿。使用文件对象可降低内存消耗，而输入流需要更多的内存，因为它必须缓冲整个文件。

- rjdkolb

这给了我一个错误，指出：

Caught: java.lang.LinkageError: loader constraint violation: when resolving interface method "org.xml.sax.XMLReader.setEntityResolver(Lorg/xml/sax/EntityResolver;)V" the class loader (instance of org/codehaus/groovy/tools/RootLoader) of the current class, org/dom4j/io/SAXReader, and the class loader (instance of <bootloader>) for the method's defining class, org/xml/sax/XMLReader, have different Class objects for the type org/xml/sax/EntityResolver used in the signature

。我正在使用 poi-3.9。 - kiltek

1

@rjdkolb，您能看到我的帖子吗？https://stackoverflow.com/questions/48772021/how-to-solve-the-java-heap-space-error-while-loading-the-large-xls-file-using-po - Mandrek

当使用超过15MB的文件时，性能并没有得到改善。我已经设置了-Xmx2048m，但仍然会抛出内存不足的错误。 - saran3h

3

我遇到了类似的问题，只是有很少的行但非常长的字符串。

由于我不需要一直保持我的数据加载，我发现可以使用SXSSF而不是XSSF。它们具有相似的接口，这有助于如果您已经编写了大量的代码。但是，使用SXSSF可以设置您要保持加载的行数。

以下是链接。 http://poi.apache.org/spreadsheet/how-to.html#sxssf

- DomLavoie

2

如果您想要自适应或设置样式或在大型（30k+行）xlsx文件中编写所有行，请使用SXSSFWorkbook。以下是帮助您的示例代码...

SXSSFWorkbook wb = new SXSSFWorkbook();
            SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("writetoexcel");
            Font font = wb.createFont();
                font.setBoldweight((short) 700);
                // Create Styles for sheet.
                XSSFCellStyle Style = (XSSFCellStyle) wb.createCellStyle();
                Style.setFillForegroundColor(new XSSFColor(java.awt.Color.LIGHT_GRAY));
                Style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                Style.setFont(font);
                //iterating r number of rows
            for (int r=0;r < 30000; r++ )
            {
                Row row = sheet.createRow(r);
                //iterating c number of columns
                for (int c=0;c < 75; c++ )
                {
                    Cell cell = row.createCell(c);
                    cell.setCellValue("Hello"); 
                    cell.setCellStyle(Style);
                }
    }
            FileOutputStream fileOut = new FileOutputStream("E:" + File.separator + "NewTest.xlsx");

- Santosh Gdr

1

我在处理一个HSSF文件（.xls）时使用了Event API，但是我发现关于记录顺序的文档非常缺乏。

- ArturoTena

我知道这是旧的，但你有找到关于HSSF和/或XSSF事件顺序的任何信息吗？ - cripox

0

最好的例子可以在以下Stack Overflow主题中找到：通过Apache POI读取大型Excel文件（xlsx）时出错

该主题中主要答案中的代码片段说明了围绕SAX xml解析的Apache POI包装，以及如何轻松地循环遍历所有工作表，然后遍历每个单元格。

该代码已经过时，因为endRow() API提供了已完成处理的当前行号。

使用该代码片段，您应该可以轻松地逐个单元格解析大型XLSX文件。例如，对于每个工作表；对于每个行单元格；行已结束事件。您可以轻松创建应用程序逻辑，在每行末尾创建一个列名称到单元格值的映射。

- 99Sono

0

我使用SAX解析器来处理XML结构。它适用于XLSX文件。

https://dev59.com/JnfZa4cB1Zd3GeqPT6C0#44969009

- Yan Khonski

0

如果你是在写XLSX，我发现将数据分别写入同一Excel文件的不同工作表可以提高写入效率。你也可以尝试将数据分别写入不同的Excel文件来提升效率。但最好先尝试将数据写入不同的工作表。

- Alexander Mills

0

我曾经遇到过同样的问题，有800,000个单元格和3M个字符，其中XSSF分配了1GB的堆空间！

我使用Python与openpyxl和numpy读取xlsx文件（从Java代码中），并首先将其转换为普通文本。然后我在Java中加载了文本文件。这可能看起来有很大的开销，但实际上速度非常快。

Python脚本如下：

import openpyxl as px
import numpy as np

# xlsx file is given through command line foo.xlsx
fname = sys.argv[1]
W = px.load_workbook(fname, read_only = True)
p = W.get_sheet_by_name(name = 'Sheet1')

a=[]
# number of rows and columns
m = p.max_row
n = p.max_column

for row in p.iter_rows():
    for k in row:
        a.append(k.value)

# convert list a to matrix (for example maxRows*maxColumns)
aa= np.resize(a, [m, n])

# output file is also given in the command line foo.txt
oname = sys.argv[2]
print (oname)
file = open(oname,"w")
mm = m-1
for i in range(mm):
    for j in range(n):
        file.write( "%s " %aa[i,j]  )
    file.write ("\n")

# to prevent extra newline in the text file
for j in range(n):
    file.write("%s " %aa[m-1,j])

file.close()

然后在我的Java代码中，我写了：

try {
  // `pwd`\python_script  foo.xlsx  foo.txt
  String pythonScript =  System.getProperty("user.dir") + "\\exread.py ";
  String cmdline = "python " + pythonScript +
                    workingDirectoryPath + "\\" + fullFileName + " " + 
                    workingDirectoryPath + "\\" + shortFileName + ".txt";
  Process p = Runtime.getRuntime().exec(cmdline);
  int exitCode = p.waitFor();
  if (exitCode != 0) {
    throw new IOException("Python command exited with " + exitCode);
  }
} catch (IOException e) {
  System.out.println( e.getMessage() );
} catch (InterruptedException e) {
  ReadInfo.append(e.getMessage() );
}

之后，您将获得一个名为foo.txt的文件，它与foo.xlsx类似，但是以文本格式呈现。

- mahmood

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

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

- markusk · Accepted Answer

尝试使用事件API。有关详细信息，请参见POI文档中的事件API（仅限HSSF）和XSSF和SAX（事件API）。该页面摘录了一些引用：

HSSF：

事件API比用户API更新。它适用于愿意学习一些低级别API结构的中级开发人员。它相对简单易用，但需要基本了解Excel文件的各个部分（或愿意学习）。提供的优势是可以使用相对较小的内存占用读取XLS。

XSSF：

如果内存占用是一个问题，则对于XSSF，您可以访问底层XML数据并自行处理。这适用于愿意学习.xlsx文件的低级结构并且愿意在Java中处理XML的中级开发人员。它相对简单易用，但需要基本了解文件结构。提供的优势是可以使用相对较小的内存占用读取XLSX文件。

对于输出，一种可能的方法在博客文章流式处理xlsx文件中进行了描述。（基本上，使用XSSF生成容器XML文件，然后将实际内容作为纯文本流式传输到xlsx zip归档文件的适当xml部分。）