README.md
1
2<!-- README.md is generated from README.Rmd. Please edit that file -->
3[](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) 
4
5<img src="http://i.imgur.com/RJJy15I.jpg" width="270" align="right" />
6
7Helper package to support R scripts or packages that interact with spreadsheets.
8
9### Installation
10
11Option 1: Install from CRAN:
12
13``` r
14install.packages("cellranger")
15```
16
17Option 2: Install the development version from GitHub:
18
19``` r
20# install.packages("devtools")
21devtools::install_github("jennybc/cellranger")
22```
23
24### What is `cellranger` for?
25
26**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.
27
28**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).
29
30**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.
31
32### Describing rectangles via `cell_limits`
33
34`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:
35
36``` r
37cell_limits(ul = c(ROW_MIN, COL_MIN), lr = c(ROW_MAX, COL_MAX), sheet = "SHEET")
38```
39
40Think of it like `R3C1:R7C4` notation, but with the `R` and `C` removed.
41
42More 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):
43
44``` r
45library(googlesheets)
46gs_read(..., range = "D12:F15")
47gs_read(..., range = "raw_data!R1C12:R6C15")
48gs_read(..., range = cell_limits(c(1, 1), c(6, 15)))
49gs_read(..., range = cell_limits(c(2, 1), c(NA, NA)))
50gs_read(..., range = cell_rows(1:100))
51gs_read(..., range = cell_cols(3:8))
52gs_read(..., range = cell_cols("B:MZ"))
53gs_read(..., range = anchored("B4", dim = c(2, 10)))
54gs_read(..., range = anchored("A1", dim = c(5, 6), col_names = TRUE))
55## internal usage in functions that put data into a googlesheet
56anchored(input = head(iris))
57anchored(input = head(iris), col_names = FALSE)
58anchored(input = head(LETTERS))
59anchored(input = head(LETTERS), byrow = TRUE)
60```
61
62Read the docs for more information on some specialized helpers:
63
64- Row- or column-only specification: `cell_rows()`, `cell_cols()`.
65- Specification via an object you want to write and, optionally, an anchor cell: `anchored()`
66
67``` r
68library("cellranger")
69(cl <- as.cell_limits("raw_data!R1C12:R6C15"))
70#> <cell_limits (1, 12) x (6, 15) in 'raw_data'>
71```
72
73The `dim` method reports dimensions of the targetted cell rectangle. `as.range()` converts a `cell_limits` object back into an Excel range.
74
75``` r
76dim(cl)
77#> [1] 6 4
78
79as.range(cl)
80#> [1] "raw_data!R1C12:R6C15"
81
82as.range(cl, fo = "A1", sheet = FALSE, strict = TRUE)
83#> [1] "$L$1:$O$6"
84```
85
86Use `NA` to leave a limit unspecified, i.e. describe a degenerate rectangle
87
88``` r
89cell_limits(c(3, 2), c(7, NA))
90#> <cell_limits (3, 2) x (7, -)>
91```
92
93If the maximum row or column is specified but the associated minimum is not, then it is set to 1.
94
95``` r
96cell_limits(c(NA, NA), c(3, 5))
97#> <cell_limits (1, 1) x (3, 5)>
98```
99
100### Utilities for spreadsheet annoyances
101
102We've exposed utility functions which could be useful to anyone manipulating Excel-like references.
103
104``` r
105## convert character column IDs to numbers ... and vice versa
106letter_to_num(c('AA', 'ZZ', 'ABD', 'ZZZ', ''))
107#> [1] 27 702 732 18278 NA
108
109num_to_letter(c(27, 702, 732, 18278, 0, -5))
110#> [1] "AA" "ZZ" "ABD" "ZZZ" NA NA
111
112## convert between A1 and R1C1 cell references
113A1_to_R1C1(c("$A$1", "$AZ$10"))
114#> [1] "R1C1" "R10C52"
115A1_to_R1C1(c("A1", "AZ10"), strict = FALSE)
116#> [1] "R1C1" "R10C52"
117
118R1C1_to_A1(c("R1C1", "R10C52"))
119#> [1] "$A$1" "$AZ$10"
120R1C1_to_A1(c("R1C1", "R10C52"), strict = FALSE)
121#> [1] "A1" "AZ10"
122
123## detect cell reference formats with
124## is_A1() and is_R1C1()
125x <- c("A1", "$A4", "$b$12", "RC1", "R[-4]C9", "R5C3")
126data.frame(x, A1 = is_A1(x), R1C1 = is_R1C1(x))
127#> x A1 R1C1
128#> 1 A1 TRUE FALSE
129#> 2 $A4 TRUE FALSE
130#> 3 $b$12 TRUE FALSE
131#> 4 RC1 TRUE TRUE
132#> 5 R[-4]C9 FALSE TRUE
133#> 6 R5C3 FALSE TRUE
134
135## guess format with
136## guess_fo()
137refs <- c("A1", "$A1", "A$1", "$A$1", "a1",
138 "R1C1", "R1C[-1]", "R[-1]C1", "R[-1]C[9]")
139data.frame(refs, guessed = guess_fo(refs))
140#> refs guessed
141#> 1 A1 A1
142#> 2 $A1 A1
143#> 3 A$1 A1
144#> 4 $A$1 A1
145#> 5 a1 A1
146#> 6 R1C1 R1C1
147#> 7 R1C[-1] R1C1
148#> 8 R[-1]C1 R1C1
149#> 9 R[-1]C[9] R1C1
150```
151
){kind=link}
){kind=link}
){kind=link}
