如何在openxlsx的setHeaderFooter()中用str_replace实现动态日期替换

你不需要用str_replace()，直接通过动态生成字符串的方式，把全局filter_date变量插入到页脚文本中即可，这样只需要更新一次filter_date，页脚会自动同步。

核心思路 #

1. 先把filter_date格式化为你需要的日期样式（比如December 31st, 2024）
2. 将格式化后的日期与固定文本拼接，生成最终的页脚内容
3. 把拼接好的字符串传入setHeaderFooter()的footer参数

完整修改后的代码 #

方式1：Base R 实现（无需额外包） #

library(openxlsx)
library(openxlsx2)
library(dplyr) # 用于处理序数词的case_when

## Sample data
test <- as_tibble(data.frame(
  `ID` = c("1","2","3"),
  `Gender` = c("Female","Male","Non-Binary")
))

## Sets a global date filter for report
filter_date <- as.Date("2024-12-31")    

# 定义函数：给日期添加序数词（st/nd/rd/th）
add_ordinal <- function(date) {
  day <- as.integer(format(date, "%d"))
  suffix <- case_when(
    day %in% c(11,12,13) ~ "th",
    day %% 10 == 1 ~ "st",
    day %% 10 == 2 ~ "nd",
    day %% 10 == 3 ~ "rd",
    TRUE ~ "th"
  )
  paste0(format(date, "%B %d"), suffix, ", %Y") |> sprintf(date)
}

# 格式化结束日期
formatted_end_date <- add_ordinal(filter_date)

## Writing dataset to Excel
output <- createWorkbook()
addWorksheet(output, "test") 
writeData(output, "test", test, 
          startCol = 1, startRow = 1,
          colNames = TRUE, rowNames = FALSE, keepNA = TRUE) 

# 动态生成页脚文本并设置
setHeaderFooter(output,
                sheet = "test",
                header = c(NA, "test", NA),
                footer = c(paste("Data Window: June 21st, 2021 -", formatted_end_date, "unless otherwise stated"), NA, NA))

saveWorkbook(output, "test.xlsx", overwrite = TRUE)

方式2：用glue包简化字符串拼接（更直观） #

如果觉得Base R的拼接不够简洁，可以用glue包，直接在字符串中用{}引用变量：

library(openxlsx)
library(openxlsx2)
library(dplyr)
library(glue)

## Sample data
test <- as_tibble(data.frame(
  `ID` = c("1","2","3"),
  `Gender` = c("Female","Male","Non-Binary")
))

## Sets a global date filter for report
filter_date <- as.Date("2024-12-31")    

# 定义函数：给日期添加序数词
add_ordinal <- function(date) {
  day <- as.integer(format(date, "%d"))
  suffix <- case_when(
    day %in% c(11,12,13) ~ "th",
    day %% 10 == 1 ~ "st",
    day %% 10 == 2 ~ "nd",
    day %% 10 == 3 ~ "rd",
    TRUE ~ "th"
  )
  paste0(format(date, "%B %d"), suffix, ", %Y") |> sprintf(date)
}

## Writing dataset to Excel
output <- createWorkbook()
addWorksheet(output, "test") 
writeData(output, "test", test, 
          startCol = 1, startRow = 1,
          colNames = TRUE, rowNames = FALSE, keepNA = TRUE) 

# 用glue生成页脚文本
footer_text <- glue("Data Window: June 21st, 2021 - {add_ordinal(filter_date)} unless otherwise stated")

setHeaderFooter(output,
                sheet = "test",
                header = c(NA, "test", NA),
                footer = c(footer_text, NA, NA))

saveWorkbook(output, "test.xlsx", overwrite = TRUE)

说明 #

• 只需要更新filter_date变量的值，页脚的结束日期会自动同步，无需手动修改setHeaderFooter中的文本
• add_ordinal函数是为了处理日期序数词的通用情况（比如1st、2nd、3rd），如果你的报告固定使用每月最后一天，也可以直接手动格式化日期（比如format(filter_date, "%B %dst, %Y")），省去函数部分

内容的提问来源于stack exchange，提问作者Mary Rachel