openxlsx2 (version 0.7)

wb_to_df: Create Dataframe from Workbook

Description

Simple function to create a dataframe from a workbook. Simple as in simply written down.

Usage

wb_to_df(
  xlsxFile,
  sheet,
  startRow = 1,
  startCol = NULL,
  rowNames = FALSE,
  colNames = TRUE,
  skipEmptyRows = FALSE,
  skipEmptyCols = FALSE,
  skipHiddenRows = FALSE,
  skipHiddenCols = FALSE,
  rows = NULL,
  cols = NULL,
  detectDates = TRUE,
  na.strings = "#N/A",
  na.numbers = NA,
  fillMergedCells = FALSE,
  dims,
  showFormula = FALSE,
  convert = TRUE,
  types,
  named_region
)

Arguments

xlsxFile

An xlsx file, Workbook object or URL to xlsx file.

sheet

Either sheet name or index. When missing the first sheet in the workbook is selected.

startRow

first row to begin looking for data.

startCol

first column to begin looking for data.

rowNames

If TRUE, the first col of data will be used as row names.

colNames

If TRUE, the first row of data will be used as column names.

skipEmptyRows

If TRUE, empty rows are skipped.

skipEmptyCols

If TRUE, empty columns are skipped.

skipHiddenRows

If TRUE, hidden rows are skipped.

skipHiddenCols

If TRUE, hidden columns are skipped.

rows

A numeric vector specifying which rows in the Excel file to read. If NULL, all rows are read.

cols

A numeric vector specifying which columns in the Excel file to read. If NULL, all columns are read.

detectDates

If TRUE, attempt to recognize dates and perform conversion.

na.strings

A character vector of strings which are to be interpreted as NA. Blank cells will be returned as NA.

na.numbers

A numeric vector of digits which are to be interpreted as NA. Blank cells will be returned as NA.

fillMergedCells

If TRUE, the value in a merged cell is given to all cells within the merge.

dims

Character string of type "A1:B2" as optional dimensions to be imported.

showFormula

If TRUE, the underlying Excel formulas are shown.

convert

If TRUE, a conversion to dates and numerics is attempted.

types

A named numeric indicating, the type of the data. 0: character, 1: numeric, 2: date, 3: posixt, 4:logical. Names must match the returned data

named_region

Character string with a named_region (defined name or table). If no sheet is selected, the first appearance will be selected.

Details

Depending if the R package hms is loaded, wb_to_df() returns hms variables or string variables in the hh:mm:ss format.

Examples

  ###########################################################################
  # numerics, dates, missings, bool and string
  xlsxFile <- system.file("extdata", "readTest.xlsx", package = "openxlsx2")
  wb1 <- wb_load(xlsxFile)

  # import workbook
  wb_to_df(wb1)

  # do not convert first row to colNames
  wb_to_df(wb1, colNames = FALSE)

  # do not try to identify dates in the data
  wb_to_df(wb1, detectDates = FALSE)

  # return the underlying Excel formula instead of their values
  wb_to_df(wb1, showFormula = TRUE)

  # read dimension without colNames
  wb_to_df(wb1, dims = "A2:C5", colNames = FALSE)

  # read selected cols
  wb_to_df(wb1, cols = c(1:2, 7))

  # read selected rows
  wb_to_df(wb1, rows = c(1, 4, 6))

  # convert characters to numerics and date (logical too?)
  wb_to_df(wb1, convert = FALSE)

  # erase empty rows from dataset
  wb_to_df(wb1, sheet = 3, skipEmptyRows = TRUE)

  # erase empty columns from dataset
  wb_to_df(wb1, skipEmptyCols = TRUE)

  # convert first row to rownames
  wb_to_df(wb1, sheet = 3, dims = "C6:G9", rowNames = TRUE)

  # define type of the data.frame
  wb_to_df(wb1, cols = c(1, 4), types = c("Var1" = 0, "Var3" = 1))

  # start in row 5
  wb_to_df(wb1, startRow = 5, colNames = FALSE)

  # na string
  wb_to_df(wb1, na.strings = "")

  # read_xlsx(wb1)

  ###########################################################################
  # inlinestr
  xlsxFile <- system.file("extdata", "inline_str.xlsx", package = "openxlsx2")
  wb2 <- wb_load(xlsxFile)

  # read dataset with inlinestr
  wb_to_df(wb2)
  # read_xlsx(wb2)

  ###########################################################################
  # named_region // namedRegion
  xlsxFile <- system.file("extdata", "namedRegions3.xlsx", package = "openxlsx2")
  wb3 <- wb_load(xlsxFile)

  # read dataset with named_region (returns global first)
  wb_to_df(wb3, named_region = "MyRange", colNames = FALSE)

  # read named_region from sheet
  wb_to_df(wb3, named_region = "MyRange", sheet = 4, colNames = FALSE)