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

In excel.link: Convenient Data Exchange with Microsoft Excel

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 = "")

## S3 method for class 'excel.range'
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)

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.

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.

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)

excel.link documentation built on May 31, 2023, 5:27 p.m.