Java Memory issue using Apache.POI to read write E

Question

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.

Answer 1

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.

Answer 2

In the new version of poi they have used Java streams to solve the memory issue.Have a look