When I populate a sheet in Excel, I seem to run out of memory. In my example I have about 50k items. I’ve updated the component to do “streaming mode” which is quick and doesn’t error, but then it won’t allow me to preserve existing data in other sheets. My requirement is to populate 6 sheets in the Excel Workbook. I don’t mind populating 6 different workbooks assuming there is a way to combine them at the end. Is there an efficient way to do this that you can point me towards?
Hello Nick,
Please try the following setup of properties on Writer.
It should clear just the target sheet into which you write, but keep the rest untouched.
Hope this helps.
Best regards,
Petr
this works great, thanks.
Actually this works great if I do an individual sheet. But if then try to do 2 sheets sequentially one after the other I’m hitting the following memory issues.
Exception in thread “http-nio-52115-Poller” java.lang.OutOfMemoryError: Java heap space
I’m guessing the designer is holding the previous sheet data in memory. Is there a way of doing some garbage collection so it flushes the previous sheet data and then it’s clear to run the next sheet with new data?
Hi Nick,
Your observation is correct. CloverDX does need to load all existing sheets into memory before writing into a new one. Unfortunately, this behavior is heavily determined by the way how the underlying Apache POI library works (which is the library CloverDX uses to interact with Excel files) and there is no easy way to change that.
Generally speaking, you can approach this challenge either by increasing the heap size limit or by advanced manipulation of the sheets outside of CloverDX (i.e. writing individual sheets per spreadsheets and combining them later by using a 3rd party tool like VBA macro).
Regards,
-Vladi
Worth mentioning is also that working with high-sized Excel spreadsheets and/or manipulating with large volume of data is a task suited primarily for CloverDX Server. If you are using CloverDX Designer the default heap size limit might need increasing. You can do that by navigating to Window > Preference > CloverDX > CloverDX Runtime > Memory setting and changing the “Max. heap size” attribute value to a higher number of MBs. Please consider the RAM you have available on the computer where your CloverDX Designer is installed.
Cheers,
Thanks I upped the heap size, which was fairly low and it now runs without issue. Appreciate the support.