使用XLConnect软件包从URL将Excel文件读入R [英] Read Excel file into R with XLConnect package from URL

问题描述

有很多很好的例子说明如何使用XLConnect软件包将Microsoft Excel文件读入R，但是我无法找到任何直接从URL读取Excel文件的示例。以下可重现的示例返回 FileNotFoundException（Java）。但是，我知道该文件存在，因为我可以直接通过将URL粘贴到浏览器中。

There are lots of good examples out there on how to read Microsoft Excel files into R with the XLConnect package, but I can't find any examples of how to read in an Excel file directly from a URL. The reproducible example below returns a "FileNotFoundException (Java)". But, I know the file exists because I can pull it up directly by pasting the URL into a browser.

fname <- "https://www.misoenergy.org/Library/Repository/Market%20Reports/20140610_sr_nd_is.xls"
sheet <- c("Sheet1")
data  <- readWorksheetFromFile(fname, sheet, header=TRUE, startRow=11, startCol=2, endCol=13)

尽管URL前缀为 https：，它是一个不需要用户名或密码的公共文件。

Although, the URL is prefixed with "https:" it is a public file that does not require a username or password.

I尝试使用 download.file（fname，destfile =test.xls）下载文件，并收到一条消息，表示已下载，但是当我尝试打开在Excel中查看是否成功我得到一个Excel弹出框，说在.xls中发现不可读内容。

I have tried to download the file first using download.file(fname, destfile="test.xls") and got a message that says it was downloaded but when I try to open it in Excel to check to see if it was successful i get a Excel popup box that says "..found unreadable content in 'test.xls'.

下面是我的系统细节：

计算机：64位戴尔运行
操作系统：Windows 7 Professional
R版本：R-3.1.0

Computer: 64-bit Dell running Operating System: Windows 7 Professional R version: R-3.1.0

非常感谢任何帮助。

推荐答案

您可以使用 RCurl 下载文件：

library(RCurl)
library(XLConnect)
appURL <- "https://www.misoenergy.org/Library/Repository/Market%20Reports/20140610_sr_nd_is.xls"
f = CFILE("exfile.xls", mode="wb")
curlPerform(url = appURL, writedata = f@ref, ssl.verifypeer = FALSE)
close(f)
out <- readWorksheetFromFile(file = "exfile.xls", sheet = "Sheet1", header = TRUE
                      , startRow = 11, startCol = 2, endCol = 15, endRow = 35)
> head(out)
Col1 EEI Col3 IESO MHEB Col6 PJM SOCO SWPP TVA WAUE Col12 Other Total
1 Hour  1 272   NA  768 1671   NA 148  200  -52 198  280    NA   700  4185
2 Hour  2 272   NA  769 1743   NA 598  200  -29 190  267    NA   706  4716
3 Hour  3 272   NA  769 1752   NA 598  200  -28 194  267    NA   710  4734
4 Hour  4 272   NA  769 1740   NA 598  200  -26 189  266    NA   714  4722
5 Hour  5 272   NA  769 1753   NA 554  200  -27 189  270    NA   713  4693
6 Hour  6 602   NA  769 1682   NA 218  200  -32 223  286    NA   714  4662

这篇关于使用XLConnect软件包从URL将Excel文件读入R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！