R/mergeExcel.R

In IU-MMGE/NCRAD: Convenience functions for NCRAD

#' Merge Excel workbooks
#'
#' @param files A vector of Excel file locations
#' @param output_location Path for output file
#' @param output_name Name for output Excel file
#' @description This function will take a group of workbooks that mutliple sheets
#' with common sheet names. It will combine all of the sheets with the same name into
#' one sheet and write a single workbook with merged content.
#' @return An Excel 2007 workbook.
#' @export
mergeExcel <- function(files, output_location = ".", output_name = "merged_excel"){

  files <- files[grep("xlsx|XLSX", files)]

  wbs <- lapply(files, function(file){
    sheets <- excel_sheets(file)
    dfs <- lapply(sheets, function(i, file){
      try(read_excel(file, i), silent = TRUE)
    }, file = file)
    names(dfs) <- gsub("\\s", "_", sheets)
    dfs
  })

  sheet_names <- unique(unlist(lapply(wbs, names)))

  sheets <- lapply(sheet_names, function(sheet_name, wbs){
    dfs <- lapply(wbs, function(wb, sheet_name){
      wb[[sheet_name]]
    }, sheet_name = sheet_name)
  }, wbs = wbs)

  dfs <- lapply(sheets, function(sheet_list){
    sheet_class <- sapply(sheet_list, class)
    sheet_list <- sheet_list[sapply(sheet_class, function(x) x[1] == "tbl_df")]
    df_names <- unique(unlist(lapply(sheet_list, names)))
    for(i in seq_along(sheet_list)){
      sheet_list[[i]] <- as.data.frame(sheet_list[[i]])
      for(k in names(sheet_list[[i]])){
        sheet_list[[i]][, k] <- as.character(sheet_list[[i]][, k])
      }
      if(dim(sheet_list[[i]])[2] < length(df_names)){
        missing_names <- df_names[!df_names %in% names(sheet_list[[i]])]
        for(j in missing_names){
          sheet_list[[i]][, j] <- as.character(NA)
        }
      }
    }
    Reduce(rbind, sheet_list)
  })

  names(dfs) <- sheet_names

  output_file <- paste0(output_location, "/", output_name, ".xlsx")

  wb <- createWorkbook()

  lapply(seq_along(dfs), function(n, dfs, wb, sheet_names){
    sheet <- createSheet(wb, sheet_names[n])
    addDataFrame(dfs[[n]], sheet, row.names = FALSE)
  }, dfs = dfs, wb = wb, sheet_names)

  saveWorkbook(wb, output_file)

}