JAVA: How to write to .xlsx with a low memory footprint

1.4k Views Asked by At

I have an input .xlsx file f1 and I am trying to create another file f2 using f1 after some data modification.

Currently I am trying to use apache-poi streaming api to accomplish this task. The problem is that I am unable to accomplish this task with a low memory footprint. Here is my code snippet

import com.monitorjbl.xlsx.StreamingReader;

public static void streamingWriter()
{
    SXSSFWorkbook workbook = new SXSSFWorkbook(50);
    workbook.setCompressTempFiles(true);
    try (InputStream inputStream = new FileInputStream(new File("inputFile.xlsx"));
         Workbook inputWorkbook = StreamingReader.builder()
                 .rowCacheSize(50)
                 .bufferSize(512)
                 .open(inputStream)) {
        Runtime runtime = Runtime.getRuntime();
        Sheet newSheet;
        Row newRow;
        Cell newCell;
        for (Sheet sheet : inputWorkbook) {
            newSheet = workbook.createSheet();
            for (Row row : sheet) {
                newRow = newSheet.createRow(row.getRowNum());
                for (Cell cell : row) {
                    newCell = newRow.createCell(cell.getColumnIndex());
                    copyCell(cell, newCell, workbook);
                }
            }
        }
        System.out.println("Mem2: " + (runtime.totalMemory() - runtime.freeMemory()));
        String fileName = "outputFile.xlsx";
        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        System.out.println("Mem3: " + (runtime.totalMemory() - runtime.freeMemory()));
        outputStream.flush();
        outputStream.close();
        workbook.dispose();
    } catch (IOException e) {
        System.out.println("error releasing respurces: " + e.getClass().getSimpleName() +
                e.getMessage());
    }

}

Here are the run results -

Mem1: 112MB

Mem2: 464MB

Mem3: 697MB

Size of original "inputFile.xlsx" is 223KB.

As can be seen from the run results, calling workbook.write() is taking a lot of memory, is there a way to write to an excel file without using extra memory.

Main goal is to reduce both Mem2 and Mem3 of run results.

0

There are 0 best solutions below