r语言 - Excel中的Openxlsx超链接输出显示

Question

我正在尝试在数据框中获取具有两列的 excel 表，

列 A 包含商店的名称 B 列包含这些存储的 URL。

我想将 A 列设置为可点击的超链接，而不是纯文本，而是指向商店网站的超链接。

我尝试使用 openxlsx 包来生成正确的输出。

我尝试使用以下代码截图。

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)

它来自这个类似性质的帖子。 https://stackoverflow.com/a/48973469/11958444

但是，我的问题是当我替换代码的适当部分时，例如：

x <- df$b
names(x) <- df$a
class(x) <- "hyperlink"
writeData(wb, sheet = 1, x = x, startCol = 10)

它不是给我一列以商店名称作为输出的超链接，而是给我整个 URL 作为输出。我的代码中缺少什么吗？

我得到一个具有可点击链接的输出，但它不是与名称一起显示的 URL，而是打印出 URL。

Answer 1

一种使用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)

Answer 2

我喜欢当前的答案，但对我来说，它将超链接保留为黑色。以下是我想出的三种替代解决方案...

1. 将 URL 列标记为超链接

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. 将超链接应用于"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")
)
# 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. 新列，其中链接应用于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)