13 Comments and Working with Shapes in openxlsx2

13.1 Adding Comments

Comments in Excel are useful for annotating cells with additional information. Using openxlsx2, we can create comments and we can even create, reply to, and close threads programmatically. Threads are a feature introduced in MS365 and replace the “comment”, while legacy comments are now called “note”. We use comment and thread (their names in the XML code), but each their own.

13.1.1 Creating a Comment

wb <- wb_workbook()$
  add_worksheet()

# Add a comment to cell A1
wb$add_comment(
  dims = "B2",
  comment = "This is a sample comment."
)

It is possible to style the comment, the manual page provides a few examples of this. openxlsx2 provides additional niche features such as background images for comments.¹ For this we are going to remove this previous comment.

# eh what was it again?
wb$get_comment(dims = "B2")
#>   ref author                              comment cmmt_id
#> 1  B2 runner runner: \n This is a sample comment.       1

# okay, sample comment. can be removed
wb$remove_comment(dims = "B2")

13.1.2 Comments with background images

tmp <- tempfile(fileext = ".png")
png(file = tmp, bg = "transparent")
plot(1:10)
rect(1, 5, 3, 7, col = "white")
dev.off()
#> png 
#>   2

c1 <- wb_comment(text = "this is a comment", author = "", visible = TRUE)
wb$add_comment(dims = "B12", comment = c1, file = tmp)

13.2 Working with Threads

In its foundation a comment is just some text in a quadratic shape. Usually it contains some auther information, but this is entirely optional, it could also be just some fictional text or the authors name can be removed entirely. It is also quite complex to reply to a comment. For this, threads were invented. A thread is something similar to a chat or mail chain. It is created chronologically and it has a person attached to it. It is possible to answer to a thread and to close it. As in, the question was solved, but it is left for everyone to see.

In spreadsheet software that does not support threads, a comment is shown with the information content of the thread and a hint that the comment should not be altered.

13.2.1 Persons, create one or become one

To create a comment, you need to be a person assigned with the worksheet. Persons could be corporate accounts with specific Ids (you need to import a worksheet with such an id). Afterwards you can get the persons attached to the worksheet with wb_get_person(). While there is an id attached to the person, it is not different compared to an email username. It can be spoofed, and basically, if you select your id from the list of available names, please consider if your company finds it as hilarious as you do, if you decide to create, open or answer threads as someone else. For now, we will create two persons.

wb <- wb_workbook()$add_worksheet()
# Add a person to the workbook.
wb$add_person(name = "somebody")
wb$add_person(name = "you")

Now we want to create a thread as "you". The id pid itself is rather uninteresting, it is a guid, similar to many others used in openxlsx2.

pid <- wb$get_person(name = "you")$id

13.2.2 Creating a Thread

And that’s it. Now we can create a thread as you.

wb$add_thread(dims = "A1", comment = "wow it works!", person_id = pid)
wb$add_thread(dims = "A2", comment = "indeed", person_id = pid, resolve = TRUE)
wb$add_thread(dims = "A1", comment = "so cool", person_id = pid, reply = TRUE)

13.3 Working with Shape Objects

Besides comments and notes, openxlsx2 allows for the addition of shapes, such as rectangles, circles, and other graphical elements, to a worksheet.

13.3.1 Adding a Rectangle Shape

If you are wondering why the section about comment and threads is in the same section as shapes, after all comments are something like a rectangular shape with a text.

rect <- create_shape(
  shape = "rect", text_align = "center",
  text = fmt_txt("I want to become a comment!", font = "Tahoma", size = "10",
                 color = wb_color("black"), family = 2),
  fill_colour = wb_color(hex = "ffffe1"),
  line_color = wb_color("black"),
  line_transparency = 50
)

wb <- wb_workbook()$add_worksheet()$
   add_drawing(dims = "B2:C5", xml = rect)

As seen it is possible to assign fmt_txt() strings to shape objects. And given some trial and error it is even possible to create complex images with create_shape() objects.

## heart
txt <- fmt_txt("openxlsx2 is the \n", bold = TRUE, size = 15) +
  fmt_txt("best", underline = TRUE, bold = TRUE, size = 15) +
  fmt_txt("\n!", bold = TRUE, size = 15)

heart <- create_shape(
  shape = "heart", text = txt, text_align = "center",
  fill_colour = wb_color("pink"), text_colour = wb_color("red"))

## ribbon
txt <- fmt_txt("\nthe ") +
  fmt_txt("very", underline = TRUE, font = "Courier", color = wb_color("gold")) +
  fmt_txt(" best")

ribbon <- create_shape(shape = "ribbon", text = txt, text_align = "center")

wb <- wb_workbook()$add_worksheet(grid_lines = FALSE)$
  add_drawing(dims = "B2:E11", xml = heart)$
  add_drawing(dims = "B12:E14", xml = ribbon)$
  add_worksheet()$add_drawing(dims = "B2:E5", xml = create_shape("rect", text = txt, fill_color = wb_color(theme = 5), fill_transparency =  50))

Since there is no dialog option for this on MS365 for Mac, I was not even sure what the user requesting this feature, was even talking about.↩︎