I’m building automated reports in a KNIME workflow, which exports data tables into Excel by writing the data into an existing Excel template file. This Excel template is the basis of each template and for now only includes the company logo and basic report information.
As the standard Excel Writer node does not allow me to use an Excel Template file, I came across the community extension Write to Excel Template created by @anotherfrauduser which contains a node “Write to Excel Templates”.
For small reports this node is doing exactly what I need, but for the bigger reports (couple of thousand rows) the node produces the following error:
Execute failed: Reason: Error while writing The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
I would like to know if this is a known issue and if there is a workaround / fix for this. The Excel file should be at least capable of handling 50k records, but preferably there should not be a limitation.
I’ve attached a small workflow which is generating the error.
Thanks and best regards,
Write to Excel Template issue.knar (17.9 KB)
thanks for the info - never hat this issue but will look into it
Maybe a few questions so i can narrow the problem down:
-what file format has your original file?.xlsx/.xls/.xlsm?
-would it be possible to have an example file? (e. g. your file without any data - only containing the formatting)
(on the phone currently - so if your example workflow already answer the questions - then you can ignore them )
Thank you very much for the quick response.
The original file is an .xlsx file and is attached in the example workflow.
i found and internally fixed the issue.
I created new text-styles for each text-column instead of reusing them.
Most likely will try to set it live at the end of the week with some other changes.
(hope that works for you?)
That sounds like music to my ears!
Your help is much appreciated. In case you need anything from me, just let me know!
my 4.3 version is now updated (and at least working for me )
You might have to start knime with the -clean option in the knime.ini once after the update.
Currently I do not have my extension availible for 4.4 - but will try to get it soon there as well (I am somewhat slow to upgrade )
Hi @AnotherFraudUser ,
Thank you very much for the node update.
We moved to version 4.4, so I will wait for the upgrade release.
If you could let me know via this forum that would be great!
just so you have a update about the timeline.
The 4.4 update is already ready and is only waiting for deployment by the KNIME colleagues.
(I just do not want to hurry them - after I took weeks to create the 4.4 version )
Hopefully this will be done in the next few days.
In the meantime - if you have any wish for an enhancement for the node - or need a different node… then just give me a heads up.
I have a long boring train ride tomorrow and might be able to fill the time with an update or a new node
Sorry for the delay and best regards
the 4.4. update is now live
It worked for me in 4.4. - but please give me an info if the update does not work on your side
(sorry for the delay)
I’ve tested a heavy file (10.000 rows and 200 column) and I hereby confirm the updated node is working great! Wonderful!
Your help is much appreciated !
In case I come up with enhancements for the node I will let you know. One thing which came to my mind is to force the file output file extension to xls, xlsx or xlsm by a dropdown selection independent of the template extension.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.