导入 Excel 文件
有几个 R 包可以读取 excel 文件,每个文件使用不同的语言或资源,如下表所示:
R 包 | 用途 |
---|---|
XLSX |
Java |
XLconnect |
Java |
openxlsx |
C++ |
readxl |
C++ |
RODBC |
ODBC |
GDATA |
Perl |
对于使用 Java 或 ODBC 的软件包,了解系统的详细信息非常重要,因为根据你的 R 版本和操作系统,可能存在兼容性问题。例如,如果你使用的是 R 64 位,那么你还必须使用 Java 64 位来使用 xlsx
或 XLconnect
。
下面提供了使用每个包读取 excel 文件的一些示例。请注意,许多软件包具有相同或非常相似的函数名称。因此,明确说明包,如 package::function
是很有用的。openxlsx
包需要事先安装 RTools。
使用 xlsx 包读取 excel 文件
library(xlsx)
要导入工作表的索引或名称。
xlsx::read.xlsx("Book1.xlsx", sheetIndex=1)
xlsx::read.xlsx("Book1.xlsx", sheetName="Sheet1")
使用 XLconnect 包读取 Excel 文件
library(XLConnect)
wb <- XLConnect::loadWorkbook("Book1.xlsx")
# Either, if Book1.xlsx has a sheet called "Sheet1":
sheet1 <- XLConnect::readWorksheet(wb, "Sheet1")
# Or, more generally, just get the first sheet in Book1.xlsx:
sheet1 <- XLConnect::readWorksheet(wb, getSheets(wb)[1])
XLConnect
自动导入 Book1.xlsx
中嵌入的预定义 Excel 单元格样式。当你希望格式化工作簿对象并导出格式完美的 Excel 文档时,这非常有用。首先,你需要在 Book1.xlsx
中创建所需的单元格格式并保存它们,例如 myHeader
,myBody
和 myPcts
。然后,在 R
中加载工作簿后(见上文):
Headerstyle <- XLConnect::getCellStyle(wb, "myHeader")
Bodystyle <- XLConnect::getCellStyle(wb, "myBody")
Pctsstyle <- XLConnect::getCellStyle(wb, "myPcts")
单元格样式现在保存在 R
环境中。要将单元格样式指定给数据的某些范围,你需要定义范围,然后指定样式:
Headerrange <- expand.grid(row = 1, col = 1:8)
Bodyrange <- expand.grid(row = 2:6, col = c(1:5, 8))
Pctrange <- expand.grid(row = 2:6, col = c(6, 7))
XLConnect::setCellStyle(wb, sheet = "sheet1", row = Headerrange$row,
col = Headerrange$col, cellstyle = Headerstyle)
XLConnect::setCellStyle(wb, sheet = "sheet1", row = Bodyrange$row,
col = Bodyrange$col, cellstyle = Bodystyle)
XLConnect::setCellStyle(wb, sheet = "sheet1", row = Pctrange$row,
col = Pctrange$col, cellstyle = Pctsstyle)
请注意,XLConnect
很容易,但格式化可能会变得非常慢。openxlsx
提供了更快,但更麻烦的格式化选项。
使用 openxlsx 包读取 excel 文件
可以使用包 openxlsx
导入 Excel 文件
library(openxlsx)
openxlsx::read.xlsx("spreadsheet1.xlsx", colNames=TRUE, rowNames=TRUE)
#colNames: If TRUE, the first row of data will be used as column names.
#rowNames: If TRUE, first column of data will be used as row names.
可以通过在 sheet
参数中提供其位置来选择应该读入 R 的工作表:
openxlsx::read.xlsx("spreadsheet1.xlsx", sheet = 1)
或者通过声明其名称:
openxlsx::read.xlsx("spreadsheet1.xlsx", sheet = "Sheet1")
此外,openxlsx
可以检测读取表中的日期列。为了允许自动检测日期,应将参数 detectDates
设置为 TRUE
:
openxlsx::read.xlsx("spreadsheet1.xlsx", sheet = "Sheet1", detectDates= TRUE)
使用 readxl 包读取 excel 文件
可以使用 readxl
软件包将 Excel 文件作为数据框导入 R
。
library(readxl)
它可以读取 .xls
和 .xlsx
文件。
readxl::read_excel("spreadsheet1.xls")
readxl::read_excel("spreadsheet2.xlsx")
要导入的工作表可以通过编号或名称指定。
readxl::read_excel("spreadsheet.xls", sheet = 1)
readxl::read_excel("spreadsheet.xls", sheet = "summary")
参数 col_names = TRUE
将第一行设置为列名。
readxl::read_excel("spreadsheet.xls", sheet = 1, col_names = TRUE)
参数 col_types
可用于将数据中的列类型指定为向量。
readxl::read_excel("spreadsheet.xls", sheet = 1, col_names = TRUE,
col_types = c("text", "date", "numeric", "numeric"))
使用 RODBC 包读取 excel 文件
可以使用与 Windows 的 Access 数据库引擎(ACE)(以前称为 JET)连接的 ODBC Excel 驱动程序读取 Excel 文件。使用 RODBC 包,R 可以连接到此驱动程序并直接查询工作簿。假设工作表在第一行中维护列标题,并在相似类型的有组织列中维护数据。注意: 此方法仅限于 Windows / PC 计算机,因为 JET / ACE 安装了 .dll 文件,而在其他操作系统上不可用。
library(RODBC)
xlconn <- odbcDriverConnect('Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\\Path\\To\\Workbook.xlsx')
df <- sqlQuery(xlconn, "SELECT * FROM [SheetName$]")
close(xlconn)
使用此方法连接 SQL 引擎,可以查询 Excel 工作表,类似于数据库表,包括 JOIN
和 UNION
操作。语法遵循 JET / ACE SQL 方言。注意: 只有数据访问 DML 语句,特别是 SELECT
可以在工作簿上运行,被认为是不可更新的查询。
joindf <- sqlQuery(xlconn, "SELECT t1.*, t2.* FROM [Sheet1$] t1
INNER JOIN [Sheet2$] t2
ON t1.[ID] = t2.[ID]")
uniondf <- sqlQuery(xlconn, "SELECT * FROM [Sheet1$]
UNION
SELECT * FROM [Sheet2$]")
甚至可以从指向当前工作簿的同一 ODBC 通道查询其他工作簿:
otherwkbkdf <- sqlQuery(xlconn, "SELECT * FROM
[Excel 12.0 Xml;HDR=Yes;
Database=C:\\Path\\To\\Other\\Workbook.xlsx].[Sheet1$];")