makeHyperlinkString: create Excel hyperlink string

In openxlsx: Read, Write and Edit xlsx Files

create Excel hyperlink string

Description

Wrapper to create internal hyperlink string to pass to writeFormula(). Either link to external urls or local files or straight to cells of local Excel sheets.

Usage

makeHyperlinkString(sheet, row = 1, col = 1, text = NULL, file = NULL)

Arguments

sheet	Name of a worksheet
row	integer row number for hyperlink to link to
col	column number of letter for hyperlink to link to
text	display text
file	Excel file name to point to. If NULL hyperlink is internal.

See Also

writeFormula()

Examples

## Writing internal hyperlinks
wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
addWorksheet(wb, "Sheet2")
addWorksheet(wb, "Sheet 3")
writeData(wb, sheet = 3, x = iris)

## External Hyperlink
x <- c("https://www.google.com", "https://www.google.com.au")
names(x) <- c("google", "google Aus")
class(x) <- "hyperlink"

writeData(wb, sheet = 1, x = x, startCol = 10)


## Internal Hyperlink - create hyperlink formula manually
writeFormula(
  wb, "Sheet1",
  x = '=HYPERLINK(\"#Sheet2!B3\", "Text to Display - Link to Sheet2")',
  startCol = 3
)

## Internal - No text to display using makeHyperlinkString() function
writeFormula(
  wb, "Sheet1",
  startRow = 1,
  x = makeHyperlinkString(sheet = "Sheet 3", row = 1, col = 2)
)

## Internal - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 2,
  x = makeHyperlinkString(
    sheet = "Sheet 3", row = 1, col = 2,
    text = "Link to Sheet 3"
  )
)

## Link to file - No text to display
writeFormula(
  wb, "Sheet1",
  startRow = 4,
  x = makeHyperlinkString(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "loadExample.xlsx", package = "openxlsx")
  )
)

## Link to file - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 3,
  x = makeHyperlinkString(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "loadExample.xlsx", package = "openxlsx"),
    text = "Link to File."
  )
)

## Link to external file - Text to display
writeFormula(
  wb, "Sheet1",
  startRow = 10, startCol = 1,
  x = '=HYPERLINK("[C:/Users]", "Link to an external file")'
)

## Link to internal file
x = makeHyperlinkString(text = "test.png", file = "D:/somepath/somepicture.png")
writeFormula(wb, "Sheet1", startRow = 11, startCol = 1, x = x)

## Not run: 
saveWorkbook(wb, "internalHyperlinks.xlsx", overwrite = TRUE)

## End(Not run)

openxlsx documentation built on Sept. 20, 2024, 5:08 p.m.