rworksheetxlconnect

Attempting the create multiple sheets in an excel workbook using R but recieving error after a few

I have about 30 or so R outputs that each go in their own sheets of the same file. However after about 7 or 8, I recieve this error sporadically.

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

library(readxl)
library(dplyr)
library(formattable)
options(java.parameters = "-Xmx1024m")
library(XLConnectJars)
library(XLConnect)

writeWorksheetToFile("CDFI_NACA.xlsx", 
                     data = iris, 
                     sheet = "Iris Dataset", 
                     header = TRUE,
                     clearSheets = TRUE)

So the above will be repeated in different ways about 30 times, yet around 7 or 8 entries, I receive the error, sporadically all for different types of outputs.

Solution

  • You can use openxlsx. It does not depend on java. You can even write all 30 dataframes in a single shot.

    library(openxlsx)
df_list = list(FIRST=df1, SECOND=df2, THIRD=df3) #list of data frames with name
write.xlsx(df_list, "output.xlsx", overwrite=T) #list element name will become the sheetname.