excel.link (version 0.9.4)

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)

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.

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)