How do we apply openxlsx formatting when using purrr to output multiple files on multiple tabs?

I am trying to create formats in excel output files. In my example, I would like cost 1 and cost 2 to be formatted as dollars and I would like to add the customer to Cell A 1 in the excel output files. Below is my attempt. Any tips?

library(tidyverse)
library(openxlsx)


df.1 <- tribble(
  ~customer  ,~period, ~cost1, ~cost2 , ~prod,
  'cust1',  '202201', 5, 10, 'online',
  'cust1',  '202202', 5, 10, 'online',
  'cust1',  '202203', 5, 10, 'in-person',
  'cust1',  '202204', 5, 10, 'in-person',
  'cust2',  '202203', 5, 10,'online',
  'cust2',  '202204', 5, 10, 'in-person',
  'cust2',  '202202', 5, 10, 'online',
  'cust3',  '202204', 5, 10, 'online',
  'cust4',  '202101', NA, NA, 'online',
  'cust4',  '202102', NA,10, 'online'
  
)


df.1 %>% 
  split(.$customer) %>%
  iwalk(\(df, nm) {
    df %>%
      janitor::remove_empty(which = 'cols') %>% 
      split(.$prod) |> 
      write.xlsx(addStyle(cols = 3:4, rows = 1:nrow(), style = createStyle(numFmt = "0.0%")) ## make cost 1 and cost 2 dollar format
                 ,writeData(paste0('Client Name: ', customer), startCol = 1, startRow = 3) # add the customer to row 1 column 1 , cell A1 in excel
                 ,paste0(nm, ".xlsx"), startRow = 6)
  })

Quite interesting piece of code. write.xlsx is meant for a quick export of a data.frame or list of data.frames to xlsx.

If you require additional formatting or want to add additional data you have to directly manipulate the workbook object, i.e. both addStyle and writeData require a workbook object as their first argument.

For that reason in the code below I use the lower level functions to create a workbook, to add the worksheets, to write the data, to apply your desired styles and to finally save the workbook. Doing so requires a second iwalk (or ...) where for convenience I make use of a custom function:

library(dplyr)
library(openxlsx)

export_sheet <- function(wb, customer, data, sheet) {
  addWorksheet(wb, sheet)
  writeData(
    wb,
    paste0("Client Name: ", customer),
    sheet = sheet,
    startCol = 1, startRow = 3
  )
  writeData(wb, data, sheet = sheet, startRow = 6)
  addStyle(wb, sheet,
    cols = 3,
    rows = 6 + seq_len(nrow(data)),
    style = createStyle(numFmt = "0.0%")
  )
  addStyle(wb, sheet,
    cols = 4,
    rows = 6 + seq_len(nrow(data)),
    style = createStyle(numFmt = "0.0%")
  )
}

df.1 %>%
  split(.$customer) %>%
  iwalk(\(df, nm) {
    wb <- createWorkbook()
    df %>%
      janitor::remove_empty(which = "cols") %>%
      split(.$prod) |>
      iwalk(
        ~ export_sheet(wb = wb, customer = nm, data = .x, sheet = .y)
      )

    saveWorkbook(wb, paste0(nm, ".xlsx"), overwrite = TRUE)
  })