Creating and saving multiple .xlsx files using for loop openxlsx

I am attempting to create save multiple formatted Excel files, each of which are subsetted from a certain data frame by a factor.

This is an example of what I have tried so far

# Create data
df <- data.frame(category = rep(c("a","b","c","d"),times = 20),
                 values = rnorm(20,5,2))

# Create workbooks named after specific level of factor
l1 <- sapply(levels(df$category), assign, value = createWorkbook())

# Create styles
hs <- createStyle(fgFill = "#808080", border = "bottom", textDecoration = "bold")
lt8 <- createStyle(bgFill = "#ff0000")
gt30 <- createStyle(bgFill = "#00b0f0")
grn <- createStyle(bgFill = "#00b000")

# For loop
for (i in l1) {
  addWorksheet(i, names(i))
  writeData(i, names(i), df[df$category == names(i),], headerStyle = hs)

  conditionalFormatting(i, names(i), cols = 1:2, rows = 2:nrow(df[df$category == names(i),]), rule = "$B2<2", type = "expression", style = lt8)
  conditionalFormatting(i, names(i), cols = 1:2, rows = 2:nrow(df[df$category == names(i),]), rule = "$B2>=7", type = "expression", style = gt30)
  conditionalFormatting(i, names(i), cols = 1:2, rows = 2:nrow(df[df$category == names(i),]), rule = "AND($B2>=4, $B2<5.5)", style = grn)
  setColWidths(i, names(i), cols=1:2, widths = "auto")

  saveWorkbook(paste(i, ".wb", sep = ""), file = paste(i, " Report ", ".xlsx", sep = ""))
}

Each time, I run into this error Error in if (tolower(sheetName) %in% tolower(wb$sheet_names)) stop("A worksheet by that name already exists! Sheet names must be unique case-insensitive.")

This is the first time I've attempted to assign any sheets so I'm not exactly sure why I keep getting this error.

Ultimately, I would like to save the subsetted and formatted excel workbooks through a repetitive process because my real data would produce many more workbooks. The workbooks must be separate and placing these subsets in sheets won't work.

Any and all advice on how to achieve this would be greatly appreciated.

Solution

Your error is coming from this line:

addWorksheet(i, names(i))

because names(i) is empty:

> names(l1[['a']])
character(0)

You might be better off looping over the names of l1, so you have the categories you want, using that to pull the appropriate workbook from the list. Something like:

for (i in names(l1)) {
  wb = l1[[i]]
  addWorksheet(wb, i)
  category_data <- df[df$category == i,]
  writeData(wb, i, category_data, headerStyle = hs)

  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "$B2<2", type = "expression", style = lt8)
  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "$B2>=7", type = "expression", style = gt30)
  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "AND($B2>=4, $B2<5.5)", style = grn)
  setColWidths(wb, i, cols=1:2, widths = "auto")

  saveWorkbook(wb, file = paste(i, " Report ", ".xlsx", sep = ""))
}

There's still one subtle error here:

l1 <- sapply(levels(df$category), assign, value = createWorkbook())

createWorkbook() is only being called once, so you have 4 copies of the same workbook. That means the final save will have all 4 tabs. Compare:

> identical(l1$a, l1$b)
[1] TRUE

with 2 separate calls to createWorkbook():

> identical(createWorkbook(), createWorkbook())
[1] FALSE

Might be worth just looping over the distinct categories, and creating the workbook inside the loop. That is:

library(openxlsx)

# Create data
df <- data.frame(category = rep(c("a","b","c","d"),times = 20),
                 values = rnorm(20,5,2))

# Create styles
hs <- createStyle(fgFill = "#808080", border = "bottom", textDecoration = "bold")
lt8 <- createStyle(bgFill = "#ff0000")
gt30 <- createStyle(bgFill = "#00b0f0")
grn <- createStyle(bgFill = "#00b000")

# For loop
for (i in levels(df$category)) {
  wb <- createWorkbook()
  addWorksheet(wb, i)
  category_data <- df[df$category == i,]
  writeData(wb, i, category_data, headerStyle = hs)

  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "$B2<2", type = "expression", style = lt8)
  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "$B2>=7", type = "expression", style = gt30)
  conditionalFormatting(wb, i, cols = 1:2, rows = 2:nrow(category_data), rule = "AND($B2>=4, $B2<5.5)", style = grn)
  setColWidths(wb, i, cols=1:2, widths = "auto")

  saveWorkbook(wb, file = paste(i, " Report ", ".xlsx", sep = ""))
}