Search code examples
rxlsxrjava

writing multiple dataframe into one excel sheet using xlsx and R


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 ?


Solution

  • 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.