admin 管理员组

文章数量: 1086019

I would like to create an Excel sheet which has columns containing hyperlinks and missing values (NA's).

library(openxlsx2)
options(openxlsx2.na.strings = "")

dat <- data.frame(link = c("/", "/", NA, NA),
                  custom_text_link = c("Wikipedia", "Google", NA, NA))
class(dat$link) <- "hyperlink"

wb_workbook() %>% 
  wb_add_worksheet(sheet = "dat") %>% 
  wb_add_data(sheet = 1, x = dat) %>% 
  wb_add_hyperlink(sheet = 1,
                   dims = wb_dims(rows = which(!is.na(dat$link)) + 1,
                                  cols = which(colnames(dat) == "custom_text_link")),
                   target = dat$link[!is.na(dat$link)]) %>%
  wb_open()

Created on 2025-03-27 with reprex v2.1.1

Questions

  1. If I use the class hyperlink method, how can I avoid NA links (I would prefere if those cells are empty)
  2. Is there a simpler way to create custom text links and automatically create no links where target is NA? At the moment I have to explicitly unselect specific rows using dims.

I would like to create an Excel sheet which has columns containing hyperlinks and missing values (NA's).

library(openxlsx2)
options(openxlsx2.na.strings = "")

dat <- data.frame(link = c("https://www.wikipedia./", "https://www.google/", NA, NA),
                  custom_text_link = c("Wikipedia", "Google", NA, NA))
class(dat$link) <- "hyperlink"

wb_workbook() %>% 
  wb_add_worksheet(sheet = "dat") %>% 
  wb_add_data(sheet = 1, x = dat) %>% 
  wb_add_hyperlink(sheet = 1,
                   dims = wb_dims(rows = which(!is.na(dat$link)) + 1,
                                  cols = which(colnames(dat) == "custom_text_link")),
                   target = dat$link[!is.na(dat$link)]) %>%
  wb_open()

Created on 2025-03-27 with reprex v2.1.1

Questions

  1. If I use the class hyperlink method, how can I avoid NA links (I would prefere if those cells are empty)
  2. Is there a simpler way to create custom text links and automatically create no links where target is NA? At the moment I have to explicitly unselect specific rows using dims.
  • r
  • openxlsx2
Share Improve this question asked Mar 27 at 9:42 retodomaxretodomax 6456 silver badges16 bronze badges 0
Add a comment  | 

1 Answer 1

Reset to default 3

It would be up to me, I would write the named links iterating with a for-loop using add_formula + as per create_hyperlink as per this

library(openxlsx2)

df <- data.frame(
  link = c("https://www.wikipedia./", NA, NA, "https://www.google/"),
  custom_text_link = c("Wikipedia", NA, NA, "Google")
)

wb <- wb_workbook() %>%
  wb_add_worksheet(sheet = "dat") %>%
  wb_add_data(x="Links", start_col = 1, start_row = 1) # add Links header

for (i in 1:nrow(df)) { # add named links
  if(!is.na(df$link[i])){
    wb$add_formula(x = create_hyperlink(text = df$custom_text_link[i], file = df$link[i]), dims = paste0("A",i+1))
  }
}

wb$open()

You can also do this vectorized. We create an extra column using ifelse + create_hyperlink and set the class to hyperlink. Then use wb_add_data and empty link-rows will be left empty.

df$hyperlinks <- ifelse(!is.na(df$link), create_hyperlink(text = df$custom_text_link, file = df$link), "")
class(df$hyperlinks) <- "hyperlink"

wb <- wb_workbook() %>%
  wb_add_worksheet(sheet = "dat") %>%
  wb_add_data(x = "Links", start_col = 1, start_row = 1) %>%
  wb_add_data(x = df$hyperlinks, start_col = 1, start_row = 2) %>%
  wb_open()

both giving

To fix your approach

You could use dplyr for example to mutate only the links to class hyperlink which are not NA. I would not recommend this though, since cases where NA's are inbetween valid links like in the example above will lead to errors.

library(dplyr)

dat_alt <- data.frame(
  link = c("https://www.wikipedia./", "https://www.google/", NA, NA),
  custom_text_link = c("Wikipedia", "Google", NA, NA)
) %>%
  mutate(
    link = ifelse(!is.na(link), structure(link, class = "hyperlink"), link)
  )

wb <- wb_workbook() %>% 
  wb_add_worksheet(sheet = "dat") %>% 
  wb_add_data(sheet = 1, x = dat_alt) %>% 
  wb_add_hyperlink(sheet = 1,
                   dims = wb_dims(rows = which(!is.na(dat_alt$link)) + 1,
                                  cols = which(colnames(dat_alt) == "custom_text_link")),
                   target = dat_alt$link[!is.na(dat_alt$link)]) %>%
  wb_open()

giving

本文标签： rHandeling of missing values (NA) in a column of hyperlinks with openxlsx2Stack Overflow

版权声明：本文标题：r - Handeling of missing values (NA) in a column of hyperlinks with openxlsx2 - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1744098976a2533406.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。