Macro to aggregate Excel from a directory, into a Master, using a template (with existing sheets with Pivot Tables, Graphs, and the likes)

AggregateExcel.R

library(openxlsx)
library(plyr)

template <- "template.xlsx"
masterfile <-
  "master.xlsx"

dirpath <-
  "data/"

sheets <-
  c("Column1",
    "Column2",
    "COlumn3",
    "Column4",
    "Column5",
    "Column6",
    "Column7")

files <- dir(dirpath, pattern = ".xlsx")

master <- list()

filepath <- paste(dirpath, files[1], sep = "")
print(paste("Processing the file" , filepath))
fn <-
  setNames(data.frame(matrix(ncol = 1, nrow = 1)), c("Filename"))
fn[1] <- files[1]
for (j in 1:length(sheets)) {
  print(paste("Processing the sheet: " , sheets[j]))
  sheetFromFile <- read.xlsx(filepath, sheets[j])
  if (nrow(sheetFromFile) > 0)
    sheetFromFile <- cbind(sheetFromFile, fn)
  master[[j]] <- sheetFromFile
}

for (i in 2:length(files)) {
  print(paste("Processing the file " , filepath))
  filepath <- paste(dirpath, files[i], sep = "")
  fn <-
    setNames(data.frame(matrix(ncol = 1, nrow = 1)), c("Filename"))
  fn[1] <- files[i]
  for (j in 1:length(sheets)) {
    print(paste("Processing the sheet: " , sheets[j]))
    sheetFromFile <- read.xlsx(filepath, sheets[j])
    if (nrow(sheetFromFile) > 0) {
      print("Adding the filename")
      sheetFromFile <- cbind(sheetFromFile, fn)
    }
    master[[j]] <- rbind.fill(sheetFromFile, master[[j]])
  }
}

wb <- loadWorkbook(template)

for (i in 1:length(sheets)) {
  addWorksheet(wb, sheets[i])
}

for (i in 1:length(sheets)) {
  writeDataTable(wb, sheets[i], master[[i]])
  setColWidths(wb,
               sheets[i],
               cols = 1:20,
               widths = "auto")
}

saveWorkbook(wb, masterfile, overwrite = TRUE)