xl.bind.range: Active bindings to Excel ranges

In gdemin/excel.link: Convenient Data Exchange with Microsoft Excel

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 %<xl-% value

sym %<xlr-% value

sym %<xlc-% value

sym %<xlrc-% value

sym %<cr-% value

sym %<crr-% value

sym %<crc-% value

sym %<crrc-% value

xl.binding.address(sym)

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.

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.

Author(s)

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

See Also

xl, xlr, xlc, xlrc

Examples

## Not run: 
 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
 

## End(Not run)

gdemin/excel.link documentation built on Feb. 10, 2024, 5 p.m.