Add one excel worksheet from one file to another workbook in R-CodePudding

so I have one workbook wb with one sheet named "Ice Data" I have an excel file "path/test.xlsm" with 5 sheets but I only want to clone the first sheet named "Pasta" and add it to the other workbook wb.

I can't figure out how to do it. I already looked up openxlsx but I can't seem to manage.

THANKS

CodePudding user response：

library(openxlsx)

## Open the first workbook,  add a new sheet called Pasta 
## and get its sheet number.
wb1 = loadWorkbook("test1.xlsx")
addWorksheet(wb1, "Pasta")
TargetSheet = grep("^Pasta$", names(wb1))[[1]]

## open the second workbook, find and read the Pasta sheet.
wb2 = loadWorkbook("test2.xlsx")
SourceSheet = grep("^Pasta$", names(wb2))[[1]]
Sheet2Data = readWorkbook(wb2, SourceSheet)

## Write the data to the new sheet in the first workbook and save. 
writeData(wb1, TargetSheet, Sheet2Data)
saveWorkbook(wb1, "Test3.xlsx")

CodePudding user response：

Similar to G5W's but you could stay entirely within openxlsx and use getSheetNames function from openxlsx package and avoid creating extra objects in environment. Also if you have colleagues like me that like to name sheets with nearly identical names, "grep"ping a pattern might not always yield you what you want.

You would have two workbooks initially: 1) Ice Data that is called test.xlsm and 2) Other file that contains the Pasta data on sheet "Pasta", henceforth pasta.xlsx.

Start by getting the sheet names/positions for pasta.xlsx:

library(openxlsx)
getSheetNames("path/pasta.xlsx")

[1] "Pasta"       "Not pasta"   "maybe pasta" "salad"

This will list out all sheet names in the file that can either be indexed by position or name. So if you have tabs: Pasta, Not Pasta, maybe Pasta, and "salad" and you'd only want the first one, "Pasta"

You'd read in that sheet only either by index :

pb <- readWorkbook("path/pasta.xlsx", sheet = 1)

or name

pb <- readWorkbook("path/pasta.xlsx", sheet = "Pasta")

Now that you have just that sheet. Load the test.xlsm file, then add a blank sheet, in this case calling it "Pasta" for simplicity. This is ultimately where the Pasta data will be written to.

wb <- loadWorkbook("path/test.xlsm")

addWorksheet(wb, "Pasta")

Write pasta data, pb, to blank Pasta worksheet in the test.xlsm file called wb.

writeData(wb, sheet = "Pasta", pb)

#Confirm it looks good
openXL(wb)