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 ?

I don't use this package, but write.xlsx seems to expect a data.frame. I don't think this function does what you seem to think it does. You need to study the documentation. — Roland
– Roland, Commented Nov 15, 2013 at 22:40
is there any other package you can recommend ?! basically, I want to have 1 xlsx file including all csv files as sheets. — Areza
– Areza, Commented Nov 15, 2013 at 23:22

Ryan Walker · Accepted Answer · 2013-11-15 23:26:29Z

10

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.

answered Nov 15, 2013 at 23:26

Ryan Walker

3,2861 gold badge26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

writing multiple dataframe into one excel sheet using xlsx and R

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related