Accessing Excel file from Sharepoint with R

2019-01-25 06:28发布

问题:

am trying to write an R script that will access an Excel file that is stored on my company's Sharepoint page so that I can make a few calculations and plot the results. I've tried various ways to do this (download.file, RCurl getURL(), gdata), but I can't seem to figure out how to do this. The url is HTTPS and there should be a username and password required. I've gotten the closest with this code:

require(RCurl)
URL<-"https://companyname.sharepoint.com/sites/folder/_layouts/15/WopiFrame.aspx?sourcedoc={2DCC2ED7-1C13-4910-AFAD-4A9ACFF1C797}&file=myfile.xlsx&action=default'  
f<-getURL(URL,verbose=T,ssl.verifyhost=F,ssl.verifypeer=F,userpwd="mylogin:mypw")

This seems to connect (although the username and password don't seem to matter) and returns

> f  
[1] "<html><head><title>Object moved</title></head><body>\r\n<h2>Object moved to <a href=\"https://companyname.sharepoint.com/sites/_layouts/15/WopiFrame2.aspx?sourcedoc={2DCC2ED7-1C13-4910-AFAD-4A9ACFF1C797}&amp;file=MyFile.xlsx&amp;action=default\">here</a>.</h2>\r\n</body></html>\r\n"`

However, I'm not sure what to do at this point, or even if I'm on the right track. Any help will be greatly appreciated.

回答1:

I use

library(readxl) read_excel('//companySharepointSite/project/.../ExcelFilename.xlsx', 'Sheet1', skip=1)

Note, no https:, and sometimes I have to open the file first (i.e., cut and paste //companySharepointSite/project/.../ExcelFilename.xlsx into my browser's address bar)

Peter A

回答2:

Try using the link in this format: http://site/_layouts/download.aspx?SourceUrl=url-of-document-in-library