/
README.Rmd
137 lines (98 loc) · 5.45 KB
/
README.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
---
output: github_document
---
<!-- README.md is generated from README.Rmd. Please edit that file -->
[](https://travis-ci.org/rsheets/cellranger)
[](https://ci.appveyor.com/project/rsheets/cellranger)
[](https://codecov.io/github/rsheets/cellranger?branch=master) [](http://dx.doi.org/10.5281/zenodo.21970)
[](https://cran.r-project.org/package=cellranger)
<img src="http://i.imgur.com/RJJy15I.jpg" width="270" align="right" />
```{r, echo = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
fig.path = "README-"
)
```
Helper package to support R scripts or packages that interact with spreadsheets.
### Installation
Option 1: Install from CRAN:
```{r eval = FALSE}
install.packages("cellranger")
```
Option 2: Install the development version from GitHub:
```{r eval = FALSE}
# install.packages("devtools")
devtools::install_github("jennybc/cellranger")
```
### What is `cellranger` for?
**Describe a rectangle of cells**. For example, what you've got is the string "D12:F15" and what you want is an R object that holds the row and column for the upper left and lower right corners of this rectangle. Read below about the `cell_limits` class. The [`googlesheets`](https://github.com/jennybc/googlesheets) and [`readODS`](https://github.com/chainsawriot/readODS) packages use `cellranger` to translate user-supplied cell range info into something more programmatically useful.
**Handle cell references found in spreadsheet formulas**. If you're parsing unevaluated spreadsheet formulas, use the `ra_ref` and `cell_addr` classes for handling absolute, relative, and mixed cell references. Classes inspired by [Spreadsheet Implementation Technology](https://mitpress.mit.edu/books/spreadsheet-implementation-technology) from Sestoft (MIT Press, 2014).
**Convert between annoying spreadsheet reference formats**. Some utility functions are exposed, such as `A1_to_R1C1()`, which converts from A1 formatted strings to R1C1, and `letter_to_num()`, which converts a Excel column ID to a number, e.g. column AQZ is more usefully known as column 1144.
### Describing rectangles via `cell_limits`
`cellranger` provides an S3 class, `cell_limits`, as the standard way to store a cell range. You can explicitly construct a `cell_limits` object by specifying the upper left and lower right cells and, optionally, the hosting worksheet:
```{r eval = FALSE}
cell_limits(ul = c(ROW_MIN, COL_MIN), lr = c(ROW_MAX, COL_MAX), sheet = "SHEET")
```
Think of it like `R3C1:R7C4` notation, but with the `R` and `C` removed.
More often you'll get a `cell_limits` object by sending diverse user input through `as.cell_limits()`. That's what's going on in calls like these from [`googlesheets`](https://github.com/jennybc/googlesheets):
```{r eval = FALSE}
library(googlesheets)
gs_read(..., range = "D12:F15")
gs_read(..., range = "raw_data!R1C12:R6C15")
gs_read(..., range = cell_limits(c(1, 1), c(6, 15)))
gs_read(..., range = cell_limits(c(2, 1), c(NA, NA)))
gs_read(..., range = cell_rows(1:100))
gs_read(..., range = cell_cols(3:8))
gs_read(..., range = cell_cols("B:MZ"))
gs_read(..., range = anchored("B4", dim = c(2, 10)))
gs_read(..., range = anchored("A1", dim = c(5, 6), col_names = TRUE))
## internal usage in functions that put data into a googlesheet
anchored(input = head(iris))
anchored(input = head(iris), col_names = FALSE)
anchored(input = head(LETTERS))
anchored(input = head(LETTERS), byrow = TRUE)
```
Read the docs for more information on some specialized helpers:
* Row- or column-only specification: `cell_rows()`, `cell_cols()`.
* Specification via an object you want to write and, optionally, an anchor cell: `anchored()`
```{r}
library("cellranger")
(cl <- as.cell_limits("raw_data!R1C12:R6C15"))
```
The `dim` method reports dimensions of the targetted cell rectangle. `as.range()` converts a `cell_limits` object back into an Excel range.
```{r}
dim(cl)
as.range(cl)
as.range(cl, fo = "A1", sheet = FALSE, strict = TRUE)
```
Use `NA` to leave a limit unspecified, i.e. describe a degenerate rectangle
```{r}
cell_limits(c(3, 2), c(7, NA))
```
If the maximum row or column is specified but the associated minimum is not, then it is set to 1.
```{r}
cell_limits(c(NA, NA), c(3, 5))
```
### Utilities for spreadsheet annoyances
We've exposed utility functions which could be useful to anyone manipulating Excel-like references.
```{r}
## convert character column IDs to numbers ... and vice versa
letter_to_num(c('AA', 'ZZ', 'ABD', 'ZZZ', ''))
num_to_letter(c(27, 702, 732, 18278, 0, -5))
## convert between A1 and R1C1 cell references
A1_to_R1C1(c("$A$1", "$AZ$10"))
A1_to_R1C1(c("A1", "AZ10"), strict = FALSE)
R1C1_to_A1(c("R1C1", "R10C52"))
R1C1_to_A1(c("R1C1", "R10C52"), strict = FALSE)
## detect cell reference formats with
## is_A1() and is_R1C1()
x <- c("A1", "$A4", "$b$12", "RC1", "R[-4]C9", "R5C3")
data.frame(x, A1 = is_A1(x), R1C1 = is_R1C1(x))
## guess format with
## guess_fo()
refs <- c("A1", "$A1", "A$1", "$A$1", "a1",
"R1C1", "R1C[-1]", "R[-1]C1", "R[-1]C[9]")
data.frame(refs, guessed = guess_fo(refs))
```