Use MS-Excel as Viewer for a Data.Frame
XLView 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()x |
is a data.frame to be transferred to MS-Excel. If data is missing a new file will be created. |
row.names |
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. |
col.names |
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' |
na |
the string to use for missing values in the data. |
preserveStrings |
logical, will preserve strings from being converted to numerics when imported in MS-Excel. See details. Default is |
sep |
the field separator string used for export of the object. Values within each row of x are separated by this string. |
at |
can be a range adress as character (e.g. |
byrow |
logical, defines if the vector should be inserted by row or by column (default). |
xl |
the pointer to a MS-Excel instance. An new instance can be created with |
... |
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).
Andri Signorell <andri@signorell.net>, ToXL() is based on code of Duncan Temple Lang <duncan@r-project.org>
## Not run: # 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)) ## End(Not run)
Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.