XLConnect (version 0.2-3)

readWorksheet-methods: Reading data from worksheets

Description

Reads data from worksheets of a workbook.

Usage

## S3 method for class 'workbook,numeric':
readWorksheet(object,sheet,startRow,startCol,endRow,endCol,region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names)
## S3 method for class 'workbook,character':
readWorksheet(object,sheet,startRow,startCol,endRow,endCol,region,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names)

Arguments

object

The workbook to use

sheet

The name or index of the worksheet to read from

startRow

The index of the first row to read from

startCol

The index of the first column to read from

endRow

The index of the last row to read from

endCol

The index of the last column to read from

region

A range specifier in the form 'A10:B18'. This provides an alternative way to specify startRow, startCol, endRow and endCol. Range specifications take precedence over index specifications.

header

Interpret the first row of the specified area as column headers. The default is TRUE.

rownames

Index (numeric) or name (character) of column that should be used as row names. The corresponding column will be removed from the data set. Defaults to NULL which means that no row names are applied.

colTypes

Column types to use when reading in the data. Specified as a character vector of the corresponding type names (see XLC; XLC$DATA_TYPE.). You may also use R class names such as

forceConversion

logical specifying if conversions to less generic types should be forced. Defaults to FALSE meaning that if a column is specified to be of a certain type via the colTypes argument and a more generic type is det

dateTimeFormat

Date/time format used when doing date/time conversions. Defaults to getOption("XLConnect.dateTimeFormat"). This should be a POSIX format specifier according to strptime although not all spe

check.names

logical specifying if column names of the resulting data.frame should be checked to ensure that they are syntactically valid valid variable names and are not duplicated. See the check.names argument of

Details

Reads data from the worksheet specified by sheet. Data is read starting at the top left corner specified by startRow and startCol down to the bottom right corner specified by endRow and endCol. If header = TRUE, the first row is interpreted as column names of the resulting data.frame. If startRow <= 0<="" code=""> then the first available (logical) row is assumed. If startCol <= 0<="" code=""> then the column of the first (logical) cell of the start row (startRow) is assumed. If endRow <= 0<="" code=""> then the last available (logical) row is assumed. If endCol <= 0<="" code=""> then the maximum column between startRow and endRow is assumed. In other words, if no boundaries are specified readWorksheet assumes the "bounding box" of the data as the corresponding boundaries. If all four coordinate arguments are missing this behaves as above with startRow = 0, startCol = 0, endRow = 0 and endCol = 0. In this case readWorksheet assumes the "bounding box" of the data as the corresponding boundaries. All arguments (except object) are vectorized. As such, multiple worksheets (and also multiple data regions from the same worksheet) can be read with one method call. If only one single data region is read, the return value is a data.frame. If multiple data regions are specified, the return value is a list of data.frame's returned in the order they have been specified. If worksheets have been specified by name, the list will be a named list named by the corresponding worksheets.

See Also

workbook, writeWorksheet, readNamedRegion, writeNamedRegion, readWorksheetFromFile, onErrorCell

Examples

## 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 worksheet 'mtcars' (providing no specific area bounds;
# with default header = TRUE)
data <- readWorksheet(wb, sheet = "mtcars")


## Example 2:
# mtcars xlsx file from demoFiles subfolder of package XLConnect
demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect")

# Load workbook
wb <- loadWorkbook(demoExcelFile)

# Read worksheet 'mtcars' (providing area bounds; with default header = TRUE)
data <- readWorksheet(wb, sheet = "mtcars", startRow = 1, startCol = 3,
                      endRow = 15, endCol = 8)


## 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 worksheet 'mtcars' (providing area bounds using the region argument;
# with default header = TRUE)
data <- readWorksheet(wb, sheet = "mtcars", region = "C1:H15")


## Example 4:
# conversion xlsx file from demoFiles subfolder of package XLConnect
excelFile <- system.file("demoFiles/conversion.xlsx", package = "XLConnect")

# Load workbook
wb <- loadWorkbook(excelFile)

# Read worksheet '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 <- readWorksheet(wb, sheet = "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")