How to combine multiple excel files into one using R-CodePudding

As the title states. I have to download at least 30 excel files from an online database and it is a headache because I have to combine one by one , I would like to have a single excel file where every tap belongs to a different file, any suggestion?

For example: Excel Files File1 File2 File 3 so on...

Combine these files into macroexcelfiles, and tap1 is file 1, tap2 is file2, and so on, so forth...

Thanks in advance guys!

CodePudding user response：

Here is an approach that avoids quite a bit of unnecessary looping:

library(openxlsx)
setwd("~/MyXLSXFiles/")
files <- list.files(pattern="xlsx")
files
#[1] "Bar.xlsx" "Foo.xlsx"

data <- setNames(lapply(files, read.xlsx), files)
write.xlsx(data, file = "Combined.xlsx")

Note also that the individual sheets are named based on the list data's names.

CodePudding user response：

I use this code almost every day

library(openxlsx)

files <- list.files(pattern = ".xlsx")
wb <- createWorkbook()
for(file in files)) {
    df <- read.xlsx(file)
    addWorksheet(wb,file)
    writeData(wb,file,df)
}
saveWorkbook(wb,'combined.xlsx',overwrite=TRUE)