Export R dataframe to Excel named range

Question

I am collating data from various sources into several dataframes. Each dataframe would have dimension R_k X C_k. I would then like to export each dataframe (identified by a unique tag say Object1_Time1) to a corresponding named range in Excel (which corresponds to the unique tag i.e. Object1_Time1). Is something like this possible using R?

I have investigated openxlsx and xlsx, and none of them appear to support this natively. Would it perhaps be an idea to somehow isolate the cell ranges of the Excel named ranges using R and then using those as start and end columns in the exporting of the dataframe?

I include a reproducible example with data below - I just have not been able to succeed yet.

The range values are calculated and stored as follows:

# Load the Excel workbook
wb <- loadWorkbook("...\\Test_Excel.xlsx")

# Get information about all named ranges
range_info <- getNamedRegions(wb)

# Extract position attribute as a vector
positions <- attr(range_info, "position")

# Drop values after (inclusive) the ":" character
positions_cleaned <- sub(":.*", "", positions)

col_start <- gsub("\\d", "", positions_cleaned)
row_start <- as.numeric(gsub("\\D", "", positions_cleaned))

# Function to convert Excel column letters to numeric values
excel_col_to_numeric <- function(col) {
  col = toupper(col)
  result = 0
  
  for (i in 1:nchar(col)) {
    result = result * 26 + (as.numeric(charToRaw(substr(col, i, i))) - as.numeric(charToRaw("A")) + 1)
  }
  
  return(result)
}


# Convert to numeric values
column_start_numeric <- lapply(col_start, excel_col_to_numeric)

The body of the code is as follows:

library(writexl)
library(openxlsx)
library(dplyr)
library(purrr)
library(MASS)
library(tidyr)

# Create some sample dataframes
set.seed(123)

# Number of time periods and countries
num_time_periods <- 10
num_countries <- 10

# Generate panel data for the first data set
panel_data_1 <- expand.grid(TimePeriod = 1:num_time_periods, Country = 1:num_countries) %>%
  mutate(Value = rnorm(n()))

# Reshape the data to wide format for the first set
panel_data_wide_1 <- spread(panel_data_1, key = TimePeriod, value = Value)

# Generate panel data for the second data set
panel_data_2 <- expand.grid(TimePeriod = 1:num_time_periods, Country = 1:num_countries) %>%
  mutate(Value = rnorm(n()))

# Reshape the data to wide format for the second set
panel_data_wide_2 <- spread(panel_data_2, key = TimePeriod, value = Value)

# Store dataframes in a list with names based on named ranges
df_list <- list(NamedRange1 = panel_data_wide_1, NamedRange2 = panel_data_wide_2)

# Export each dataframe to an EXISTING named range in Excel
write_to_excel <- function(df, sheet_name = "Panel1", range_name) {
  wb <- loadWorkbook("..\\Test_Excel.xlsx")
  # Check if the sheet exists before writing data
  if (sheet_name %in% getSheetNames(wb)) {
    writeData(wb, sheet = sheet_name, x = df, startCol = 1, startRow = 1)
    defineName(wb, name = range_name, formula = sheet_name)
    saveWorkbook(wb, "..\\Test_Excel.xlsx", overwrite = TRUE)
  } else {
    warning(paste("Sheet", sheet_name, "does not exist. Data not exported."))
  }
}

# Iterate over the list and export each dataframe
walk(df_list, ~write_to_excel(.x, range_name = .y))

This is an exemplar of the sheet in Excel I want to paste to with named ranges extending across green shaded areas.

Excel sheet