从 R 脚本直接读取 Excel 文件

Question

如何直接将Excel文件读入R？还是应该先将数据导出为文本或CSV文件，然后再将该文件导入到R中？

- waanders

---

尚未测试，但也有基于Java的xlsx和基于Perl的WriteXLS软件包。 - Marek

gdata 版本 2.8.2 可以使用 read.xls 函数读取 xlsx 文件。 - Ben

请注意，如果您从Excel将数据导出到文本格式，则可能会丢失精度。请参见下面的警告（作为答案）。 - russellpierce

xlsx包用于处理xlsx/xlsm/xls格式的文件，但不支持xlam等其他格式。 - Qbik

我从来没有想过不先导出到文本文件。当我导出为CSV时，其中一个字段没有被写入。这似乎是某种DRM，但由于我没有编写电子表格，所以我不知道。 - Nate Reed

Answer 1

让我重申一下@Chase的建议：使用XLConnect。

我认为使用XLConnect的原因有：

1. 跨平台。XLConnect是用Java编写的，因此可以在Win、Linux、Mac上运行而无需更改您的R代码（除非是可能需要更改路径字符串）。
2. 无需加载其他内容。只需安装XLConnect并继续生活即可。
3. 您只提到了读取Excel文件，但是XLConnect也可以编写Excel文件，包括更改单元格格式。而且它可以在Linux或Mac上执行此操作，不仅限于Win。

与其他解决方案相比，XLConnect相对较新，因此在博客文章和参考文档中提及的频率较低。对我来说它非常有用。

- JD Long

Answer 2

现在有了readxl:

readxl包使得从Excel中获取数据并导入到R变得更加容易。与现有的包（例如gdata、xlsx、xlsReadWrite等）相比，readxl没有外部依赖，因此可以在所有操作系统上轻松安装和使用。它被设计用于处理存储在单个工作表中的表格数据。

readxl是基于libxls C库构建的，该库抽象了底层二进制格式的许多复杂性。

它支持传统的.xls格式和.xlsx格式。

readxl可从CRAN获取，或者你可以使用以下命令从github安装：

# install.packages("devtools")
devtools::install_github("hadley/readxl")

使用方法

library(readxl)

# read_excel reads both xls and xlsx files
read_excel("my-old-spreadsheet.xls")
read_excel("my-new-spreadsheet.xlsx")

# Specify sheet with a number or name
read_excel("my-spreadsheet.xls", sheet = "data")
read_excel("my-spreadsheet.xls", sheet = 2)

# If NAs are represented by something other than blank cells,
# set the na argument
read_excel("my-spreadsheet.xls", na = "NA")

请注意，尽管描述中说“没有外部依赖项”，但它确实需要Rcpp包，该包又需要Rtools（适用于Windows）或Xcode（适用于OSX），这些是R之外的外部依赖项。虽然许多人出于其他原因已经安装了它们。

- Ben

---

比xlsx快得多，读取时间类似于read.xlsx2，但它可以推断数据类型。 - Steve Rowe

@SteveRowe 请查看新答案，其中包含一些（尝试的）客观基准，证实了这一点。 - MichaelChirico

有没有办法使用read_excel将字符串读取为因子？我喜欢它相对于read.xlsx的速度，但是需要手动将列从字符转换为因子，这样做就失去了意义。 - coip

+1的好处是它没有依赖性。我讨厌安装Java。我已经尝试过它并且对我来说非常有效。 - Bastian

readxl 和 openxlsx 是最好的。readxl 更快，但它不允许写入。无论如何，在尝试指定列类/类型时，它们都表现不佳。 - skan

Answer 3

是的。请参考R百科相关页面：http://en.wikibooks.org/wiki/R_Programming/Importing_and_exporting_data#Excel_.28xls.2Cxlsx.29。简而言之，gdata包中的read.xls通常可行（尽管您需要在系统上安装Perl - 在MacOS和Linux上通常已经存在，但在Windows上需要额外步骤，例如参见http://strawberryperl.com/）。 R百科页面列出了各种注意事项和替代方案。

我唯一看到不直接这样做的原因是您可能希望检查电子表格以查看其是否存在问题（奇怪的标题、多个工作表[每次只能读取一个，虽然您可以循环遍历它们所有]、包含绘图等）。但对于格式良好、矩形的电子表格，其中只有普通数字和字符数据（即没有逗号格式化的数字、日期、除零错误的公式、缺失值等），我通常使用此方法没有问题。

- Ben Bolker

---

个人经验发现需要考虑很多潜在问题。带有逗号的数字字段需要在R中剥离并转换为数值。带有“-”的字段需要重新编码为NA。总的建议是要仔细查看Excel中的数字并确保它们被正确地转换为R。 - Brandon Bertelsen

“你真的需要查看你的数字”这句话无法争辩……“-”字段有什么问题？na.strings="-"可以解决这个问题吗？这些问题中有多少是通用的，而另外一些问题（例如带逗号的数字字段）可以通过其他工具（如XLConnect）来解决...？ - Ben Bolker

那条评论是针对楼主的，不是针对你本人Ben的，我放置位置不好，我的错。 - Brandon Bertelsen

相关趣闻：read.xls 成功读取了一个非常大的 Excel 文件中的工作表，而 XLConnect 和 xlsx 都失败了（我相信这是因为它们都依赖于 Apache POI）。 - Matt Parker

Answer 4

编辑于2015年10月：正如其他人在这里评论的，openxlsx 和readxl包比xlsx包快得多，并且实际上能够打开更大的 Excel 文件（>1500行和>120列） 。@MichaelChirico演示了当速度更重要时，readxl更好，而openxlsx替换了xlsx包提供的功能。如果您正在寻找2015年读取、写入和修改Excel文件的软件包，请选择openxlsx而不是xlsx。

2015年之前：我使用了xlsx包。它改变了我的Excel和R的工作流程。再也没有烦人的弹出窗口询问我是否确定要将Excel表格保存为.txt格式。该软件包还可以编写Excel文件。

但是，我发现当打开大型Excel文件时，read.xlsx函数很慢。read.xlsx2函数要快得多，但不能猜测数据框列的向量类别。如果您使用read.xlsx2函数，则必须使用colClasses命令来指定所需的列类别。以下是一个实用示例：

read.xlsx("filename.xlsx", 1)读取文件并使数据框列类别几乎有用，但对于大型数据集非常慢。也适用于.xls文件。

read.xlsx2("filename.xlsx", 1)更快，但您必须手动定义列类别。一种快捷方式是运行该命令两次（请参见下面的示例）。character规范将您的列转换为因子。使用Date和POSIXct选项进行时间处理。

coln <- function(x){y <- rbind(seq(1,ncol(x))); colnames(y) <- colnames(x)
rownames(y) <- "col.number"; return(y)} # A function to see column numbers

data <- read.xlsx2("filename.xlsx", 1) # Open the file 

coln(data)    # Check the column numbers you want to have as factors

x <- 3 # Say you want columns 1-3 as factors, the rest numeric

data <- read.xlsx2("filename.xlsx", 1, colClasses= c(rep("character", x),
rep("numeric", ncol(data)-x+1)))

- Mikko

Answer 5

考虑到在 R 中读取 Excel 文件的不同方式以及这里提到的大量答案，我想尝试一下阐明哪些选项在几种简单情况下表现最佳。

我自己从开始使用 R 起就一直在使用 xlsx，主要是出于惯性，近期我注意到似乎没有任何关于哪个包更好的客观信息。

任何基准测试都充满了困难，因为某些软件包肯定会比其他软件包处理某些情况更好，还有一系列其他注意事项。

话虽如此，我使用一个（可再现的）数据集，我认为它是以相当常见的格式（8个字符串字段、3个数字、1个整数、3个日期）呈现的：

set.seed(51423)
data.frame(
  str1 = sample(sprintf("%010d", 1:NN)), #ID field 1
  str2 = sample(sprintf("%09d", 1:NN)),  #ID field 2
  #varying length string field--think names/addresses, etc.
  str3 = 
    replicate(NN, paste0(sample(LETTERS, sample(10:30, 1L), TRUE),
                         collapse = "")),
  #factor-like string field with 50 "levels"
  str4 = sprintf("%05d", sample(sample(1e5, 50L), NN, TRUE)),
  #factor-like string field with 17 levels, varying length
  str5 = 
    sample(replicate(17L, paste0(sample(LETTERS, sample(15:25, 1L), TRUE),
                                 collapse = "")), NN, TRUE),
  #lognormally distributed numeric
  num1 = round(exp(rnorm(NN, mean = 6.5, sd = 1.5)), 2L),
  #3 binary strings
  str6 = sample(c("Y","N"), NN, TRUE),
  str7 = sample(c("M","F"), NN, TRUE),
  str8 = sample(c("B","W"), NN, TRUE),
  #right-skewed integer
  int1 = ceiling(rexp(NN)),
  #dates by month
  dat1 = 
    sample(seq(from = as.Date("2005-12-31"), 
               to = as.Date("2015-12-31"), by = "month"),
           NN, TRUE),
  dat2 = 
    sample(seq(from = as.Date("2005-12-31"), 
               to = as.Date("2015-12-31"), by = "month"),
           NN, TRUE),
  num2 = round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L),
  #date by day
  dat3 = 
    sample(seq(from = as.Date("2015-06-01"), 
               to = as.Date("2015-07-15"), by = "day"),
           NN, TRUE),
  #lognormal numeric that can be positive or negative
  num3 = 
    (-1) ^ sample(2, NN, TRUE) * round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L)
)

我将数据写入csv文件，然后在LibreOffice中打开并保存为.xlsx文件，接着对这篇帖子中提到的4个包：xlsx, openxlsx, readxl和gdata进行基准测试（使用默认选项；我还尝试了一种不指定列类型的版本，但这没有改变排名）。

由于我使用Linux系统，因此排除了RODBC；XLConnect似乎主要用于导入整个Excel工作簿，而不是读取单个Excel表格，因此仅考虑其读取能力似乎有失公正；xlsReadWrite无法与我的R版本兼容，似乎已经逐渐淘汰。

然后我使用NN=1000L和NN=25000L（在每次声明上述data.frame之前重置种子）进行基准测试，以考虑Excel文件大小的影响。 gc主要用于xlsx包，因为我发现有时会出现内存问题。以下是我得到的结果：

1,000行Excel文件

benchmark1k <-
  microbenchmark(times = 100L,
                 xlsx = {xlsx::read.xlsx2(fl, sheetIndex=1); invisible(gc())},
                 openxlsx = {openxlsx::read.xlsx(fl); invisible(gc())},
                 readxl = {readxl::read_excel(fl); invisible(gc())},
                 gdata = {gdata::read.xls(fl); invisible(gc())})

# Unit: milliseconds
#      expr       min        lq      mean    median        uq       max neval
#      xlsx  194.1958  199.2662  214.1512  201.9063  212.7563  354.0327   100
#  openxlsx  142.2074  142.9028  151.9127  143.7239  148.0940  255.0124   100
#    readxl  122.0238  122.8448  132.4021  123.6964  130.2881  214.5138   100
#     gdata 2004.4745 2042.0732 2087.8724 2062.5259 2116.7795 2425.6345   100

所以readxl是胜者，openxlsx有竞争力而gdata则明显失败。相对于列最小值进行每项测量：

#       expr   min    lq  mean median    uq   max
# 1     xlsx  1.59  1.62  1.62   1.63  1.63  1.65
# 2 openxlsx  1.17  1.16  1.15   1.16  1.14  1.19
# 3   readxl  1.00  1.00  1.00   1.00  1.00  1.00
# 4    gdata 16.43 16.62 15.77  16.67 16.25 11.31

我们看到我个人最喜欢的xlsx比readxl慢了60%。

25000行Excel文件

由于所需时间较长，因此我只在较大的文件上进行了20次重复，否则命令相同。以下是原始数据：

# Unit: milliseconds
#      expr        min         lq       mean     median         uq        max neval
#      xlsx  4451.9553  4539.4599  4738.6366  4762.1768  4941.2331  5091.0057    20
#  openxlsx   962.1579   981.0613   988.5006   986.1091   992.6017  1040.4158    20
#    readxl   341.0006   344.8904   347.0779   346.4518   348.9273   360.1808    20
#     gdata 43860.4013 44375.6340 44848.7797 44991.2208 45251.4441 45652.0826    20

这里是相关数据：

#       expr    min     lq   mean median     uq    max
# 1     xlsx  13.06  13.16  13.65  13.75  14.16  14.13
# 2 openxlsx   2.82   2.84   2.85   2.85   2.84   2.89
# 3   readxl   1.00   1.00   1.00   1.00   1.00   1.00
# 4    gdata 128.62 128.67 129.22 129.86 129.69 126.75

因此，readxl在速度方面是明显的赢家。gdata最好还有其他优点，因为它读取Excel文件的速度非常慢，对于较大的表格这个问题只会更加严重。

openxlsx的两个优势是：1）它的其他方法非常丰富（readxl仅设计了做一件事情，这也可能是它如此快的部分原因），特别是其write.xlsx函数；2）（对于readxl来说更像是一个缺点）readxl中的col_types参数目前仅接受一些非标准的R："text"而不是"character"和"date"而不是"Date"。

- MichaelChirico

---

如果您能为XLConnect添加基准测试，那将非常棒。另外请注意，readxl无法写入.xlsx文件，而openxlsx和xlsx在使用col_types或colClasses选项时存在问题。 - skan

@skan 我最初使用了 XLConnect 进行了一些测试，但它非常慢；我相信 readxl 的缺点在我的最后一段已经足够涵盖了；而且我对于 xlsx 或 openxlsx 没有类似于你的经验，因为我经常使用两者来指定类型。 - MichaelChirico

Answer 6

我使用XLConnect取得了不错的运气：http://cran.r-project.org/web/packages/XLConnect/index.html

- Chase

---

在其他的SE网络中，因为答案仅包含链接，该答案将被标记为关闭。 - luchonacho

Answer 7

library(RODBC)
file.name <- "file.xls"
sheet.name <- "Sheet Name"

## Connect to Excel File Pull and Format Data
excel.connect <- odbcConnectExcel(file.name)
dat <- sqlFetch(excel.connect, sheet.name, na.strings=c("","-"))
odbcClose(excel.connect)

个人而言，我喜欢RODBC并且可以推荐它。

- Brandon Bertelsen

---

注意：除了Windows平台，ODBC有时可能很难配置运行。 - JD Long

@JD Long，即使在 Windows 上也很麻烦。对我来说，在 64 位 W7 上使用 ODBC 没有什么吸引力的时间... - Roman Luštrik

加载所需的包：RODBC odbcConnectExcel(file.name)中出错： odbcConnectExcel仅适用于32位Windows。 - andrekos

Answer 8

今天我试用了openxlsx包，它的表现非常好（而且运行速度很快）。 http://cran.r-project.org/web/packages/openxlsx/index.html

- Chris

Answer 9

另一种解决方案是 xlsReadWrite 包，它不需要额外的安装，但在第一次使用前需要下载额外的 shlib。

require(xlsReadWrite)
xls.getshlib()

忘记这一点会导致极度的沮丧。我也曾经历过这样的情况...

顺便说一下：你可能需要考虑转换为基于文本的格式（例如csv）并从那里读取。有很多原因：

- 无论你使用什么解决方案（RODBC，gdata，xlsReadWrite），当你的数据被转换时，一些奇怪的事情可能会发生。特别是日期可能会非常麻烦。HFWutils软件包有一些工具来处理EXCEL日期（根据@Ben Bolker的评论）。 - 如果你有大型表格，从文本文件中读取比从EXCEL中读取更快。 - 对于.xls和.xlsx文件，可能需要不同的解决方案。例如，xlsReadWrite软件包目前不支持.xlsx，而gdata则需要你安装额外的perl库以支持.xlsx。xlsx软件包可以处理相同名称的扩展名。

- Joris Meys

---

@Ben 谢谢你的提示，我会在我的答案中加入它。虽然我没有尝试完整，因为被接受的答案链接到的维基页面已经相当完整了。但它没有提到HFWutils包。 - Joris Meys

-1；请看我的回答。简而言之，Excel不会将完整精度的数据集保存到CSV（或剪贴板）中，只有可见值会被保留。 - russellpierce

Answer 10

在 @Mikko 提供的答案基础上，您可以使用一个巧妙的技巧来加速操作，而不必提前“知道”列类。只需使用 read.xlsx 获取有限数量的记录以确定类别，然后再使用 read.xlsx2 进行跟进。

示例

# just the first 50 rows should do...
df.temp <- read.xlsx("filename.xlsx", 1, startRow=1, endRow=50) 
df.real <- read.xlsx2("filename.xlsx", 1, 
                      colClasses=as.vector(sapply(df.temp, mode)))

- JasonAizkalns

---

你的解决方案在我的电脑上返回 numeric 类型的 factors。read.xlsx 函数使用 character 来指定 factors 的读取列。我相信有更优雅的方法将 factors 作为字符获取，但这里是一个修改过的函数版本：df.real <- read.xlsx2("filename.xlsx", 1, colClasses=gsub("factor", "character", as.vector(sapply(df.temp, class))))。 - Mikko

这仅在列类型可以通过第一行充分确定的情况下有效。通常需要解析超过第一行以进行该确定。为了回应aaa90210的评论，可以通过命名这些函数所属的包来改进答案。 - russellpierce