java - 使用 apache POI 将 xlsx 文件分页到 XSSFworkbook

Question

现在在我的代码中，我正在将 xlsx 文件读取到 XSSFWorkbook 中，然后最终将其写入数据库中。但是，当 xlsx 文件的大小增加时，会导致内存不足错误。我无法增加服务器大小，或将 xlsx 文件分成几部分。我尝试使用文件(而不是输入流)加载工作簿，但这也没有帮助。

我正在寻找一种方法来一次读取 10k 行(而不是一次读取整个文件)并迭代写入工作簿，然后写入数据库。

有没有一个好的方法可以使用 Apache POI 来做到这一点？

Answer 1

POI 包含一个称为“事件模型”的东西，它正是为此目的而设计的。提到了in the FAQ :

The SS eventmodel package is an API for reading Excel files without loading the whole spreadsheet into memory. It does require more knowledge on the part of the user, but reduces memory consumption by more than tenfold. It is based on the AWT event model in combination with SAX. If you need read-only access, this is the best way to do it.

但是，您可能需要首先仔细检查问题是否出在其他地方。查看this item :

14. I think POI is using too much memory! What can I do? This one comes up quite a lot, but often the reason isn't what you might initially think. So, the first thing to check is - what's the source of the problem? Your file? Your code? Your environment? Or Apache POI?

(If you're here, you probably think it's Apache POI. However, it often isn't! A moderate laptop, with a decent but not excessive heap size, from a standing start, can normally read or write a file with 100 columns and 100,000 rows in under a couple of seconds, including the time to start the JVM). Apache POI ships with a few programs and a few example programs, which can be used to do some basic performance checks. For testing file generation, the class to use is in the examples package, SSPerformanceTest. Run SSPerformanceTest with arguments of the writing type (HSSF, XSSF or SXSSF), the number rows, the number of columns, and if the file should be saved. If you can't run that with 50,000 rows and 50 columns in HSSF and SXSSF in under 3 seconds, and XSSF in under 10 seconds (and ideally all 3 in less than that!), then the problem is with your environment. Next, use the example program ToCSV to try reading the a file in with HSSF or XSSF. Related is XLSX2CSV, which uses SAX parsing for .xlsx. Run this against both your problem file, and a simple one generated by SSPerformanceTest of the same size. If this is slow, then there could be an Apache POI problem with how the file is being processed (POI makes some assumptions that might not always be right on all files). If these tests are fast, then any performance problems are in your code!