Learn R Programming

openxlsx2 (version 0.8)

create_hyperlink: Create Excel hyperlink string

Description

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

Usage

create_hyperlink(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.

Examples

Run this code

## Writing internal hyperlinks
wb <- wb_workbook()
wb$add_worksheet("Sheet1")
wb$add_worksheet("Sheet2")
wb$add_worksheet("Sheet 3")
wb$add_data(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"

wb$add_data(sheet = 1, x = x, startCol = 10)


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

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

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

## Link to file - No text to display
write_formula(
  wb, "Sheet1",
  startRow = 4,
  x = create_hyperlink(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2")
  )
)

## Link to file - Text to display
write_formula(
  wb, "Sheet1",
  startRow = 3,
  x = create_hyperlink(
    sheet = "testing", row = 3, col = 10,
    file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"),
    text = "Link to File."
  )
)

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

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

Run the code above in your browser using DataLab