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