0

I am using RConnect to write to a workbook. The function is below I am getting the error message

Error: POIXMLException (Java): java.lang.reflect.InvocationTargetException

My research, [2] on the topic so far has said that this basically means the java machine doesn't have enough memory (i may be wrong)

This ran last week without any issues so I'm confused as to why it has suddenly become a problem with the file not having changed. It seems to bomb on Load workbook the template workbook

Basic Details

File size is 3MBs My computer has 16 Gigs of memory

Function

# Export it all to Excel
export_report <- function(mydf, startRow, Offset, myoutputTemplate, myoutput, mycomments) {


  # Load workbook the template workbook
  wb <- loadWorkbook(myoutputTemplate)

  # write to the workbook the data frame
  writeWorksheet(wb, mydf, sheet= "Analysis",  
             startRow=startRow, startCol=1, header=TRUE)                

  # write the analysis
  writeWorksheet(wb, mycomments, sheet= "Analysis", header=FALSE,   
             startRow=startRow+Offset, startCol=1)   

  # Save the workbook
  saveWorkbook(wb, myoutput)

}

I cant use open.xlsx because it requires RTools (i think) and my IT department is slow to install it.

I would take any alternatives anyone can suggest

Community
  • 1
  • 1
John Smith
  • 2,448
  • 7
  • 54
  • 78

1 Answers1

0

I have used a bunch of Excel libraries and this is not uncommon. My explanation based on experience (not the code under the hood) and some understanding of memory is as follows:

Java runs outside of R, unlike most other languages called by the r environment, and it is not automatically garbage collected by R because R does not see the stack of dead bodies piling up out there.

Your system does not garbage collect it because it is not a system function. So, those multiple copies of data frames stack up in memory allocated to Java and your script becomes slow or crashes.

My response to this was to run gc() after each load write and save of a workbook or anytime I renamed or restructured a worksheet using the Excel library (in my case XLconnect). Also, the second you no longer need a java native object in your work space, remove it and garbage collect again. even if you are not crashing, it does speed up processing on larger files.

I know this is a very un-technical explanation, but it has worked for me and the folks in my division using these packages. Give it a try.

sconfluentus
  • 4,693
  • 1
  • 21
  • 40
  • Thanks @bethanyP, it seems you are right. The laptop had not been shutdown for a while and although the gc() didn't seem to rectify the problem, when i reset the PC it seems to work. I will just reset the computer fresh every time i run the report – John Smith Nov 06 '16 at 08:14
  • There was probably stuff in the java 'heaps' left from other work. I have also had that happen!: ) good luck with your project! – sconfluentus Nov 07 '16 at 02:30