writing multiple dataframe into one excel sheet using xlsx and R

Question

I have a set of csv files in different directories, I would like to put them all in one excel file, each table in one excel sheet.

I am using R and xlsx package.

# loading the library
library(xlsx)
rm(list = ls())

# getting the path of all reports (they are in csv format)
restab = system("ls /home/ubuntu/ibasruns/control/*/report",intern = TRUE)

# creating work book
wb <- createWorkbook()


# going through each csv file
for (item in restab)
{
    # making each as a sheet
    sheet <- createSheet(wb, sheetName=strsplit(item,"/")[[1]][6])
    addDataFrame(read.csv(item), sheet)
    # saving the workbook
    saveWorkbook(wb, "AliceResultSummary.xlsx")
}

# finally writing it.
write.xlsx(wb, "AliceResultSummary.xlsx")

However, in the last line, I am getting the following error,

Error in as.data.frame.default(x[[i]], optional = TRUE) : cannot coerce class "structure("jobjRef", package = "rJava")" to a data.frame

Is there any thing that I am missing ?

Answer 1

You're close:

# creating work book
wb <- createWorkbook()


# going through each csv file
for (item in restab)
{
    # create a sheet in the workbook
    sheet <- createSheet(wb, sheetName=strsplit(item,"/")[[1]][6])

    # add the data to the new sheet
    addDataFrame(read.csv(item), sheet)
}

# saving the workbook
saveWorkbook(wb, "AliceResultSummary.xlsx")

The write.xlsx is not needed here; it's just used to create a workbook from a single data frame.