library(openxlsx2)
wb <- wb_workbook()$
add_worksheet()
# Add a comment to cell A1
wb$add_comment(
dims = "B2",
comment = "This is a sample comment."
)13 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. These are a feature introduced in MS365 and is called “comment”, while comments are called “note”. We use to comment and thread, but each their own.
13.1.1 Creating a 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.[^1][^1: 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.] 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.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")$id13.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
rect <- create_shape(
shape = "rect", text_align = "center",
fill_colour = wb_color("yellow")
)
13.1.2 Comments with background images