-5

I need help in R.

I have data of 3 departments sales, marketing, accounts data. When exporting these data into excel, my desired output looks like of in the first sheet I need 3 rows of data i.e.: sales, marketing, accounts.

These 3 rows contains hyperlinks to the next sheets When I click into sales it will takes me into the sales sheet as well as remaining.

Can any one help how this can be done? Any help would be greatly appreciated.

Vincent Bonhomme
  • 7,235
  • 2
  • 27
  • 38
Ravikumar Shalivahana
  • 5
  • 4
  • How is your question related to R? Can you please show us what you have tried so far in R to create the Excel file(s)? – R Yoda Apr 16 '16 at 11:46
  • Welcome to StackOverflow. Please read [how do I ask a good question](http://stackoverflow.com/help/how-to-ask) and [proding a minimal reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example#answer-5963610) and edit your post accordingly. I.e., provide input data, the expected output + what you tried and in what way it failed. – lukeA Apr 16 '16 at 11:58
  • I exported multiple datasets (sales, marketing, account) into one excel files with different sheets, and also created hyperlink in excel but here what happens only web page hyperlinks is created but trying to assign hyperlinks to these 3 sheets within a file – Ravikumar Shalivahana Apr 16 '16 at 12:31
  • Can i expect answer from any one – Ravikumar Shalivahana Apr 20 '16 at 06:07
  • Please help me in this task – Ravikumar Shalivahana Apr 25 '16 at 04:39
  • I used this code to give hyperlinks to the next sheet library(xlsx) wb <- createWorkbook() sheet1 <- createSheet(wb, "Sheet1") rows <- createRow(sheet1, 1:10) # 10 rows cells <- createCell(rows, colIndex=1:8) # 8 columns links <- c("D://r datasets/sales data.xlsx") names(links) <- c("hyperlinks") for (row in 1:length(links)) { setCellValue(cells[[row,1]], names(links)[row]) addHyperlink(cells[[row,1]], links[row]) } saveWorkbook(wb, "links.xlsx") shell.exec("links.xlsx") How to mention sheet name in this code – Ravikumar Shalivahana May 16 '16 at 05:34

1 Answers1

1

To link to another sheet, use 

addHyperlink(cell, "sheetname!colRow", linkType="DOCUMENT")

For example

library(xlsx) 
wb <- createWorkbook() 
sheet1 <- createSheet(wb, "Sheet1") 
sheet2 <- createSheet(wb, "Sheet2") 
rows <- createRow(sheet1, 1:10) # 10 rows 
cells <- createCell(rows, colIndex=1:8) # 8 columns 

row <- 1
link <- paste0(names(getSheets(wb))[2], "!", "A", row)
setCellValue(cells[[row,1]], "link to other sheet") 
addHyperlink(cells[[row,1]], link, linkType="DOCUMENT") 

saveWorkbook(wb, "links.xlsx") 
shell.exec("links.xlsx")
lukeA
  • 53,097
  • 5
  • 97
  • 100
  • Thank u for help i have a small doubt if already i have a excel file it contains sales, marketing, account sheets respectively file name is company details, i am little bit confusing how to give hyperlinks already exists excel file – Ravikumar Shalivahana May 17 '16 at 05:53
  • Sorry, I don't understand. Please post a specific coding problem. (Like you did in the comments, but add it to your posting in a code format for easy copy/paste) – lukeA May 17 '16 at 06:25
  • I have sales data in excel file in that i have to open sheet3 i tried below code but unable to give hyperlinks to sheet3 library(xlsx) wb <- createWorkbook() sheet1 <- createSheet(wb, "Sheet1") rows <- createRow(sheet1) cells <- createCell(rows) links <- c("[D://r datasets/sales data.xlsx]sheet3!") names(links) <- c("link1") for (row in 1:length(links)) { setCellValue(cells[[row,1]], names(links)[row]) addHyperlink(cells[[row,1]], links[row]) } saveWorkbook(wb, "hyperlinks to file.xlsx") shell.exec("hyperlinks to file.xlsx") – Ravikumar Shalivahana May 17 '16 at 06:33
  • You can do library(xlsx) wb1 <- createWorkbook() sheet1 <- createSheet(wb1, "Sheet1") saveWorkbook(wb1, tf1 <- tempfile(fileext = ".xlsx")) wb2 <- createWorkbook() sheet2 <- createSheet(wb2, "Sheet2") rows <- createRow(sheet2) cells <- createCell(rows) setCellValue(cells[[1,1]], "To Other File") addHyperlink(cells[[1,1]],sprintf("file:///%s#%s!%s", normalizePath(tf1, "/"), names(getSheets(wb))[1], "B2"), "FILE") saveWorkbook(wb2, tf2 <- tempfile(fileext = ".xlsx")) shell.exec(tf2) – lukeA May 17 '16 at 06:55
  • Sorry not understanding ("file:///%s#%s!%s", normalizePath(tf1, "/") – Ravikumar Shalivahana May 26 '16 at 11:28