I have researched the original error: Error java.lang.OutOfMemoryError: GC overhead limit exceeded on this site as well as other and have tried various solutions. (including XLConnect, removing library xlsx, writing to CSV)
I am currently trying to export a data frame with 467,244 rows and 19 columns to an excel file and receiving the above error. What is interesting is that it does work intermittently, when it fails, it is creating an xlsx that is significantly larger than it "should" be.
I am trying to replace an excel sheet, but still have an option to export for other end users, so I am prepared to export this data frame, and compare to the current excel sheet to make sure everything is working as expected.
the file size of the comparison document is 5.86 MB and it includes a significant amount of pivot tables, charts, formulas etc in addition to the data being compared to in the data frame.
The first export worked with no error with file size 956 KB.
the second export, with the addition of 151 rows, failed and the broken file is showing to be 29.2 MB
The third attempt to CSV worked, however the file size is 51 MB
Nothing in my process or code changed other than append of the 150 lines of data.
Can anyone provide insight into why it works perfectly in once occasion, and then completely fails the next and why the file size is suddenly excessively large?
I understand I added data, however quite a small amount. Not significant enough to alter the file size that much.
I am new to R, and programming in general, so some of the complications with this memory error seem to be hard to grasp as my knowledge of how it works isn't quite there.
Below are some of the various options I have tried:
#Option 1
df3<-df3[c(2,1,9:12,3:8,13:19)]
save(df3,file="Monthly_Staffing_Data.RData")
Exp_Name <- paste("../Monthly_Staffing_Data - ",F_date,".xlsx",sep="")
write.xlsx2(df3,Exp_Name);beep(2)````
#Option 2
Exp_Name <- paste("../Monthly_Staffing_Data - ",F_date,".csv",sep="")
wb<-XLConnect::loadWorkbook("text.xlsx", create=TRUE)
createSheet(wb,"Data")
writeWorksheet(wb,df3, sheet="Data")
saveWorkbook(wb)
beep(2)
#Option 3
require(XLConnect)
writeWorksheetToFile("XLConnectExample2.xlsx", data = df3,
sheet = "data")
#Option 4
write.csv(df3,Exp_Name, row.names=FALSE)
Update
I connected remotely to my desktop computer and was able to successfully run this program with no problems whatsoever multiple times. It looks like it may be my laptop. However I would still be interested to know any more efficient ways to do this so that the program will work without the need for a more powerful computer.
question from:
https://stackoverflow.com/questions/65926532/intermittant-error-java-lang-outofmemoryerror-gc-overhead-limit-exceeded-and-ex 
