Add Table to Excel File with R, without overwriting excisiting content

I'm trying to create Excel Files using a Template I created. The template is an excel file, which include "placeholder"patterns, like "##TABLE1##". Were the placeholder is, my data should be inserted.

What makes it a little difficult is, that my data can have different length, so I can't leave empty lines after the placeholder, because I can't know, how many I will need. I tried it with multiple libraries (openxlsx, xslx, XLConnect), but they all just overwrite content, coming after the placeholder. Is there maybe a possibility to automaticaly add the right amount of empty rows after the placeholder? Or is there a different solution?

If someone could help me, I would be really thankful!

Here is some example-code, of what my process looks like, with openxlsx2:

library(openxlsx2)

# Load Template to Workboak
file_name <- "template.xlsx"
sheet_name <- "sheet1"
wb <- wb_load(file_name, sheet_name, data_only = FALSE)

# Find placeholder position
data <- read_xlsx(file_name, sheet = sheet_name, skip_empty_rows = FALSE, skip_empty_cols = FALSE)
placeholder <- "##PLACEHOLDER##"
position <- which(data == placeholder, arr.ind = TRUE)
start_row <- position[1,'row'] + 1
start_col <- position[1, 'col']

# Test Table
test_table <- data.frame(
  Colum1 = c("TestValue1", "TestValue2", "TestValue3"),
  Colum2 = c(10, 20, 30)
)

# Add Data to Workbook
wb <- wb_add_data_table(wb, x = test_table, start_col = start_col, start_row = start_row)

Solution

I found a solution, which works alright. It works with the win32com client though, so I think, that it only works on Windows. But maybe it helps someone. It adds "number_of_rows"-rows after the "start_row"-row:

insert_empty_rows <- function(filename, sheet_name, start_row, number_of_rows){
  
  
  # create an instance of Excel
  excel_app <- RDCOMClient::COMCreate("Excel.Application")
  
  # hide excel
  excel_app[["Visible"]] <- FALSE
  excel_app[["DisplayAlerts"]] <- FALSE
  
  
  # open workbook
  wb_rdcom <- excel_app$Workbooks()$Open(filename)
  ws_rdcom <- wb_rdcom$Sheets(sheet_name)

  # insert lines
  for (. in 1:number_of_rows){
    ws_rdcom$Rows(start_row + 1)$Insert()
  }
  
  
  # save and close workbook
  wb_rdcom$Save()
  wb_rdcom$Close()
  excel_app$Quit()
  
  # clean up
  rm(excel_app, wb_rdcom)
  wb_rdcom <- NULL
  excel_app <- NULL
  gc()
  
}