CODEWITHSUNDEEP
excelreplaceopenxlsxopenxlsx2
Mary Rachel
Mary Rachel

Reputation: 309

Replacing a string when writing to setHeaderFooter() in openxlsx

I am writing datasets to Excel using the openxlsx and openxlsx2 packages. The report generated from this code has to be updated on a monthly basis, so the header/footer annotations need to reflect updated data validity windows. I was manually updating the text that was being written to the setHeaderFooter function, but I would often forget to do that and then have to re-run the report.

My code includes a "global date filter", where I picked one date and assigned it to a variable, then used that variable to stand in when filtering datasets so I only had to update the date once in the script before running the report, reducing errors and saving me time. I want to use str_replace() in the setHeaderFooter function to call my global date filter value into the text annotations for my Excel file. I know how to use this function in dplyr, but I was unsure how to write it for a base R function nested within an argument (setHeaderFooter(...footer=c("..."))).

# Sample Code

library(openxlsx)
library(openxlsx2)

## 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")    


## Writing dataset to Excel

### opens an active workbook for binding
output<-createWorkbook()

### adds a sheet to the active workbook
addWorksheet(output, "test") 

### writes out a dataframe to the sheet
writeData(output, "test", test, 
          startCol = 1,
          startRow = 1,
          colNames = TRUE,
          rowNames = FALSE,
          keepNA = TRUE) 

### sets the header and footer values, requires manual updating of the ending date
setHeaderFooter(output,
                sheet = "test",
                header = c(NA, "test", NA),
                footer = c("Data Window: June 21st, 2021 - December 31st, 2024 unless otherwise stated", NA, NA))

### saves the workbook to the destination folder
saveWorkbook(output, "test.xlsx", overwrite = TRUE)

Upvotes: 0

Views: 19

Answers (1)

Mary Rachel
Mary Rachel

Reputation: 309

Thankfully, the answer is fairly straightforward. We can designate "dummy" text in the footer's text string ("TODAY"), then call str_replace() within the footer argument, using the original vector called to write the footer as the string argument, the "dummy' text as the pattern argument, and the variable that serves as a global date filter as the replacement argument. The global date filter variable has to be nested within a format() function, otherwise it will throw an error.

## Sets a global date filter for report

filter_date<-as.Date("2024-12-31")    

### Original header and footer code (requires manual updating of the ending date)
setHeaderFooter(output,
                sheet = "test",
                header = c(NA, "test", NA),
                footer = c("Data Window: June 21st, 2021 - December 31st, 2024 unless otherwise stated", NA, NA))

### New header and footer code that updates with the global date filter

setHeaderFooter(output,
                sheet = "Report Notes",
                header = c(NA, "Report Notes", NA),
                footer = str_replace(c("Data Window: June 21st, 2021 - TODAY unless otherwise stated", NA, NA),
                                     "TODAY",
                                     format(filter_date,"%B %d, %Y")))

Upvotes: 0

Related Questions