out of heap memory, java

Question

i am new to java. I am just trying to understand how to deal with heap memory overflow and its causes. Can somebody please help me in below code why it is throwing this error. and how could i have avoided it.

error:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space at java.util.Arrays.copyOf(Arrays.java:2361) at java.lang.AbstractStringBuilder.expandCapacity(AbstractStringBuilder.java:117) at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:406) at java.lang.StringBuffer.append(StringBuffer.java:237) at com.ugam.qa.tittle.XlsxToCsv.xlsx(XlsxToCsv.java:49) at com.ugam.qa.tittle.XlsxToCsv.main(XlsxToCsv.java:77)

package com.ugam.qa.tittle;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XlsxToCsv {

    static void xlsx(File inputFile, File outputFile) {
        // For storing data into CSV files
        StringBuffer data = new StringBuffer();

        try {
            FileOutputStream fos = new FileOutputStream(outputFile);
            // Get the workbook object for XLSX file
            XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
            // Get first sheet from the workbook
            XSSFSheet sheet = wBook.getSheetAt(0);
            Row row;
            Cell cell;
            // Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();

            while (rowIterator.hasNext()) {
                row = rowIterator.next();
                {
                // For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    cell = cellIterator.next();

                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");

                            break;
                        case Cell.CELL_TYPE_STRING:
                            data.append(cell.getStringCellValue() + ",");
                            break;

                        case Cell.CELL_TYPE_BLANK:
                            data.append("" + ",");
                            break;
                        default:
                            data.append(cell + ",");

                    }
                    data.append("\r\n");
                }}
            }

            fos.write(data.toString().getBytes());
            fos.close();

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }
    //testing the application 

    public static void main(String[] args) {
        //reading file from desktop
        File inputFile = new File("D:\\files\\listing\\test.xlsx");
        //writing excel data to csv 
        File outputFile = new File("D:\\files\\listing\\test1.csv");
        xlsx(inputFile, outputFile);

    }
}

Answer 1

Increasing heap is one solution: java -Xmx<MegaBytes>M <YourClass>

A better solution is to use less memory, which is easy in your case: why are you storing the whole output in a StringBuffer before dumping it in your stream? It would be much more memory efficient to write each part directly to the stream as you find them.

Another improvement to your code is to work with a FileWriter rather than a FileOutputStream: it lets you control the output encoding, and accepts strings directly without requiring you to call String#getBytes().

Answer 2

First (though unrelated with memory consumption), you are creating a text file, so use a FileWriter rather than a FileOutputStream.

FileWriter writer = new FileWriter(outputFile);

Second, you are constructing a very long string entirely in memory. Obviously this consumes a lot of memory. It's better to structure the program so that the output file is generated as you read the input file, without accumulating everything in memory. For example, replace:

data.append(cell.getBooleanCellValue() + ",");

with

writer.write(cell.getBooleanCellValue() + ",");

Answer 3

just run your application with

java -Xmx<whatever value in megs/gigs> yourApp

Answer 4

I too faced the same issue of OOM while parsing xlsx file...after two days of struggle, I finally found out the below code that was really perfect;

This code is based on sjxlsx. It reads the xlsx and stores in a HSSF sheet.

            // read the xlsx file
       SimpleXLSXWorkbook = new SimpleXLSXWorkbook(new File("C:/test.xlsx"));

        HSSFWorkbook hsfWorkbook = new HSSFWorkbook();

        org.apache.poi.ss.usermodel.Sheet hsfSheet = hsfWorkbook.createSheet();

        Sheet sheetToRead = workbook.getSheet(0, false);

        SheetRowReader reader = sheetToRead.newReader();
        Cell[] row;
        int rowPos = 0;
        while ((row = reader.readRow()) != null) {
            org.apache.poi.ss.usermodel.Row hfsRow = hsfSheet.createRow(rowPos);
            int cellPos = 0;
            for (Cell cell : row) {
                if(cell != null){
                    org.apache.poi.ss.usermodel.Cell hfsCell = hfsRow.createCell(cellPos);
                    hfsCell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
                    hfsCell.setCellValue(cell.getValue());
                }
                cellPos++;
            }
            rowPos++;
        }
        return hsfSheet;