excel.link (version 0.9.4)

xl.connect.table: Live connection with data on Microsoft Excel sheet

Description

xl.connect.table returns object that can be operated as usual data.frame object and this operations (e. g. subsetting, assignment) will be immediately reflected on connected Excel range. See examples. Connected range is 'current region', e. g. selection which can be obtained by pressing Ctrl+Shift+* when selected str.rng (or top-left cell of this range is active).

Usage

xl.connect.table(str.rng = "A1", row.names = TRUE, col.names = TRUE, na = "")
"sort"(x, decreasing = FALSE, column, ...)

Arguments

str.rng

string which represents Excel range

row.names

a logical value indicating whether the Excel range contains row names as its first column

col.names

a logical value indicating whether the Excel range contains column names as its first row

na

character. NA representation in Excel. By default it is empty string

x

object of class excel.range

decreasing

logical. Should the sort be increasing or decreasing?

column

numeric or character. Column by which we will sort. There is special value - 'rownames'. In this case 'x' will be sorted by row names if it has it.

...

arguments to be passed to or from methods or (for the default methods and objects without a class)

Value

• xl.connect.table returns object of excel.range class which represent data on Excel sheet. This object can be treated similar to data.frame. So you can assign values, delete columns/rows and so on. For more information see examples.
• sort sorts Excel range by single column (multiple columns currently not supported) and invisibly return NULL.

Details

Subsetting. Indices in subsetting operations are numeric/character/logical vectors or empty (missing). Numeric values are coerced to integer as by 'as.integer' (and hence truncated towards zero). Character vectors will be matched to the 'colnames' of the object (or Excel column names if has.colnames = FALSE). For extraction form if column name doesn't exist error will be generated. For replacement form new column will be created. If indices are logical vectors they indicate elements/slices to select. Such vectors are recycled if necessary to match the corresponding extent. Indices can also be negative integers, indicating elements/slices to leave out of the selection.

Examples

## Not run: 
# ### session example 
# 
# library(excel.link)
# xl.workbook.add()
# xl.sheet.add("Iris dataset", before = 1)
# xlrc[a1] = iris
# xl.iris = xl.connect.table("a1", row.names = TRUE, col.names = TRUE)
# dists = dist(xl.iris[, 1:4])
# clusters = hclust(dists, method = "ward.D")
# xl.iris$clusters = cutree(clusters, 3)
# plot(clusters)
# pl.clus = current.graphics()
# cross = table(xl.iris$Species, xl.iris$clusters)
# plot(cross)
# pl.cross = current.graphics()
# xl.sheet.add("Results", before = 2)
# xlrc$a1 = list("Crosstabulation", cross,pl.cross, "Dendrogram", pl.clus)
# 
# ### completely senseless actions       
# ### to demonstrate various operations and  
# ### compare them with operations on usual data.frame
# 
# # preliminary operations 
# data(iris)
# rownames(iris) = as.character(rownames(iris))
# iris$Species = as.character(iris$Species)
# xl.workbook.add()
# 
# # drop dataset to Excel and connect it
# xlrc[a1] = iris
# xl.iris = xl.connect.table("a1", row.names = TRUE, col.names = TRUE)
# identical(xl.iris[], iris)
# 
# # dim/colnames/rownames
# identical(dim(xl.iris),dim(iris))
# identical(colnames(xl.iris),colnames(iris))
# identical(rownames(xl.iris),rownames(iris))
# 
# # sort datasets
# iris = iris[order(iris$Sepal.Length), ]
# sort(xl.iris, column = "Sepal.Length")
# identical(xl.iris[], iris)
# 
# # sort datasets by rownames
# sort(xl.iris, column = "rownames")
# iris = iris[order(rownames(iris)), ]
# identical(xl.iris[], iris)
# 
# # different kinds of subsetting
# identical(xl.iris[,1:3], iris[,1:3])
# identical(xl.iris[,3], iris[,3])
# identical(xl.iris[26,1:3], iris[26,1:3])
# identical(xl.iris[-26,1:3], iris[-26,1:3])
# identical(xl.iris[50,], iris[50,])
# identical(xl.iris$Species, iris$Species)
# identical(xl.iris[,'Species', drop = FALSE], iris[,'Species', drop = FALSE])
# identical(xl.iris[c(TRUE,FALSE), 'Sepal.Length'], 
#              iris[c(TRUE,FALSE), 'Sepal.Length'])
# 
# # column creation and assignment 
# xl.iris[,'group'] = xl.iris$Sepal.Length > mean(xl.iris$Sepal.Length)
# iris[,'group'] = iris$Sepal.Length > mean(iris$Sepal.Length)
# identical(xl.iris[], iris)
# 
# # value recycling
# xl.iris$temp = c('aa','bb')
# iris$temp = c('aa','bb')
# identical(xl.iris[], iris)
# 
# # delete column
# xl.iris[,"temp"] = NULL
# iris[,"temp"] = NULL
# identical(xl.iris[], iris)
# 
# ## End(Not run)