易语言读取excel内容,易语言读取excel

导读：本文将讨论Excel、pdf等文件的读取，以及相应函数的参数设置。

作者：刘健 邬书豪

如需转载请联系华章科技

下图总结了主要程序包，希望读者在日常练习和工作中遇到不同格式的文件时，能够瞬间反应出读取该格式所需的包及对应的函数。（限于篇幅，本文未包含图中“平面文档格式”这部分的内容，如果你有兴趣，可以继续关注大数据后续文章。）

▲不同格式的数据文件读取所用的R包

readxl是微软Excel文件读取的必备R包，是Hadley Wickham、Jennifer Bryan以及其他6名成员合作完成的经典程序包之一。

值得一提的是，该包的开发者之一兼实际维护者Jennifer Bryan（网络上多称她为Jenny Bryan），可以称得上是与Hadley齐名且为数不多的女性R语言神级人物。可能是因为其身为大学教授，因此她总能够用很生动有趣的方式将复杂的问题简化成通俗易懂的知识传递给“小白”，强烈建议有英文基础的读者能够搜集一些她的主题演讲或者书籍。

更新后的readxl包中虽然也还是只有5个函数，不过功能却比以前的版本更强大了。对于起初的版本，数据会被读取成常见的data.frame格式，而对于现在的版本，读取后的数据集格式则为tibble，可以理解为提升版的data.frame。

readxl包括两个探测性函数excel_format和excel_sheets，一个引用例子的函数readxl_example，新加入的读取特定单元格的函数cell-specification以及最重要的read_excel函数。本节将着重讨论read_excel的参数设置及用法技巧。

数据导入函数read_excel主要参数及功能对照：

• path
• 数据文件路径 文件名，也可以是一个url
• sheet
• 工作表序号或名称，默认值为第一个工作表
• range
• 读取指定区间，可以限定函数读取原始Excel文件的范围，例如，“A1:D100”会读取这个区间中的所有单元格，包括空白单元格。“工作表1!A1:D100”会读取名为“工作表1”中的该区间。这个参数的优先级高于参数 'skip'、'n_max'、'sheet'
• col_names
• 该参数具有三个选择，具体如下。
• 1）为真（TRUE），原始数据文件的第一行被用作列名，且不在数据集内。
• 2）为假（FALSE），数据列名被自动赋值成X__1、X__2、X__3等。
• 3）自定义字符串向量传给参数。此时字符串向量会被用作列名，而原数据文件的第一列将被保存到数据集的第一列。如果有默认列名的话，则会发出警告，并自动赋值成X1、X2、X3等，但不会影响读取进程。重复的列名也会发出警告，并且会在重复列名前加数字序号以做区分
• col_types
• 列数据类型。可以有两种传参形式，具体如下。
• 1）NULL，默认值。函数会自动解析每一列数据的类型。
• 2）指定变量类型。字符串参照为："skip"、"guess"、"logical"、"numeric"、"date"、"text"或"list"。需要注意的是，如果仅指定一个数据类型（例如，"numeric"）那么所有的变量都会被读成字符型数据。如果指定一列为"skip"，那么这一列就不会被读取到R中来。新增加的"list"属性对处理有经纬度的变量列将会有很大帮助
• na
• 原始数据文件中是否有一些字符需要用na来代替。空白单元格被默认作为默认值
• trim_ws
• 每个数据值前后的空白是否处理掉，取值为真或假
• skip
• 是否跳过几行读取原始数据文件，默认取值为0，表示不跳过；可以传参任意数字
• n_max
• 最大读取行数

首先还是需要加载readxl包。尽管Hadley从2017年开始就一直在网络上宣传这个包已经属于tidyverse的一部分，但用户还是必须手动单独加载这个包。加载readxl包代码如下：

> library(readxl)

readxl包自带示范文件，使用函数readxl_example可以查看文件名字，以及获取文件路径，代码如下：

> readxl_example() [1] "clippy.xls" "clippy.xlsx" "datasets.xls" "datasets.xlsx" [5] "deaths.xls" "deaths.xlsx" "geometry.xls" "geometry.xlsx" [9] "type-me.xls" "type-me.xlsx"

获取示例文件的路径，可以先复制readxl_example函数运行后的结果，然后将其粘贴到read_excel函数的path参数中。

下面的代码演示函数嵌套的方法，这种嵌套的代码书写方式能够在一定程度上简化代码和减少命名中间产物的频率。不过嵌套过多会使可读性变差，一般推荐只嵌套两层。将读取后的数据保存在iris中，执行str函数之后将会发现除了经典的data.frame之外，数据集还有另外两种类别，tbl_df和tbl。函数嵌套的示例代码如下：

> iris <- read_excel(path = readxl_example(path = "datasets.xlsx")) > str(iris) ## Classes 'tbl_df', 'tbl' and 'data.frame': 150 obs. of 5 variables: ## $ Sepal.Length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ... ## $ Sepal.Width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ... ## $ Petal.Length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ... ## $ Petal.Width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ... ## $ Species : chr "setosa" "setosa" "setosa" "setosa" ...

之所以命名为iris，是因为这个范例Excel文件中的第一个工作表就是该经典数据集。函数excel_sheets可用于查询同一个文件中的工作表名称，其实现代码具体如下：

> excel_sheets(path = readxl_example(path = "datasets.xlsx")) ## [1] "iris" "mtcars" "chickwts" "quakes"

在datasets.xlsx中一共存在4个工作表，其中包含了4个最经典的R语言练习数据集。在此，希望读者可以自行浏览这几个数据集，对数据集的格式、变量名称等情况有一定程度的了解。

增加参数sheet或range可以读取指定工作表中的数据。这里需要注意的是，上文提到了参数优先级的问题。对于一般常见的练习数据集，sheet参数指定的工作表已足够胜任。读者只需要记住range参数可以用来处理特殊情况，也就是说，当设置sheet后依然对读取到的数据不满意的情况可以考虑使用range。

下面的代码演示了sheet的两种传参方式：位置序号和名称。推荐读者采用后者。因为工作表被意外拖拽导致位置调换的情况常有发生，而位置意外发生调换之后读取的数据也会不同，这就增加了代码崩溃的风险。如果使用名称，则会降低发生错误的几率。示例代码如下：

> mtcars <- read_excel(path = readxl_example(path = "datasets.xlsx"),sheet = 2) > mtcars <- read_excel(path = readxl_example(path = "datasets.xlsx"),sheet = "mtcars")

对于后面的参数，读者可以根据上文的解释，每次增加一个参数，来逐步掌握每一个参数的功能，这里不再赘述。

在使用R语言和数据库进行交互之前，读者们需要明确一个问题—是否有必要使用R来处理数据。简单的数据处理任务，比如数据查询、筛选和简单运算，相应的数据库语言应该是比R语言更好的选择。

不过当你对数据库语言并不熟悉，而且需要R语言强大的统计分析和绘图环境来处理数据库中的数据时，DBI包绝对是一条捷径。因为Hadley大神再一次拯救了“小白”。

有了DBI包，不需要了解数据库交互中各个环节繁琐的理论知识和技巧，只需要明白如何通过DBI包来建立数据库连接、查询和读取数据即可。不过，这个包也并非万能钥匙，想要无障碍地与数据库进行交互，以下6点是必备的前提。

1. 已知数据库的类型，例如，MySQL、PostgreSQL。
2. 已经安装了相应数据库类型的R包。
3. 数据库服务器地址。
4. 数据库名称。
5. 接入数据库的权限、账号和密码。
6. 已安装dplyr包用来本地化数据库中的数据。

使用R与数据库进行交互的一般流程为：建立连接→发送查询请求→获取相关数据。下面，我们用PostgreSQL的数据库作为代码示例。首先加载三个必备程序包，其中，DBI和PostgreSQL将用来建立与数据库的连接以及发送请求。dplyr则是用来将数据库中的数据保存到本地。加载代码具体如下：

> library(DBI) > library(dplyr) > library(RPostgreSQL)

不同类型的数据库可能需要调整dbConnect中的参数，具体调整方法读者可以参见帮助文档。数据库服务器地址、名称、权限等信息需要输入到单引号中，请一定留意是否有空格符号不小心被复制或者因误操作输入其中。如果担心密码泄露的话，则可以使用RStudio中自带的密码弹窗功能。

dbListTables函数可以用来查询数据库中的详细内容，并以字符串向量的格式返回，如果数据库中无内容，则会返回空值。调整dbConnect参数的示例代码如下：

> db_connect <- dbConnect( RPostgreSQL::PostgreSQL(), dbname = '数据库名称', host = '服务器地址', user = '用户名', password = "密码") > dbListTables(db_connect) ## [1] "MetaData" "Table1" "Table2"

优化后的tbl函数可以直接调取已经建立连接的数据库中的指定数据，并保存为tibble格式的数据集。下面的代码中，逗号后面的参数也可以用“Table1”或“Table2”来表示：

> tbl(src = db_connect, dbListTables(db_connect)[1])

数据库交互的有关内容完全可以独立成书，这里我们只介绍了最简单的基本用法，以使大家对如何使用R来查询数据库有个最基本的印象。

学术期刊、网络杂志和电子书籍一般都会以PDF格式的文件呈现。一般的计量型数据分析很少会遇到读取PDF文件的情况，不过在进行文本挖掘（Text Mining）和主题模型（Topic Modelling）预测中，pdftools包绝对是必备R包之一。

该包只有两个母函数，一个用来从PDF中提取数据（此处的数据包括数字型和文字型数据），另一个则用来将文件渲染成PDF格式。本节我们只讨论第一个母函数——pdf_info。

pdf_info函数下面一共包含6个子函数，功能各不相同。但是6个子函数的参数完全一致，分别是pdf、opw和upw。

数据导入函数'pdf_info子函数一览：

• pdf_info：读取PDF文件的基本信息，例如，何时创建、更改，版本信息，是否有密码，页数等，详见代码演示部分
• pdf_text：提取文件中的所有文字或非文字信息，包括分页符、换行符
• pdf_data：提取数字型数据，这个提取的结果会因PDF文件而异，有时可以直接将期刊中的数据完整地提取出来，有时又会因为PDF文档在创建时使用了不一致的分隔符而导致数据提取不完整
• pdf_fonts：提取文档的字体信息
• pdf_attachments：提取文档附件
• pdf_toc：提取文档目录

数据导入函数'pdf_info参数详解：

• pdf：PDF文件路径，可以是网络链接
• opw：PDF文件所有者的密码
• upw：PDF文件用户的密码

由于篇幅有限，下面的代码只截取了部分结果进行解释。这里所用的PDF文档是pdftools包的帮助文档，读者可以自行到R官网上搜索下载。帮助文档是开放PDF文件，无须提供密码。读取文档代码如下：

> library(pdftools) > pdf_info(pdf = "./helpDocs/pdftools.pdf") ## $version ## [1] "1.5" ## ## $pages ## [1] 5 ...

当使用pdf_text提取文档内容时，全部内容都被提取为一个字符串向量，每页的内容都被单独放置于一个字符串中。帮助文档的PDF格式一共包含5页，所以这里会得到一个长度为5的字符串向量。

有两种方式可用于查看提取的文本：可以直接将结果显示在console中（通过执行print(text)或直接运行text），也可以通过“[ ]”来指定显示某一页的内容。空白的位置都会以空格的字符格式显示，“\r\n”代表换行符号。提取文档内容的代码如下：

> text<- pdf_text("./helpDocs/pdftools.pdf") > length(text) ## [1] 5 > class(text) ## [1] "character" > text[1] ## [1] " Package ‘pdftools’\r\n May 27, 2018\r\nType Package\r\nTitle Text Extraction, Rendering and Converting of PDF Documents\r\nVersion 1.8\r\nDescription Utilities based on 'libpoppler' for extracting text, fonts, attachments and\r\n

该文档无附件，所以会显示一个空列表：

> pdf_attachments(pdf = "./helpDocs/pdftools.pdf") ## list()

文档中一共包含了6种字体，pdf_fonts会给出字体的名称、类型、是否嵌入文档中这三类信息，具体如下：

> pdf_fonts(pdf = "./helpDocs/pdftools.pdf") ## name type embedded file ## 1 DSHWTW NimbusRomNo9L-Medi type1 TRUE ## 2 UTHPMJ NimbusRomNo9L-Regu type1 TRUE ## 3 DSQFGA Inconsolata-zi4r type1 TRUE ## 4 LVIJIF NimbusSanL-Regu type1 TRUE ## 5 DQRZJT NimbusRomNo9L-Regu-Slant_167 type1 TRUE ## 6 YIECHJ NimbusRomNo9L-ReguItal type1 TRUE

目录读取的子函数会将所读取的内容返回到一个列表中，如果直接将该列表显示在console中很可能会让人感觉不知所云，读者可以自行实践。最好的办法是将读取的内容使用JSONlite包转换成json列表的格式进行显示，以帮助理解文档的架构。jsonlite包转换成json列表的示例代码如下：

> jsonlite::toJSON(x = pdf_toc(pdf = "./helpDocs/pdftools.pdf"), pretty = TRUE) ## { ## "title": "", ## "children": [ ## { ## "title": "pdf_info", ## "children": [] ## }, ## { ## "title": "pdf_render_page", ## "children": [] ## }, ## { ## "title": "Index", ## "children": [] ## } ## ] ## } 04 jsonlite：JSON文件

JavaScript Object Notation（JSON）通常是作为不同语言之间互相交流信息的文件，JSON文件不但节省存储空间，其简洁明了的形式也很容易理解。

jsonlite包既能够完整地将JSON格式的文件完整地解析和读取到R语言中来，也可以将任何常见的R对象（object）输出成JSON格式。本文03节中，toJSON函数可用来将PDF文档目录转换成JSON格式，以便于理解各层级之间的关系。

读取JSON文件的fromJSON函数共包含6个参数，通常情况下，除了指定文件路径之外，其他参数使用默认设置即可。

数据导入函数fromJSON参数详解：

• txt：可以是一段JSON格式的字符串，网络链接或者文件路径加文件名
• simplifyVector：将有序数组中的原始值强制转置成原子向量，可以简单理解为只保留数据，有真假两种设置，默认为真，如果设置为假，则数据会被读取为一个列表，列表中会包含子列表，子列表中会列出变量名和相应的数据值。详见代码演示部分
• simplifyDataFrame：将JSON数组中的记录强制转换成数据集（data frame）
• simplifyMatrix：将JSON数组中的向量强制转换成矩阵或数组
• flatten：自动将嵌套的数据集转换成非嵌套的平面数据集
• …：设置显示方法

首先以JSON常见的数组形式创建一个字符串向量，保存为example。中括号代表数组的起始，双引号中代表值，值与值之间以逗号进行分隔，然后再用单引号将这一数组格式保存到字符串向量中。因为example中的数组是按照JSON格式输入的，所以直接使用fromJSON函数即可。

在默认的参数设置下，可以得到一个包含4个值的R对象—字符串向量。运行fromJSON前后的这两个字符串向量，虽然名字一样，但内容完全不同，感兴趣的读者可以单独运行example来对比其区别所在。formJSON示例代码如下：

> example <- '["a", "b", 0, "c"]' > fromJSON(example) ## [1] "a" "b" "0" "c"

当参数simplifyVector被指定为假时，返回结果为一个包含4个元素的列表。4个元素即代表共有4个值，每一个值都以列表的形式返回。

当JSON格式的原始数据文件有多重嵌套时，可以通过设置参数来查看数据结构和正确读取数据。不过，一般情况下还是建议读者使用非嵌套数据来练习和使用R语言与JSON格式数据进行交互，待有一定了解后再提高难度。返回结果如下：

> fromJSON(example,simplifyVector = F) ## [[1]] ## [1] "a" ## ## [[2]] ## [1] "b" ## ## [[3]] ## [1] 0 ## ## [[4]] ## [1] "c" 05 foreign package统计软件数据

在世界范围内，开源的数据分析工具正在逐步取代传统数据分析软件，例如SAS、SPSS。在这一过程中，foreign包可以让我们无缝连接以传统分析软件格式保存的数据。该包也是集读取和写入于一体。

因为开源统计分析软件在世界范围内不可逆转的上升势头，传统分析软件的使用频率越来越低，其数据格式也渐渐被边缘化，本节只列出读取相应拓展名所需的函数以备读者不时之需，而不会做进一步的代码演示。

数据导入程序包foreign中数据读取函数及对应读取文件一览：

• .xpt：lookup.xport
• ARFF files：read.arff
• .dbf：read.dbf
• Stata Binary Files：read.dta
• .rec：read.epiinfo
• .mtp：read.mtp
• .sav：read.spss
• .syd：read.systat

关于作者：刘健，资深R语言技术专家，数据科学工程师。在新西兰皇家植物与食品研究院工作，参与一项国际和两项国家的级别研究项目，使用R语言开发完成气象数据自动提取和模型文件自动化工具。独立开发完成R语言程序包一个。

邬书豪，资深R语言用户和数据分析工程师，是数据科学领域10万粉丝的知名公众号人工智能爱好者社区的负责人，公众号文章阅读量破百万。喜欢用SQL、R和Python解决工作中的数据科学问题，致力于成为一名有深度行业实践经验积累的数据科学家。

本文摘编自《R数据科学实战：工具详解与案例分析》，经出版方授权发布。

延伸阅读《R数据科学实战：工具详解与案例分析》

推荐语：这是一部能指导零基础的读者快速掌握R语言并利用R语言进入数据科学领域的著作。两位作者在R语言和数据科学领域有丰富的实践经验，首先是非常有针对性地讲解了利用R语言进行数据处理需要掌握和使用的6大类17种工具，然后是结合这些工具的使用给出了5个典型的综合性案例，帮助读者迅速将理论与实践融会贯通。