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.