Read in single xlsx file, perform conditional formatting and export as as multiple xlsx files in R

Question

initial dataset:

df = data.frame(Division=c("ASIA","ASIA","ASIA","ASIA","ASIA","EUROPE","EUROPE","EUROPE"),
 Country=c("India","China","Japan","Nepal","Laos","France","Italy","Norway"),
 improvement=c(1,3,7,5,9,8,2,7))

I am able to read this xlsx file into R, and I want to be able to fo the following:

1. Highlight any row where the Improvement value is lesser than 5.
2. Export the data into different xlsx files based on the Division names. Example, the first xlsx file exported will have only the first 5 rows of the data in df, with India and china highlighted and the other file exported will have 3 rows from Europe with Italy highlighted.

Any pointers will be appreciated!!

Answer 1

You could first create a function using openxlsx to create the workbook.
Then you could split the dataset by division and use map to call the workbook creation over the divisions:

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

threshold <- 6

create.workbook <- function(df,threshold) {
 wb <- createWorkbook() 
 highlight.Style <- createStyle(fontColour = "red", fgFill = "yellow")
 addWorksheet(wb, "data")
 writeData(wb, sheet = "data", x = df)
 highlight.rows <- which(df$improvement < threshold)+1
 addStyle(wb, "data", cols = 1:ncol(df), rows = highlight.rows,
          style = highlight.Style, gridExpand = TRUE)
 
 wb.name <- df$Division[1]
 saveWorkbook(wb,paste0(wb.name,'.xlsx'),overwrite = T)
}

df %>% split(.$Division) %>% map(~create.workbook(.x,threshold))

$ASIA
[1] 1

$EUROPE
[1] 1