Helper package to support R scripts or packages that interact with spreadsheets. Original development was motivated by the wish to have a common interface for specifying cell ranges in readxl and googlesheets.
Anticipated readxl usage:
read_excel(..., range = "D12:F15")
read_excel(..., range = "R1C12:R6C15")
read_excel(..., range = cell_limits(c(1, 6), c(1, 15))
read_excel(..., range = cell_limits(c(2, NA), c(1, NA))
read_excel(..., range = cell_rows(1:100))
read_excel(..., range = cell_cols(3:8))
read_excel(..., range = cell_cols("B:MZ"))
read_excel(..., range = anchored("B4", dim = c(2, 10)))The main goal is to translate Excel-like ranges, such as A3:D7 or R3C1:R7C4, into something more programmatically useful. cellranger provides an S3 class, cell_limits, as the standard way to store a cell range. Construct cell_limits like so: cell_limits(rows = c(ROW_MIN, ROW_MAX), cols = c(COL_MIN, COL_MAX)).
library("cellranger")
(foo <- cell_limits(c(1, 3), c(1, 5)))
#> <cell_limits (1, 1) x (3, 5)>The print method reports the cell rectangle as (UPPER LEFT CELL) x (LOWER RIGHT CELL) where cell locations are specifed as (ROW, COL).
The dim method reports dimensions of the targetted cell rectangle. as.range() converts a cell_limits object back into an Excel range.
dim(foo)
#> rows cols
#> 3 5
as.range(foo)
#> [1] "A1:E3"
as.range(foo, RC =TRUE)
#> [1] "R1C1:R3C5"Use NA to leave a limit unspecified
cell_limits(c(NA, 7), c(3, NA))
#> <cell_limits (-, 3) x (7, -)>as.cell_limits("C7")
#> <cell_limits (7, 3) x (7, 3)>
as.cell_limits("A1:D8")
#> <cell_limits (1, 1) x (8, 4)>
as.cell_limits("R2C3:R6C9")
#> <cell_limits (2, 3) x (6, 9)>Recall the anticipated usage: read_excel(..., range = "D12:F15"). The intent is that as.cell_limits() will be called on user input for the range = argument.
cell_rows(1:100)
#> <cell_limits (1, -) x (100, -)>
cell_cols(3:8)
#> <cell_limits (-, 3) x (-, 8)>
cell_cols("B:MZ")
#> <cell_limits (-, 2) x (-, 364)>
cell_cols(c(NA, "AR"))
#> <cell_limits (-, -) x (-, 44)>The rectangle can be described in terms of the upper left anchor cell and the dimensions of the rectangle, either directly or via an object (anticipates writing that object into the spreadsheet).
## direct specification of dimensions
anchored(anchor = "R4C2", dim = c(8, 2))
#> <cell_limits (4, 2) x (11, 3)>
as.range(anchored(anchor = "R4C2", dim = c(8, 2)), RC = TRUE)
#> [1] "R4C2:R11C3"
dim(anchored(anchor = "R4C2", dim = c(8, 2)))
#> rows cols
#> 8 2
## indirect specification of dimensions, via the dimensions of an object
input <- head(iris)
anchored(input = input)
#> <cell_limits (1, 1) x (7, 5)>
as.range(anchored(input = input))
#> [1] "A1:E7"
dim(anchored(input = input))
#> rows cols
#> 7 5The anchored() function has additional arguments header = and byrow = for more control with 2-dimensional and 1-dimensional objects, respectively.
We've exposed utility functions which could be useful to anyone manipulating Excel-like references.
## convert character column IDs to numbers ... and vice versa
letter_to_num(c('AA', 'ZZ', 'ABD', 'ZZZ', ''))
#> [1] 27 702 732 18278 NA
num_to_letter(c(27, 702, 732, 18278, 0, -5))
#> [1] "AA" "ZZ" "ABD" "ZZZ" NA NA
## convert between A1 and R1C1 cell references
A1_to_RC(c("A1", "AZ10"))
#> [1] "R1C1" "R10C52"
RC_to_A1(c("R1C1", "R10C52"))
#> [1] "A1" "AZ10"
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent