{"Package":"cellranger","Title":"Translate Spreadsheet Cell Ranges to Rows and Columns","Version":"1.1.0","Authors@R":"c(\nperson(\"Jennifer\", \"Bryan\", , \"jenny@stat.ubc.ca\", c(\"cre\", \"aut\")),\nperson(\"Hadley\", \"Wickham\", , \"hadley@rstudio.com\", \"ctb\")\n)","Description":"Helper functions to work with spreadsheets and the \"A1:D10\" style\nof cell range specification.","Depends":"R (>= 3.0.0)","License":"MIT + file LICENSE","LazyData":"true","URL":"https://github.com/rsheets/cellranger","BugReports":"https://github.com/rsheets/cellranger/issues","Suggests":"covr, testthat (>= 1.0.0), knitr, rmarkdown","RoxygenNote":"5.0.1.9000","VignetteBuilder":"knitr","Imports":"rematch, tibble","NeedsCompilation":"no","Packaged":"2016-07-26 06:50:00 UTC; jenny","Author":"Jennifer Bryan [cre, aut],\nHadley Wickham [ctb]","Maintainer":"Jennifer Bryan ","Repository":"CRAN","Date/Publication":"2016-07-27 03:17:48","readme":"\n\n[![TravisCI Build Status](https://travis-ci.org/rsheets/cellranger.svg?branch=master)](https://travis-ci.org/rsheets/cellranger) [![codecov.io](https://codecov.io/github/rsheets/cellranger/coverage.svg?branch=master)](https://codecov.io/github/rsheets/cellranger?branch=master) [![DOI](https://zenodo.org/badge/16122/jennybc/cellranger.svg)](http://dx.doi.org/10.5281/zenodo.21970) [![CRAN version](http://www.r-pkg.org/badges/version/cellranger)](https://cran.r-project.org/package=cellranger) ![](http://cranlogs.r-pkg.org/badges/grand-total/cellranger)\n\n

\n\nHelper package to support R scripts or packages that interact with spreadsheets.\n\n### Installation\n\nOption 1: Install from CRAN:\n\n``` r\ninstall.packages(\"cellranger\")\n```\n\nOption 2: Install the development version from GitHub:\n\n``` r\n# install.packages(\"devtools\")\ndevtools::install_github(\"jennybc/cellranger\")\n```\n\n### What is `cellranger` for?\n\n**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.\n\n**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).\n\n**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.\n\n### Describing rectangles via `cell_limits`\n\n`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:\n\n``` r\ncell_limits(ul = c(ROW_MIN, COL_MIN), lr = c(ROW_MAX, COL_MAX), sheet = \"SHEET\")\n```\n\nThink of it like `R3C1:R7C4` notation, but with the `R` and `C` removed.\n\nMore 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):\n\n``` r\nlibrary(googlesheets)\ngs_read(..., range = \"D12:F15\")\ngs_read(..., range = \"raw_data!R1C12:R6C15\")\ngs_read(..., range = cell_limits(c(1, 1), c(6, 15)))\ngs_read(..., range = cell_limits(c(2, 1), c(NA, NA)))\ngs_read(..., range = cell_rows(1:100))\ngs_read(..., range = cell_cols(3:8))\ngs_read(..., range = cell_cols(\"B:MZ\"))\ngs_read(..., range = anchored(\"B4\", dim = c(2, 10)))\ngs_read(..., range = anchored(\"A1\", dim = c(5, 6), col_names = TRUE))\n## internal usage in functions that put data into a googlesheet\nanchored(input = head(iris))\nanchored(input = head(iris), col_names = FALSE)\nanchored(input = head(LETTERS))\nanchored(input = head(LETTERS), byrow = TRUE)\n```\n\nRead the docs for more information on some specialized helpers:\n\n- Row- or column-only specification: `cell_rows()`, `cell_cols()`.\n- Specification via an object you want to write and, optionally, an anchor cell: `anchored()`\n\n``` r\nlibrary(\"cellranger\")\n(cl <- as.cell_limits(\"raw_data!R1C12:R6C15\"))\n#> \n```\n\nThe `dim` method reports dimensions of the targetted cell rectangle. `as.range()` converts a `cell_limits` object back into an Excel range.\n\n``` r\ndim(cl)\n#> [1] 6 4\n\nas.range(cl)\n#> [1] \"raw_data!R1C12:R6C15\"\n\nas.range(cl, fo = \"A1\", sheet = FALSE, strict = TRUE)\n#> [1] \"$L$1:$O$6\"\n```\n\nUse `NA` to leave a limit unspecified, i.e. describe a degenerate rectangle\n\n``` r\ncell_limits(c(3, 2), c(7, NA))\n#> \n```\n\nIf the maximum row or column is specified but the associated minimum is not, then it is set to 1.\n\n``` r\ncell_limits(c(NA, NA), c(3, 5))\n#> \n```\n\n### Utilities for spreadsheet annoyances\n\nWe've exposed utility functions which could be useful to anyone manipulating Excel-like references.\n\n``` r\n## convert character column IDs to numbers ... and vice versa\nletter_to_num(c('AA', 'ZZ', 'ABD', 'ZZZ', ''))\n#> [1] 27 702 732 18278 NA\n\nnum_to_letter(c(27, 702, 732, 18278, 0, -5))\n#> [1] \"AA\" \"ZZ\" \"ABD\" \"ZZZ\" NA NA\n\n## convert between A1 and R1C1 cell references\nA1_to_R1C1(c(\"$A$1\", \"$AZ$10\"))\n#> [1] \"R1C1\" \"R10C52\"\nA1_to_R1C1(c(\"A1\", \"AZ10\"), strict = FALSE)\n#> [1] \"R1C1\" \"R10C52\"\n\nR1C1_to_A1(c(\"R1C1\", \"R10C52\"))\n#> [1] \"$A$1\" \"$AZ$10\"\nR1C1_to_A1(c(\"R1C1\", \"R10C52\"), strict = FALSE)\n#> [1] \"A1\" \"AZ10\"\n\n## detect cell reference formats with\n## is_A1() and is_R1C1()\nx <- c(\"A1\", \"$A4\", \"$b$12\", \"RC1\", \"R[-4]C9\", \"R5C3\")\ndata.frame(x, A1 = is_A1(x), R1C1 = is_R1C1(x))\n#> x A1 R1C1\n#> 1 A1 TRUE FALSE\n#> 2 $A4 TRUE FALSE\n#> 3 $b$12 TRUE FALSE\n#> 4 RC1 TRUE TRUE\n#> 5 R[-4]C9 FALSE TRUE\n#> 6 R5C3 FALSE TRUE\n\n## guess format with\n## guess_fo()\nrefs <- c(\"A1\", \"$A1\", \"A$1\", \"$A$1\", \"a1\",\n \"R1C1\", \"R1C[-1]\", \"R[-1]C1\", \"R[-1]C[9]\")\ndata.frame(refs, guessed = guess_fo(refs))\n#> refs guessed\n#> 1 A1 A1\n#> 2 $A1 A1\n#> 3 A$1 A1\n#> 4 $A$1 A1\n#> 5 a1 A1\n#> 6 R1C1 R1C1\n#> 7 R1C[-1] R1C1\n#> 8 R[-1]C1 R1C1\n#> 9 R[-1]C[9] R1C1\n```","repoType":"cran"}

Description

Usage

Arguments

Value

Details

Examples