How to add multiple comments to excel workbook using openxlsx?

user_6936545 0
Viewed 235

The createComment openxlsx function description states that the comment can be a "Character vector" and hence applied to multiple cells.

However, adding a comment seems to be limited to one cell.

Is there a way to add multiple comments to a range of cells using a character vector?

Have I missed something or is this an enhancement request?

This MWE fails with the following message: "Error in comment_list[[i]]$style[[j]] : subscript out of bounds"

library(openxlsx)

x <- as.data.frame(matrix(1:12, nrow = 3))
names(x) <- letters[1:4]

comment_vector <- paste("Comment", 1:4)

wb <- createWorkbook()
addWorksheet(wb, 1)

header_comments <- createComment(comment = comment_vector)
writeComment(wb, 1, col = 1:4, row = 1, comment = header_comments)

writeData(wb, sheet = 1, startRow = 1, x)

saveWorkbook(wb, "muliple_comments.xlsx", overwrite = TRUE)

This works, but there must be a better way...


header1_comment <- createComment(comment = comment_vector[1])
writeComment(wb, 1, col = 1, row = 1, comment = header1_comment)

header2_comment <- createComment(comment = comment_vector[2])
writeComment(wb, 1, col = 2, row = 1, comment = header2_comment)

header3_comment <- createComment(comment = comment_vector[3])
writeComment(wb, 1, col = 3, row = 1, comment = header3_comment)

header4_comment <- createComment(comment = comment_vector[4])
writeComment(wb, 1, col = 4, row = 1, comment = header4_comment)

writeData(wb, sheet = 1, startRow = 1, x)

saveWorkbook(wb, "05_muliple_comments.xlsx", overwrite = TRUE)

To produce: enter image description here

openxlsx r
1 Answers

I don't think createComment allows comments in multiple cells, the character vector component is there to apply multiple styles as per the example in ?createComment.

Probably the easiest way to get around this is to use lapply or for loop.

lapply(1:4, FUN = function(x) writeComment(wb, 1, col = x, row = 1, comment = createComment(comment = comment_vector[x])))

So in your example provided it will be something like:

library(openxlsx)

x <- as.data.frame(matrix(1:12, nrow = 3))
names(x) <- letters[1:4]
comment_vector <- paste("Comment", 1:4)

wb <- createWorkbook()
addWorksheet(wb, 1)

# Add multiple comments
lapply(1:4, FUN = function(x) writeComment(wb, 1, col = x, row = 1, comment = createComment(comment = comment_vector[x])))

writeData(wb, sheet = 1, startRow = 1, x)
saveWorkbook(wb, "muliple_comments.xlsx", overwrite = TRUE)
user_28571060
Related