Ha, yea, I also sometimes have to work with some of the less “tidy” excel features. Unfortunately, I don’t know of a perfect solution for what you want (at least not for .xlsx files).
The xlsx package provides access to the Apache POI java API, which implements a lot of excel features. Not all of them are documented in the R package, but you can use rJava to call them directly.
Unfortunately, it hasn’t been updated in a while, and one feature that is missing is that with a .xlsx file, you won’t be able to preserve the existing text boxes (or images or graphs) if you want to add new ones. I believe that if the java files in the xlsxjars package were updated, you would be able to do it in .xlsx files as well. (Note that XLConnect also uses Apache POI, but I don’t think it gives access to the full API like xlsx does)
For xls files, you can add text boxes (while keeping the old ones), and here’s the code:
# If file already exists:
# wb <- loadWorkbook(file_path)
wb <- createWorkbook(type = "xls")
new_sheet <- createSheet(wb)
all_sheets <- getSheets(wb)
sheet <- all_sheets[] # First sheet
# For xlsx documents, the current version of xlsx only has
# `sheet$createDrawingPatriarch()` which will lose all
# existing text boxes and graphs in the document.
# If you're okay with that, you could replace the function below.
shapes <- sheet$getDrawingPatriarch()
# More about how to place the textbox here:
new_text_box <- shapes$createTextbox(
# More about text formatting here: (oops there was a typo here)
new_text_box$setFillColor(as.integer(255), as.integer(255), as.integer(255)) # Sets background to white