xl.bind.range: Active bindings to Excel ranges

Description

xl.bind.range and xl.bind.current.region create sym in environment env so that getting the value of sym return bound Excel range, and assigning to sym will write the value to be assigned to Excel range. In case of xl.bind.range range will be updated after each assignment accordingly to the size of the assigned value. xl.bind.current.region always returns data from current region (Ctrl+Shift+* in Excel) of bound range. %=xl% etc are shortcuts for xl.bind.range and xl.bind.current.region. "r" means with row names, "c" means with column names. Range in most cases can be provided without quotes: a1 %=xl% a1:b100. Functions with '=' and with '<-' in the names do the same things - they are just for those who prefer '=' assignment and for those who prefer '<-' assignment. Assignment and reading may be slow because these functions always read/write entire dataset.

Usage

xl.bind.range(
  sym,
  str.range,
  drop = TRUE,
  na = "",
  row.names = FALSE,
  col.names = FALSE,
  env = parent.frame()
)
xl.bind.current.region(
  sym,
  str.range,
  drop = TRUE,
  na = "",
  row.names = FALSE,
  col.names = FALSE,
  env = parent.frame()
)
sym %=xl% value
sym %=xlr% value
sym %=xlc% value
sym %=xlrc% value
sym %=cr% value
sym %=crr% value
sym %=crc% value
sym %=crrc% value
sym %
sym %
sym %
sym %
sym %
sym %
sym %
sym %
xl.binding.address(sym)

Value

xl.binding.address returns list with three components about bound Excel range: address, rows - number of rows,

columns - number of columns. All other functions don't return anything but create active binding to Excel range in the environment.

Arguments

sym: character/active binding.
str.range: character Excel range.
drop: logical. If TRUE the result is coerced to the lowest possible dimension. By default dimensions will be dropped if there are no columns and rows names.
na: character. NA representation in Excel. By default it is empty string.
row.names: logical value indicating whether the Excel range contains the row names as its first column.
col.names: logical value indicating whether the Excel range contains the column names as its first row.
env: an environment.
value: character Excel range address. It can be without quotes in many cases.

Author

Idea by Stefan Fritsch (https://github.com/gdemin/excel.link/issues/1)

Examples

Run this code

if (FALSE) {
 xl.workbook.add()
 range_a1 %=xl% a1 # binding range_a1 to cell A1 on active sheet
 range_a1 # should be NA
 range_a1 = 42 # value in Excel should be changed
 identical(range_a1, 42) 
 cr_a1 %=cr% a1 # binding cr_a1 to current region around cell A1 on active sheet
 identical(cr_a1, range_a1)
 # difference between 'cr' and 'xl':  
 xl[a2] = 43
 range_a1 # 42
 xl.binding.address(range_a1)
 xl.binding.address(cr_a1)
 cr_a1 # identical to 42:43
 # make cr and xl identical: 
 range_a1 = 42:43
 identical(cr_a1, range_a1)
 
 xl_iris %=crc% a1 # bind current region A1 on active sheet with column names
 xl_iris = iris # put iris dataset to Excel sheet
 identical(xl_iris$Sepal.Width, iris$Sepal.Width) # should be TRUE
 
 xl_iris$new_col = xl_iris$Sepal.Width*xl_iris$Sepal.Length # add new column on Excel sheet
 
}