readNamedRegion {XLConnect} | R Documentation |
Reading named regions from a workbook
Description
Reads named regions from a workbook
.
Usage
## S4 method for signature 'workbook'
readNamedRegion(object, name, header, rownames, colTypes, forceConversion,
dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy)
Arguments
object |
The |
name |
The name of the named region to read |
header |
The argument |
rownames |
Index ( |
colTypes |
Column types to use when reading in the data. Specified as a |
forceConversion |
|
dateTimeFormat |
Date/time format used when doing date/time conversions. Defaults to |
check.names |
|
useCachedValues |
|
keep |
List of column names or indices to be kept in the output data frame.
It is possible to specify either |
drop |
List of column names or indices to be dropped in the output data frame.
It is possible to specify either |
simplify |
|
readStrategy |
|
Details
The arguments name
and header
are vectorized. As such,
multiple named regions can be read with one method call. If only one
single named region is read, the return value is a data.frame
.If
multiple named regions are specified, the return value is a (named)
list
of data.frame
's returned in the order they have been
specified with the argument name
.
When reading dates, if your system uses a time zone that has / had daylight saving time,
certain dates / timestamps will not be read exactly as they were written. See
https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DateUtil.html#getJavaDate-double-
Note
If no specific column types (see argument colTypes
) are specified,
readNamedRegion
tries to determine the resulting column types
based on the read cell types. If different cell types are found in a
specific column, the most general of those is used and mapped to the
corresponding R data type. The order of data types from least to most
general is Boolean (logical
) < DateTime (POSIXct
) <
Numeric (numeric
) < String (character
). E.g. if a column
is read that contains cells of type Boolean, Numeric and String then the
resulting column in R would be character
since character
is the most general type.
Some additional information with respect to forcing data type conversion
using forceConversion = TRUE
:
Forcing conversion from String to Boolean:
TRUE
is returned if and only if the target string is "true" (ignoring any capitalization). Any other string will returnFALSE
.Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Numerics with some additional formatting, a conversion from a Numeric to a DateTime is actually possible. Numerics in this case represent the number of days since 1900-01-00 (yes, day 00! - see http://www.cpearson.com/excel/datetime.htm). Note that in R 0 is represented as 1899-12-31 since there is no 1900-01-00. Fractional days represent hours, minutes, and seconds.
Author(s)
Martin Studer
Thomas Themel
Nicola Lambiase
Mirai Solutions GmbH https://mirai-solutions.ch
References
What are named regions/ranges?
https://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
How to create named regions/ranges?
https://www.youtube.com/watch?v=iAE9a0uRtpM
See Also
workbook
,
readWorksheet
,
writeNamedRegion
,
writeWorksheet
,
readNamedRegionFromFile
,
readTable
,
onErrorCell
Examples
## Not run:
## Example 1:
# mtcars xlsx file from demoFiles subfolder of package XLConnect
demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect")
# Load workbook
wb <- loadWorkbook(demoExcelFile)
# Read named region 'mtcars' (with default header = TRUE)
data <- readNamedRegion(wb, name = "mtcars")
## Example 2;
# conversion xlsx file from demoFiles subfolder of package XLConnect
excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect")
# Load workbook
wb <- loadWorkbook(excelFile)
# Read named region 'conversion' with pre-specified column types
# Note: in the worksheet all data was entered as strings!
# forceConversion = TRUE is used to force conversion from String
# into the less generic data types Numeric, DateTime & Boolean
df <- readNamedRegion(wb, name = "conversion", header = TRUE,
colTypes = c(XLC$DATA_TYPE.NUMERIC,
XLC$DATA_TYPE.DATETIME,
XLC$DATA_TYPE.BOOLEAN),
forceConversion = TRUE,
dateTimeFormat = "%Y-%m-%d %H:%M:%S")
## Example 3:
# mtcars xlsx file from demoFiles subfolder of package XLConnect
demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect")
# Load workbook
wb <- loadWorkbook(demoExcelFile)
# Read the columns 1, 3 and 5 of the named region 'mtcars' (with default header = TRUE)
data <- readNamedRegion(wb, name = "mtcars", keep=c(1,3,5))
## End(Not run)