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

问题描述

我正在开发一个从excel文件（xlsx）读取数据的Web应用程序。我正在使用POI阅读excel表。问题是当我尝试阅读excel文件时，服务器会引发以下错误：

我正在尝试阅读的excel文件的大小几乎为80 MB。任何解决这个问题的解决方案？

实际上，用户将文件保存到磁盘后尝试读取文件。
我用于测试的代码片段是：

 文件savedFile = new File（file_path）; 
 
 FileInputStream fis = null; 
 try {
 
 fis = new FileInputStream（savedFile）; 
 XSSFWorkbook xWorkbook = new XSSFWorkbook（fis）; 
 XSSFSheet xSheet = xWorkbook.getSheetAt（5）; 
 
迭代器行= xSheet.rowIterator（）; 
 while（rows.hasNext（））{
 XSSFRow row =（XSSFRow）rows.next（）; 
迭代器单元格= row.cellIterator（）; 
 
列表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获取一条记录时，我立即将其插入到DB中，而不是将其存储到arraylist中。我知道这一个插入不是一个好办法，但是暂时我正在使用这种方法。将来如果我找到更好的一个，我肯定会转向那个。感谢所有。

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:

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();
            } 

解决方案

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屋！