how to open sheet using hyperlink in R

Question

I have sales data in an Excel file in that I have to open in sheet3. I tried below code but am unable to give hyperlinks to sheet3.

library(xlsx)
wb <- createWorkbook()
sheet1 <- createSheet(wb, "Sheet1")
rows <- createRow(sheet1)
cells <- createCell(rows)
links <- c("[D://r datasets/sales data.xlsx]sheet3!")
names(links) <- c("link1")
for (row in 1:length(links)) {
setCellValue(cells[[row,1]], names(links)[row])
addHyperlink(cells[[row,1]], links[row])
}
saveWorkbook(wb, "hyperlinks to file.xlsx")
shell.exec("hyperlinks to file.xlsx")

Can anyone help in this regard?

Answer 1

You can do

library(xlsx) 

wb1 <- createWorkbook() 
createSheet(wb1, "Sheet1")
createSheet(wb1, "Sheet2")
createSheet(wb1, "Sheet3")
saveWorkbook(wb1, tf1 <- tempfile(fileext = ".xlsx"))

wb2 <- createWorkbook() 
sheet2 <- createSheet(wb2, "Sheet2")
rows <- createRow(sheet2) 
cells <- createCell(rows) 
setCellValue(cells[[1,1]], "To Other File") 
addHyperlink(cells[[1,1]],sprintf("file:///%s#%s!%s", normalizePath(tf1, "/"), names(getSheets(wb1))[2], "B2"), "FILE") 
saveWorkbook(wb2, tf2 <- tempfile(fileext = ".xlsx"))
shell.exec(tf2)