• Stars
    star
    719
  • Rank 61,478 (Top 2 %)
  • Language
    C++
  • License
    Other
  • Created over 9 years ago
  • Updated 4 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Read excel files (.xls and .xlsx) into R 🖇

readxl

CRAN_Status_Badge R-CMD-check Codecov test coverage 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("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.

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] "/private/tmp/Rtmpjectat/temp_libpath3b7822c649d8/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 × 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 × 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().

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
#> # … with 68 more rows
read_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
#> # … 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 × 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 × 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 × 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 × 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 × 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 × 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 in R for Data Science. readxl will become increasingly consistent with other packages, such as readr.

Articles

Broad topics are explained in these articles:

We also have some focused articles that address specific aggravations presented 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) 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, 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”.

More Repositories

1

ggplot2

An implementation of the Grammar of Graphics in R
R
6,368
star
2

dplyr

dplyr: A grammar of data manipulation
R
4,675
star
3

tidyverse

Easily install and load packages from the tidyverse
R
1,610
star
4

rvest

Simple web scraping for R
R
1,481
star
5

tidyr

Tidy Messy Data
R
1,347
star
6

purrr

A functional programming toolkit for R
R
1,235
star
7

readr

Read flat files (csv, tsv, fwf) into R
R
998
star
8

magrittr

Improve the readability of R code with the pipe
R
955
star
9

datascience-box

Data Science Course in a Box
JavaScript
908
star
10

reprex

Render bits of R code for sharing, e.g., on GitHub or StackOverflow.
R
732
star
11

lubridate

Make working with dates in R just that little bit easier
R
721
star
12

glue

Glue strings to data in R. Small, fast, dependency free interpreted string literals.
R
689
star
13

dtplyr

Data table backend for dplyr
R
656
star
14

tibble

A modern re-imagining of the data frame
R
649
star
15

multidplyr

A dplyr backend that partitions a data frame over multiple processes
R
638
star
16

vroom

Fast reading of delimited files
C++
609
star
17

stringr

A fresh approach to string manipulation in R
R
583
star
18

forcats

🐈🐈🐈🐈: tools for working with categorical variables (factors)
R
538
star
19

dbplyr

Database (DBI) backend for dplyr
R
466
star
20

haven

Read SPSS, Stata and SAS files from R
C
421
star
21

modelr

Helper functions for modelling
R
399
star
22

googlesheets4

Google Spreadsheets R API (reboot of the googlesheets package)
R
354
star
23

googledrive

Google Drive R API
R
316
star
24

style

The tidyverse style guide for R code
HTML
290
star
25

design

Tidyverse design principles
R
211
star
26

tidyverse.org

Source of tidyverse.org
HTML
189
star
27

hms

A simple class for storing time-of-day values
R
137
star
28

nycflights13

An R data package containing all out-bound flights from NYC in 2013 + useful metdata
R
124
star
29

tidyversedashboard

Tidyverse activity dashboard
R
71
star
30

tidy-dev-day

Tidyverse developer day
60
star
31

tidyeval

A guide to tidy evaluation
CSS
54
star
32

dsbox

Companion R package to Data Science Course in a Box
R
48
star
33

tidytemplate

A pkgdown template for core tidyverse packages
SCSS
46
star
34

blob

A simple S3 class for representing BLOBs
R
44
star
35

code-review

32
star
36

funs

Collection of low-level functions for working with vctrs
R
31
star
37

website-analytics

Web analytics for tidyverse + r-lib sites
R
28
star
38

tidyups

21
star
39

ggplot2-docs

ggplot2 documentation. Auto-generated from ggplot2 sources by pkgdown
HTML
10
star