使用Apache.POI读写Excel的Java内存问题 [英] Java Memory issue using Apache.POI to read write Excel

查看：59
本文介绍了使用Apache.POI读写Excel的Java内存问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试读取 excel 文件...进行一些更改...保存到新文件.

我用按钮创建了小表单...按下按钮时..

  • 它将加载 Excel 文件并将所有数据加载到我创建的类的数组列表中.
  • 它将遍历数组列表并更改对象中的一些属性.
  • 它将数据保存到新的 Excel 文件中.
  • 最后，它会清除数组列表并显示完成的消息框.

现在的问题是内存问题.
加载表单时，我可以在 Windows 任务管理器中看到...javaw 使用了大约 23MB.
在读取和写入 excel...期间内存高达 170MB.
阵列列表清除后....内存未清除并保持在 150MB 左右.

以下代码附加到按钮点击事件.

MouseListener mouseListener = new MouseAdapter() {public void mouseReleased(MouseEvent mouseEvent) {如果(SwingUtilities.isLeftMouseButton(mouseEvent)){ArrayList<地址>地址 = ExcelFunctions.getExcelData(fn);对于(地址地址:地址){address.setZestimate(Integer.toString(rnd.nextInt(45000)));address.setRedfinestimate(Integer.toString(rnd.nextInt(45000)));}ExcelFunctions.saveToExcel(ofn,addresses);地址.清除()；JOptionPane.showMessageDialog(null, "Done");}}};


该类中读取/Excel文件的代码.

公共类 ExcelFunctions {公共静态 ArrayList<地址>getExcelData(String fn){ArrayList<地址>输出 = 新的 ArrayList<地址>();尝试{FileInputStream file = new FileInputStream(new File(fn));//创建包含对 .xlsx 文件的引用的 Workbook 实例XSSFWorkbook 工作簿 = 新 XSSFWorkbook(file);//从工作簿中获取第一个/所需的工作表XSSFSheet sheet = workbook.getSheetAt(0);System.out.println(sheet.getSheetName());//一一遍历每一行迭代器<行>rowIterator = sheet.iterator();而 (rowIterator.hasNext()){行行 = rowIterator.next();int r = row.getRowNum();int fc= row.getFirstCellNum();int lc = row.getLastCellNum();String msg = "Row:"+ r +"FColumn:"+ fc + "LColumn"+lc;System.out.println(msg);如果(row.getRowNum()> 0){地址添加 = 新地址();单元格 c0 = row.getCell(0);单元格 c1 = row.getCell(1);单元格 c2 = row.getCell(2);单元格 c3 = row.getCell(3);单元格 c4 = row.getCell(4);单元格 c5 = row.getCell(5);if (c0 != null){c0.setCellType(Cell.CELL_TYPE_STRING);add.setState(c0.toString());}if (c1 != null){c1.setCellType(Cell.CELL_TYPE_STRING);add.setCity(c1.toString());}if (c2 != null){c2.setCellType(Cell.CELL_TYPE_STRING);add.setZipcode(c2.toString());}if (c3 != null){c3.setCellType(Cell.CELL_TYPE_STRING);add.setAddress(c3.getStringCellValue());}if (c4 != null){c4.setCellType(Cell.CELL_TYPE_STRING);add.setZestimate(c4.getStringCellValue());}if (c5 != null){c5.setCellType(Cell.CELL_TYPE_STRING);add.setRedfinestimate(c5.getStringCellValue());}输出.添加(添加)；c0=null;c1=null;c2=null;c3=null;c4=null;c5=null;}}workbook.close();文件.关闭()；}捕获(例外 e){System.out.println(e.getMessage());}返回输出；}public static void saveToExcel(String ofn, ArrayList
 地址) {XSSFWorkbook 工作簿 = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Addresses");行标题 = sheet.createRow(0);header.createCell(0).setCellValue("State");header.createCell(1).setCellValue("City");header.createCell(2).setCellValue("Zip");header.createCell(3).setCellValue("地址");header.createCell(4).setCellValue("Zestimates");header.createCell(5).setCellValue("Redfin Estimate");整数行 = 1;对于(地址地址:地址){行数据行 = sheet.createRow(row);dataRow.createCell(0).setCellValue(address.getState());dataRow.createCell(1).setCellValue(address.getCity());dataRow.createCell(2).setCellValue(address.getZipcode());dataRow.createCell(3).setCellValue(address.getAddress());dataRow.createCell(4).setCellValue(address.getZestimate());dataRow.createCell(5).setCellValue(address.getRedfinestimate());行++；}尝试 {FileOutputStream out = new FileOutputStream(new File(ofn));workbook.write(out);关闭()；workbook.close();System.out.println("带有公式单元格的Excel写入成功");} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}}


我无法弄清楚问题出在哪里.我正在关闭工作簿/输入流/输出流并清除 Arraylist.

解决方案

您可能没有内存泄漏...

<块引用>

加载表单时，我可以在 windows 任务管理器中看到...javaw 是使用大约 23MB.在读写excel过程中...内存猛增170MB.阵列列表被清除后....内存没有清理和保持在 150MB 左右.

这不是描述内存泄漏 - 任务管理器向您显示进程保留的内存 - 而不是应用程序

图片来源:https://stopcoding.files.wordpress.com/2010/04/visualvm_hfcd4.png

在此示例中，JVM 的最大堆为 1 GiB，并且由于应用程序需要更多内存，因此从操作系统(橙色区域)预留了 400 MiB.

蓝色区域是应用程序实际使用的堆内存.锯齿效应是垃圾收集过程回收未使用内存的结果.请注意，橙色区域保持相当静态 - 它通常不会随着每个 GC 事件调整大小...

<块引用>

在几秒钟内...它最多可以达到 800MB 并一直保持到最后....我没有任何内存错误

如果发生内存泄漏，最终会出现内存不足错误.泄漏"(至少在 Java 中)是指应用程序占用堆中的内存，但不释放它以供应用程序重用.如果您观察到的内存快速增加，但应用程序没有崩溃，您可能会看到内部(在 JVM 中)内存实际上正在被释放和重用.

限制 Java 可以使用的(操作系统)内存

如果你想限制你的应用程序可以从操作系统保留的内存，你需要配置你的最大堆大小(通过 -Xmx 选项)以及你的永久代大小(如果你'仍在使用 Java 7 或更早版本).请注意，JVM 本身会使用一些内存，因此在操作系统级别(使用任务管理器等工具)显示的值可能高于您指定的应用程序内存总和.

I am trying to read excel file...make some changes...save to new file.

I have created small form with button...On pressing button..

  • It will load Excel file and load all data to Array list of class I have created.
  • It will loop through Array list and change few properties in objects.
  • It will save data to new Excel file.
  • Finally, it will clear Array list and show message box of completion.

Now the problem is memory issue.
When form is loaded, I can see in windows task manager...javaw is using around 23MB.
During read and write excel...memory shoots upto 170MB.
After array list is cleared....Memory is not clearing up and stays around 150MB.

Following code is attached to Event to button click.

MouseListener mouseListener = new MouseAdapter() {
        public void mouseReleased(MouseEvent mouseEvent) {
            if (SwingUtilities.isLeftMouseButton(mouseEvent)) {
                ArrayList<Address> addresses = ExcelFunctions.getExcelData(fn);
                for (Address address : addresses){
                    address.setZestimate(Integer.toString(rnd.nextInt(45000)));
                    address.setRedfinestimate(Integer.toString(rnd.nextInt(45000)));
                }
                ExcelFunctions.saveToExcel(ofn,addresses);
                addresses.clear();
                JOptionPane.showMessageDialog(null, "Done");
            }
        }
    };


The code for Reading/Excel file in this Class.

public class ExcelFunctions {
public static ArrayList<Address> getExcelData(String fn)
{
    ArrayList<Address> output = new ArrayList<Address>();
    try
    {
        FileInputStream file = new FileInputStream(new File(fn));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);
        System.out.println(sheet.getSheetName());
        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            int r = row.getRowNum();
            int fc= row.getFirstCellNum();
            int lc = row.getLastCellNum();
            String msg = "Row:"+ r +"FColumn:"+ fc + "LColumn"+lc;
            System.out.println(msg);
            if (row.getRowNum() > 0) {
                Address add = new Address();
                Cell c0 = row.getCell(0);
                Cell c1 = row.getCell(1);
                Cell c2 = row.getCell(2);
                Cell c3 = row.getCell(3);
                Cell c4 = row.getCell(4);
                Cell c5 = row.getCell(5);
                if (c0 != null){c0.setCellType(Cell.CELL_TYPE_STRING);add.setState(c0.toString());}
                if (c1 != null){c1.setCellType(Cell.CELL_TYPE_STRING);add.setCity(c1.toString());}
                if (c2 != null){c2.setCellType(Cell.CELL_TYPE_STRING);add.setZipcode(c2.toString());}
                if (c3 != null){c3.setCellType(Cell.CELL_TYPE_STRING);add.setAddress(c3.getStringCellValue());}
                if (c4 != null){c4.setCellType(Cell.CELL_TYPE_STRING);add.setZestimate(c4.getStringCellValue());}
                if (c5 != null){c5.setCellType(Cell.CELL_TYPE_STRING);add.setRedfinestimate(c5.getStringCellValue());}
                output.add(add);
                c0=null;c1=null;c2=null;c3=null;c4=null;c5=null;
            }
        }
        workbook.close();
        file.close();
    }
    catch (Exception e)
    {
        System.out.println(e.getMessage());
    }
    return output;
}

public static void saveToExcel(String ofn, ArrayList<Address> addresses) {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Addresses");

    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("State");
    header.createCell(1).setCellValue("City");
    header.createCell(2).setCellValue("Zip");
    header.createCell(3).setCellValue("Address");
    header.createCell(4).setCellValue("Zestimates");
    header.createCell(5).setCellValue("Redfin Estimate");

    int row = 1;

    for (Address address : addresses){
        Row dataRow = sheet.createRow(row);
        dataRow.createCell(0).setCellValue(address.getState());
        dataRow.createCell(1).setCellValue(address.getCity());
        dataRow.createCell(2).setCellValue(address.getZipcode());
        dataRow.createCell(3).setCellValue(address.getAddress());
        dataRow.createCell(4).setCellValue(address.getZestimate());
        dataRow.createCell(5).setCellValue(address.getRedfinestimate());
        row++;
    }


    try {
        FileOutputStream out =  new FileOutputStream(new File(ofn));
        workbook.write(out);
        out.close();
        workbook.close();
        System.out.println("Excel with foumula cells written successfully");

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


I am unable to figure out where the issue is. I m closing workbook/inputstream/outputstream and clearing Arraylist too.

解决方案

You probably don't have a memory leak...

When form is loaded, I can see in windows task manager...javaw is using around 23MB. During read and write excel...memory shoots upto 170MB. After array list is cleared....Memory is not clearing up and stays around 150MB.

This doesn't describe a memory leak - Task Manager is showing you the memory reserved by the process - not the application heap space.

Your JVM will allocate heap up to its configured maximum, say 200 MiB. Generally, after this memory is allocated from the OS, the JVM doesn't give it back (very often). However, if you look at your heap usage (with a tool like JConsole or JVisual VM) you'll see that the heap is reclaimed after a GC.

How Java consumes memory

As a very basic example:

Image source: https://stopcoding.files.wordpress.com/2010/04/visualvm_hfcd4.png

In this example, the JVM has a 1 GiB max heap, and as the application needed more memory, 400 MiB was reserved from the OS (the orange area).

The blue area is the actual heap memory used by the application. The saw-tooth effect is the result of the garbage collection process reclaiming unused memory. Note that the orange area remains fairly static - it generally won't resize with each GC event...

within few seconds...it shoot upto 800MB and stays there till end....I have not got any memory error

If you had a memory leak, you'd eventually get an out of memory error. A "leak" (in Java at least) is when the application ties up memory in the heap, but doesn't release it for reuse by the application. If your observed memory shoots up that quickly, but the application doesn't fall over, you'll probably see that internally (in the JVM) memory is actually being released and reused.

Limiting how much (OS) memory Java can use

If you want to limit the memory your application can reserve from the OS, you need to configure your maximum heap size (via the -Xmx option) as well as your permanent generation size (if you're still using Java 7 or earlier). Note that the JVM uses some memory itself, so the value shown at OS level (using tools like Task Manager) can be higher than the sum of application memory you have specified.

这篇关于使用Apache.POI读写Excel的Java内存问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆