Openxlsx hyperlink output display in Excel

Question

I am trying to take in a data frame an excel sheet that has two columns,

Column A contains names of stores Column B contains the URL of those stores.

I would like to take Column A and make it a clickable hyperlink so instead of plain text, it is a hyperlink to the store website.

I have attempted to use openxlsx package to generate the correct output.

I have attempted to use the following code snip.

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)

which comes from this post of a similar nature. https://mcmap.net/q/1325337/-r-write-to-excel-with-links

My problem however is when I replace the appropriate parts of the code e.g.:

x <- df$b
names(x) <- df$a
class(x) <- "hyperlink"

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

instead of giving me a column of hyperlinks that has the store name as the output, it gives me the entire URL as the output. Is there something I am missing from my code?

I get an output that has a clickable link, but instead of the URL appearing with the name, it instead just prints out the URL.

Simp

To make this reproducible, could you share part of df with head(dput(df))? – Latini

Yes, I will get you a reprex. I need to make the dataset up. For the purposes of example, I altered what my data was. I will make a dummy set and get you a reprex. – Simp

Answer 1

An approach using openxlsx:

library(openxlsx)
library(dplyr)

# create sample data
df <- data.frame(
  site_name = c("Zero Hedge", "Free Software Foundation"),
  site_url = c("https://www.zerohedge.com", "https://www.fsf.org")
)

# add new column that manually constructs Excel hyperlink formula
# note backslash is required for quotes to appear in Excel
df <- df %>%
  mutate(
    excel_link = paste0(
      "HYPERLINK(\"",
      site_url,
      "\", \"",
      site_name,
      "\")"
    )
  )

# specify column as formula per openxlsx::writeFormula option #2
class(df$excel_link) <- "formula"

# create and write workbook
wb <- createWorkbook()
addWorksheet(wb, "df_sheet")
writeData(wb, "df_sheet", df)
saveWorkbook(wb, "wb.xlsx", overwrite = TRUE)

Avan

I like this but the link is not blue – Verada

Simple. Direct. No functions. Common packages. GLORIOUS. Thank you for posting this. Agree w B Rose that it doesn't look like a typical Excel hyperlink, but it works and is so much easier than other options in this thread or elsewhere online. – Myology

You can make the hyperlink blue by stacking classes: class(df$excel_link) <- c("formula","hyperlink") – Messinger

As an extension, you can do it all in the "mutate" function: df <- df |> mutate( excel_link = paste0("HYPERLINK(\"",site_url,"\", \"",site_name,"\")"), excel_link = `class<-`(excel_link, c("formula","hyperlink")) ) – Messinger

Answer 2

I like the current answer but for me it leaves the hyperlink as black. Here are three alternative solutions that I came up with...

1. mark the url column as a hyperlink

library(openxlsx)

# create sample data
df <- data.frame(
  site_name = c("Zero Hedge", "Free Software Foundation"),
  site_url = c("https://www.zerohedge.com", "https://www.fsf.org")
)

# create and write workbook
wb <- createWorkbook()
addWorksheet(wb, "df_sheet")

class(df$site_url)<-"hyperlink" # mark as a hyperlink
writeData(wb,"df_sheet",df$site_url,startCol = which(colnames(df)=="site_url"), startRow = 2)

writeData(wb, "df_sheet", df) # overwrite the sheet

saveWorkbook(wb, "wb.xlsx", overwrite = TRUE)

2. apply the hyperlink to the site_name column

library(openxlsx)

# create sample data
df <- data.frame(
  site_name = c("Zero Hedge", "Free Software Foundation"),
  site_url = c("https://www.zerohedge.com", "https://www.fsf.org")
)

# create and write workbook
wb <- createWorkbook()
addWorksheet(wb, "df_sheet")

class(df$site_url)<-"hyperlink" # mark as a hyperlink
writeData(wb,"df_sheet",df$site_url,startCol = which(colnames(df)=="site_name"), startRow = 2)

class(df$site_url)<-"character" # change back to a character which is optional

writeData(wb, "df_sheet", df) # overwrite the sheet to get the new pretty name overlaying the hyperlink

saveWorkbook(wb, "wb.xlsx", overwrite = TRUE)

3. new column with the link applied to the names from site_name

library(openxlsx)

# create sample data
df <- data.frame(
  site_name = c("Zero Hedge", "Free Software Foundation"),
  site_url = c("https://www.zerohedge.com", "https://www.fsf.org")
)

df$pretty_link<-df$site_name #new column for pretty link

# create and write workbook
wb <- createWorkbook()
addWorksheet(wb, "df_sheet")

class(df$site_url)<-"hyperlink" # mark as a hyperlink
writeData(wb,"df_sheet",df$site_url,startCol = which(colnames(df)=="pretty_link"), startRow = 2)

class(df$site_url)<-"character" # change back to a character which is optional

writeData(wb, "df_sheet", df) # overwrite the sheet to get the new pretty name overlaying the hyperlink

saveWorkbook(wb, "wb.xlsx", overwrite = TRUE)

Verada