Apply Excel Styling Across Multiple Spreadsheets using openxlsx in R?

Question

I have an example where I write multiple individual spreadsheets, I would like to style each spreadsheet but expanding the column width, formatting it as a table, and making the header background blue:

Creation of Multiple Spreadsheets on Parameter Date

df <- data.frame(Date = Sys.Date() - 0:4, Logical = c(TRUE, FALSE, TRUE, TRUE, FALSE),
                 Currency = paste("$", -2:2), Accounting = -2:2, hLink = "https://CRAN.R-project.org/",
                 Percentage = seq(-1, 1, length.out = 5), TinyNumber = runif(5)/1e+09, stringsAsFactors = FALSE)

dat_grouped <- df %>% group_by(Date)


lapply(group_split(dat_grouped), function(x){openxlsx::write.xlsx(x, paste0(x$Date[1], ".xlsx"))})

How can I Apply Styling to Each Individual Spreadsheet - Example of Ideal Output

Where the header is light blue and columns are widened.

Answer 1

This should be enough to get you started. Note: I changed the Currency variable, keeping it as numeric. When you paste "$" it becomes text. Also, I split the data frame using split instead of group_split:

library(dplyr)
library(openxlsx)
library(purrr)

df <- data.frame(Date = Sys.Date() - 0:4, Logical = c(TRUE, FALSE, TRUE, TRUE, FALSE),
                 Currency = -2:2, Accounting = -2:2, hLink = "https://CRAN.R-project.org/",
                 Percentage = seq(-1, 1, length.out = 5), TinyNumber = runif(5)/1e+09, stringsAsFactors = FALSE)

dat_grouped <- df %>% split(~ Date)


# Create styles for each format you need
blue_white <- createStyle(fgFill = "#03a9f4", fontColour = "white")
pct <- createStyle(numFmt = "PERCENTAGE")
currency <- createStyle(numFmt = "CURRENCY")
round2 <- createStyle(numFmt = "0.000000000")

wb <- write.xlsx(dat_grouped, "output.xlsx")
sheets <- seq_along(dat_grouped)

# Apply formatting
walk2(sheets, dat_grouped, ~ addStyle(wb, .x, pct, cols = 6, rows = 2:nrow(.y)))
walk2(sheets, dat_grouped, ~ addStyle(wb, .x, currency, cols = 3, rows = 2:nrow(.y)))
walk2(sheets, dat_grouped, ~ addStyle(wb, .x, round2, cols = 7, rows = 2:nrow(.y)))
walk(sheets, ~ addStyle(wb, .x, blue_white, row = 1, cols = seq_len(ncol(df))))
walk(sheets, ~ setColWidths(wb, .x, cols = seq_len(ncol(df)), widths = "auto"))
saveWorkbook(wb, "output.xlsx", overwrite = TRUE)

Output