-
Notifications
You must be signed in to change notification settings - Fork 194
/
README.Rmd
158 lines (105 loc) · 6.7 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
```{r, echo = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "README-"
)
```
# readxl <a href="https://readxl.tidyverse.org"><img src="man/figures/logo.png" align="right" height="138" /></a>
<!-- badges: start -->
[](https://cran.r-project.org/package=readxl)
[](https://github.com/tidyverse/readxl/actions/workflows/R-CMD-check.yaml)
[](https://app.codecov.io/gh/tidyverse/readxl?branch=main)
[](https://lifecycle.r-lib.org/articles/stages.html)
<!-- badges: end -->
## Overview
The readxl package makes it easy to get data out of Excel and into R. Compared to many of the existing packages (e.g. gdata, xlsx, xlsReadWrite) readxl has no external dependencies, so it's easy to install and use on all operating systems. It is designed to work with _tabular_ data.
readxl supports both the legacy `.xls` format and the modern xml-based `.xlsx` format. The [libxls](https://github.com/libxls/libxls) C library is used to support `.xls`, which abstracts away many of the complexities of the underlying binary format. To parse `.xlsx`, we use the [RapidXML](https://rapidxml.sourceforge.net/) C++ library.
## Installation
The easiest way to install the latest released version from CRAN is to install the whole tidyverse.
```{r eval = FALSE}
install.packages("tidyverse")
```
NOTE: you will still need to load readxl explicitly, because it is not a core tidyverse package loaded via `library(tidyverse)`.
Alternatively, install just readxl from CRAN:
```{r eval = FALSE}
install.packages("readxl")
```
Or install the development version from GitHub:
```{r eval = FALSE}
#install.packages("pak")
pak::pak("tidyverse/readxl")
```
## Cheatsheet
You can see how to read data with readxl in the **data import cheatsheet**, which also covers similar functionality in the related packages readr and googlesheets4.
<a href="https://github.com/rstudio/cheatsheets/blob/main/data-import.pdf"><img src="https://raw.githubusercontent.com/rstudio/cheatsheets/main/pngs/thumbnails/data-import-cheatsheet-thumbs.png" width="630" height="252"/></a>
## Usage
```{r include = FALSE}
options(tibble.print_min = 3, tibble.print_max = 3)
```
```{r}
library(readxl)
```
readxl includes several example files, which we use throughout the documentation. Use the helper `readxl_example()` with no arguments to list them or call it with an example filename to get the path.
```{r}
readxl_example()
readxl_example("clippy.xls")
```
`read_excel()` reads both xls and xlsx files and detects the format from the extension.
```{r}
xlsx_example <- readxl_example("datasets.xlsx")
read_excel(xlsx_example)
xls_example <- readxl_example("datasets.xls")
read_excel(xls_example)
```
List the sheet names with `excel_sheets()`.
```{r}
excel_sheets(xlsx_example)
```
Specify a worksheet by name or number.
```{r}
read_excel(xlsx_example, sheet = "chickwts")
read_excel(xls_example, sheet = 3)
```
There are various ways to control which cells are read. You can even specify the sheet here, if providing an Excel-style cell range.
```{r}
read_excel(xlsx_example, n_max = 3)
read_excel(xlsx_example, range = "C1:E4")
read_excel(xlsx_example, range = cell_rows(1:4))
read_excel(xlsx_example, range = cell_cols("B:D"))
read_excel(xlsx_example, range = "mtcars!B1:D5")
```
If `NA`s are represented by something other than blank cells, set the `na` argument.
```{r}
read_excel(xlsx_example, na = "0")
```
If you are new to the tidyverse conventions for data import, you may want to consult the [data import chapter](https://r4ds.had.co.nz/data-import.html) in R for Data Science. readxl will become increasingly consistent with other packages, such as [readr](https://readr.tidyverse.org/).
## Articles
Broad topics are explained in [these articles](https://readxl.tidyverse.org/articles/index.html):
* [Cell and Column Types](https://readxl.tidyverse.org/articles/cell-and-column-types.html)
* [Sheet Geometry](https://readxl.tidyverse.org/articles/sheet-geometry.html): how to specify which cells to read
* [readxl Workflows](https://readxl.tidyverse.org/articles/articles/readxl-workflows.html): Iterating over multiple tabs or worksheets, stashing a csv snapshot
We also have some focused articles that address specific aggravations presented by the world's spreadsheets:
* [Column Names](https://readxl.tidyverse.org/articles/articles/column-names.html)
* [Multiple Header Rows](https://readxl.tidyverse.org/articles/articles/multiple-header-rows.html)
## Features
* No external dependency on, e.g., Java or Perl.
* Re-encodes non-ASCII characters to UTF-8.
* Loads datetimes into POSIXct columns. Both Windows (1900) and Mac (1904)
date specifications are processed correctly.
* Discovers the minimal data rectangle and returns that, by default. User can exert more control with `range`, `skip`, and `n_max`.
* Column names and types are determined from the data in the sheet, by default. User can also supply via `col_names` and `col_types` and control name repair via `.name_repair`.
* Returns a [tibble](https://tibble.tidyverse.org/reference/tibble.html), i.e. a data frame with an additional `tbl_df` class. Among other things, this provide nicer printing.
## Other relevant packages
Here are some other packages with functionality that is complementary to readxl and that also avoid a Java dependency.
__Writing Excel files__: The example files `datasets.xlsx` and `datasets.xls` were created with the help of [openxlsx](https://CRAN.R-project.org/package=openxlsx) (and Excel). openxlsx provides "a high level interface to writing, styling and editing worksheets".
```{r eval = FALSE}
l <- list(mtcars = mtcars, chickwts = chickwts, quakes = quakes)
openxlsx::write.xlsx(l, file = "inst/extdata/datasets.xlsx")
```
[writexl](https://cran.r-project.org/package=writexl) is a new option in this space, first released on CRAN in August 2017. It's a portable and lightweight way to export a data frame to xlsx, based on [libxlsxwriter](https://github.com/jmcnamara/libxlsxwriter). It is much more minimalistic than openxlsx, but on simple examples, appears to be about twice as fast and to write smaller files.
__Non-tabular data and formatting__: [tidyxl](https://cran.r-project.org/package=tidyxl) is focused on importing awkward and non-tabular data from Excel. It also "exposes cell content, position and formatting in a tidy structure for further manipulation".