I am creating a program that will generate a sample of a dataset for quality review. The actual data will be available on our intranet. This sample is output in an excel with a very specific user-friendly format. I am wanting to use XLconnect to add hyperlinks to the excel document based on the sample. I have been unable to find answers using several searches. I am hoping this is possible using XLconnect or similar package that will keep the format in excel. My code below adds just text, but not hyperlinks...
library(XLConnect)
Full_data_set = read.csv(paste(my.file.location, my.set, sep= "/"))
my.sample <- sample(Full_data_set$groupid, 50)
my.link <- paste("ourwebsite.org/group/" my.sample, sep = "")
wb <- loadWorkbook(filename = "my.file.location/Template2.xlsx",
create = TRUE)
writeWorksheet(wb, my.links, sheet= 1,
startRow=3, startCol=3,
header=FALSE)
saveWorkbook(wb)
You have to use
setCellFormula()
library("XLConnect")
df <- data.frame(
v1 = c("wiki", "google", "so"),
v2 = c("https://www.wikipedia.org/",
"https://www.google.com/",
"http://stackoverflow.com/"),
link = NA ,
stringsAsFactors = F)
# Load workbook (create if not existing)
wb <- loadWorkbook("hlink_example.xlsx", create = TRUE)
# Create a sheet
createSheet(wb, name = "Sheet1")
# Create a named region on sheet
createName(wb, name = "Sheet1", formula = "Sheet1!$A$1")
# Write data set to the above defined named region
writeNamedRegion(wb, df, name = "Sheet1")
# Create an excel column ref. for formula and links in data frame
excelCol <- letters[which(names(df) == "v2")]
# Construct the input range & formula
formula <- paste("HYPERLINK(",excelCol, 2:(nrow(df)+1),")", sep = "")
# Create the link column in data frame
df$link <- formula
# Set the cell formulas using index for rows/col
setCellFormula(wb, "Sheet1", 2:(nrow(df)+1), which(names(df) == "link"),
formula)
# Save workbook
saveWorkbook(wb)