How to update only one worksheet in excel and get others ? (openxlsx)

[Quito96]

Hi,
I have converted messi data into a readable data structure and saved it in test_final.
In the worksheet R-Data, I have then written the final data and saved it as xlsx.
Now I am missing the possibility to save only updated R-Data in the xlsx file, without changing existing other (Pivot) worksheets.

Does anyone know a possible solution?
Thanks in advanced

# require(openxlsx)
## Create Workbook object and add worksheets
wb<-createWorkbook("test-final.xlsx")

# add some worksheet
# addWorksheet(wb, "Pivot")
addWorksheet(wb,"R-Data")


# update the data
writeData(wb,"R-Data",test_final)
## Save workbook
## Open in excel without saving file: openXL(wb)
saveWorkbook(wb, "test-final.xlsx", overwrite = TRUE)
# open file in excel

# update only one worksheet
update_WS<-writeData(wb,"R-Data",test_final)%>%
 ????????

[Hayward]

I don't know the package, but I think this would work.

After you save the excel file:

1. open if it's not already in your environment
2. remove the old worksheet
3. add the same worksheet name
4. write your new data to the empty worksheet
5. re-save

wb <- loadWorkbook("test-final.xlsx")
removeWorksheet(wb, "R-Data")
addWorksheet(wb, "R-Data")
writeData(wb,"R-Data", test_final)
saveWorkbook(wb, "test-final.xlsx", overwrite = TRUE)

[Quito96]

Hi Hayward,
thanks for your suggestion.
In order not to overwrite existing worksheets (pivot), I now have
written the following working solution.
Still thinking about the newly created file to spend a version number.

# 5. write updated data to xlsx ----
new_data <- test_final  
out_xlsx <- "test-final.xlsx"# File name here
new_xlsx <- "test-final.xlsx" # Alternative wb name Version Control?

## a. Check to see if file doesn't exist ----
if (!file.exists(out_xlsx))  {
  
  # Create workbook using openxlsx
  wb <- createWorkbook()
  # Add worksheet
  addWorksheet(wb, "R-Data")
  # Write data frame to new worksheet
  writeData(wb, "R-Data", new_data)
  # Save file
  saveWorkbook(wb, file = out_xlsx)
} else {
  ## b. Read in existing data -----
  old_wb <- 
    readWorkbook(out_xlsx,
                 sheet = "R-Data",
                 detectDates = TRUE)
  # write updated data to var
  new_data <- test_final
  # Load and write updated data frame to existing worksheet
  wb <-  loadWorkbook(out_xlsx)
  # write new data
  writeData(wb, "R-Data", new_data) # write new data
  # 6. Save updated file ----
  saveWorkbook(wb, new_xlsx, overwrite =TRUE)
}