Java Heap Space Error when creating XLSX with dynamic number of sheets

Hello KNIME Community,

I'm using KNIME Analytics Platform 5.12.0 LTS and need to create XLSX exports from large CSV files (typically 1-2 million rows).

To stay within Excel's row limits, I split the data into Chunk Loops and create a new sheet once a sheet reaches a certain size. Depending on the dataset, the workbook ends up with 5-8 sheets, so the number of sheets is dynamic.

The workflow fails with a Java Heap Space Error during export.

From my observations, the memory issue seems to be caused by the way the workbook is written. When additional sheets are created, KNIME appears to read and keep the previously created sheets in memory rather than writing each sheet independently. As the number of sheets and rows increases, memory consumption grows until the Java Heap is exhausted.

I also tried an alternative approach:

• Split the data into chunks

• Save each chunk as a CSV file

• Read the CSVs again and combine them using a Multi-Port Excel Writer

Unfortunately, this does not work because the number of sheets is variable. Not all input ports are populated in every run, which causes the workflow (Multi-port Excel Writer) to fail.

Another challenge is that later in the workflow I need to reopen the generated XLSX file and add additional sheets using another Excel Writer node

Has anyone found a good solution for this?

• How can I create an XLSX file with a dynamic number of sheets?

• Is there a way to write sheets sequentially without keeping the whole workbook in memory?

• Are there better nodes or best practices for exporting datasets of this size?

Any suggestions would be greatly appreciated.

I look forward to hearing your input

Stefan