Conditionally Commenting with OpenXLSX

Question

I would like to add a comment on a cell only if certain conditions are met (the cell isn't empty and a different column contains specific text) using OpenXLSX in R. Not sure if this is possible.

I can, of course, conditionally apply a style based on these conditions:

Name<- c("Bob", "Fred", "Smith", "Henry", "Joe")
Other<- 1:5
df<- data.frame(Name, Other)

wb <- createWorkbook()
addWorksheet(wb, "Names")
writeData(wb, 1, df, startRow = 1, startCol=1, colNames = F)


West<- createStyle(bgFill = "#DCE6F1")
conditionalFormatting(wb, 1, cols = 2, rows = 1:5, rule = 'AND($A1 == "Smith", $B1<>"")', style = West)

But, as expected, trying to use this in combination with writeComment just returns an error that style must be a Style object:

conditionalFormatting(wb, 1, col = 2, rows = 1:5, rule = 'AND($A1 == "Smith", $B1<>"")', style = writeComment(wb, 1, col = "B", row = which(df$Name == "Smith"), comment = createComment(comment = "Test Comment")))

Is there any way to do this?

Answer 1

You could achieve this by determining the rows which qualify for a comment com_row outside openxlsx function; then use com_row within the writeComment function.

library(openxlsx)

Name<- c("Bob", "Fred", "Smith", "Henry", "Joe")
Other<- 1:5
df<- data.frame(Name, Other)

wb <- createWorkbook()
addWorksheet(wb, "Names")
writeData(wb, 1, df, startRow = 1, startCol=1, colNames = F)


West<- createStyle(bgFill = "#DCE6F1")
conditionalFormatting(wb, 1, cols = 2, rows = 1:5, rule = 'AND($A1 == "Smith", $B1<>"")', style = West)

com1 <- createComment(comment = "Test Comment")
com_row <- which(df$Name == "Smith" & df$Other != "")

# wrap `writeComment` function in an if statement to deal with case 
# where there are no names = "Smith" and NAs in Other. This does away
# with the case where `which` evaluates to `integer(0)` when there
# are no elements which are indexed.

if(length(com_row) > 0){
  
  writeComment(wb, 1, col = 2, row = com_row, comment = com1)
  
}


saveWorkbook(wb, file = "condition_and_comment_eg.xlsx", overwrite = TRUE)

Which results in:

^{Created on 2023-06-07 with reprex v2.0.2}

Test data frame

For case when no rows satisfy the conditions.

Name<- c("Bob", "Fred", "Noname", "Henry", "Joe")
Other<- c(1:2, NA_real_, 4:5)

Workbook output for data frame with no row meeting conditions: