---
title: "Reading an xlsb workbook"
author: "Michael Allen"
date: "`r Sys.Date()`"
output: rmarkdown::html_vignette
vignette: >
%\VignetteIndexEntry{Reading an xlsb workbook}
%\VignetteEngine{knitr::rmarkdown}
%\VignetteEncoding{UTF-8}
---
```{r, include = FALSE}
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>"
)
```
```{r setup}
library(readxlsb)
```
`read_xlsb` attempts to import a region from a binary format Excel workbook (xlsb)
The parameters are
```{r eval = FALSE}
read_xlsb(path, sheet, range, col_names, col_types, na, trim_ws, skip, ...)
```
## sheet
Either a name, or the index of the sheet to read. Index of the first sheet is 1. If the sheet name is embedded in the range argument, or implied if range is a named range, then this argument is ignored
## range
range can be specified as
* A named range. Named ranges are not case sensitive
* In **Sheet!A1** notation
* In **Sheet!R1C1** notation
* As a [cellranger::cell_limits](https://cran.r-project.org/package=cellranger) object
## col_names
* TRUE: The first row is used for column names. Empty cells result in a column name of the form 'column.i'
* FALSE: Column names will be 'column.i'
* Character vector: vector containing column names.
## col_types
Can be implied from the spreadsheet or specified in advanced.
When specifying types, options are
* "*logical*" (or "*boolean*"), "*numeric*" (or "*double*"), "*integer*", "*date*" and "*string*" (or "*character*")
* Use "*skip*" (or "*ignore*") to skip a column
When implying types from the underlying spreadsheet data, the resultant type is the regarded as the 'least fragile'.
Effectively the order is *logical* -- *datetime* -- *integer* -- *double* -- *string*
* If 99 rows are of type '*integer*' and 1 row is of type '*double*', then all cells are regarded as '*double*' in that column.
* If 99 rows are of type '*date*' and 1 row is of type '*string*', then all cells are promoted to '*string*'
Currently '*date*' is implied from cell formatting. It is either one of the built-in Excel datetime formats or a custom format where the format string contains only the characters Y, M, D, H, S, y, m, d, h, s and - (dash), : (colon), (space), . (dot). That should be good enough to identify any dates.
If '*date*' is specified as the column type, then any strings are converted to dates. The format, I'm afraid, isn't flexible at this stage - it's assumed to be of the type "%Y-%m-%dT%H:%M:%S".
I think there's a 1900 leap year bug in Excel. I haven't corrected for that.
If any of the cells in a '*date*' column contain time, then a POSIXct object is returned, otherwise a Date object is returned. The timezone for POSIXct is set as UTC. Seems like the sensible thing to do.
## na
A character string that is interpret as NA. This does not effect the implied data type for a column.
## trim_ws
Should leading and trailing whitespaces be trimmed from character strings?
## skip
The number of rows to skip before reading data.
## ...
Additional options. At present just debug = TRUE is supported. This returns a list with fields '*result*' set to the resulting data.frame and '*env*' set to an internal environment that may be useful for debugging.
```{r eval = TRUE}
res = read_xlsb(path = system.file("extdata", "TestBook.xlsb", package = "readxlsb"), range = "PORTFOLIO", debug = TRUE)
ls(res$env)
res$env$named_ranges
```