Background image to comment fails after 2 comments

[m-delx]

Hi,

I have tried out the recent update which enables putting images as a background to the comments. However, I encounter an error after trying to add a third such comment.

Namely, when I add two such comments, the xlsx file opens without problems and shows the comments are promised. But after adding a third comment, Excel encounters a problem, and offers a recovery of the file, which copies the image from the second comment to all further comments. However, the text inside each comment stays correct.

I have made the following reproduceable example based on the code from the pull request. Hopefully it's not just a problem with my laptop :)

library(openxlsx2)

saveto <- r"(some_saving_path)"

x <- data.frame(names=c("f","g","h"), a=c(1,2,3), b=c(1,2,3), c=c(1,2,3))

wb <- wb_workbook("Workbook1")

wb$add_worksheet(sheet = "Name1", grid_lines = TRUE)
wb$add_data_table(sheet = "Name1", x = x, row_names = FALSE, table_style = "TableStyleMedium2")

tmp <- tempfile(fileext = ".png")
png(file = tmp, bg = "transparent")
plot(1:10)
rect(1, 5, 3, 7, col = "white")
dev.off()
c1 <- wb_comment(text = "Comm1", author = "", visible = TRUE)
wb$add_comment(dims = "A2", comment = c1, file = tmp)

tmp <- tempfile(fileext = ".png")
png(file = tmp, bg = "transparent")
plot(1:10)
rect(1, 5, 3, 7, col = "red")
dev.off()
c2 <- wb_comment(text = "Comm2", author = "", visible = TRUE)
wb$add_comment(dims = "A3", comment = c2, file = tmp)

tmp <- tempfile(fileext = ".png")
png(file = tmp, bg = "transparent")
plot(1:10)
rect(1, 5, 3, 7, col = "blue")
dev.off()
c3 <- wb_comment(text = "Comm3", author = "", visible = TRUE)
wb$add_comment(dims = "A4", comment = c3, file = tmp)

wb_save(wb, file=paste0(saveto, "\\WB1.xlsx"), overwrite = T)

[JanMarvin]

Hi @m-delx , thanks for the report! Unfortunately it looks like I only tested one and two comments ...

Could you have a look if #919 solves the issue for you? Something like:

remotes::install_github("JanMarvin/openxlsx2#919")

should install the development branch. Thanks!

[Edit:] fixed typo ... its #919 not #918

[m-delx]

Thank you for the fast response and a quick fix. The commit solved the problem for the example above, and it also worked well for my use case which creates several thousand such comments (however, it did take 15 minutes to complete the task...).

[JanMarvin]

Yeah, but imagine doing this by manually. Saved you a few weeks :)

[JanMarvin]

Just as a note:

• pngs are copied to the file in full resolution. So if you make a high resolution png and add it to a comment, it will be copied unchanged into the xlsx file. If you are only looking at small comments, then I recommend reducing the size of the pngs. Several thousand files can greatly increase the size of the xlsx file.
• The wb_add_comment() function has not been fine-tuned to work well with several thousand comments. There might be something in there that slows down the process because I usually write only one or two comments with it, not thousands. So if you're willing to go down that rabbit hole and want to (a) profile the function to identify bottle necks, (b) improve it and (c) show that a PR is actually faster, I'd be happy to review a PR. However, I spent about 12 hours last weekend trying to speed up the writing process, resulting in a microsecond time gain (in two PR that I ultimately discarded), and it's unlikely I'll try to invest the same time for the comment function. After all, 15 minutes isn't so bad if you don't have to do this process every quarter of an hour 😉
• Unaware of your specific task: openxlsx2 also supports sparklines, which might be worth a look? This of course assumes that all the data is in the xlsx file (unlike the comment solution your currently working with, especially - and I'm sure I don't have to tell you - nobody is going to watch thousand comments in a spreadsheet).