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

View source: R/xl.connect.table.R

xl.connect.tableR Documentation

Live connection with data on Microsoft Excel sheet


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).


xl.connect.table(str.rng = "A1", row.names = TRUE, col.names = TRUE, na = "")

## S3 method for class 'excel.range'
sort(x, decreasing = FALSE, column, ...)



string which represents Excel range


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


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


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


object of class excel.range


logical. Should the sort be increasing or decreasing?


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)


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.


  • 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.


## Not run: 
### session example 

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)
pl.clus = current.graphics()
cross = table(xl.iris$Species, xl.iris$clusters)
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 
rownames(iris) = as.character(rownames(iris))
iris$Species = as.character(iris$Species)

# 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

# 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 29, 2024, 1:21 a.m.