File: README.md

package info (click to toggle)
r-cran-readxl 1.3.1-1
  • links: PTS, VCS
  • area: main
  • in suites: sid
  • size: 4,488 kB
  • sloc: ansic: 4,565; cpp: 3,401; makefile: 2
file content (264 lines) | stat: -rw-r--r-- 9,597 bytes parent folder | download
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264

<!-- README.md is generated from README.Rmd. Please edit that file -->

# readxl <img src='man/figures/logo.png' align="right" height="139" />

[![Travis-CI Build
Status](https://travis-ci.org/tidyverse/readxl.svg?branch=master)](https://travis-ci.org/tidyverse/readxl)
[![AppVeyor Build
Status](https://ci.appveyor.com/api/projects/status/github/tidyverse/readxl?branch=master&svg=true)](https://ci.appveyor.com/project/tidyverse/readxl)
[![Coverage
Status](https://img.shields.io/codecov/c/github/tidyverse/readxl/master.svg)](https://codecov.io/github/tidyverse/readxl?branch=master)
[![CRAN\_Status\_Badge](https://www.r-pkg.org/badges/version/readxl)](https://cran.r-project.org/package=readxl)
[![lifecycle](https://img.shields.io/badge/lifecycle-stable-brightgreen.svg)](https://www.tidyverse.org/lifecycle/#stable)

## 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/evanmiller/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](http://rapidxml.sourceforge.net) C++ library.

## Installation

The easiest way to install the latest released version from CRAN is to
install the whole tidyverse.

``` r
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
install.packages("readxl")
```

Or install the development version from GitHub:

``` r
# install.packages("devtools")
devtools::install_github("tidyverse/readxl")
```

## Usage

``` 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()
#>  [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("clippy.xls")
#> [1] "/Users/jenny/resources/R/library/readxl/extdata/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)
#> # A tibble: 150 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa 
#> # … with 147 more rows

xls_example <- readxl_example("datasets.xls")
read_excel(xls_example)
#> # A tibble: 150 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa 
#> # … with 147 more rows
```

List the sheet names with `excel_sheets()`.

``` r
excel_sheets(xlsx_example)
#> [1] "iris"     "mtcars"   "chickwts" "quakes"
```

Specify a worksheet by name or number.

``` r
read_excel(xlsx_example, sheet = "chickwts")
#> # A tibble: 71 x 2
#>   weight feed     
#>    <dbl> <chr>    
#> 1    179 horsebean
#> 2    160 horsebean
#> 3    136 horsebean
#> # … with 68 more rows
read_excel(xls_example, sheet = 4)
#> # A tibble: 1,000 x 5
#>     lat  long depth   mag stations
#>   <dbl> <dbl> <dbl> <dbl>    <dbl>
#> 1 -20.4  182.   562   4.8       41
#> 2 -20.6  181.   650   4.2       15
#> 3 -26    184.    42   5.4       43
#> # … with 997 more rows
```

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)
#> # A tibble: 3 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa
read_excel(xlsx_example, range = "C1:E4")
#> # A tibble: 3 x 3
#>   Petal.Length Petal.Width Species
#>          <dbl>       <dbl> <chr>  
#> 1          1.4         0.2 setosa 
#> 2          1.4         0.2 setosa 
#> 3          1.3         0.2 setosa
read_excel(xlsx_example, range = cell_rows(1:4))
#> # A tibble: 3 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 setosa 
#> 2          4.9         3            1.4         0.2 setosa 
#> 3          4.7         3.2          1.3         0.2 setosa
read_excel(xlsx_example, range = cell_cols("B:D"))
#> # A tibble: 150 x 3
#>   Sepal.Width Petal.Length Petal.Width
#>         <dbl>        <dbl>       <dbl>
#> 1         3.5          1.4         0.2
#> 2         3            1.4         0.2
#> 3         3.2          1.3         0.2
#> # … with 147 more rows
read_excel(xlsx_example, range = "mtcars!B1:D5")
#> # A tibble: 4 x 3
#>     cyl  disp    hp
#>   <dbl> <dbl> <dbl>
#> 1     6   160   110
#> 2     6   160   110
#> 3     4   108    93
#> # … with 1 more row
```

If `NA`s are represented by something other than blank cells, set the
`na` argument.

``` r
read_excel(xlsx_example, na = "setosa")
#> # A tibble: 150 x 5
#>   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>          <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#> 1          5.1         3.5          1.4         0.2 <NA>   
#> 2          4.9         3            1.4         0.2 <NA>   
#> 3          4.7         3.2          1.3         0.2 <NA>   
#> # … with 147 more rows
```

If you are new to the tidyverse conventions for data import, you may
want to consult the [data import
chapter](http://r4ds.had.co.nz/data-import.html) in R for Data Science.
readxl will become increasingly consistent with other packages, such as
[readr](http://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](http://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
l <- list(iris = iris, 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”.

Please note that the readxl project is released with a [Contributor Code
of Conduct](.github/CODE_OF_CONDUCT.md). By contributing to this
project, you agree to abide by its terms.