Read excel files (.xls and .xlsx) into R 🖇
C++ C R Objective-C
Clone or download
apreshill and jennybc add mini-vignette on multiple header rows (#494)
* add mini-vignette on multiple header rows

* fix things I didn't mean to touch

* added news bullet

* point to blog post at end of vignette
Latest commit 75cffbf Jul 12, 2018
Permalink
Failed to load latest commit information.
.github Mod CODEOWNERS to stop auto-notification (#489) Jul 3, 2018
R Make sure path is in native encoding (#477) May 11, 2018
clippy Add Excel logo May 10, 2017
docs Add link to v1.1.0 blog post May 17, 2018
inst/extdata Add a 2nd worksheet to clippy.xls[x] Jul 10, 2018
man Refactor file format guessing Apr 17, 2018
revdep Make revdep notes more legible Apr 17, 2018
src Make sure path is in native encoding (#477) May 11, 2018
talks Add 2017 May RStudio webinar materials May 11, 2017
tests Make sure path is in native encoding (#477) May 11, 2018
tools Updates based on win-builder results Apr 15, 2017
vignettes add mini-vignette on multiple header rows (#494) Jul 12, 2018
.Rbuildignore Patch to https://github.com/evanmiller/libxls/dd658814c97d453f592ea5e… Apr 26, 2018
.gitignore Patch to https://github.com/evanmiller/libxls/d2726da2e37c7dd95af8dd4… Apr 27, 2018
.travis.yml Hide internal symbols on supported platforms (#384) Oct 21, 2017
DESCRIPTION Patch to https://github.com/evanmiller/libxls/d2726da2e37c7dd95af8dd4… Apr 27, 2018
LICENSE.note Use LICENSE.note to provide libxls license Apr 18, 2017
NAMESPACE Refactor file format guessing Apr 17, 2018
NEWS.md add mini-vignette on multiple header rows (#494) Jul 12, 2018
README.Rmd Canonical URL Apr 18, 2018
README.md Canonical URL Apr 18, 2018
_pkgdown.yml Add link to v1.1.0 blog post May 17, 2018
appveyor.yml Turn on appveyor Jan 17, 2017
codecov.yml Turn on codecov Jan 20, 2017
cran-comments.md Update cran-comments Apr 19, 2018
logo.png Update to blue Sad Clippy Apr 6, 2017
readxl.Rproj Rename to readxl Mar 18, 2015

README.md

readxl

Travis-CI Build Status AppVeyor Build Status Coverage Status CRAN_Status_Badge lifecycle

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 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 C++ library.

Installation

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

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:

install.packages("readxl")

Or install the development version from GitHub:

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

Usage

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.

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.

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.10        3.50         1.40       0.200 setosa 
#> 2         4.90        3.00         1.40       0.200 setosa 
#> 3         4.70        3.20         1.30       0.200 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.10        3.50         1.40       0.200 setosa 
#> 2         4.90        3.00         1.40       0.200 setosa 
#> 3         4.70        3.20         1.30       0.200 setosa 
#> # ... with 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 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.80      41.
#> 2 -20.6  181.  650.  4.20      15.
#> 3 -26.0  184.   42.  5.40      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.

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.10        3.50         1.40       0.200 setosa 
#> 2         4.90        3.00         1.40       0.200 setosa 
#> 3         4.70        3.20         1.30       0.200 setosa
read_excel(xlsx_example, range = "C1:E4")
#> # A tibble: 3 x 3
#>   Petal.Length Petal.Width Species
#>          <dbl>       <dbl> <chr>  
#> 1         1.40       0.200 setosa 
#> 2         1.40       0.200 setosa 
#> 3         1.30       0.200 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.10        3.50         1.40       0.200 setosa 
#> 2         4.90        3.00         1.40       0.200 setosa 
#> 3         4.70        3.20         1.30       0.200 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.50         1.40       0.200
#> 2        3.00         1.40       0.200
#> 3        3.20         1.30       0.200
#> # ... 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 NAs are represented by something other than blank cells, set the na argument.

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.10        3.50         1.40       0.200 <NA>   
#> 2         4.90        3.00         1.40       0.200 <NA>   
#> 3         4.70        3.20         1.30       0.200 <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 in R for Data Science. readxl will become increasingly consistent with other packages, such as readr.

Articles

You can find more detail in these articles:

  • Cell and Column Types
  • Sheet Geometry
  • readxl Workflows

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.

  • Returns a tibble, 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 (and Excel). openxlsx provides “a high level interface to writing, styling and editing 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 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. 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 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 this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.