Search code examples
rxlsxrjava

createSheet error in R xlsx package when trying to write multiple sheets using forl oop

I have 5 data frames and when I try to create a workbook with several spreadsheets all works except one . My sheet names are string. Can anyone help me in rectifying this? All I could find is createSheet error xlsx llibrary, which is not helpful in this case.

To_write <- c('PR1','PR2','PR3','PR4','PR5')

#Create a new workbook for ouputs
workBook <- createWorkbook(type="xlsx")

for(tp in To_write ){

        sheet= xlsx :: createSheet(workBook,sheetName = as.character(tp) )
        addDataFrame(Data.Trans[[tp]], sheet, startRow = 1, row.names=FALSE)

}


saveWorkbook(workBook, file=paste0( ResultDir, '/','tTest.xlsx')   )

Where Data.Trans is list . The error is generated only when tp <- To_write[3] and the error message is Error in .jcall(workBook, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet", : java.lang.ArrayIndexOutOfBoundsException: 0

Please let me know what other information is required.

Thanks in advance

Solution

  • Test this:

    Data.Trans<-list(PR1=data.frame(rnorm(100)),PR2=data.frame(rnorm(100)),PR3=data.frame(rnorm(100)),PR4=data.frame(rnorm(100)),PR5=data.frame(rnorm(100)))

To_write <- c('PR1','PR2','PR3','PR4','PR5')
library(xlsx)
#Create a new workbook for ouputs
workBook <- createWorkbook(type="xlsx")

for(tp in To_write ){

  sheet= xlsx :: createSheet(workBook,sheetName = as.character(tp) )
  addDataFrame(Data.Trans[[tp]], sheet, startRow = 1, row.names=FALSE)

}

dir.create("ResultDir")
saveWorkbook(workBook, file=paste0( "ResultDir", '\\','tTest.xlsx')   )