CODEWITHSUNDEEP
javaapache-poi
Sanatbek Matlatipov
Sanatbek Matlatipov

Reputation: 171

JAVA OutOfMemoryError issue With Excel Exporting 150K+ data in the Apache POI Stream API

My Apache POI version is 3.14. I am trying to export large data to excel? To do that I found this solution on internet and implemented it.

So, I have following List where it came from my Database(PostgresSQL):

List<TransactionDTO> result = new ArrayList<>(); result.addAll(transactionService.findByInDateRange(status, fromDate, toDate));

Then I followed the link which I mentioned above (logic exactly the same).

   DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd_hh_mm_ss");
    String excelFileName = "Statistics_by_partner" + formatter.format(LocalDateTime.now()) + ".xlsx";
    SXSSFWorkbook wb = (new ExportRevisionResponseExcel()).exportExcel(new String[] { "Status",
        "Request",}, result);
    try {
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);
        byte[] outArray = outByteStream.toByteArray();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setContentLength(outArray.length);
        response.setHeader("Expires:", "0"); // eliminates browser caching
        response.setHeader("Content-Disposition", "attachment; filename=" + excelFileName);
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();
        wb.dispose();
        wb.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }`

However, It is still giving me Memory error.

By the way, I don't think increasing JAVA Heap memory is good idea.

Upvotes: 2

Views: 870

Answers (1)

Stephen C
Stephen C

Reputation: 719386

The most likely reason that you are running out of heap space is that you are streaming the entire workbook stream into a byte array buffer in memory.

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    wb.write(outByteStream);

This can consume up to three times1 the actual stream length in bytes. If you want to use less memory, write the data straight to the response stream:

    OutputStream outStream = response.getOutputStream();
    wb.write(outStream);

Obviously, that means that you won't be able to set a content length in the response. If that is really important to you, then use a temporary file to buffer the data rather than a ByteArrayOutputStream.

1 - BAOS uses an internal byte[] as the buffer. When the buffer is full, it allocates a new one of twice the size, and copies data from the old one to the new one. When you are doing the copying, you have 2 byte arrays, occupying 3 times the number of bytes that have been buffered so far.

Upvotes: 4

Related Questions