RDCOMClient
is used to open an Excel workbook and return the content (value) of one (or several) given range(s)
in a specified sheet. Helpful, if pathologically scattered data on an Excel sheet, which can't simply be saved as CSV-file, has to be imported in R.
XLGetWorkbook does the same for all the sheets in an Excel workbook.XLGetRange(file = NULL, sheet = NULL, range = NULL, as.data.frame = TRUE,
header = FALSE, stringsAsFactors = FALSE)
XLGetWorkbook(file)
NULL
, the
function will look for a running Excel-Application and use its current sheet. The parameter sheet
will be
ignored in this case."A1:F10"
.
If set to NULL
(which is the default), the function will look for a seheader
is ignored if as.data.frame
has been set to FALSE.TRUE
, a single data.frame or a list of data.frames will be returned.
If set to FALSE
a list of the cell values in the specified Excel range, resp. a list of lists will be returned.
XLGetWorkbook returns a list of lists of the values in the given workbook.as.data.frame
is set to FALSE
.
Be then prepared to encounter NULL
values. Those will prevent from easily being able to coerce
the square data structure to a data.frame.
The following code will replace the NULL
values by NA
and coerce the data to a data.frame.
# get the range D1:J69 from an excel file
xlrng <- XLGetRange(file="myfile.xlsx", sheet="Tabelle1",
range="D1:J69", as.data.frame=FALSE)
# replace NULL values by NA
xlrng[unlist(lapply(xlrng, is.null))] <- NA
# coerce the square data structure to a data.frame
d.lka <- data.frame(lapply(data.frame(xlrng), unlist))
This of course can be avoided by setting as.data.frame
= TRUE
.
The function will return dates as integer values, because XL stores them as integers.
An Excel date can be converted with the (unusual) origin of
as.Date(myDate, origin="1899-12-30")
.GetNewXL
, XLGetWorkbook
# Windows-specific example
XLGetRange(file="C:\My Documents\data.xls",
sheet="Sheet1",
range=c("A2:B5","M6:X23","C4:D40"))
# if the current region is to be read (inkl. a header), place the cursor in the interesting region
# and run:
d.set <- XLGetRange(header=TRUE)
Run the code above in your browser using DataLab