Can't add worksheets to an excel file through my R script

Question

I'm trying to create a workbook with several spreadsheets where I have to pass three data frames to each sheet. However, I'm having problems creating the sheets, having the following error:

Error in .jcall(wb, "Lorg/apache/poi/ss/usermodel/Sheet;", "createSheet", : method createSheet with signature (D)Lorg/apache/poi/ss/usermodel/Sheet; not found

I'm using the xlsx package and the relevant code part is the following:

wb <- createWorkbook(type="xlsx")
saveWorkbook(wb, 'output.xlsx')

for (i in year)
{
  sheet.1 <- createSheet(wb, sheetName = i)
  data.filter <- realdata[realdata$year_ == i,]
  data.filter <- data.filter[data.filter$month_ >= month[1],]
  data.filter <- data.filter[data.filter$month_ <= month[4],]
  ptable_data_usado <- cast(data.filter, mondat ~ BASE, value = "myidx")
  correl_usado <- cor(ptable_data_usado)
  addDataFrame(correl_usado, sheet = i, startRow = 0, startColumn = 0)
  ptable_data_prx <- cast(data.filter, mondat ~ NearestBaseName, value = "myidx")
  correl_prx <- cor(ptable_data_prx)
  addDataFrame(correl_prx, sheet = i, startRow = 14, startColumn = 0)

}

Answer 1

I ran into a similar problem. My solution was to coerce sheet name into a character.

So in your case it might be

sheet.1 <- createSheet(wb, sheetName = as.character(i))

Hope it will help.

Answer 2

xlsx package is using rJava to call functions written in JAVA from the APACHE POI project for functionality. The function to creat worksheet is declared as:

public XSSFSheet createSheet(java.lang.String sheetname)

this means that you need to pass a string to the function. Return back to R, the sheet name must be a character vector. @Oleksii-Sh answer is right.

Or you can use:

sheet.1 <- createSheet(wb, sheetName = paste0("sheet", i))

if you want to name it sheet1, sheet2 ...