How to combine multiple workbooks into one excel file displayed as tabs/work sheets?

user_12991295 0
Viewed 584

I've been using the openxlsx package to format different excel files (ie. highlighting a row based on a condition).

I created two workbooks with this package and each workbook is formatted differently. Now, I am trying to combine these two workbooks into a single excel file where these individual workbooks are tabs. Is there a way to do that? I know you can do it with multiple dataframes, but if I do that, then I lose my formatting.

For example, I tried this: 

wb <- createWorkbook()
addWorksheet(wb, sheetName="data")
writeData(wb, sheet="data", x=data)

wb2 <- createWorkbook()
addWorksheet(wb2, sheetName="data2")
writeData(wb2, sheet="data2", x=data2)

write.xlsx(wb, file = "combined.xlsx", sheetName="data", row.names=FALSE)
write.xlsx(wb2,file = "combined.xlsx", sheetName="data2", append = TRUE, row.names=FALSE)

but it seems to only work for dataframes.

openxlsx r excel
1 Answers

Following your example I would do the following:

wb <- createWorkbook()
addWorksheet(wb, sheetName="data")
writeData(wb, sheet="data", x=data)
write.xlsx(wb, "combined.xlsx")

wb2 <- createWorkbook()
addWorksheet(wb2, sheetName="data")
writeData(wb2, sheet="data", x=data)
write.xlsx(wb2, "wb2.xlsx")

wb_comb <-loadWorkbook("combined.xlsx")

lapply(sheets(wb2), function(s) {
  dt <- read.xlsx("wb2.xlsx", sheet = s)
  addWorksheet(wb_comb , sheetName = s)
  writeData(wb_comb, s, dt)
})

write.xlsx(wb_comb, "combined.xlsx")
user_107973260
Related