# readxl

## 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

install.packages("tidyverse")
```

NOTE: you will still need to load readxl explicitly, because it is not a
core tidyverse package loaded via
[`library(tidyverse)`](https://tidyverse.tidyverse.org).

Alternatively, install just readxl from CRAN:

``` r

install.packages("readxl")
```

Or install the development version from GitHub:

``` r

#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.

[![](https://raw.githubusercontent.com/rstudio/cheatsheets/main/pngs/thumbnails/data-import-cheatsheet-thumbs.png)](https://github.com/rstudio/cheatsheets/blob/main/data-import.pdf)

## Usage

``` r

library(readxl)
```

readxl includes several example files, which we use throughout the
documentation. Use the helper
[`readxl_example()`](https://readxl.tidyverse.org/reference/readxl_example.md)
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] "/private/tmp/RtmpkPT2Y6/temp_libpathfed65a294c90/readxl/extdata/clippy.xls"
```

[`read_excel()`](https://readxl.tidyverse.org/reference/read_excel.md)
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: 32 × 11
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> # ℹ 29 more rows

xls_example <- readxl_example("datasets.xls")
read_excel(xls_example)
#> # A tibble: 32 × 11
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> # ℹ 29 more rows
```

List the sheet names with
[`excel_sheets()`](https://readxl.tidyverse.org/reference/excel_sheets.md).

``` r

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

Specify a worksheet by name or number.

``` r

read_excel(xlsx_example, sheet = "chickwts")
#> # A tibble: 71 × 2
#>   weight feed     
#>    <dbl> <chr>    
#> 1    179 horsebean
#> 2    160 horsebean
#> 3    136 horsebean
#> # ℹ 68 more rows
read_excel(xls_example, sheet = 3)
#> # A tibble: 1,000 × 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
#> # ℹ 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 × 11
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
read_excel(xlsx_example, range = "C1:E4")
#> # A tibble: 3 × 3
#>    disp    hp  drat
#>   <dbl> <dbl> <dbl>
#> 1   160   110  3.9 
#> 2   160   110  3.9 
#> 3   108    93  3.85
read_excel(xlsx_example, range = cell_rows(1:4))
#> # A tibble: 3 × 11
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5     0     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0     0     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
read_excel(xlsx_example, range = cell_cols("B:D"))
#> # A tibble: 32 × 3
#>     cyl  disp    hp
#>   <dbl> <dbl> <dbl>
#> 1     6   160   110
#> 2     6   160   110
#> 3     4   108    93
#> # ℹ 29 more rows
read_excel(xlsx_example, range = "mtcars!B1:D5")
#> # A tibble: 4 × 3
#>     cyl  disp    hp
#>   <dbl> <dbl> <dbl>
#> 1     6   160   110
#> 2     6   160   110
#> 3     4   108    93
#> # ℹ 1 more row
```

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

``` r

read_excel(xlsx_example, na = "0")
#> # A tibble: 32 × 11
#>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1  21       6   160   110  3.9   2.62  16.5    NA     1     4     4
#> 2  21       6   160   110  3.9   2.88  17.0    NA     1     4     4
#> 3  22.8     4   108    93  3.85  2.32  18.6     1     1     4     1
#> # ℹ 29 more rows
```

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

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”.

# Package index

## Read spreadsheets

Functions for reading tabular data out of xls and xlsx files.

- [`read_excel()`](https://readxl.tidyverse.org/reference/read_excel.md)
  [`read_xls()`](https://readxl.tidyverse.org/reference/read_excel.md)
  [`read_xlsx()`](https://readxl.tidyverse.org/reference/read_excel.md)
  : Read xls and xlsx files

## Get spreadsheet metadata

Functions to learn properties of xls and xlsx files.

- [`excel_sheets()`](https://readxl.tidyverse.org/reference/excel_sheets.md)
  : List all sheets in an excel spreadsheet
- [`excel_format()`](https://readxl.tidyverse.org/reference/excel_format.md)
  [`format_from_ext()`](https://readxl.tidyverse.org/reference/excel_format.md)
  [`format_from_signature()`](https://readxl.tidyverse.org/reference/excel_format.md)
  : Determine file format

## Describe a target rectangle

Flexible specification of cell rectangles.

- [`cell-specification`](https://readxl.tidyverse.org/reference/cell-specification.md)
  [`cell_limits`](https://readxl.tidyverse.org/reference/cell-specification.md)
  [`cell_rows`](https://readxl.tidyverse.org/reference/cell-specification.md)
  [`cell_cols`](https://readxl.tidyverse.org/reference/cell-specification.md)
  [`anchored`](https://readxl.tidyverse.org/reference/cell-specification.md)
  : Specify cells for reading

## Example files

List or get the path to xls and xlsx example files in the package.

- [`readxl_example()`](https://readxl.tidyverse.org/reference/readxl_example.md)
  : Get path to readxl example

## Options

Options consulted by readxl.

- [`readxl_progress()`](https://readxl.tidyverse.org/reference/readxl_progress.md)
  : Determine whether to show progress spinner

# Articles

### All vignettes

- [Cell and Column
  Types](https://readxl.tidyverse.org/articles/cell-and-column-types.md):
- [Column Names](https://readxl.tidyverse.org/articles/column-names.md):
- [Multiple Header
  Rows](https://readxl.tidyverse.org/articles/multiple-header-rows.md):
- [readxl
  Workflows](https://readxl.tidyverse.org/articles/readxl-workflows.md):
- [Sheet
  Geometry](https://readxl.tidyverse.org/articles/sheet-geometry.md):