使用 POI 读取 excel 文件 (xlsx) 时出现 java.lang.outofmemory 异常 [英] java.lang.outofmemory exception while reading excel file (xlsx) using POI

问题描述

我正在开发一个从 excel 文件 (xlsx) 读取数据的 Web 应用程序.我正在使用 POI 来阅读 Excel 表格.问题是当我尝试读取excel文件时，服务器抛出以下错误:

I am developing a web application which reads data from excel file (xlsx). I am using POI for reading excel sheet. The problem is when I try to read excel file, the server throws the following error:

我试图读取的 excel 文件的大小接近 80 MB.这个问题有什么解决办法吗?

The excel file I am trying to read has size of almost 80 MB. Any solution to this problem?

实际上用户在将文件保存到磁盘后正在上传文件和应用程序尝试读取文件.我用于测试的代码片段是:

Actually user is uploading file and application after saving file to disk try to read file. The code snippet I am using for testing is:

 File savedFile = new File(file_path);

FileInputStream fis = null;
            try {

                fis = new FileInputStream(savedFile);
                XSSFWorkbook xWorkbook = new XSSFWorkbook(fis);
                XSSFSheet xSheet = xWorkbook.getSheetAt(5);

                Iterator rows = xSheet.rowIterator();
                while (rows.hasNext()) {
                    XSSFRow row = (XSSFRow) rows.next();
                    Iterator cells = row.cellIterator();

                    List data = new ArrayList();
                    while (cells.hasNext()) {
                        XSSFCell cell = (XSSFCell) cells.next();
                        System.out.println(cell.getStringCellValue());
                        data.add(cell);
                    }

                }
            } catch (IOException e) {
                e.printStackTrace();
            } 

推荐答案

我已经通过改变实现解决了这个问题.实际上，首先我从 Excel 文件中获取所有数据，并且数据以 ArrayList 类型存储.之后我将数据插入数据库，这才是真正的问题.现在我根本不存储数据.当我从 ResultSet 获取一条记录时，我立即将其插入到数据库中，而不是将其存储到数组列表中.我知道逐个插入不是一个好方法，但暂时我正在使用这种方法.以后如果我找到更好的，我肯定会转向那个.谢谢大家.

I have solved the problem through change in implementation. Actually firstly I was fetching all data from Excel file and data was being stored in ArrayList type. After that I was inserting data into DB and that was the real problem. Now I am not storing data at all. As I get one record from ResultSet, I insert it into DB immediately instead of storing it into arraylist. I know this one by one insertion is not a good approach but for time being I am using this approach. In future if I find better one, I definitely switch to that one. Thanks to all.

这篇关于使用 POI 读取 excel 文件 (xlsx) 时出现 java.lang.outofmemory 异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！