XLView.RdXLView can be used to view and edit a data.frame directly in MS-Excel, resp. to create a new data.frame in MS-Excel.
XLView(x, col.names = TRUE, row.names = FALSE, na = "",
preserveStrings = FALSE, sep = ";")
ToXL(x, at, ..., xl=DescToolsOptions("lastXL"))
# S3 method for class 'data.frame'
ToXL(x, at, ..., xl=DescToolsOptions("lastXL"))
# S3 method for class 'matrix'
ToXL(x, at, ..., xl=DescToolsOptions("lastXL"))
# Default S3 method
ToXL(x, at, byrow = FALSE, ..., xl=DescToolsOptions("lastXL"))
XLKill()is a data.frame to be transferred to MS-Excel. If data is missing a new file will be created.
either a logical value indicating whether the row names of x are to be written along with x, or a character vector of row names to be written.
either a logical value indicating whether the column names of x are to be written
along with x, or a character vector of column names to be written.
See the section on 'CSV files' write.table for the meaning of col.names = NA.
the string to use for missing values in the data.
logical, will preserve strings from being converted to numerics when imported in MS-Excel. See details. Default is FALSE.
the field separator string used for export of the object. Values within each row of x are separated by this string.
can be a range adress as character (e.g. "A1"), a vector of 2 integers (e.g c(1,1)) or a cell object as it is returned by xl$Cells(1,1), denominating the left upper cell, where the data.frame will be placed in the MS-Excel sheet.
logical, defines if the vector should be inserted by row or by column (default).
the pointer to a MS-Excel instance. An new instance can be created with GetNewXL(), returning the appropriate handle. A handle to an already running instance is returned by GetCurrXL().
Default is the last created pointer stored in DescToolsOptions("lastXL").
further arguments are not used.
The data.frame will be exported in CSV format and then imported in MS-Excel. When importing data, MS-Excel will potentially change characters to numeric values. If this seems undesirable (maybe we're loosing leading zeros) then you should enclose the text in quotes and preset a =.
x <- gettextf('="%s"', x) would do the trick.
Take care: Changes to the data made in MS-Excel will NOT automatically be updated in the original data.frame.
The user will have to read the csv-file into R again.
See examples how to get this done.
ToXL() is used to export data frames or vectors directly to MS-Excel, without export the data to a csv-file and import it on the XL side. So it it possible to export several data.frames into one Workbook and edit the tables after ones needs.
XLKill will kill a running XL instance (which might be invisible). Background is the fact, that the simple XL$quit() command
would not terminate a running XL task, but only set it invisible (observe the TaskManager). This ghost version may sometimes confuse XLView and hinder to create a new instance. In such cases you have to do the garbage collection...
the name/path of the temporary file edited in MS-Excel.
The function works only in Windows and requires RDCOMClient to be installed (see: Additional_repositories in DESCRIPTION of the package).
if (FALSE) { # \dontrun{
# Windows-specific example
XLView(d.diamonds)
# edit an existing data.frame in MS-Excel, make changes and save there, return the filename
fn <- XLView(d.diamonds)
# read the changed file and store in new data.frame
d.frm <- read.table(fn, header=TRUE, quote="", sep=";")
# Create a new file, edit it in MS-Excel...
fn <- XLView()
# ... and read it into a data.frame when in R again
d.set <- read.table(fn, header=TRUE, quote="", sep=";")
# Export a ftable object, quite elegant...
XLView(format(ftable(Titanic), quote=FALSE), row.names = FALSE, col.names = FALSE)
# Export a data.frame directly to XL, combined with subsequent formatting
xl <- GetNewXL()
owb <- xl[["Workbooks"]]$Add()
sheet <- xl$Sheets()$Add()
sheet[["name"]] <- "pizza"
ToXL(d.pizza[1:10, 1:10], xl$Cells(1,1))
obj <- xl$Cells()$CurrentRegion()
obj[["VerticalAlignment"]] <- xlConst$xlTop
row <- xl$Cells()$CurrentRegion()$rows(1)
# does not work: row$font()[["bold"]] <- TRUE
# works:
obj <- row$font()
obj[["bold"]] <- TRUE
obj <- row$borders(xlConst$xlEdgeBottom)
obj[["linestyle"]] <- xlConst$xlContinuous
cols <- xl$Cells()$CurrentRegion()$columns(1)
cols[["HorizontalAlignment"]] <- xlConst$xlLeft
xl$Cells()$CurrentRegion()[["EntireColumn"]]$AutoFit()
cols <- xl$Cells()$CurrentRegion()$columns(4)
cols[["WrapText"]] <- TRUE
cols[["ColumnWidth"]] <- 80
xl$Cells()$CurrentRegion()[["EntireRow"]]$AutoFit()
sheet <- xl$Sheets()$Add()
sheet[["name"]] <- "whisky"
ToXL(d.whisky[1:10, 1:10], xl$Cells(1,1))} # }