java.lang.OutOfMemoryError: Java heap space - Can not fix it

Question

I have this code and this error constantly is appearing. I have only one excel , but nothing seems to work, I already tried a lot of option that I found surfing on internet, but nothing seems to work according of what I want to do.

I use different case to make easier the logical of my business and I am not going to change that, so I am not sure how to do solve this issue.

private static final String nombreArchivo = "casoPrueba.xlsx";
private static final String rutaArchivo = "src\\test\\resources\\data\\" + nombreArchivo;




 public static XSSFSheet SacaHojaSegunTipo(String tipo) throws IOException {
        if (workbook == null) {
            try (FileInputStream fis = new FileInputStream(new File(rutaArchivo))) {
                workbook = new XSSFWorkbook(fis);
            }
        }
        XSSFSheet spreadsheet = null;
        switch (tipo) {
            case "Candidatos Minorista":
                spreadsheet = workbook.getSheetAt(1);
                break;
            case "Conversion Candidatos":
                spreadsheet = workbook.getSheetAt(2);
                break;
            case "Cuentas":
                spreadsheet = workbook.getSheetAt(3);
                break;
            case "Detalle Cuenta":
                spreadsheet = workbook.getSheetAt(4);
                break;
            case "Historial de Cuentas":
                spreadsheet = workbook.getSheetAt(5);
                break;
            case "Cuentas Financieras":
                spreadsheet = workbook.getSheetAt(6);
                break;
            case "AR Estado Automático":
                spreadsheet = workbook.getSheetAt(7);
                break;
            case "Oportunidades":
                spreadsheet = workbook.getSheetAt(8);
                break;
            default:
                spreadsheet = workbook.getSheetAt(0);
                break;
        }
        return spreadsheet;
    }

I know this is not a efficient method.Hope anyone can help me with this.

Answer 1

Something like this (I tried to change your code as little as possible, so it's not perfect)

private static final String nombreArchivo = "casoPrueba.xlsx";
private static final String rutaArchivo = "src\\test\\resources\\data\\" + nombreArchivo;

private static XSSFWorkbook workbook = null;

public static XSSFSheet SacaHojaSegunTipo(String tipo) throws IOException {
    if (workbook == null) {
        try (FileInputStream fis = new FileInputStream(new File(rutaArchivo))) {
            workbook = new XSSFWorkbook(fis);
        }
    }
    XSSFSheet spreadsheet = null;
    switch (tipo) {
    case "Candidatos Minorista":
        spreadsheet  = workbook.getSheetAt(1);          
        break;
    case "Conversion Candidatos":
        spreadsheet  = workbook.getSheetAt(2);          
        break;
    case "Cuentas":
        spreadsheet  = workbook.getSheetAt(3);          
        break;
    case "Detalle Cuenta":
        spreadsheet  = workbook.getSheetAt(4);          
        break;
    case "Historial de Cuentas":
        spreadsheet  = workbook.getSheetAt(5);          
        break;
    case "Navegar Cuentas":
        spreadsheet  = workbook.getSheetAt(6);          
        break;
    case "Validar Número Operación":
        spreadsheet  = workbook.getSheetAt(7);          
        break;
    case "Validar Tipos de Productos":
        spreadsheet  = workbook.getSheetAt(8);          
        break;
    case "Validar Referencia y Cód. Auto.":
        spreadsheet  = workbook.getSheetAt(9);          
        break;
    default:
        spreadsheet = workbook.getSheetAt(0);
    }
    return spreadsheet;
}

Answer 2

First, a quick aside : it's worth noting the following from
https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/usermodel/XSSFWorkbook.html#XSSFWorkbook-java.io.InputStream-

Using an InputStream requires more memory than using a File, so if a File is available then you should instead do something like

   OPCPackage pkg = OPCPackage.open(path);
   XSSFWorkbook wb = new XSSFWorkbook(pkg);
   // work with the wb object
   ......
   pkg.close(); // gracefully closes the underlying zip file

(although doing wb.close() also closes the files and streams).

Now, your core issue is that you need to release resources after the sheet or workbook are no longer required, but at present you cannot do so since these are hidden local inside the method.

So you need to give your caller access to close them when it's done. It's a matter of preference, but personally I would prefer encapsulating the spreadsheet into it's own class - a spreadsheet IS a clearly defined object in its own right, after all ! As such, this would necessitate a change from static, so something like :

public class RutaArchivo implements AutoCloseable {
    private static final String nombreArchivo = "casoPrueba.xlsx";
    private static final String rutaArchivo = "src\\test\\resources\\data\\" + nombreArchivo;

    public static final String CANDIDATOS_MINORISTA = "Candidatos Minorista";
    public static final String CONVERSION_CANDIDATOS = "Conversion Candidatos"
    public static final String CUENTAS = "Cuentas";

    private XSSFWorkbook workbook;

    public RutaArchivo() throws InvalidFormatException, IOException {
        workbook = new XSSFWorkbook(new File(rutaArchivo));
    }

    @Override
    public void close() throws Exception {
        if (workbook != null) {
            workbook.close();
            workbook = null;
        }
    }

    public XSSFSheet sacaHojaSegunTipo(String tipo) {
         if (workbook == null) {
             throw new IllegalStateException("It's closed");
         }
         XSSFSheet spreadsheet = workbook.getSheetAt(0);
         if (tipo .equals(CANDIDATOS_MINORISTA)) {
             spreadsheet  = workbook.getSheetAt(1);
         }else if(tipo.equals(CONVERSION_CANDIDATOS)){
             spreadsheet  = workbook.getSheetAt(2);
         }else if(tipo.equals(CUENTAS)){
              spreadsheet  = workbook.getSheetAt(3);
         // etc, etc
         }

         return spreadsheet;
    }
}

A couple of things to note :

• If we want the caller to close the file, then we should explictly make them take some action to open it as well, otherwise it's too easy for it to be left hanging. In the example above, this is implicit in creating the object - just like the standard Java types like FileInputStream, etc.

• Making RutaArchivo AutoCloseable means that it can used in try-with-resources, so closed automatically - eg :

  try (RutaArchivo rutaArchivo = new RutaArchivo()) {
      XSSFSheet cuentas = rutaArchivo.getSheet(RutaArchivo.CUENTAS);
  }
  

• Using constants for the names of the sheets reduces bugs (eg, no typos when the method is called)

• As this is it's own class rather than static methods, it's easier to substitute or mock when writing unit tests.

Anyhow, a few thoughts - hope they help.