Working with Excel from R

Link: R

Export §

Export worksheet to an existing Excel workbook §

# 4. Export --------------------------------------------------------------
library(openxlsx)
 
wb <- loadWorkbook("Export.xlsx")
addWorksheet(wb, sheetName = 'Data')
writeData(wb, sheet = "Data", df)
saveWorkbook(wb,"Export.xlsx",overwrite = T)

Export to Excel with current date or quarter §

# Export with current date and time
write.xlsx(df, file = paste0("Output/FileName_", format(Sys.time(), format="%Y-%b-%d-%H-%M"), ".xlsx"))
 
# Export with current quarter
# Set reporting quarter number: FY starts from November
qtr <- paste0("Q", quarter(today()-30, fiscal_start = 11))
print(qtr)
 
# Function to generate file path as per naming convention eg.FileNameQ3_20210812_v1521.xlsx
path_func <- function(x) {
  path <-  paste0("Output/", x, qtr, "_", format(Sys.Date(), "%Y%m%d"), "_v", format(Sys.time(), "%H%M"), ".xlsx")
  return(path)
 
# Example: Q1
write.xlsx(df, file = path_func("FileName"))

Replace . In column names with space §

colnames(df) <- gsub("\\.", " ", colnames(df))
 
## Export to excel with multiple worksheets
list_of_datasets <- list("Sheet1" = df1,
                         "Sheet2" = df2,
                         "Sheet3" = df3)
 
write.xlsx(list_of_datasets, file = "file_name.xlsx")

Generate report file for each group §

## Generate a subset as report file for each value in df$group, each file is named with the corresponding value
for (i in c(unique(df$group_name))) {
  df %>% select(Application.Code, Application.Name, group_name) %>%
    filter(group_name == i) %>%
    write.xlsx(paste0("Output/", i, ".xlsx"))
}

Copy to clipboard for Excel §

• For vector: writeClipboard(x)
• For data frame: clipr::write_clip(x)