# java heap error when using write to excel template node

**URL:** <https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494>\
**Category:** KNIME Analytics Platform\
**Created:** [October 13, 2023, 1:26pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494 "2023-10-13T13:26:22Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Heinz](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/f04885/32.png) [@Heinz](https://forum.knime.com/u/Heinz)\
**Post date:** [October 13, 2023, 1:26pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/1 "2023-10-13T13:26:22Z")

</div>

Dear all,

Multiple data sources are processed and saved into multiple sheets in a single Exceltable  
If executing the Write to Excel Template node multiple times, Java Heap Error appears

Circumstances:  
Win10 Enterprise 64bit, 48 GB RAM, Office365 Enterprise, Knime. Latest updates installed.

How to reproduce:  
copy all attached file to c:\temp  
[1.xlsx](https://forum.knime.com/uploads/short-url/eeHHporI6aJ6309ABAz9N9WPhC3.xlsx) (19.8 KB)  
[java\_heap\_error.knwf](https://forum.knime.com/uploads/short-url/qtDVSLIm63oJN1mLHH5ELU6OjxA.knwf) (42.8 KB)  
[outbound\_template.xlsx](https://forum.knime.com/uploads/short-url/slK0ux74U2KU2cJ5yh8LW4v19LN.xlsx) (19.1 KB)  
[outbound\_template.xlsx](https://forum.knime.com/uploads/short-url/slK0ux74U2KU2cJ5yh8LW4v19LN.xlsx) (19.1 KB)

Open 1.xlsx in Excel 1st sheet  
Copy row 2 100.000 times; so 100.000 rows are filled  
Save 1.xlsx  
(Step neccessary to go around upload limit)

load Workflow java\_heap\_error  
Remark: For demo I have only one source reading this multiple times

execute node Write to Excel template Write Table Outbound sheet 1-\> Operation is executed ok; Java Heap increases  
execute node Write to Excel template Write Table Outbound sheet 2-\> Operation is executed ok; time increases, Java Heap increases  
execute node Write to Excel template Write Table Outbound sheet 3-\> Operation is executed ok; time increases, Java Heap increases  
execute node Write to Excel template Write Table Outbound sheet 4-\> Operation is executed ok; time increases, Java Heap increases  
execute node Write to Excel template Write Table Outbound sheet 5-\> Operation fails

Questions:  
Is it a bug?  
Is there a way to relase the java heap memory?  
Is there a better way to write to excel (Excel sheet will grow to 210 MB on disk at the end of the application)

Thx for help!

BR, Heinz

---

<div class="post-metadata">

**Author:** ![ScottF](https://forum-cdn.knime.com/user_avatar/forum.knime.com/scottf/32/61316_2.png) [@ScottF](https://forum.knime.com/u/ScottF)\
**Post date:** [October 20, 2023, 4:42pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/2 "2023-10-20T16:42:51Z")

</div>

@Heinz thanks for the workflow and example files. Let me see if I can find someone to reproduce and we will circle back. Thanks for your patience!

---

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [October 20, 2023, 5:33pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/3 "2023-10-20T17:33:04Z")

</div>

> [@Heinz](#):
>
> Is there a way to relase the java heap memory?

There is the [Garbage Collection](https://forum.knime.com/t/execute-failed-java-heap-space/35585/2). More performance hints:

> **[Mastering KNIME: Unlocking Peak Performance with Expert Tips and Smart Settings](https://medium.com/low-code-for-advanced-data-science/mastering-knime-unlocking-peak-performance-with-expert-tips-and-smart-settings-49e2c0dab9cc)**
>
> The low-code tool KNIME is great and a lot of guides are out there to help you master it. You are encouraged to read the official ones…

> [@Heinz](#):
>
> Is there a better way to write to excel

Not sure if it is better but you can use OpenPyxl to append data to an existing excel sheet.

> [@Data extraction from .xls file/s](https://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12):
>
> @Matt9301 there is a way to do this with the use of [Python](https://hub.knime.com/mlauber71/spaces/Public/latest/_knime_and_python_meta_collection) pandas and [openpyxl](https://openpyxl.readthedocs.io/en/stable/) You would have to provide an existing excel file and a sheet name and then sheets with the same structure.

And I assume it is necessary to write to a template to preserve the formats? Or would it be possible to write to a blank sheet and add the formats later?

---

<div class="post-metadata">

**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\
**Post date:** [October 21, 2023, 12:00am UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/4 "2023-10-21T00:00:47Z")

</div>

I tried out the workflow albeit I accidentally set it up with 200000 rows and got the same results as @Heinz described. The problem is that it is running out of heap _during_ the execution of the Write to Excel Template node so there is no scope for assisting with garbage collection before or after. The first call I made completed ok but it ran out on the second one. For the one that completed, it had released most if not all of the heap after execution and an added call to garbage collection made no difference.

Looping in @AnotherFraudUser as maybe there is something that can be done inside the node, but it may be something buried in the Apache POI libraries, that I think that node is using, perhaps having problems with large datasets. I’ve not generally tried writing that many rows to a formatted xlsx.

---

<div class="post-metadata">

**Author:** ![Heinz](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/f04885/32.png) [@Heinz](https://forum.knime.com/u/Heinz)\
**Post date:** [October 26, 2023, 1:22pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/5 "2023-10-26T13:22:42Z")

</div>

Dear all,

thanks for good support.  
I have understand that I run into a bug inside Knime node.

Questions:  
How do you go on?  
Will there be a fix?  
When?

Thx,

Heinz

---

<div class="post-metadata">

**Author:** ![AnotherFraudUser](https://forum-cdn.knime.com/user_avatar/forum.knime.com/anotherfrauduser/32/16912_2.png) [@AnotherFraudUser](https://forum.knime.com/u/AnotherFraudUser)\
**Post date:** [October 29, 2023, 4:10pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/6 "2023-10-29T16:10:30Z")

</div>

Hi @Heinz,

in general the library behind my Write to Excel template is quite memory intensive.  
Even if you write into a new sheet the process loads the whole file in an umcompressed format including all formats etc.  
So I think here is not much I can do there - from what I can see garbabe collection cleans up the files as intended 😕

Regarding the final file size I i will try to investigate - pretty sure this looks like that the shared strings are not properly set (normally if you have 5 times the string “abbaaa” excel would put that into the shared strings and save 5 times the shared string id instead 🤔)  
Will check if I have to trigger that somewhat manually to properly compress the file.

To have a longterm solution I will check if I can setup a node which interacts directly with the unterlaying xmls 🤔 most likely with a bit of reduced features…  
but as I am not sure if I will actually manage to set this up will not be able to provide a timeline for that 😄

Questions:  
How do you go on? - see above  
Will there be a fix? - no at least do not see how i could teak the existing node to handle that. But if anyone knows more happy to add fixes to the node 👍  
When? - cannot say

in the meantime i can only second what e. g @mlauber71 said. Never tested the python excel library myself but it always looked like a good way to do it as well 🙂

---

<div class="post-metadata">

**Author:** ![Heinz](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/h/f04885/32.png) [@Heinz](https://forum.knime.com/u/Heinz)\
**Post date:** [October 31, 2023, 4:56pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/7 "2023-10-31T16:56:28Z")

</div>

Thank you very much for your answer.

Is there - maybe - a better way; e.g. saving the 5 Excelsheets into Knime DB tablses and then write them in one step in an exceltable but to different sheets?

How can I format existing excel sheets?

BR,

Heinz

---

<div class="post-metadata">

**Author:** ![RKrom](https://forum-cdn.knime.com/user_avatar/forum.knime.com/rkrom/32/48561_2.png) [@RKrom](https://forum.knime.com/u/RKrom)\
**Post date:** [March 15, 2024, 4:19pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/8 "2024-03-15T16:19:15Z")

</div>

Hi @AnotherFraudUser,

We are experiencing similar issues as Heinz. For most of the scenarios the Write to Excel Template will work for us, but in specific cases where we have 20 sheets to write data into it could happen the Java Heap issues pop up.

In our case the issue is so big it crashed our KNIME Server Executer frequently but didn’t leave enough information in the log file to determine actually which workflow caused the issue. Only after some time we found out it was the Write to Excel Template node.

We are still on 4.7, soon to be updating to 5.1. I noticed the 4.7 is now deprecated in 5.X, but could you let me know if there are any performance improvements applied in the new version? If not, we will have to build our own node with Python to workaround this issue in specific cases. As we did in the past, I’m willing to help you in any testing 😁

Thanks!

---

<div class="post-metadata">

**Author:** ![RKrom](https://forum-cdn.knime.com/user_avatar/forum.knime.com/rkrom/32/48561_2.png) [@RKrom](https://forum.knime.com/u/RKrom)\
**Post date:** [March 15, 2024, 6:45pm UTC](https://forum.knime.com/t/java-heap-error-when-using-write-to-excel-template-node/73494/9 "2024-03-15T18:45:52Z")

</div>

I’ve build a Python script which can simply copy data into an existing sheet from a specific cell and on. This is my (temporary) work around for the workflows which could lead to java heap issues.

It would be great if the performance fixes could be applied on the Write to Excel Template node, as this node is much more powerful than the Python script I built.

My KNIME 5.1 installation with (for the sake of testing) 2GB of memory assigned crashed while executing all 10 “Write to Excel Template” nodes, but will be successful when executing the 10 “Write To Excel Template With Python” components.

[Write to Excel Template - Java Heap issue and fix in Python (KNIME Forum version).knar](https://forum.knime.com/uploads/short-url/sVa31CqbTxrCgI6cor8rvpW06JE.knar) (305.0 KB)
