Search code examples
ropenxls

Setting up a cell-to-cell hyperlink

I would like to know if it was possible to set up a link that redirects me to cell id1 sheet b (A1, B1, ect.) when I click on cell 1 sheet a (A2, A3, ect.) ?

(Or must better create a link from line 2,3,ect. sheet a to column A,B,ect. sheet b ) 

library(openxlsx)

a <- data.frame("id" = 1:2, "val" = paste0(1:2, "a"))
b <- data.frame("id1" = 1:5, "id2" = 11:15)

sheets <- list(
  "a" = "a",
  "b" = "b")

wb <- openxlsx::createWorkbook()
addWorksheet(wb, sheets$a)
addWorksheet(wb, sheets$b)

writeData(wb, sheet = sheets$a, x = a, startRow = 1)
writeData(wb, sheet = sheets$b, x = b, startRow = 1)

saveWorkbook(wb, "inst/test/file.xlsx", overwrite = T)
Solution

  • After you have written your data, but before you save the workbook, do this:

    for(i in seq_along(a$id))
{
  link <- makeHyperlinkString(sheets$b, row = 1, col = i, text = a$id[i])
  writeFormula(wb, sheets$a, link, startCol = 1, startRow = i + 1)
}