R to Merge All Sheets From All Excel Files

Question

I am trying to merge data from all sheets in all Excel files in a folder. All sheets and all files have the same headers and same data sets. I thought the code below would read all sheets, but it seems to be reading ONLY the first sheet in each file.

# This needs several other packages
# install.packages("XLConnect")
require(XLConnect)

setwd("C:/Users/Excel/Desktop/Coding/R Programming/Excel/Excel_Files/")

fpattern <- "File.*.xls*?"  # pattern for filenames
output.file <- "Test.xls"
lfiles <- list.files(pattern = fpattern)

# Read data from all sheets
lfiles %>% 
  excel_sheets() %>% 
  set_names() %>% 
  map(read_excel, lfiles = lfiles)

Answer 1

This is an example using only R base functions and XLConnect:

library(XLConnect)

testDir <- "Excel_Files"

re_file <- ".+\\.xls.?"
testFiles <- list.files(testDir, re_file, full.names = TRUE)

# This function rbinds in a single dataframe
# the content of multiple sheets in the same workbook
# (assuming that all the sheets have the same column types)
rbindAllSheets <- function(file) {
  wb <- loadWorkbook(file)
  sheets <- getSheets(wb)
  do.call(rbind,
          lapply(sheets, function(sheet) {
            readWorksheet(wb, sheet)
          })
  )
}

# Getting a single dataframe for all the Excel files
result <- do.call(rbind, lapply(testFiles, rbindAllSheets))