{"id":779,"date":"2015-02-22T17:50:52","date_gmt":"2015-02-23T00:50:52","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=779"},"modified":"2015-02-22T17:50:52","modified_gmt":"2015-02-23T00:50:52","slug":"readworksheet-methods-xlconnect","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2015\/02\/22\/readworksheet-methods-xlconnect\/","title":{"rendered":"readWorksheet-methods {XLConnect}"},"content":{"rendered":"
\n
\n
Reading data from worksheets<\/div>\n<\/div>\n<\/div>\n
\n
\n
\n
\n
Package:<\/div>\n

XLConnect<\/p><\/div>\n<\/div>\n<\/div>\n

\n
\n
\n
Version:<\/div>\n

0.2-7<\/p><\/div>\n<\/div>\n<\/div>\n<\/div>\n

\n
\n

Description<\/h3>\n

Reads data from worksheets of a workbook<\/code><\/span>.<\/p>\n<\/div>\n

\n

Usage<\/h3>\n
 \n## S4 method for class 'workbook,numeric':\nreadWorksheet((object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol,\nregion,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names,\nuseCachedValues,keep,drop, simplify, readStrategy))\n\n## S4 method for class 'workbook,character':\nreadWorksheet((object,sheet,startRow,startCol,endRow,endCol,autofitRow,autofitCol,\nregion,header,rownames,colTypes,forceConversion,dateTimeFormat,check.names,\nuseCachedValues,keep,drop, simplify, readStrategy))\n\n<\/pre>\n<\/div>\n
\n
Arguments<\/h3>\n
\n
object<\/dt>\n
The workbook<\/code><\/span> to use<\/dd>\n
sheet<\/dt>\n
The name or index of the worksheet to read from<\/dd>\n
startRow<\/dt>\n
The index of the first row to read from. Defaults to \u00a0<\/code><\/span> meaning that the start row is determined automatically.<\/dd>\n
startCol<\/dt>\n
The index of the first column to read from. Defaults to \u00a0<\/code><\/span> meaning that the start column is determined automatically.<\/dd>\n
endRow<\/dt>\n
The index of the last row to read from. Defaults to \u00a0<\/code><\/span> meaning that the end row is determined automatically.<\/dd>\n
endCol<\/dt>\n
The index of the last column to read from. Defaults to \u00a0<\/code><\/span> meaning that the end column is determined automatically.<\/dd>\n
autofitRow<\/dt>\n
logical<\/a><\/code><\/span> specifying if leading and trailing empty rows should be skipped. Defaults to TRUE<\/code><\/span>.<\/dd>\n
autofitCol<\/dt>\n
logical<\/a><\/code><\/span> specifying if leading and trailing empty columns should be skipped. Defaults to TRUE<\/code><\/span>.<\/dd>\n
region<\/dt>\n
A range specifier in the form ‘A10:B18’. This provides an alternative way to specify startRow<\/code><\/span>,startCol<\/code><\/span>, endRow<\/code><\/span> and endCol<\/code><\/span>. Range specifications take precedence over index specifications.<\/dd>\n
header<\/dt>\n
Interpret the first row of the specified area as column headers. The default is TRUE<\/code><\/span>.<\/dd>\n
rownames<\/dt>\n
Index (numeric<\/a><\/code><\/span>) or name (character<\/a><\/code><\/span>) of column that should be used as row names. The corresponding column will be removed from the data set. Defaults to NULL<\/code><\/span> which means that no row names are applied. Row names must be either integer<\/a><\/code><\/span> or character<\/a><\/code><\/span>. Non-numeric columns will be coerced to character<\/a><\/code><\/span>.<\/dd>\n
colTypes<\/dt>\n
Column types to use when reading in the data. Specified as a character<\/a><\/code><\/span> vector of the corresponding type names (see XLC<\/code><\/span>; XLC$DATA_TYPE.<?><\/code><\/span>). You may also use R class names such as numeric<\/a><\/code><\/span>,character<\/a><\/code><\/span>, logical<\/a><\/code><\/span> and POSIXt<\/code><\/span>. The types are applied in the given order to the columns – elements are recycled if necessary. Defaults to character<\/a>(0)<\/code><\/span> meaning that column types are determined automatically (see the Note section for more information).
\nBy default, type conversions are only applied if the specified column type is a more generic type (e.g. from Numeric to String) – otherwise NA<\/code><\/span> is returned. The forceConversion<\/code><\/span> flag can be set to force conversion into less generic types where possible.<\/dd>\n
forceConversion<\/dt>\n
logical<\/a><\/code><\/span> specifying if conversions to less generic types should be forced. Defaults to FALSE<\/code><\/span> meaning that if a column is specified to be of a certain type via the colTypes<\/code><\/span> argument and a more generic type is detected in the column, then NA<\/code><\/span> will be returned (example: column is specified to be DateTime but a more generic String is found). Specifying forceConversion = TRUE<\/code><\/span> will try to enforce a conversion – if it succeeds the corresponding (converted) value will be returned, otherwise NA<\/code><\/span>. See the Note section for some additional information.<\/dd>\n
dateTimeFormat<\/dt>\n
Date\/time format used when doing date\/time conversions. Defaults to
\ngetOption<\/a>(\"XLConnect.dateTimeFormat\")<\/code><\/span>. This should be a POSIX format specifier according tostrptime<\/code><\/span> although not all specifications have been implemented yet – the most important ones however are available.<\/dd>\n
check.names<\/dt>\n
logical<\/a><\/code><\/span> specifying if column names of the resulting data.frame<\/a><\/code><\/span> should be checked to ensure that they are syntactically valid variable names and are not duplicated. See the check.names<\/code><\/span> argument ofdata.frame<\/a><\/code><\/span>. Defaults to TRUE<\/code><\/span>.<\/dd>\n
useCachedValues<\/dt>\n
logical<\/a><\/code><\/span> specifying whether to read cached formula results from the workbook instead of re-evaluating them. This is particularly helpful in cases for reading data produced by Excel features not supported in XLConnect like references to external workbooks. Defaults to FALSE<\/code><\/span>, which means that formulas will be evaluated by XLConnect.<\/dd>\n
keep<\/dt>\n
Vector of column names or indices to be kept in the output data frame. It is possible to specify eitherkeep<\/code><\/span> or drop<\/a><\/code><\/span>, but not both at the same time. Defaults to NULL<\/code><\/span>. If a vector is passed as argument, it will be wrapped into a list. This list gets replicated to match the length of the other arguments. Example: ifsheet = c<\/a>(\"Sheet1\", \"Sheet2\", \"Sheet3\")<\/code><\/span> and keep = c<\/a>(1,2)<\/code><\/span>, keep<\/code><\/span> will be internally converted into list<\/a>(c<\/a>(1,2))<\/code><\/span> and then replicated to match the number of sheets, i.e. keep =list<\/a>(c<\/a>(1,2), c<\/a>(1,2), c<\/a>(1,2))<\/code><\/span>. The result is that the first two columns of each sheet are kept. Ifkeep = list<\/a>(1,2)<\/code><\/span> is specified, it will be replicated as list<\/a>(1,2,1)<\/code><\/span>, i.e. respectively the first, second and first column of the sheets “Sheet1”, “Sheet2”, “Sheet3” will be kept.<\/dd>\n
drop<\/dt>\n
Vector of column names or indices to be dropped in the output data frame. It is possible to specify eitherkeep<\/code><\/span> or drop<\/a><\/code><\/span>, but not both at the same time. Defaults to NULL<\/code><\/span>. If a vector is passed as argument, it will be wrapped into a list. This list gets replicated to match the length of the other arguments. Example: ifsheet = c<\/a>(\"Sheet1\", \"Sheet2\", \"Sheet3\")<\/code><\/span> and drop<\/a> = c<\/a>(1,2)<\/code><\/span>, drop<\/a><\/code><\/span> will be internally converted into list<\/a>(c<\/a>(1,2))<\/code><\/span> and then replicated to match the number of sheets, i.e. drop<\/a> =list<\/a>(c<\/a>(1,2), c<\/a>(1,2), c<\/a>(1,2))<\/code><\/span>. The result is that the first two columns of each sheet are dropped. If drop<\/a> = list<\/a>(1,2)<\/code><\/span> is specified, it will be replicated as list<\/a>(1,2,1)<\/code><\/span>, i.e. respectively the first, second and first column of the sheets “Sheet1”, “Sheet2”, “Sheet3” will be dropped.<\/dd>\n
simplify<\/dt>\n
logical<\/a><\/code><\/span> specifying if the result should be simplified, e.g. in case the data.frame<\/a><\/code><\/span> would only have one row or one column (and data types match). Simplifying here is identical to calling unlist<\/a><\/code><\/span> on the otherwise resulting data.frame<\/a><\/code><\/span> (using use.names = FALSE<\/code><\/span>). The default is FALSE<\/code><\/span>.<\/dd>\n
readStrategy<\/dt>\n
character<\/a><\/code><\/span> specifying the reading strategy to use. Currently supported strategies are:<\/p>\n