1

I have different workbooks with openxlsx let´s say wb1, wb2 and wb3, each workbook has different formatted worksheets. I need some way to append all the worksheets in the same workbook, please note that making a single workbook and add each worksheet from the begining is not an option to me since I need each .xlsx file individually and then a big one with all the sheets (keeping the formatting).

If you know how to cloneWorksheet from different wbs, that would do the trick as well.

Thanks in advance!

Peter
  • 11,500
  • 5
  • 21
  • 31
Edgar Velasco
  • 15
  • 3
  • 1
    It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. – MrFlick Jul 09 '20 at 22:25

1 Answers1

0

Try this:


library(openxlsx)
library(xlsx)
library(readxl)
library(purrr)

Assume you have created two workbooks each with multiple worksheets:

I'm using the example workbooks from packages openxlsx and xlsx

set out the workbook file paths I've used the example test xlsx files, in practice you'd just use your_wb <- "path_to_your_file.xlsx"

# sample workbooks
wb1 <- system.file("extdata", "readTest.xlsx", package = "openxlsx")
wb2 <- system.file("tests", "test_import.xlsx", package = "xlsx")

# vector of workbook paths
wbs <- c(wb1, wb2)

# read worksheet names from both workbooks into a list which becomes the contents of your "cloned" or "big" workbook
sh_nm <- map(wbs, excel_sheets)

# number of sheets in each workbook
sh_nr <- unlist(map(sh_nm, length))

# list of workbook names to match the number of sheets respectively for each workbook
wb_list <- rep(wbs, sh_nr)

# list of all sheet names
sh_list <- unlist(sh_nm)

# combine data from all worksheets and give each list element a unique name
sh_all <- 
  map2(wb_list, sh_list, ~openxlsx::read.xlsx(.x, sheet = .y)) %>% 
  set_names(paste(rep(c("wb1", "wb2"), sh_nr), sh_list, sep = "_")) 

#save data, openxlsx automatically places each list element into a separate worksheet with the list element name as the worksheet name.
openxlsx::write.xlsx(sh_all, "wb_all.xlsx")

Created on 2020-07-10 by the reprex package (v0.3.0)

Peter
  • 11,500
  • 5
  • 21
  • 31
  • It's a very nice way to append the worksheets, thanks a lot for your time, my biggest issue was to keep the format for each sheet. I managed to solve the specific problem with the format and sheets. Basically as I was the creator of all the wb within R, first I saved the 'big' - joined wb with all the sheets, and in a cycle for the number of 'individual' wb needed, I deleted the worksheets that did not belong to that wb. At the end, I had the big - joined one and the individuals in the correct format. I may not have been the best practice, but worked for now. Thakns again!! – Edgar Velasco Jul 11 '20 at 02:31