Data

Tools

Indexes

Applications

Experiments

Open Source Science

readxl

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

https://github.com/tidyverse/readxl

Science Score: 36.0%

This score indicates how likely this project is to be science-related based on various indicators:

  • ○
    CITATION.cff file
  • ✓
    codemeta.json file
    Found codemeta.json file
  • ✓
    .zenodo.json file
    Found .zenodo.json file
  • ○
    DOI references
  • ○
    Academic publication links
  • ✓
    Committers with academic emails
    4 of 42 committers (9.5%) from academic institutions
  • ○
    Institutional organization owner
  • ○
    JOSS paper metadata
  • ○
    Scientific vocabulary similarity
    Low similarity (20.7%) to scientific vocabulary

Keywords

excel r spreadsheet xls xlsx

Keywords from Contributors

tidy-data data-manipulation grammar visualisation setup rmarkdown unit-testing package-creation fwf parsing
Last synced: 6 months ago · JSON representation

Repository

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

Basic Info
Statistics
  • Stars: 742
  • Watchers: 38
  • Forks: 196
  • Open Issues: 50
  • Releases: 13
Topics
excel r spreadsheet xls xlsx
Created almost 11 years ago · Last pushed 12 months ago
Metadata Files
Readme Changelog Contributing License Code of conduct Codeowners Support

README.Rmd 

---
output: github_document
---



```{r, echo = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-"
)
```

# readxl 


[![CRAN_Status_Badge](https://www.r-pkg.org/badges/version/readxl)](https://cran.r-project.org/package=readxl)
[![R-CMD-check](https://github.com/tidyverse/readxl/actions/workflows/R-CMD-check.yaml/badge.svg)](https://github.com/tidyverse/readxl/actions/workflows/R-CMD-check.yaml)
[![Codecov test coverage](https://codecov.io/gh/tidyverse/readxl/branch/main/graph/badge.svg)](https://app.codecov.io/gh/tidyverse/readxl?branch=main)
[![lifecycle](https://img.shields.io/badge/lifecycle-stable-brightgreen.svg)](https://lifecycle.r-lib.org/articles/stages.html)


## 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 eval = FALSE}
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 eval = FALSE}
install.packages("readxl")
```

Or install the development version from GitHub:
 
```{r eval = FALSE}
#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

```{r include = FALSE}
options(tibble.print_min = 3, tibble.print_max = 3)
```

```{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()
readxl_example("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)

xls_example <- readxl_example("datasets.xls")
read_excel(xls_example)
```

List the sheet names with `excel_sheets()`.

```{r}
excel_sheets(xlsx_example)
```

Specify a worksheet by name or number.

```{r}
read_excel(xlsx_example, sheet = "chickwts")
read_excel(xls_example, sheet = 3)
```

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)
read_excel(xlsx_example, range = "C1:E4")
read_excel(xlsx_example, range = cell_rows(1:4))
read_excel(xlsx_example, range = cell_cols("B:D"))
read_excel(xlsx_example, range = "mtcars!B1:D5")
```

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

```{r}
read_excel(xlsx_example, na = "0")
```

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 eval = FALSE}
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".

Owner

  • Name: tidyverse
  • Login: tidyverse
  • Kind: organization

The tidyverse is a collection of R packages that share common principles and are designed to work together seamlessly

GitHub Events

Total
  • Create event: 4
  • Release event: 2
  • Issues event: 20
  • Watch event: 21
  • Delete event: 2
  • Issue comment event: 36
  • Push event: 71
  • Pull request event: 14
  • Fork event: 3
Last Year
  • Create event: 4
  • Release event: 2
  • Issues event: 20
  • Watch event: 21
  • Delete event: 2
  • Issue comment event: 36
  • Push event: 71
  • Pull request event: 14
  • Fork event: 3

Committers

Last synced: 8 months ago

All Time
  • Total Commits: 661
  • Total Committers: 42
  • Avg Commits per committer: 15.738
  • Development Distribution Score (DDS): 0.499
Past Year
  • Commits: 30
  • Committers: 7
  • Avg Commits per committer: 4.286
  • Development Distribution Score (DDS): 0.267
Top Committers
Name Email Commits
Jenny Bryan j****n@g****m 331
hadley h****m@g****m 154
jennybc j****y@s****a 78
Jeroen Ooms j****s@g****m 11
Jonathan Marshall j****l@n****d 10
Jonathan Marshall j****l@x****g 8
Mara Averick m****k@g****m 7
Jim Hester j****r@g****m 6
Jirka Lewandowski j****i@p****e 6
Bastiaan Quast b****t@g****m 4
Sergio Oller s****r@g****m 4
Shelby Bearrows 3****s 4
Gábor Csárdi c****r@g****m 3
Duncan Garmonsway d****y@d****k 3
Michael Chirico c****m@g****m 3
Anatoliy Sokolov a****v@d****m 2
Davis Vaughan d****s@p****o 2
Fernando Munoz Mendez f****2@g****m 1
Derek Chiu d****u@b****a 1
Erin Grand e****8@c****u 1
Fonti Kar f****r@u****u 1
Rohan Shah r****h 1
Sam Albers s****s@g****m 1
Stephen Connolly s****y@g****m 1
Thomas Klebel t****l 1
olivroy 5****y 1
struckma s****n@u****e 1
Pedram Navid p****d@g****m 1
Ben Marwick b****k@h****m 1
Averi Perny 1****y 1
and 12 more...
Committer Domains (Top 20 + Academic)
pobox.com: 1 uni-rostock.de: 1 unsw.edu.au: 1 columbia.edu: 1 bccrc.ca: 1 posit.co: 1 dow.com: 1 google.com: 1 digital.cabinet-office.gov.uk: 1 posteo.de: 1 xbmc.org: 1 never.you.mind: 1 stat.ubc.ca: 1

Issues and Pull Requests

Last synced: 6 months ago

All Time
  • Total issues: 114
  • Total pull requests: 41
  • Average time to close issues: 12 months
  • Average time to close pull requests: 4 months
  • Total issue authors: 90
  • Total pull request authors: 19
  • Average comments per issue: 3.12
  • Average comments per pull request: 1.22
  • Merged pull requests: 24
  • Bot issues: 0
  • Bot pull requests: 0
Past Year
  • Issues: 11
  • Pull requests: 13
  • Average time to close issues: 26 days
  • Average time to close pull requests: 28 days
  • Issue authors: 7
  • Pull request authors: 5
  • Average comments per issue: 2.0
  • Average comments per pull request: 0.77
  • Merged pull requests: 7
  • Bot issues: 0
  • Bot pull requests: 0
View more stats
Top Authors
Issue Authors
  • jennybc (20)
  • hidekoji (2)
  • ramiromagno (2)
  • moodymudskipper (2)
  • mkoohafkan (2)
  • matthiasgomolka (2)
  • ucb (1)
  • jeromyanglim (1)
  • misea (1)
  • MeWu-IDM (1)
  • Knutchanat (1)
  • mbeer (1)
  • kfhk (1)
  • Brunox13 (1)
  • rik-glt (1)
Pull Request Authors
  • jennybc (12)
  • gaborcsardi (5)
  • olivroy (4)
  • SokolovAnatoliy (4)
  • jeroen (3)
  • alberthat (2)
  • drorberel (2)
  • spaette (2)
  • DavisVaughan (2)
  • stephenc (1)
  • MichaelChirico (1)
  • cderv (1)
  • elgabbas (1)
  • KaiAragaki (1)
  • DanChaltiel (1)
Top Labels
Issue Labels
bug (8) feature (8) cells :black_square_button: (8) xls 👵 (7) col_types (6) libxls (6) xlsx :paperclip: (5) datetime :calendar: (5) reprex (3) upkeep (3) formats :nail_care: (3) filepaths 📁 (3) documentation (2) problems :exclamation: (2) tidy-dev-day :nerd_face: (1)
Pull Request Labels

Packages

  • Total packages: 2
  • Total downloads:
    • cran 949,905 last-month
  • Total docker downloads: 45,933,370
  • Total dependent packages: 377
    (may contain duplicates)
  • Total dependent repositories: 1,823
    (may contain duplicates)
  • Total versions: 26
  • Total maintainers: 1
cran.r-project.org: readxl

Read Excel Files

  • Versions: 13
  • Dependent Packages: 377
  • Dependent Repositories: 1,823
  • Downloads: 949,905 Last month
  • Docker Downloads: 45,933,370
Rankings
Dependent repos count: 0.2%
Downloads: 0.3%
Forks count: 0.3%
Dependent packages count: 0.3%
Stargazers count: 0.5%
Average: 3.2%
Docker downloads count: 17.3%
Maintainers (1)
jenny@posit.co
Last synced: 6 months ago
proxy.golang.org: github.com/tidyverse/readxl
  • Versions: 13
  • Dependent Packages: 0
  • Dependent Repositories: 0
Rankings
Dependent packages count: 5.5%
Average: 5.7%
Dependent repos count: 5.9%
Last synced: 6 months ago

Dependencies

DESCRIPTION cran
  • R >= 3.4 depends
  • cellranger * imports
  • tibble >= 2.0.1 imports
  • utils * imports
  • knitr * suggests
  • rmarkdown * suggests
  • testthat >= 3.0.0 suggests
.github/workflows/R-CMD-check.yaml actions
  • actions/checkout v3 composite
  • r-lib/actions/check-r-package v2 composite
  • r-lib/actions/setup-pandoc v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
.github/workflows/pkgdown.yaml actions
  • JamesIves/github-pages-deploy-action v4.4.1 composite
  • actions/checkout v3 composite
  • r-lib/actions/setup-pandoc v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
.github/workflows/pr-commands.yaml actions
  • actions/checkout v3 composite
  • r-lib/actions/pr-fetch v2 composite
  • r-lib/actions/pr-push v2 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite
.github/workflows/test-coverage.yaml actions
  • actions/checkout v3 composite
  • actions/upload-artifact v3 composite
  • r-lib/actions/setup-r v2 composite
  • r-lib/actions/setup-r-dependencies v2 composite