system.dataset.dataSetToExcel OutOfMemoryError

Ignition
1

Hello to all,
I have a problem with system.dataset.dataSetToExcel. I need to export big data from a table. In my vision application I have some table where I can see the data and I have create a button to export all the table. But the table with few data don't have problem when I export the table with many data after a lug I get the error "java.lang.OutOfMemoryError: java.lang.OutOfMemoryError: Java heap space".
I tried to export using dataSetToCSV the same data and it work very well and very fast but if is possible exporting to excel would be more convenient. Anyone have same problem o some ideas?

This i my code:

spreadsheet = system.dataset.toExcel(True, [ctbgrezzi.data])
filePath = system.file.saveFile("grezzi.csv")
#spreadsheet = system.dataset.toCSV(ctbgrezzi.data)
#filePath = system.file.saveFile("grezzi.csv","csv","Comma separated values")
if filePath != None:
system.file.writeFile(filePath, spreadsheet)

Traceback (most recent call last):
File "event:mouseClicked", line 19, in
java.lang.OutOfMemoryError: java.lang.OutOfMemoryError: Java heap space

caused by OutOfMemoryError: Java heap space

Ignition v8.0.16 (b2020082513)
Java: Azul Systems, Inc. 11.0.7

2

Did you try allocating more memory to the client?

1 Like
3

Can you do this in chunks? I don’t know what is forming your ctbgrezzi.data variable, if its always going to be 10,000 rows no matter what, more ram will do the trick, but if its a table that is going to keep getting longer, you should try to refactor your code now to do it in chunks imo.

4

Thank you for the reply. More memory solve the problem but there is no way to find a solution with a limited resource request?
The “toCSV” function works immediatly without memory problem. The toExcel function take a lot of seconds and in case of a lot of data cause memory problem with limited resource. If there is no way to optimize the toExcel function I think I will use the CSV exportation.
Thanks

5

Thank you for the reply,
ctbgrezzi.data is a stored value for generating a certificate. In this case I used 4000 rows X 70 cols.
The maximun size of this registration can be about 16000rowsx70cols
Chunks is a possibility but not so friendly for the enduser.
Thanks

6

Converting a dataset to excel is more memory intensive than converting it to csv, because the converted data is larger. Since it stays in memory until the end, more memory is required to do the operation.

1 Like
7

What Jordan said is exactly right. The conversion to Excel takes a lot more processing than CSV – setting datatypes and formatting, autosizing columns, etc, which are all done in memory. As the number of cells increase in your worksheet, the amount of memory needed grows very quickly.

I took a quick look at the code and it looks pretty optimized, so unfortunately more memory is the way you need to go here.

8

Thank you for the reply,
I try to check the better way to my project.
Thank you very much

1 Like