How to move to the next sheet in R while exporting

r
rstudio

#1

Hi need help in R programming
I have two data frames contains sales,account data. Exported this data into excel in first sheet only contains two observations i.e. Sales Accounts Second sheet contains sales data & third sheet contain accounts data What my requirement is can i give hyperlink through coding When i click sales it will open sales sheet(2nd sheet)

Please help me in this regard Thanks in advance


#2

Can i expect answer from any one?


#3

@ravikumar_bavigadda Hi Ravi, You can always expect a reply from the community. It can be late but never ignored.
Well, first of all I would need some clarification regarding your question. For now, it more looks like you want help with hyperlinks in excel rather than R.
Here are few things that I am assuming.
1- You have exported your data frames into three worksheets within the same workbook.
2- You want to navigate to the last two sheets using the entries in the first sheet.

If all my assumptions are correct, then below is the method you can use to place the hyperlinks:

1- Select the cell where you want to place the observation “sales”.

2- Right click on the cell and choose “hyperlink”.

3-The Insert Hyperlink dialog window appears on the screen.

  1. Choose Place in This Document in the Link to section if your task is to link the cell to a specific location in the same workbook.

  2. Select the worksheet that you want to link to in the Or select a place in this document field.

  3. Enter the cell address in the Type the cell reference box if you want to link to a certain cell of another worksheet.

  4. Enter a value or name into the Text to display box to represent the hyperlink in the cell.

  5. Click OK.

  6. The cell content becomes underlined and highlighted in blue. It means that the cell contains the hyperlink. To check if the link works, just hover the pointer over the underlined text and click on it to go to the specified location.

  7. Repeat with the other observation.

Please let me know, if you wanted something else.


#4

Thank you NSS
while exporting data frames to the excel only i need hyperlinks
It can be done through R programming.

After exporting into excel creating hyperlinks to the next sheet it is very easy i know it.


#5

@ravikumar_bavigadda
Now since you have made your point pretty clear. Here is the solution that you intend.

The “XLSX” package has the function “addHyperlink”, for exactly this purpose.
example(addHyperlink) should get you started.

Hope this helps :slight_smile:


#6

I tried using XLSX package but unable to give hyperlink to the next sheet
can anyone forward me the code please


#7

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


#8

‘C:\Reports[Budget.xlsx]Annual’!C10:C25

This is how you refer to exact cells from an external source. Try this if it helps. It is the path followed by workbook name, followed by sheet name, exclamation point and then the cells engaged.


#9

"D://r datasets[sales data.xlsx]"sheet5!A1:E50
followed what ever you say but getting error
Error: unexpected ‘!’ in links <-c("D://r datasets[sales data.xlsx]"sheet5!A1:E50