readxl package - RDocumentation (2024)

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 toinstall and use on all operating systems. It is designed to work withtabular data.

readxl supports both the legacy .xls format and the modern xml-based.xlsx format. The libxls C libraryis used to support .xls, which abstracts away many of the complexitiesof the underlying binary format. To parse .xlsx, we use theRapidXML C++ library.

Installation

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

install.packages("tidyverse")

NOTE: you will still need to load readxl explicitly, because it is not acore tidyverse package loaded via library(tidyverse).

Alternatively, install just readxl from CRAN:

install.packages("readxl")

Or install the development version from GitHub:

Cheatsheet

You can see how to read data with readxl in the data importcheatsheet, which also covers similar functionality in the relatedpackages readr and googlesheets4.

Usage

library(readxl)

readxl includes several example files, which we use throughout thedocumentation. Use the helper readxl_example() with no arguments tolist them or call it with an example filename to get the path.

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/RtmpM1GkLC/temp_libpatha8e46f7f62bf/readxl/extdata/clippy.xls"

read_excel() reads both xls and xlsx files and detects the format fromthe extension.

xlsx_example <- readxl_example("datasets.xlsx")read_excel(xlsx_example)#> # A tibble: 150 × 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 #> # ℹ 147 more rowsxls_example <- readxl_example("datasets.xls")read_excel(xls_example)#> # A tibble: 150 × 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 #> # ℹ 147 more rows

List the sheet names with excel_sheets().

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

Specify a worksheet by name or number.

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 rowsread_excel(xls_example, sheet = 4)#> # 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 evenspecify the sheet here, if providing an Excel-style cell range.

read_excel(xlsx_example, n_max = 3)#> # A tibble: 3 × 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 setosaread_excel(xlsx_example, range = "C1:E4")#> # A tibble: 3 × 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 setosaread_excel(xlsx_example, range = cell_rows(1:4))#> # A tibble: 3 × 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 setosaread_excel(xlsx_example, range = cell_cols("B:D"))#> # A tibble: 150 × 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#> # ℹ 147 more rowsread_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 NAs are represented by something other than blank cells, set thena argument.

Articles

Broad topics are explained in thesearticles:

Cell and ColumnTypes
SheetGeometry:how to specify which cells to read
readxlWorkflows:Iterating over multiple tabs or worksheets, stashing a csv snapshot

We also have some focused articles that address specific aggravationspresented by the world’s spreadsheets:

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) andMac (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, bydefault. User can also supply via col_names and col_types andcontrol name repair via .name_repair.
Returns atibble, i.e.adata frame with an additional tbl_df class. Among other things, thisprovide nicer printing.

Other relevant packages

Here are some other packages with functionality that is complementary toreadxl and that also avoid a Java dependency.

Writing Excel files: The example files datasets.xlsx anddatasets.xls were created with the help ofopenxlsx (and Excel).openxlsx provides “a high level interface to writing, styling andediting worksheets”.

l <- list(iris = iris, mtcars = mtcars, chickwts = chickwts, quakes = quakes)openxlsx::write.xlsx(l, file = "inst/extdata/datasets.xlsx")

writexl is a new option inthis space, first released on CRAN in August 2017. It’s a portable andlightweight way to export a data frame to xlsx, based onlibxlsxwriter. It is muchmore minimalistic than openxlsx, but on simple examples, appears to beabout twice as fast and to write smaller files.

Non-tabular data and formatting:tidyxl is focused onimporting awkward and non-tabular data from Excel. It also “exposes cellcontent, position and formatting in a tidy structure for furthermanipulation”.